1. Original Entry + Comments2. Write a Comment3. Preview Comment

June 23, 2017  |  Subs in VBA: calling and exposing  |  464 hit(s)

I rassled a bit recently with a couple of dumb issues when creating some Word macros, so I thought I'd better write these up for my own future reference. To be clear, "dumb" here means that I should already have known this stuff, and I wasted time learning it.

1. Calling subroutines

I was trying to call a sub like this:
Sub SomeMacro
SomeOtherSub(p1, p2)
End Sub
Word got so mad about that SomeOtherSub call:

Turns out that when you call a subroutine in VBA and pass parameters, you do that without parentheses:
SomeOtherSub p1, p2
The parameters can be positional, as here, or named. For the latter, use the := syntax:
SomeOtherSub p1:="a value", p2:="another value" 

2. Exposing subroutines (implicit access modifiers)

Here was another kind of bonehead mistake I made. I wrote a subroutine sort of like this:
Sub MyMacro(param1 As String, param2 As String)
' Code here
End Sub
Then I tried to actually run this macro (Developer > Macros). The macro stubbornly refused to appear in the Macros dialog box. If I was in the macro editor and pressed F5 to try to launch it in the debugger, Word just displayed the Macros dialog box for me to pick which macro to run, but again, did not display the actual macro that I actually wanted to run.

Anyway, long story short (too late, haha), the problem was that the Sub definition included parameters:
Sub MyMacro(param1 As String, param2 As String)
Apparently if a subroutine has parameters like that, VBA considers it to have protected access—it can be called from another macro, but it can't be launched as a main. This makes sense, but it wasn't immediately obvious. What I really wanted was this:
Sub MyMacro()
I had included the parameters by accident (copy/paste error), so it was basically a dumb mistake. I just removed them and then things worked. Well, they worked until VBA ran into the next dumb mistake, whatever that was. (In my code there's always another one.)