Pinterest Stumbleupon Whatsapp
Ads by Google

Microsoft Excel is a very capable data analysis tool already, but with the ability to automate repetitive tasks with macros How To Send Emails From An Excel Spreadsheet Using VBA Scripts How To Send Emails From An Excel Spreadsheet Using VBA Scripts In the past, I’ve used email a whole lot in my batch jobs and other automated scripts, just like I’ve described in past articles. These are great for those times when you have a script... Read More 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 Build Your Own Weather Dashboard In This Google Spreadsheet Master Class Build Your Own Weather Dashboard In This Google Spreadsheet Master Class Would you like to turn your Google Drive account into an intelligent weather analysis system, and live out a Star Trek fantasy? Ok! Read More , 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.

excel-vba1

Make sure the checkbox is enabled, and now the Developer tab will appear in your Excel menu.

Ads by Google

excel-vba2

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 How You Can Make Your Own Simple App With VBA How You Can Make Your Own Simple App With VBA For those of you that would really love to be able to write your own application, but have never typed a single line of code before, I'm going to walk you through making your very... Read More . 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 How to See All Your PC Information Using a Simple Excel VBA Script How to See All Your PC Information Using a Simple Excel VBA Script Excel and 10 minutes of work will give you more detailed information about your computer than you ever thought possible. Sounds too good to be true? That's Windows, if you know how to use it. Read More .

excel-vba3

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.

excel-vba4

This way, when you refer to the sheet name in your Excel VBA code Using VBA to Automate Internet Explorer Sessions From an Excel Spreadsheet Using VBA to Automate Internet Explorer Sessions From an Excel Spreadsheet Its integration with Windows allows control of Internet Explorer in a number of surprising ways using Visual Basic for Applications (VBA) script from any application that supports it, such as Word, Outlook or Excel. Read More , 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 6 Life Habits That Programming Could Teach You Today 6 Life Habits That Programming Could Teach You Today Everything important that you need to know about living a successful life, you can get from a computer program. Don't believe me? Read on. Read More 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.

excel-vba5

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 The Basics Of Computer Programming 101 - Variables And DataTypes The Basics Of Computer Programming 101 - Variables And DataTypes Having introduced and talked a little about Object Oriented Programming before and where its namesake comes from, I thought it's time we go through the absolute basics of programming in a non-language specific way. This... Read More 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.

icecube-trays

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.

excel-vba6

What you’ll find in this window are all of the currently selected references for your current VBA project.

excel-vba7

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.

excel-vba8

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.

excel-vba9

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 Saving Time with Text Operations in Excel Saving Time with Text Operations in Excel Excel can do magic with numbers and it can handle characters equally well. This manual demonstrates how to analyze, convert, replace, and edit text within spreadsheets. These basics will allow you to perform complex transformations. Read More 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

  1. Mike
    May 24, 2016 at 12:06 am

    1. Make use of types for dealing with how function work i.e. do they error, pass data
    2. use enumerators to record columns used in VBA, chnage the enumerator value, not the code
    3. get used to error handling and informing users to the error instead of them whinning away because most of the time they are the cause.
    4. Enjoy VBA, easy and powerful

  2. Joe Lavery
    April 19, 2016 at 8:34 pm

    1) not writing an On Error Goto near the top
    2) not using Debug.Print to troubleshoot the contents of variables
    3) not using Watch to pause/halt code under certain conditions to troubleshoot

Leave a Reply

Your email address will not be published. Required fields are marked *