We’ve tried to explain macro variables as well as we can. They’re little storage baskets that act as temporary store areas while your macro is running. You’ll get into the hang of it soon and find yourself creating variables all over the place – they end up being really useful.
- Here is an example that combines a “Loop” with variables to add a list of data. It combines a number of the subjects we’ve been looking at (variables, selecting ranges, looping and message boxes). It would be pretty hard to get this example working without creating the two variables we’ve called
- Here is the code for the example:
'This macro loops through a list keeping and outputting a running total
'Enter values into column A to see it at work
Dim SubTotal As Double
Dim CellContents As Double
' Select cell A1
'Start loop, which later stops when an empty cell is reached.
Do Until IsEmpty(ActiveCell)
'Define CellContents as being equal to the value of the active cell
CellContents = ActiveCell.Value
'Take the value of the active cell (CellContents) and add it to the subtotal
SubTotal = SubTotal + CellContents
' Step down 1 row in column 1
'Output the result into cell B1
Range("B1") = SubTotal
'Message box - just for fun - it can be deleted
MsgBox ("The total is " & SubTotal)
' Select cell A1, taking you back to the top left of the spreadsheet
- Here is our first of three rules for variables. Rule 1: always declare your variables at the top. It makes it look like you know what you’re doing. The computer aficionados know that declaring variables saves Excel memory (Excel has to reserve some memory while the work gets done). Being clear about your variables can help stop you getting into trouble later e.g. if you misspell the variable later. You will find that your macros will usually run without you needing to declare variables at the top but you won’t look so clever to someone who picks up your work and actually knows what they are doing. They’ll start to worry about you. Here are some other things it’s worth saying about variables:
- Get into the habit of starting your variable declaration with the code
Dim. We tried to explain what that’s all about when we introduced you to macro variables. Now you just need to get into the habit of doing it.
- Declare/ collect up your variables at the top of your code – it’s just what people do (it’s clearer).
- The structure you need to get used to using when you declare your variables goes like this:
Dim SubstituteYourOwnVariableName As [VariableType]
- For variable type we most often end up using
Double(a number with a decimal point),
String(a text string) or
Variant(lets Excel do the hard work and figure out what sort of variable it is but uses a bit more memory – sometimes you find yourself needing to use this to get your macro to work). So a typical example of a variable declaration would be something like this:
Dim VariableName As Double.
- There are lots of other variable types out there but just the three we’ve mentioned above will get you most of the way there most of the time. If you want to see the full range of choices type the following code into the VBA editor
Dim YourOwnVariableName as Varand you should get a list popping up:
- You should be able to see the icon against the word variant. If you scroll through the list looking for the same icon you’ll be able to see the other choices you have. “VBA help” will explain further from there (type the word “Variables” into Excel VBA help and it’ll give you an article with the full list). Remember the variable types
Variantwill get you most of the way there most of the time.
- Our first rule told you to declare your variables. Here is our second rule for variables. You don’t have to worry about this one most of the time – only when you’ve got a number of different macros or parts of macros running say in the same spreadsheet. Here’s the second rule. If you want your variables available to other parts of your code, declare your variables up the top of your code above absolutely everything else (Above
Sub) and declare them as public variables e.g.
Public SubstituteYourVariableName As [variable type]. Note we have replaced the usual code
Public. The result of declaring our variables this way means Excel remembers them/ keeps them in storage for longer so that they’re available to all our code. We showed you an example of that running when we got a user form working. You’re only going to have to worry about this one sometimes.
- Here is our third rule for variables. It’s not a biggie. VBA doesn’t like spaces.
dontdeclareyourvariableslikethisbecausetheyrehardertoreadanditmakesyoulooklikeanamateur. You’ll notice underscore characters or strategically-placed uppercase letter are what everyone else uses.
V stands for “VBA”. VBA stands for Visual Basic for Applications. It’s the computer programming language that’s used for “Macros“.
V also stands for “VBA editor”. Like the “Developer tab“, it’s where a lot of the fun happens. The VBA editor allows you to view and edit your macro code. The quickest way to open the VBA editor is to hit “Alt” “F11” on your keyboard when you’ve got Excel open. We guarantee you’ll get used to doing that. We introduced you to the VBA editor when we first showed you how to view macro code.
V also stands for “VBA help”. Could well become your best friend. It’s a mine of technical information. With the “VBA editor” already open press “F1” to get VBA help up. Imagine you recorded a macro and it produced some code that kind of worked. Typing keywords from the code into VBA help is likely to help you understand that particular code a bit better. VBA help might even contain some examples of the code working in a slightly different way. That might enable you to use your initial code in a slightly different way. It’s a process. Eventually you get there. The “Macro recorder” and VBA help (plus your own dogged determination as the magic X-factor ingredient) is going to make for a pretty powerful combination.