Microsoft Excel is already a capable data analysis tool, but with the ability to automate repetitive tasks with macros by writing simple code in Visual Basic for Applications (VBA) it’s that much more powerful. Used incorrectly, however, VBA can cause problems.
Even if you’re not a programmer, VBA offers simple functions that allow you to add impressive functionality to your spreadsheets.
It doesn’t matter if you’re a VBA guru who creates dashboards in Excel, or a newbie writing simple scripts that do basic cell calculations. Follow these simple programming techniques to learn how to write clean, bug-free code.
Getting Started With VBA
VBA can do all kinds of neat things for you. From writing macros that modify sheets to sending emails from an Excel spreadsheet using VBA scripts there are very few limits on your productivity.
If you haven’t programmed in VBA in Excel before you’ll need to enable the Developer tools in your Excel program. The good news is, enabling the Developer tools is actually pretty easy. Just go to File > Options and then Customize Ribbon. Just move the Developer tab from the left pane over to the right.
Make sure the checkbox has this tab enabled, and now the Developer tab will appear in your Excel menu.
The easiest way to get into the code editor window from here is just to click on the View Code button under Controls in the Developer menu.
You’re now ready to write VBA code! This tab is where you will access VBA as well as recording macros in Excel. Now that Excel is ready to work let’s go over some common mistakes to avoid, and the ways to prevent them.
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. Not properly naming variables is one of the most common programming mistakes that new developers make.
Throughout my decades of code writing, I have come across many schools of thought when it comes to variable naming conventions and learned a few rules the hard way. Here are some fast tips for creating variable names:
- Make them as short as possible.
- Make them as descriptive as possible.
- Preface them with the variable type (boolean, integer, etc…).
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 a function within 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 is a personal preference that helps later on while you’re programming because you’ll always know what type of data the variable holds by glancing at the name.
Also, be sure to name the sheets in your Excel workbook.
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”. 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 VBA programmers have when they start writing code is properly dealing with loops.
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 (VBA For loops or VBA Do While loops) instantly when a certain condition is true.
Here’s an example of this method being used to break a VBA loop.
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. Try and avoid explicitly breaking a loop.
More often than not, the code that comes after that “break” is important to process. 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 VBA 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. Now the code will loop through and stop once it reaches 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.
This could also lead to major performance problems. Looping through a column and extracting the values every single time is 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.
If you ever want to perform some other calculation on these same values the process would be clunky. You’d have to duplicate this code, process down through all of these cells, and perform your new calculation. All for one change!
Here’s a better example, using an array. First, let’s create the array.
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.
Now that you have the array it’s very simple to process the contents.
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.
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.
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.
You should check this list because unnecessary references can 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, etc.
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.
Don’t stop there though. Build a strong foundation of Excel skills with a VBA tutorial for beginners. Then keep learning about VBA and macros with resources to help you automate your spreadsheets.
Read the full article: 4 Mistakes to Avoid When Programming Excel Macros With VBA
Read Full Article
No comments:
Post a Comment