Simple Excel VBA Programming Concepts You Need to Know to Reclaim Lost Time

In the previous post, How to Learn Excel VBA, we looked at what a macro is and we looked at the VBE (Visual Basic Editor) – the place where all of your code writing takes place – and how to use it.

We even wrote a very simple piece of code – the ubiquitous “Hello world!”.

Now it’s time to move on and start to look at some simple Excel VBA programming concepts.

1 Conditional Statements (If … Then)

Let’s take a look at the first of our Excel VBA programming concepts – the If … Then statement.

This is the cornerstone of most programming languages – actions based upon decisions.  If the outcome is “a” then we do one thing else if it is “b” then we do another.

Here is a simple example of the If … Then construct in VBA:

The basic syntax of this test is : If condition Then action. 

The code should, I think, be pretty straightforward to understand but just to make sure:

• Sub WellWhatIf(). This is the name of the sub-routine. It could just as easily be called “Richie” but it is much more useful to give your code meaningful names.
• If Range (“A1”).Value = 123 Then. This is our If … Then decision line. We look at the value in cell A1 and compare it to the value given of “123”. If the outcome of the comparison is true the Then part tells the code to move to the next line. If it is False the test process is finished.
• MsgBox “Do something”. This is the code that is executed on a True outcome. The Messagebox is just a simple line for testing purposes – you can add whatever code you like.
• End If. The decision-making process is finished.
• End Sub. The sub-routine itself is finished.

So, If … Then gives a simple either/or scenario. What if we want to have two possible outcomes?

We add in an Else, that’s what. Like this:

Still not enough for you?

Then how about ElseIf (presumably created by Doctor Frankenstein by combining If and Else!):

In this scenario we can now compare cell A1 with multiple options, each providing its own outcome.

As you can see this is effectively a series of ‘If’ tests all rolled into one.  This can become a little cumbersome, not to mention repetitive. 

Where all of the tests concern the same test object an alternative is to make use of the Select Case statement.  This allows us to say ‘OK, all of our tests will be concerned with cell A1 lets just evaluate the various results’.

Although the number of lines of code is no less, the structure is far more easily read and understood.

Among those rules, I would suggest, is one that your code should be both easy to read and easy to understand. This isn’t just for the benefit of any other people that may be reading your code. If you have cause to re-visit your own code after 6 months you need to understand it!

2 Looping Structures (For … Next, Do … Loop)

Next up in our Excel VBA programming concepts is another cornerstone of programming – the loop structure.  Loops allow us to perform an action a set number of times.

Consider the routine below.  Its starts by adding the value 1 to cell A1 and progressing down until it has added 10 to cell A10.

Now it should be obvious here that there is a common theme to each of these lines of code.  In general if there appears to be some repetition in your code then it may be possible to improve its structure by incorporating it within a loop.

The above routine could be modified to a For … Next loop structure as follows.

The syntax here is simply : For counter equals start value to end value do something and then do the Next something until finished. 

We use a variable (iCounter) to act as our counter from 1 to 10.  We will discuss variables in more detail in the next section.

So, on the first pass through the loop the variable iCounter has a value of 1 (the first of our 1 to 10 iterations). Therefore, in the line where a value is added to a cell this effectively becomes:

And on the second pass, yes, you guessed it, the variable iCounter has a value of 2. The result:

The loop instruction continues until an iCounter value of 10 has been used. It then ceases and moves on to the next line, whatever that may be.

There is a variation of the For … Next loop and that is the For Each … Next loop. 

The only difference here is that rather than using a counter to determine the number of loops performed, we loop through each item in a particular group. 

For example, we could loop through each worksheet in the group of worksheets contained in a workbook – see below.

If you look at the Project Window section of the above image you will see the names of each worksheet in the workbook.

So, on the first pass through the loop the Messagebox will display “Sheet1”. And on its second and third passes it will display “Sheet2” and “Sheet3” respectively.

With me so far?

Ready for another loop?

OK, the final loop we’re going to look at is the Do … Loop. This one repeats the instructions within a loop until such time as a condition is met.

That is, rather than looping a pre-determined number of times it keeps repeating until it gets the result that it is looking for (much like a referee at Old Trafford when United are losing and he keeps play going rather than blowing the whistle for full time).

The syntax can be varied slightly to give two forms:

• Do condition … Loop
• Do … Loop condition

Furthermore, the condition test itself has two forms; either While or Until.

