VBA Excel issue when declaring multiple listobjects with DIM

Question

I was troubled by a VBA Compile error (ByRef argument type mismatch) which bugged me because in another vba project of mine I did (seemingly) the same but didn't get this error. So after some trial and error I come accross the REAL issue and could also fix it, but I don't understand why... Could someone perhaps take a stab at it?

This is code will result in a ByRef type mismatch error:

Dim qtbl, crtbl, urtbl As ListObject
Set qtbl = qws.ListObjects("Questions")
Set crtbl = crws.ListObjects("CoreResult")
Set urtbl = urws.ListObjects("UserResult")
...
...
' clean the core team and user result tables
Call ResetTable(crtbl)

While this code will run just fine:

Dim qtbl As ListObject
Set qtbl = qws.ListObjects("Questions")

Dim crtbl As ListObject
Set crtbl = crws.ListObjects("CoreResult")

Dim urtbl As ListObject
Set urtbl = urws.ListObjects("UserResult")
...
...
' clean the core team and user result tables
Call ResetTable(crtbl)

The subroutine is the same in both cases

Sub ResetTable(tbl As ListObject)

    'Delete all table rows except first row
      With tbl.DataBodyRange
        If .Rows.Count > 1 Then
          .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
        End If
      End With

    'Clear out data from first table row
      tbl.DataBodyRange.Rows(1).ClearContents

End Sub

The issue was to put all Listobjects into the same line of the DIM statement separated by commas. It turns out that the compiler (?) only knows the LAST varibale in such a statement, the first two still work for the most part, except in this call to the subroutine, where I got the error. Is this the expected behavior?

FunThomas · Accepted Answer · 2018-01-15 09:59:13Z

1

Your line

Dim qtbl, crtbl, urtbl As ListObject

doesn't define 3 ListObjects, it defines qtbl and crtbl as Variant You can assign a ListObject to a Variant, but when calling the Sub, the compiler still sees a Variant which results in the error.

Write

Dim qtbl As ListObject, crtbl  As ListObject, urtbl As ListObject

instead

answered Jan 15, 2018 at 9:59

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

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

Collectives™ on Stack Overflow

VBA Excel issue when declaring multiple listobjects with DIM

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related