Hidden pitfalls of VBA

This question is meant to augment the Hidden Features question at Hidden features of VBA

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

Plea开发者_JS百科se 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

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!

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.

For any API calls on a 64-bit system or with Office 2010 x64, you have to rewrite your code. You can still write it in and for earlier versions of VBA, but you need to use conditional compilation variables of VBA7 and/or Win64. For example:

#If VBA7 Then
   Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#Else
   Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#End If

Here's an article on it http://msdn.microsoft.com/en-us/library/ee691831(office.14).aspx