This gives us a total of four types of Do … Loop:
Do While…Loop, Do … Loop While, Do Until … Loop, Do … Until Loop.

Take a look at the two looping routines below:

As you can see, the main difference is whether the condition test takes part at the start of the loop or at the end of the loop.

Depending on what you are trying to achieve, one may be preferable to the other.

So, the use of a looping structure can greatly simplify a routine and reduce the amount of code required. 

And the same principle would apply with 100 or even with 1,000 iterations.  In fact, the higher the number of repetitive lines of code the more that you will benefit from the use of a loop structure.

By way of example …

… in one of the first routines that I ever wrote, I used the Macro Recorder to obtain the code to perform some formatting to a range of cells as well as adding data to the cells. 

Pleased with my new-found skills I then proceeded to replicate this code for a great many other cells until my routine was approaching the size of a small novel! 

Fortunately, there was help at hand. One of the regulars on an Excel forum showed me how, with the help of a loop as outlined above, to reduce the volume of code to little more than a page.

3 Variables

A key element of the Excel VBA programming concepts that you need to learn is the use of variables.

A variable is like your own little VBA Post-It. It is used to store a value in memory until you need to refer to it again at a later stage.

There are a number of different variable types. Each type is used for storing a particular sort of information. We’ll outline the different types in the next section.

Each variable also needs to have a name so that it may be referred to in your routines. We’ll discuss naming variables after the types section.

There are certain restrictions on the names that may be used:

• Must begin with an alphabetic character
• Must be unique within the same scope
• Can’t exceed 255 characters
• Can’t contain a full-stop (period for our readers in the USA)

3.1 Variables – Types

The data type of a variable determines what type of data that it can store.

Data type	Size (bytes)	Range
Byte	1	0 – 255
Boolean	2	True or False
Integer	2	-32,768 to 32,767
Long	4	-2,147,483,648 to 2,147,483,647
Single	4	-3.402823E38 to 3.402823E38
Double	8	-1.79769313486231E308 to 1.79769313486231E308
Currency	8	-922,337,203,685,477.5808 to 922,337,203,685,477.5807
Decimal	14	+/-79,228,162,514,264,337,593,543,950,335
Date	8	January 1, 100 to December 31, 9999
Object	4	Any object reference
String (variable-length)	10 + string length	0 to approximately 2 billion characters
String (fixed-length)	String length	1 to approximately 65,400 characters
Variant (numbers)	16	Any numeric value up to the range of a Double
Variant (characters)	22 + string length	Same as for variable-length string
User-defined (using Type)	Number required	Range of each element is the same as the range of its data type

A variable’s data type is given by declaring it. This declaration is completed by using the instruction Dim.

You can see examples of this in the images for the looping routines shown above.

Variables, as their names suggests, hold data that may vary. But what if we have something that we want to refer to but which we know we won’t change?

For that we use a Constant. And we declare this using the instruction Const.

For an example of both Dim and Const, take a look at the image below:

Finally, have you notice the Option Explicit line at the top of the module in the example images?

This forces the user (you!) to explicitly declare all variables (which is a good thing). Think of it as the VBE’s Jiminy Cricket to your Pinocchio!

You can activate it by going to the Tools menu in the VBE, then Options, and then select “Require Variable Declaration” on the Editor tab.

3.2 Variables – Naming Conventions

As you can imagine, although there are some in-built restrictions on the form that variables take there is still tremendous scope.

Remember my suggestion to develop a set of coding rules to follow? Well, here’s another – use variable names that are meaningful.

OK, you may be thinking, what is a meaningful variable name?

It’s one which:

• Indicates what the variable is being used for. If you are looking to use a variable for holding the value of sales in January then “JanuarySales” is a far better choice than just using “x”.
• Indicates the variable type being used. For example, you could use “iColumn” or “intColumn” to indicate that an Integer data type is being used to store a value for the column number.
• Indicates a constant.
• Indicates the scope of the variable.

Now, you can make up your own naming convention or you can use (or modify) an existing one.

One of the most popular is by a man called Reddick. (Not to be confused with a man called Riddick!!!)

3.3 Variables – Scope

The scope of a variable determines which procedures can use that variable.

But how big is that scope?

You probably think that the planet Earth is pretty big, right?

Well, it depends on the scope of your consideration.

The planet Earth is one of a number of planets in our Solar System which orbit around a star that we call the Sun. The solar system is one among more than 100 billion star systems in the Milky Way galaxy. And there are 100 billion galaxies in the visible universe.

