5

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?

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
Community
  • 1
  • 1
Kimball Robinson
  • 3,287
  • 9
  • 47
  • 59

3 Answers3

4

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!

Derrick Turk
  • 4,246
  • 1
  • 27
  • 27
  • Also, this article explains some additional gotchas--such as when y is an object, you can't use parenthesis. http://www.cpearson.com/Excel/byrefbyval.aspx – Kimball Robinson Mar 23 '10 at 16:38
  • 1
    @k.robinson: Excellent! I'm bookmarking that in case I ever get dragged into another macro maintenance exercise. – Derrick Turk Mar 23 '10 at 17:27
  • @Derrick Turk: For subs, `Call f(y)` is most definitely not a syntax error! Also, `f(y)` will compile since the whole `(y)` will be taken as the argument, i.e. including the brackets. – Jean-François Corbett Jul 04 '11 at 07:53
  • the difference between `f(y)` and `f y` is that one skips the keyword `Call` and passes the `y` `ByVal` the other allows to pass `ByRef`. I don't think its annoying it's just something to keep in mind :) –  Sep 12 '13 at 15:25
3

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.

naivists
  • 32,681
  • 5
  • 61
  • 85
3

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

Todd Main
  • 28,951
  • 11
  • 82
  • 146