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.

IMO the first code fragment is more readable and thus preferred. Is there a benefit to having multiple definitions in-line? — sMaN
– sMaN, Commented Dec 2, 2015 at 6:19
Can you please provide more code example? and formate it as code? thanks — Luboš Suk
– Luboš Suk, Commented Dec 2, 2015 at 6:25

Luboš Suk · Accepted Answer · 2015-12-02 06:47:13Z

11

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

answered Dec 2, 2015 at 6:47

Luboš Suk

1,57614 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tomasz P. Szynalski Over a year ago

learn.microsoft.com/en-us/office/vba/language/concepts/… says it explicitly

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related