VBA compile errors that make no sense

Question

Can someone explain to me why the VBA compiler gets upset in these two situations? The fixes look like they should be completely unnecessary.

With parentheses, it expects an expression, which is not applicable here since the default message box doesn't return a value. It's unintuitive since not using/needing parenthesis around function arguments is contrary to coding norms.

The compile error goes away if I put MsgBox("Select Employee") on a newline. It's not an issue with the initial If statement, which runs fine on its own. And I thought VBA doesn't have meaningful indentation, so why does a newline affect the following Elseif?

Answer 1

> I get a compile error ('Else without If') if I put the first If-Then statement on one line, but not if I have no ElseIf statements. Why does the newline matter? Why does the compiler not recognize the preceding If statement if it's on one line?

There are two If statements in VBA, as far as the parser goes. It is spelled out in the help page: the simple one-line If ... Then, and the Block If.

Answer 2

You guys keep fretting over the code but it's just those few lines that matter. Do you see now?

Hi WCZH,

What to answer next?

It is a choice between to have compiled code or not to have compiled code.

I like the compiled code!

Imb.

Answer 3

I get a compile error ('Else without If') if I put the first If-Then statement on one line, but not if I have no ElseIf statements.

There are two forms of the If…Else…End If construct, single line and block.  Only the block form supports the use of ElseIf statements.  You cannot mix the two as you have attempted, hence the compilation error. To use ElseIf you must use the block form for the whole construct.

Answer 4

[several more ElseIf statements with the exact same structure]

Hi WCZH,

As an alternative to IF THEN ELSE, did you consider a SELECT CASE construct?

imb.

Answer 5

Actually, vb.net, or even .net c# also allows you to call a function as a sub, and ignore (not use) the return value. Only difference is c# and vb.net always requires the parenesis.

Where VBA trips up is when using a one-liner. This support goes back a whopping 40 years ago when BASIC first appeared for personal computers. The issue then becomes that basic compiler uses spaces for delimiters to determine when the next statement starts, and that confuses the compiler.

So, even this c# code is fine, and we are free to call/use a function as a sub, and ignore the return value:

        void testing()

        {

            string MyString = mytest("abc");

            mytest("def");

        }

        string mytest(string s)

        {

            return s + " hello";

        }

So, in above, placing mytest("def"); alone ignores the return value.

The main issue is that support of one line if statements. This can conflict somewhat with VB's support of calling a function without using the possible return values.

As noted the 2nd VBA example of using Else is fine, but ElseIF requires a block structure.

I suggest adopting a block structure always. It allows one with greater ease to modify and maintain code over time, despite such syntax being somewhat more "wordy".

So, even vb.net and c# supports calling functions as subs, and that of ignoring the return value (such as the case with MsgBox). The only difference here is that VBA notes this difference by not requiring paratheses when calling a function as a sub. But then again, with spaces, and attempting to NOT use a block structure, then troubles appear.

So, much of this comes down to the original BASIC that shipped with many computers in the early 1980's time frame. Hence, support for if statements without a ending block remains in place. In fact, the early basic languages did not even have a end block, and for multiple lines to execute for a given if, you separated each statement with a ":" and VBA to this day still supports that syntax. (and really avoid that ":" syntax if you can.

As noted, adopt a if/then block coding style, and that will not only eliminate such issues, but is also how most other modern languages today work.

So the ability to call a function as a sub? Yes, this remains very common in most languages of our time.

However, the requirement of not using paratheses is somewhat of a VB and VBA coding quirk.

in vb.net, in all such cases, you always have to use paratheses. I often wonder if all these languages, including c# should allow calling functions as subs, and ignoring the return value. However, that's what the industry has decided on. The trip up in VBA is the legacy support for on-line if statements, and that tends to conflict with calling functions as subs, and more so when doing so, paratheses are not required.

As noted, due to these conflicting goals (supporting old style BASIC syntax), then as noted, adopt a block coding style, and this will eliminate the ambiguities here.

To be fair, on occasion, even in vb.net, I still often use one-liners, since it can save some coding, and thus I will suggest that one-line if's can still be used, but I would not attempt to introduce a "else" or "ElseIF" into such code.

Hence, I still guility of often doing this in code:

                    If sImageRowIDList <> "" Then sImageRowIDList &= ","

or in VBA this:

If s <> "" Then s = s & "," 

So, when I suggest always adopting a block if/then style? Well, you are free to break such suggestions for above simple expression code, since I don't believe the extra effort of writing the above this way below with 3 full lines of code is worth the extra efforts:

If s <> "" Then 

    s = s & "," 

End If 

So, even in c#, or vb.net, I still often use a "if statement one liner". However, the instant ElseIf or even a Else is introduced, then I fall back to the always rule of adopting if/then code blocks. They are easier to read and maintain over time anyway, and as such ambiguities are eliminated.

So, this ability to call a function as a sub and ignore the return values is common in other coding languages. VBA's quirk is not requiring paratheses when doing as such, and this messes up the one-line if statements.

Really, just avoid trying to call functions as subs with a one liner "if" in VBA WHEN an Else, or ElseIf is required. While Else seems to work, why bother with this confusing. Adopt a block coding style.