4 Mistakes You Can Avoid when Programming Excel Macros with VBA
Whatsapp Pinterest

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 Our code template will help you set up automated emails from within Excel using Collaboration Data Objects (CDO) and VBA scripts. 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.


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 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 .


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 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.


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

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.


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
      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
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
      For x = 1 To intNumRows
         arrMyArray(x-1) = Range("A" & str(x)).value)        
         ActiveCell.Offset(1, 0).Select
   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   
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 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

Explore more about: Microsoft Excel, Programming, Visual Basic Programming.

Enjoyed this article? Stay informed by joining our newsletter!

Enter your Email

Leave a Reply

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

  1. Ron
    August 20, 2019 at 5:11 am

    Why does this statement not work in Excel/VBA when it is suppose to be legal in standard VBA?

  2. Ron
    July 19, 2019 at 1:45 pm

    "Dim arrMyArray(12)" creates an array with 13 elements, not 12. You could declare it like "Dim arrMyArray(11)" or "Dim arrMyArray(1 to 12)" to get 12 elements.

    (Funny... Microsoft's documentation says the same thing you did...)

    Also, I agree with what everyone else said about using the cells in the worksheets.. If you're going to use cell data, never use ActiveCell or Select, unless you need to select a cell for the user. (i.e. User did not enter data into a required column before leaving the row)

    Oh... and your nesting is horrible.. I wish Microsoft would have defaults to 2 spaces instead of 4, but your nesting is all over the place.

  3. dave
    September 29, 2017 at 1:47 pm

    Tip No. 3 does not take the loop out at 6.
    While (x>=1 AND x<=20 AND x6)
    For x = 1 To 20
    y = x + intRoomTemp
    Next i

    • stupid
      July 19, 2018 at 7:38 am

      Yep, it does not even enter. let alone actually run.
      First of all 'Next i' should be 'Next x' to even begin to run.
      Second running this code would not enter the while loop because x=Empty. Yes, Empty. Not 0, because you don't declare the variable x (even if only for example purposes). Also to enter the while loop x should be given the value 1.
      After that why would you even do a for loop if you already have the conditions in the while loop... Just add a freaking x= x+ 1 line.
      Third if you're exiting at x=6 anyway, why would you not just loop from 1 to 5. For example purposes you should replace x6 with some other meaningful control variable. Did not read the rest of the article.

      • Stupid
        July 19, 2018 at 7:59 am

        Correction. Read more. BIG MISTAKE. Please stop giving people bad code. If you're gonna use arrays, please stop using .Select. If you're performing calculations on consequent cells you can just read the whole range to an array at once (without looping). Perform calculations in a for loop (on the vba-array) and write the whole array back to sheet without looping. Also please don't create a new Range variable every time you loop (and show doing it to other people) especially from strings...

        • Gadi Bizinyan
          April 19, 2019 at 11:27 pm

          I totally agree with everything "Stupid" said. Very BAD code snippets in this article. That example with the .Select is horrible! It is ought to be the most important tip in VBA - Avoid selecting cells or other objects in your code!!!

          I have been coding in VBA for 16 years and I do that for a living. I believe I'm entitled to totally disagree with Tip No. 2, making the loop break point a one-liner:
          If x = 6 Then Exit For
          In a code featuring hundreds of lines it's so easy to miss!
          There's nothing wrong about breaking it:
          If x = 6 Then
          Exit For
          End If
          Programming-wise it's working in the exact same manner. This is just an individual preference of one programmer. Feel free to implement separate Exit For's everywhere in the code, it's making it very easy to distinguish where there's an exit point from the loop. The separate "Exit For" doesn't mean your programming style is of a beginner!

          The need of naming sheets appropriately should have been a tip of its own and not mixed with Tip No. 1 which is specifically relating to naming conventions of variables.

          Lastly, one very wrong paragraph is about "Public". Public makes the variable available to every module and even across different workbooks. If you have already mentioned "Public", you should have also explained what are "Private" and "Dim" (when appearing at the top of a module), because this is not explained anywhere. Just my two cents.

  4. Eddie
    February 3, 2017 at 9:27 pm

    Sorry, I agree with most of what you are saying, but I write pretty advanced code and I used Exit For, Exit Do all the time. It has literally never caused me an issue and seems much cleaner than what you are suggesting.

    • Ryan
      May 20, 2017 at 4:44 am

      I agree, that's a lot of additional code and logic which is already processed out with the end/exit statement. I also think adding the data type is completely unnecessary and only further adds to the variable length while providing very little value in return. We know what the data type is when the variable is declared -- or at least should since the only time you don't is for a variant which carries a hefty performance hit if your not working with variables of unknown data types or change it frequently.

      I'd also add that every function should have a goto exitFct where any object variables are reset/emptied before ending the function. This is important to ensure your scripts don't lead to performance and memory issues.

  5. JBagel
    January 3, 2017 at 2:56 pm

    Don't forget to use Option Explicit.
    It forces you to name and type your variables ahead of time. This can greatly reduce ambiguous errors and prevent accidentally rewriting the wrong variable name.

  6. 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

    • john
      May 25, 2017 at 9:21 am

      hi Mike,

      I am John, I am very interest to learn VBA. but I don't where to begin and how?
      but i am good at using excel. If you can pls advice me how to could i cal learn VBA from the beginning.

  7. 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