Having trouble visualising that?

The Earth could fit into the Sun over 1 million times. The Sun is so big that it accounts for over 99% of the mass in the Solar System.

Now, if you imagine that the Sun has been shrunk to the size of a grain of sand, then the Solar System would just about fit in the palm of your hand. Using the same scale (don’t drop the Solar System while you contemplate this!) the Milky Way galaxy would be the size of North America! And that’s just one of 100 billion galaxies in the universe.

Sorry, I got a bit carried away there.

What was I going to say?

Oh yes, the scope of variables.

A variable’s scope can be at 3 levels:

• Procedure level (Earth)
• Private module level (Solar System)
• Public module level (Milky Way)

I’ll illustrate the above by way of a couple of example images:

OK, there is plenty going on here.

I suggest that you copy the code (note that there are 2 modules) into your own VBE and then run the first routine to see what happens.

I’ll give a run-through below:

• Firstly, notice that I have ‘commented out’ the Option Explicit declaration. If you don’t do this the VBE will produce an error in the second routine because the procedure-level variable, sstrProcedureVar, has only been declared in the first procedure.
• You’ll see that the two module-level variables, mstrPrivateVar and gstrPublicVar, need to be made outside of any procedures.
• OK, on to the routines. There are 3 routines; 2 in Module1 and 1 in Module2.
• In the first routine, we now declare our procedure-level variable, sstrProcedureVar.
• We then give a value to each of the 3 variables and use a Messagebox to display them all.
• The final 2 lines of code run (with the Call instruction) the other 2 routines. These each have the same Messagebox lines but nothing else.
• Place your cursor in the first routine and run it (F5 or press the green ‘Play’ arrow).
• The messages that you see will illustrate the scope of the variables used.
• In the first procedure, the Messageboxes show the values for the procedure-level variable, sstrProcedureVar, the private module-level variable, mstrPrivateVar, and the public module-level variable, gstrPublicVar : Earth, Solar System, and Milky Way.
• In the second procedure, the Messageboxes show the values for the private module-level variable, mstrPrivateVar, and the public module-level variable, gstrPublicVar : Solar System and Milky Way. Earth is not displayed because sstrProcedureVar is not in scope in this procedure.
• In the third procedure (which is in Module2), the Messageboxes only show the value of the public module-level variable, gstrPublicVar : Milky Way. Earth and Solar System are not displayed because sstrProcedureVar and mstrPrivateVar are not in scope in this module.

Still with me?

Good, because that was an important section and can be difficult to grasp for beginners.

To finish off this section I have another suggestion for your set of coding rules.

When it comes to the scope of variables, always aim to use the narrowest scope possible – ideally, all within procedures and passed as arguments. Do not be lazy and make all of your variables public module-level.

3.4 Variables – Passing Arguments

So, having suggested that you should maintain narrow scope and pass variables as arguments … what the heck does that mean and how do you do it?

Have you noticed the parentheses at the end of each Sub name in the various images?

Well, this section is for adding arguments to the procedure. And so, when we Call that procedure we also provide a value for that argument.

Take a look at the image below:

Things to note about the routines:

• I’m lazy and I kept the same Sub names!
• We now have just one, procedure-level, variable: sstrProcedureVar
• The variable is assigned a value (Earth) within the procedure that contains it
• As expected, the Messagebox displays the value within the first procedure
• In the Call instruction, we have now added an argument
• We use this argument to pass the value of our variable to the second procedure
• The Messagebox in the second procedure displays the value of its argument (Earth)

Before we move on to the next section, I also want to make you aware of an extension to the ability to pass and receive arguments between procedures.

You can make the argument Optional.

So, if we add Optional at the start of the argument section for the second procedure we are no longer forced to pass a value to the routine.

Take a look at how the routines look if we make this amendment:

What can we note from these changed routines?

• The Call instruction in the first procedure no longer passes an argument to the second procedure
• This does not cause an error because the argument section of that procedure now starts with the word Optional
• In order to allow for the possibility that a value isn’t passed as an argument, we need to carry out a test
• So, we use the If … Then … Else construct that we looked at earlier

4 Avoiding Repeated References (Object Variables, With … End With)

We briefly looked at this in the initial How to Learn Excel VBA post. However, it is quite an important concept so I think it is worth re-visiting it.

Using an Object Variable is both easier on the eye and more efficient for processing purposes.

When combined with a With … End With statement the effect is even greater.

