Microsoft Excel is a very capable data analysis tool already, but with the ability to automate repetitive tasks with macros by writing simple code in (VBA), it’s that much more powerful. However, used incorrectly, VBA can cause a lot of problems.
Even if you’re not a programmer, VBA offers simple functions that allow you to add some really impressive functionality to your spreadsheets, so don’t leave just yet!
Whether you’re a VBA guru, who creates dashboards in Excel, or a newbie, who only knows how to write simple scripts that do basic cell calculations, you can follow easy programming techniques, that will help you improve the odds of writing clean and bug-free code.
Getting Started with VBA
If you haven’t programmed in VBA in Excel before, enabling the Developer tools to do so is actually pretty easy. Just go to File > Options and then Customize Ribbon. Just move the Developer command group from the left pane over to the right.
Make sure the checkbox is enabled, and now the Developer tab will appear in your Excel menu.
The easiest way to get into the code editor window at this point is just to click on the View Code button under Controls in the Developer menu.
1. Horrible Variable Names
Now that you’re in the code window, it’s time to start writing VBA code. The first important step in most programs, whether it’s in VBA or any other language, is defining your variables.
Throughout my couple of decades of code writing, I came across many schools of thought when it comes to variable naming conventions and learned a few things the hard way. Here are the fast tips for creating variable names:
- Make them as short as possible.
- Make them as descriptive as possible.
- Preface them with variable type (boolean, integer, etc…).
- Remember to use the right scope (see below).
Here’s a sample screenshot from a program that I use often to make WMIC Windows calls from Excel to gather PC information.
When you want to use the variables inside of any function inside of the module or object (I will explain this below), then you need to declare it as a “public” variable by prefacing the declaration with Public. Otherwise, variables get declared by prefacing them with the word Dim.
As you can see, if the variable is an integer, it’s prefaced with int. If it’s a string, then str. This helps later on while you’re programming because you’ll always know what type of data the variable holds, just by glancing at the name. You’ll also notice that if there’s something like a string that holds a computer name, then the variable is called strComputerName.
Avoid making very convoluted or confusing variable names that only you understand. Make it easier for another programmer to come behind you and understand what it all means!
Another mistake people make is leaving sheet names as the default “Sheet1”, “Sheet2”, etc… This adds further confusion to a program. Instead, name the sheets so that they make sense.
This way, when you refer to the sheet name in your Excel VBA code, you’re referring to a name that makes sense. In the example above, I have a sheet where I pull in Network information, so I call the sheet “Network”. Now in the code, any time I want to reference the Network sheet, I can do it quickly without looking up what sheet number it is.
2. Breaking Instead of Looping
One of the most common problems newer programmer have when they start writing code is properly dealing with loops. And since so many people who use Excel VBA are very much code newbies, poor looping is epidemic.
Looping is very common in Excel because often you are processing data values down an entire row or a column, so you need to loop to process all of them. New programmers often want to just break out of a loop (either a For loop or a While look) instantly when a certain condition is true.
You can ignore the complexity of the code above, just note that inside the inner IF statement, there’s an option to exit the For loop if the condition is true. Here’s a simpler example:
For x = 1 To 20 If x = 6 Then Exit For y = x + intRoomTemp Next i
New programmers take this approach because it’s easy. When a condition occurs that you’re waiting for in order to quit a loop, the temptation to just immediate jump out of it is strong, but don’t do it.
More often than not, the code that comes after that “break” is important to process, even the last time through the loop before exiting. A much cleaner and more professional way to handle conditions where you want to leave a loop halfway through, is just to include that exit condition in something like a While statement.
While (x>=1 AND x<=20 AND x<>6) For x = 1 To 20 y = x + intRoomTemp Next i Wend
This allows for a logical flow of your code, with the last run through when x is 5, and then gracefully exiting once the For loop counts up to 6. No need to include awkward EXIT or BREAK commands mid-loop.
3. Not Using Arrays
Another interesting mistake that new VBA programmers make is trying to process everything inside of numerous nested loops that filter down through rows and columns during the calculation process.
While this can work, it could also lead to major performance problems, if you constantly have to perform the same calculations on the same numbers in the same column. Looping through that column and extracting the values every single time is not only tedious to program, it’s a killer on your processor. A more efficient way to handle long lists of numbers is to utilize an array.
If you’ve never used an array before, have no fear. Imagine an array as an ice cube tray with a certain number of “cubes” you can put information into. The cubes are numbered 1 to 12, and that’s how you “put” data into them.
You can easily define an array just by typing Dim arrMyArray(12) as Integer.
This creates a “tray” with 12 slots available for you to fill up.
Here’s what a row looping code without an array might look like:
Sub Test1() Dim x As Integer intNumRows = Range("A2", Range("A2").End(xldown)).Rows.Count Range("A2").Select For x = 1 To intNumRows If Range("A" & str(x)).value < 100 then intTemp = (Range("A" & str(x)).value) * 32 - 100 End If ActiveCell.Offset(1, 0).Select Next End Sub
In this example, the code is processing down through every single cell in the range and performing the temperature calculation.
Later on in the program, if you ever want to perform some other calculation on these same values, you’d have to duplicate this code, process down through all of these cells, and perform your new calculation.
Now, if you instead, use an array, then you can store the 12 values in the row into your convenient storage array. Then later, whenever you want to run calculations against those numbers, they’re already in memory and ready to go.
Sub Test1() Dim x As Integer intNumRows = Range("A2", Range("A2").End(xldown)).Rows.Count Range("A2").Select For x = 1 To intNumRows arrMyArray(x-1) = Range("A" & str(x)).value) ActiveCell.Offset(1, 0).Select Next End Sub
The “x-1” for pointing to the array element is only necessary because the For loop starts at 1. Array elements need to start at 0.
But once you have your array all loaded up with the values from the row, later in the program you can just whip together any calculations you want by using the array.
Sub TempCalc() For x = 0 To UBound(arrMyArray) arrMyTemps(y) = arrMyArray(x) * 32 - 100 Next End Sub
This example goes through the entire row array (UBound gives you the number of data values in the array), does the temperature calculation, and then puts it into another array called arrMyTemps.
You can see how much simpler the second calculation code is. And from this point on, any time you want to perform more calculations on the same set of numbers, your array is already pre-loaded and ready to go.
4. Using Too Many References
Whether you’re programming in full-fledged Visual Basic, or VBA, you’ll need to include “references” to access certain features, like accessing an Access database, or writing output to a text file.
References are sort of like “libraries” filled with functionality that you can tap into, if you enable that file. You can find References in Developer view by clicking on Tools in the menu and then clicking on References.
What you’ll find in this window are all of the currently selected references for your current VBA project.
In my experience, the selections here can change from one Excel install to another, and from one PC to another. A lot depends on what other people have done with Excel on this PC, whether certain add-ons or features were added or enabled, or whether some other programmers might have used certain references in past projects.
The reason it’s good to check this list is because unnecessary references waste system resources. If you don’t use any XML file manipulation, then why keep Microsoft XML selected? if you don’t communicate with a database, then remove Microsoft DAO. If you don’t write output to an text file, then remove Microsoft Scripting Runtime.
If you’re not sure what these selected references do, press F2 and you’ll see the Object Explorer. At the top of this window, you can choose the reference library to browse.
Once selected, you’ll see all of the objects and available functions, which you can click on to learn more about.
For example, when I click on the DAO library, it quickly becomes clear that this is all about connecting to and communicating with databases.
Reducing the number of references you use in your programming project is just good sense, and will help make your overall application run more efficiently.
Programming in Excel VBA
The whole idea of actually writing code in Excel scares a lot of people, but this fear really isn’t necessary. Visual Basic for Applications is a very simple language to learn, and if you follow the basic common practices mentioned above, you’ll ensure that your code is clean, efficient, and easy to understand.
Do you code in VBA? What sort of lessons have you learned through the years that you can share with other readers learning VBA for the first time? Share your tips in the comments section below!
Image credits: Computer by www.BillionPhotos.com via Shutterstock, Colourful Ice Cube Trays by hahauk via Shutterstock.com