Excel Training and Consultancy Solutions

MS Excel VBA (macros) : Level 1 (Beginner)

The following table of topics outlines what this course will be covering :

Main topics specific areas A brief description of what we'll be covering
1. Getting started The Visual Basic Editor We dive straight in to the macros area of Excel - how to
  get there and what it looks like
2. Windows The Project Explorer How to view our different spreadsheet's macros and their
 The Properties Window respective list of properties
 The Code Window respective list of properties
3. Common terms Modules The common terms used within macros and
 Procedures what they mean.
 Keywords These are just the terms used by people
 Comment Text who write macros, but are so commonly
 Line continuation character referred to that we need to know what they mean
4. Important definitions Objects These four definitions are at the heart of most macros.
 Properties Here, we look at how all four are related and how they
 Methods apply to each other when we start looking
  at what types of macro we may need
5. The macro recorder How to record a macro Straight away, we'll show you how to record a macro.
 Simplify recorded macro However, also straight away, we'll show you how
  Excel automatically includes a lot of unnecessary
  code that really does not need to be there. We'll show
  you how to clean up any recorded code which will
  result in your macros running much faster and more
  efficiently - also providing you with a more
  professional approach to writing your own macros
  from the very beginning
6. Common Objects Application Every macro we write will include an object that is the
 Workbook centre of attention. Here, we look at the four most-commonly
 Worksheet used objects, with examples of some common procedures
 Range we apply to them in macros
7. Variables Data types We explain what variables are, some important rules
 Declarations concerning the makeup of our variables, how to choose
 Scope which data type they are (to prevent errors and use your
  computer's memory efficiently), and whether or not we need
  to declare the variable.
8. User interaction Message Boxes We can use message boxes to popup messages for the
  user at strategic points within our macro as it runs. We
  can also use these to determine a yes/no answer from the
  user which determines which direction the macro then
 Input Boxes proceeds in. Similarly, we can use input boxes to gather
  any information we need from the user (ie a required
  month and year) to, again, determine how our macro
 Statusbar proceeds. We can also use the statusbar (bottom left of
  your screen usually saying "Ready") to relay messages
  to the user as the macro runs (ie "10% completed", "20%
  completed" etc etc) so they are aware of the macro's
  progress as it runs
9. Loops For Each We can use "looping" logic very effectively within our
 Do While macros to perform the same chunk of instructions
 Do Until over and over again, without having to write it over
  and over again. Here, we look at the four most-common
  methods of looping - loop while something is true, loop
  until something is true, loop from x to y (ie from 1 to
  100 - {100 times}), loop through each item within a
  range of items.
10. Decision making IF Here, we look at how to steer our macros in certain
 AND direction based on the outcome of some IF
 OR statements and look at how to use the SELECT CASE
 NOT IF scenario for multiple IF statements. So, for example, if
 SELECT CASE our macro could perform 3 different things based on
  whether A1 contained the word "Budget", "Sales" or
  "Variance", we would achieve that using any of these
11. Worksheet Functions How to find them We can add power and flexibility to our macros by using
 =SUM Excel's standard worksheet functions within our code.
 =COUNTIF Here, we show you how to find what is available via VBA
 =VLOOKUP and look at some easy examples, =SUM, =COUNTIF and
  =VLOOKUP used in our code

[Place a provisional booking]

Frequently asked questions       Contact us       Course documentations
Course locations       About us       Consultancy details