调用函数时 vba Byref 参数类型不匹配
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22023034/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
vba Byref argument type mismatch when calling the function
提问by vuyy1182
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"
但它给出了一个错误“Byref 参数类型不匹配”
采纳答案by Wayne G. Dunn
It failed for me because you did not define "fld". The following is a lot more direct than looping thru all tables / fields:
它对我来说失败了，因为你没有定义“fld”。以下内容比遍历所有表/字段更直接：
Public Function Rename_Column(tablename As String, oldcolumn As String, newcolumn As String)
Dim dbs As Database
Dim tdf As TableDef
Dim fld As Field
Set dbs = CurrentDb
Set tdf = dbs.TableDefs(tablename)
Set fld = tdf.Fields(oldcolumn)
Set fld = Nothing
Set tdf = Nothing
dbs.Close
Set dbs = Nothing
End Function
回答by CodeKid
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:
现在 firstSubMenu 是 Variant 类型，而 secondSubMenu 是 CommandBarPopup 类型。这可能不是您想要的，也可能是通过引用需要 CommandBarPopup 类型参数的函数传递它们时导致上述错误的原因。在这种情况下，解决方案是在两行中声明它们：
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.
请注意，您必须为每个变量重复 'As' 类型声明。
