VBA variable declaration okay on two lines but not when comma separated. Compiler bug?

Question

I have the following variable declaration in a procedure which works fine.

Dim table_rng As Range
Dim attachments_rng As Range

I did have it as:

Dim table_rng, attachments_rng As Range

but this caused a "Compile error: ByRef argument type mismatch".

Since I have working code I'm not in a crisis but I wasted an hour finding this solution.

I'm using Excel 2010 with Visual Basic for Applications 7.0.1628

As far as I know the second declaration is syntactically correct. Am I missing something? I searched in vain on the web and stackoverflow.com for any wisdom on the topic.

Thanks in advance.

Answer 1

So, please take a look at VBA editor on this. First statement seems like to declare 3 integers, but not. Variable i and j are variant and only k is integer.

So if you look on your code, table_rng is declared as variant/empty and maybe this is what causing issue, but without rest of your code i cant tell you more.

But i recommend you to use single variable declaration on line, its far more easier to read. Or if you wana to declare more variable on single line, you need to specifi date type for each of them (like last line on my example. If you wana to know variable types, use debugging in VBA and View/Locals window).

Sub testVarDeclar()

    Dim i, j, k As Integer

    Dim a As Integer, b As Integer

    Dim table_rng, attachments_rng As Range

    Dim table_rng2 As Range, attachments_rng2 As Range
End Sub