4 Mistakes to Avoid When Programming Excel Macros With VBA

Affiliate Disclosure: By buying the products we recommend, you help keep the site alive. Read more.

Advertisement

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.

Unlock the FREE "Essential Excel Formulas" cheat sheet now!

This will sign you up to our newsletter

Enter your Email

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

Excel Developer Tab Ribbon

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

Excel Developer Tab Shortcut

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 How to Record a Macro in Excel 2016 How to Record a Macro in Excel 2016 Did you know that you could automate repetitive tasks in Microsoft Excel by using macros? We'll show you how to record a macro in Excel 2016 to save lots of time. Read More . 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 10 Most Common Programming and Coding Mistakes 10 Most Common Programming and Coding Mistakes Coding mistakes can lead to so many problems. These tips will help you avoid programming mistakes and keep your code meaningful. Read More 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 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 VBA Variables

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.

Naming Excel Sheets in VBA Code

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 How Do-While Loops Work in Computer Programming How Do-While Loops Work in Computer Programming Loops are one of the first control types you'll learn in programming. You probably know about while and for loops, but what does a do-while loop accomplish? Read More ) instantly when a certain condition is true.

Exit Statement in Excel VBA Code

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

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.

Excel References in VBA

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

Excel VBA references

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.

Excel VBA object explorer

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.

Keep references low in VBA

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 The Excel VBA Programming Tutorial for Beginners The Excel VBA Programming Tutorial for Beginners VBA is a Microsoft Office power tool. You can use it to automate tasks with macros, set triggers, and lots more. We'll introduce you to Excel visual basic programming with a simple project. Read More . Then keep learning about VBA and macros with resources to help you automate your spreadsheets 5 Resources for Excel Macros to Automate Your Spreadsheets 5 Resources for Excel Macros to Automate Your Spreadsheets Searching for Excel macros? Here are five sites that have got what you're looking for. Read More .

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

Whatsapp Pinterest

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. Bob
    January 24, 2020 at 1:47 am

    I have at least 150 Excel VBA macros that I use. Unfortunately, MS in its idiotic decisions, eliminated that facility on the Mac versions several iterations ago. Ergo, I run older versions of Excel. Fortunately, the macros created earlier do still work in the later versions, but one cannot create new macros in them.

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

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

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

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

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

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

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

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