Hidden pitfalls of VBA

Question

This question is meant to augment the Hidden Features question at http://stackoverflow.com/questions/1070863/hidden-features-of-vba

What are the biggest, most common, or most annoying pitfalls of VBA?

Please list anything that might be described as

• programmer beware
• VBA behavior that requires painful and constant workarounds.
• Things that consistently make bugs easy to produce.
• Things that confuse programmers
• Unconventional syntax or behavior compared to other langauges, etc

Answer 1

Since I've been writing VB.net for a while, it is always a problem for me to prefix object assignment lines with Set, like

Dim d as Object
Set D = CreateObject("SomethingUseful")

instead of just

D = CreateObject("SomethingUseful")

which the IDE would not complain about, but you get a runtime error (object reference not set).

But this is basically VB.Net/VB6 difference, not especially VBA.

Answer 2

The whole language?

Eh, I'll be concrete: the fact that:

• x = f(y)
• f(y)
• f y
• Call f(y)

All have subtly different semantics, depending on whether f is a Function or a Sub and whether y is ByRef or ByVal.

To wit, if f is a function:

• x = f(y) does what you expect
• f(y) does call f and discard the return value, but if y is ByRef it will be passed ByVal as a special case
• f y is a syntax error
• Call f(y) does as f(y) above, but without the ByVal caveat

On the other hand, if f is a Sub:

• f(y) is a syntax error
• f y is correct
• Call f(y) is a syntax error

Huzzah!