Top VBA-Excel errors

Some months ago #VBA was finally considered the most dreaded language in the 2019 StackOverflow survey:

https://twitter.com/vitoshacademy/status/1115618203261112320

One of the reasons for this, is probably that #VBA is quite too easy to enter, thus plenty of non-IT professionals feel fascinated about it and start writing #VBA code. Then the code grows and grows, they say the magic words “I am actually not a developer, but a controller/accountant/analyst” and the unmaintainable “thing” is being sent to the IT department. With no documentation, no time and pressure from the management the IT guys are forced to see what is wrong with the code and to make it work. After all, it works somehow and the management does not understand why is it a problem to keep it running.

In this article, I am trying to summarize the top VBA-Excel errors, which I have noticed so far in my career and in StackOverflow. Probably the list is going to grow bigger and I would maintain further this article.

These are the erros which I have selected:

• Not defining parent worksheet – Range(Cells(),Cells())
• Declaring variables “The C++ way” – Dim a, b as Long
• Using Integer variables – Dim a as Integer
• Passing variable ByVal, when you think it is passed ByRef
• Using useless Select, Activate, ActiveCell
• Calling VBA “macro”, not realizing what exactly VBA is

Not defining parent worksheet

I guess this is the most common error, which every VBA developer has done at least once. What is the problem? Let’s take the following code (from this StackOverflow question):

What does it do? In the worksheet “Settings”, it finds the last row in column “A” and writes it in a variable lastRow. Then in a variable dateRange the offset value of this variable is written. With the third line, a cell 2 rows below the last row gets the dateRange+7

Why is this a problem?

The problem is that the code does not define explicitly the parent worksheet of the Cells() object in Row 2 and Row 3. In Row 1, it is well defined. In this case, VBA takes one of the two options for a Parent:

• If the code is written in the correct Worksheet object (in this case the worksheet “Settings”), then the worksheet “Settings” is considered the Parent. No matter, which ActiveSheet is selected. However, this is rarely the case, usually the code is in modules.
• If the currently ActiveSheet is the sheet “Settings” and the code is in a module, then VBA considers the ActiveSheet as a parent and the code works as intended.
• There is a second option, which happens quite often – the code is in a module and the ActiveSheet is actually not “Settings”, then for the first row Settings would be the parent worksheet and in the second and the third row the ActiveSheet would be the parent worksheet.

The third option is a disaster, because the code depends on the ActiveSheet and there are times it would work and time it would not work. Furthermore, if you are using Range(Cells(),Cells() object, then the Cells() should refer to the parent worksheet, and not only the range. Consider this in a module, with some other worksheet selected (not Settings):

This would throw run-time 1004: Method ‘Range’ of object ‘_Worksheet’ failed when using cells. So, what should you do? The following, adding the dots to the Cells() as well:

The above dot works as a silver bullet – it does not matter where the code is written nor which worksheet is the active one. I can bet a virtual beer, that there is not a single VBA developer, who has never encountered this problem. (Back to the list)

Declaring variables “The C++ way” – Dim a, b as Long

This is quite small, but actually can bring some problems. In C++ you may have something like this which works quite nicely:

Thus, all of the three are declared as ints. However, in VBA this would be not the case. Only the last variable would be from the specified type and the others would be variants. Take a look at this small code as an example:

The MsgBox, resulting from the code looks like this:

If you want to declare multiple variables on the same row, then this is the correct way:

(Back to the list)

Using Integer variables – Dim a as Integer

Integer in VBA are stored as 16-bit (2-byte) numbers. They represent the integer numbers between -32,768 and 32,767. That’s quite small. Anyway, people think that because of the small amount of bits in the memory this type should be faster. This seems to be the basics of computer science…

The problem is, that if you work in a in a 32bit system (or higher), the integer gets silently converted to a long type. But it still throws overflow exception, when someone assigns i = 32768. Thus, you are actually using slower type (because of the conversion time) and it is taking smaller range number. BUT on a 32 bit system, a 16 bit integer gets silently converted to a long without the benefit of the larger range of numbers to work with. With other words – you are paying much more for much less. To go around – work with Longs and avoid Integers at all costs. (Back to the list)

Passing variable ByVal, when you think it is passed ByRef

This one deserves a separate article actually. And it has one here. If the article has to be summarized – the parenthesis in the arguments force VBA to pass the parameter ByVal, without “caring” what we want. Thus, imagine the following example:

We have 2 identical functions, incrementing values either ByRef and ByVal with 100. This is the only thing they do. The problem is that, because of the parenthesis in Increment2(var2) the parameter is passed ByVal to a function, which accepts ByRef. And the result looks like this:

Using useless Select, Activate, ActiveCell

Using Select, Activate and ActiveCell in VBA can bring problems. And shows problems. In general, if you use the macro-recorder and record a macro, then you would see lots of Select, Activate, ActiveCell and etc. And it would work. Then why is it a problem?

1. It is slow. All these .Activate and .Select actions take time.
2. It is not necessary – they could be avoided.

Consider this code (generated from a macro recorder):

And compare it with a code, which actually does the same, but with declaring the variables, without using the active ones and depending on them:

My adivece here is to consider .Select() and .Activate() as needless parts of the code and to refactor it until they are completely out. The only reason to leave them in the code is for aesthetics, e.g. at the end of the code, having a specific worksheet selected for display. This is a nice StackOverflow topic with some examples how to avoid them. (Back to the list)

Calling VBA “macro”, not realizing what exactly VBA is

This is a rather broad one and a bit philosophical. Plenty of people call VBA “macro” or “macro language” or “funny macro language”. I have the idea that very few people actually know that VBA compiles and although it “lives” in the host application (Excel, Access, Visio, Catia or the other 100+) it is a fully independent language, using the libraries of the host. This is a rather good article in WikiPedia for it.

VBA is not a “macro”. Macro means something recorded and executed. In VBA, we have variables, compilation, almost fully functioning OOP and it is not a bad language after all! It is used for macro-recording in Excel and it does this task pretty well, but still it goes well far above that – it shares the same libraries and virtual machine with VB6 and it is pretty much VB6, being hosted on another application and not being able to provide its own exe files. Thus, respect the tool you are using. It is actually pretty decent.
(Back to the list)

Thanks for reading up to the end:)