vba Byref argument type mismatch when calling the function

Question

I have a method to rename the table column name.

Public Function Rename_Column(tablename As String, oldcolumn As String, newcolumn As String)
  Dim dbs As Database, tdf As TableDef
  Set dbs = CurrentDb

  For Each tdf In dbs.TableDefs
    If tdf.Name = tablename Then
      For Each fld In tdf.Fields
        If fld.Name = oldcolumn Then
          fld.Name = newcolumn
        End If
      Next
    End If
  Next
  dbs.Close
End Function

And i'm calling this function in other procedure

Public Sub querylistboxitems()
  Dim strTableName As String

  strTableName = "Table1"
  Call Rename_Column(strTableName, "old", "New")
End Sub

But it is giving an error "Byref argument type mismatch"

Answer 1

There are other tricky situations where this problem may occur. For example when declaring two (or more) variables on one line:

Dim firstSubMenu, secondSubMenu As CommandBarPopup

Now firstSubMenu is of type Variant, while secondSubMenu is of type CommandBarPopup. This may not be what you intended and can also be a cause for the above mentioned error when passing them by reference to a function that expects a parameter of type CommandBarPopup. In this case a solution is to declare them on two lines:

Dim firstSubMenu As CommandBarPopup
Dim secondSubMenu As CommandBarPopup

Or, if you really want to squeeze it on one line, it can be done like this:

Dim firstSubMenu As CommandBarPopup, secondSubMenu As CommandBarPopup

Note that you'll have to repeat the 'As' type declaration for each variable.