Excel VBA 中的 ByRef 参数类型不匹配

ByRef argument type mismatch in Excel VBA

提问by George

I'm working with VBA. I wrote a user define function that takes a string, process it and return a cleaned string. I am not sure what is wrong with it. I am not able to call it and ask it to process my string and return it. I am thinking there are a mistake in the way I am defining or returning it.

我正在使用 VBA。我写了一个用户定义的函数，它接受一个string，处理它并返回一个清理过的string. 我不确定它有什么问题。我无法调用它并要求它处理我的字符串并返回它。我认为我定义或返回它的方式有误。

Public Function ProcessString(input_string As String) As String
    ' The temp string used throughout the function
    Dim temp_string As String

    For i = 1 To Len(input_string)
        temp_string = Mid(input_string, i, 1)
        If temp_string Like "[A-Z, a-z, 0-9, :, -]" Then
            return_string = return_string & temp_string
        End If
    Next i
    return_string = Mid(return_string, 1, (Len(return_string) - 1))
    ProcessString = return_string & ", "
End Function

And I use this function like this

我像这样使用这个功能

Worksheets(data_sheet).Range("C2").Value = ProcessString(last_name)

Last name is a string variable, usually looks like this Lastname*****, and I am trying to remove all the stars behind it. Have it return Lastnamewithout the stars.

姓氏是一个字符串变量，通常看起来像这样Lastname*****，我试图删除它后面的所有星星。让它在Lastname没有星星的情况下返回。

I received Compile error: ByRef arugment type mismatchwhen I tried to run this. I am using Windows XP with Office 2003.

Compile error: ByRef arugment type mismatch当我尝试运行它时我收到了。我正在使用 Windows XP 和 Office 2003。

EDIT: I added the basic struction of the code I have, I have about 20 lines of the similar code. Doing the same thing for each field I need.

编辑：我添加了我拥有的代码的基本结构，我有大约 20 行类似的代码。为我需要的每个领域做同样的事情。

Private Sub CommandButton2_Click()
' In my original production code I have a chain of these
' Like this Dim last_name, first_name, street, apt, city, state, zip As String
Dim last_name As String

' I get the last name from a fixed position of my file. Because I am 
' processing it from another source which I copied and pasted into excel
last_name = Mid(Range("A4").Value, 20, 13)

' Insert the data into the corresponding fields in the database worksheet
Worksheets(data_sheet).Range("C2").Value = ProcessString(last_name)

回答by Bathsheba

I suspect you haven't set up last_nameproperly in the caller.

我怀疑你没有last_name在调用者中正确设置。

With the statement Worksheets(data_sheet).Range("C2").Value = ProcessString(last_name)

随着声明 Worksheets(data_sheet).Range("C2").Value = ProcessString(last_name)

this will only work if last_nameis a string, i.e.

这仅适用last_name于字符串，即

Dim last_name as String

appears in the caller somewhere.

出现在调用者的某个地方。

The reason for this is that VBA passes in variables by referenceby default which means that the data types have to match exactly between caller and callee.

这样做的原因是 VBA默认通过引用传递变量，这意味着数据类型必须在调用者和被调用者之间完全匹配。

Two fixes:

两个修复：

1) Force ByVal-- Change your function to pass variable ByVal:
Public Function ProcessString(ByVal input_string As String) As String, or

1)强制 ByVal-- 更改您的函数以传递变量 ByVal:
Public Function ProcessString(ByVal input_string As String) As String, 或

2) Dim varname-- put Dim last_name As Stringin the caller before you use it.

2) Dim varname-Dim last_name As String在使用之前放入调用者。

(1) works because for ByVal, a copy of input_string is taken when passing to the function which will coerce it into the correct data type. It also leads to better program stability since the function cannot modify the variable in the caller.

(1) 之所以有效ByVal，是因为 for ，在传递给函数时会获取 input_string 的副本，该函数会将其强制转换为正确的数据类型。由于函数无法修改调用者中的变量，因此它还可以提高程序稳定性。

回答by ArpaG

I don't know why, but it is very important to declare the variables separately if you want to pass variables (as variables) into other procedure or function.

我不知道为什么，但是如果要将变量（作为变量）传递给其他过程或函数，单独声明变量非常重要。

For example there is a procedure which make some manipulation with data: based on ID returns Part Number and Quantity information. ID as constant value, other two arguments are variables.

例如，有一个程序可以对数据进行一些操作：基于 ID 返回部件号和数量信息。ID为常量值，其他两个参数为变量。

Public Sub GetPNQty(ByVal ID As String, PartNumber As String, Quantity As Long)

the next main code gives me a "ByRef argument mismatch":

下一个主要代码给了我一个“ByRef 参数不匹配”：

Sub KittingScan()  
Dim BoxPN As String
Dim BoxQty, BoxKitQty As Long

  Call GetPNQty(InputBox("Enter ID:"), BoxPN, BoxQty) 

End sub

and the next one is working as well:

下一个也在工作：

Sub KittingScan()
Dim BoxPN As String
Dim BoxQty As Long
Dim BoxKitQty As Long

  Call GetPNQty(InputBox("Enter ID:"), BoxPN, BoxQty)

End sub

回答by Gaffi

I changed a few things to work with Option Explicit, and the code ran fine against a cell containing "abc.123", which returned "abc.12,". There were no compile errors.

我更改了一些要使用的东西Option Explicit，代码在包含 的单元格上运行良好"abc.123"，返回"abc.12,". 没有编译错误。

Option Explicit ' This is new

Public Function ProcessString(input_string As String) As String
    ' The temp string used throughout the function
    Dim temp_string As String
    Dim i As Integer ' This is new
    Dim return_string As String ' This is new
    For i = 1 To Len(input_string)
        temp_string = Mid(input_string, i, 1)
        If temp_string Like "[A-Z, a-z, 0-9, :, -]" Then
            return_string = return_string & temp_string
        End If
    Next i
    return_string = Mid(return_string, 1, (Len(return_string) - 1))
    ProcessString = return_string & ", "
End Function

I'll suggest you post more of your relevant code (that calls this function). You've stated that last_name is a String, but it appears that may not be the case. Step through your code line by line and ensure that this is actually the case.

我建议您发布更多相关代码（调用此函数）。您已经声明 last_name 是一个字符串，但似乎并非如此。逐行执行代码并确保确实如此。

回答by PowerUser

While looping through your string one character at a time is a viable method, there's no need. VBA has built-in functions for this kind of thing:

虽然一次循环一个字符是一种可行的方法，但没有必要。VBA 具有用于此类事情的内置函数：

Public Function ProcessString(input_string As String) As String
    ProcessString=Replace(input_string,"*","")
End Function

回答by Gorodeckij Dimitrij

Something is wrong with that string try like this:

该字符串有问题尝试如下：

Worksheets(data_sheet).Range("C2").Value = ProcessString(CStr(last_name))