Take a look at the two example routines below:

Now, I appreciate that the Are_You_With_Me routine is actually a little longer. However, it is much easier to understand what is going on and to maintain the code, and it will be processed more quickly.

It is so much easier to be able to refer to a range using a variable, like rngFiringRange, than having to give a fully qualified reference every time.

Note also that it is possible to nest With … End With statements. For example, within the With … End With statement for the range variable we also have statements for the Font property and the Borders property.

Basically, whenever you see duplication – look to see if you can make use of Object Variables and/or With … End With statements. You may wish to add this to your rules.

5 Sub-Routines Vs Functions

To finish off this introduction to Excel VBA programming concepts let’s consider ‘What’s the difference between a Sub-Routine and a Function?’

Well, the primary difference is that we use functions to perform tasks and then return a value.

Consider the Sub_Routine and Function in the image below:

What do you notice about the Function called I_Raise_You_10?

Well, for a start, like Grant Mitchell in The Queen Vic, you’ll see that it is expecting an Argument. Specifically, a Range argument.

So, when we call the function we pass a range to it (in this case, cell A1 on Sheet1). The Function then uses the value in that range to perform a simple task – it adds 10 to the value.

You’ll also notice that within the Function we use it’s name to load it with the value arrived at after the task is completed.

When the Function is complete control passes back to the Sub-Routine and it is able to display the calculated value.

Another useful attribute of Functions is that you can create your own User Defined Functions for use within Excel, rather than being restricted to using them as VBA functions.

Take a look at the image below:

You’ll see that we are back in the main Excel program. However, if you look at the function bar you will notice a familiar function being displayed for cell B1.

That’s right, that’s our I_Raise_You_10 Function.

And you can see that it is being used to perform a calculation based on the value contained in cell A1.

Now, don’t go reinventing the wheel – Excel’s native functions will be far more efficient than any UDF copy that you write. However, if there is a niche requirement that you have it’s nice to know that you can take the DIY approach!

6 Your VBA Reference Library

I know that we live in the age of the internet and that you can find pretty much everything online if you look for long enough.

However, sometimes, it’s just quicker to pick up a book and flip the pages until you reach the section that you are looking for.

Consequently, I have a small reference library of Excel and VBA books. If you are serious about learning VBA I would suggest that you do the same.

Now, to be honest, some of the books are quite dry and, frankly, not easy to read! These are the sort of books that you have solely for reference – you don’t actually read them to help you learn VBA.

I’m going to recommend two books that I think that you should have.

7 Frequently Asked Questions

To round things off, here are a few frequently asked questions concerning Excel VBA programming concepts.

What is a Conditional Statement in VBA?

A conditional statement is a section of code that makes a decision based upon a value given to it.

The simplest example is the If … Then statement. So, If <Condition> Then <Action>

Sub WellWhatIf()
If Range("A1").Value = 123 Then
MsgBox "Do Something"
End If
End Sub

What is a Looping Structure in VBA?

A loop in VBA code allows us to repeat an action a set number of times.

So, rather than having to repeat the same line of code 10 times or 100 times, or however many is desired, it can be wrapped in a looping structure instead.

The most common such loops are the For … Next and the Do … Loop. For example:

Sub ForNextLoop()
Dim iCounter as Integer
For iCounter = 1 to 10
Range("A" & iCounter).Value = iCounter
Next
End Sub

What is a Variable in VBA?

A variable is used to store a value in memory until you need to refer to it again at a later stage.

There are different variable types that can be used, depending upon the type of value that you wish to store. These include types such as Boolean (True or False), Integer, Date, Object and String (Text).

Variables all have scope. Their scope determines which procedures can access the stored values. These are procedure level, private module level, and public module level.

What is the Difference Between Sub-Routines and Functions in VBA?

Sub-Routines are the basic containers for the VBA code that you create. You may have a number of Sub-Routines within one Module and a number of Modules within one VBA Project.

Functions, on the other hand, are designed to perform an action based upon information provided to them and then return a value. Functions are usually called in Sub-Routines but some, User Defined Functions, may be utilised in worksheets within the main Excel program.

8 Acknowledgements

9 The End

I hope that you’ve enjoyed this post.

Feel free to navigate around the site to see if there is anything else that may be of interest to you.

If you liked this post please share it. Thank you 🙂

If you’d like a heads-up when the next post is issued sign-up to the Richie’s Room Newsletter.

And, if you’d like to add a comment that would be great too – you can do that below.