在Excel VBA中的ByRef参数类型不匹配 [英] ByRef argument type mismatch in Excel VBA

问题描述

我正在与VBA合作。我写了一个用户定义函数，它需要一个 string ，处理它并返回一个清除的字符串。我不知道有什么问题。我无法调用它，并要求它处理我的字符串并返回。我想我有一个错误的方式我定义或返回它。

 公共功能ProcessString（input_string as String）As String 
'整个函数中使用的临时字符串
 Dim temp_string As String 
 
 For i = 1 To Len（input_string）
 temp_string = Mid（input_string，i， 1）
如果temp_string像[AZ，az，0-9，...， - ]然后
 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 
  

我使用这样的功能这样的

 工作表（data_sheet）.Range（C2）。Value = ProcessString（last_name）
  pre> 
 
 
姓氏是一个字符串变量，通常看起来像这样姓氏***** ，而我试图删除它背后的所有星星。让它返回姓氏没有星星。 
 
 
 
我收到编译错误：ByRef arugment type mismatch 当我尝试运行这个。我正在使用Windows XP与Office 2003. 
 
 
 
编辑：我添加了代码的基本结构，我有大约20行的类似代码。对每个需要的字段做同样的事情。
  Private Sub CommandButton2_Click（）
'在我的原始制作代码中有一个链这些
'像这个Dim last_name，first_name，street，apt，city，state，zip As String 
 Dim last_name As String 
 
'我得到姓氏从我的文件的固定位置。因为我是
'从另一个源处理它，我复制并粘贴到excel 
 last_name = Mid（Range（A4）。Value，20，13）
 
'将数据插入数据库工作表中的相应字段
 Worksheets（data_sheet）.Range（C2）。Value = ProcessString（last_name）
  
 
 
解决方案
我怀疑你没有在调用者中正确设置 last_name  / p> 
 
 
使用语句 Worksheets（data_sheet）.Range（C2）。Value = ProcessString（last_name）如果 last_name 是一个字符串，即
 
 
 
 pre>  Dim last_name as String 
  

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

两个修复：

1）将函数更改为公共函数ProcessString（ByVal input_string As String）As String

2）put



（1）因为 ByVal ，当传递给将强制为正确的数据类型的函数时，会输入一个input_string的副本。它也导致更好的程序稳定性，因为该函数不能修改调用者中的变量。

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.

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 Lastname without the stars.

I received Compile error: ByRef arugment type mismatch when I tried to run this. I am using Windows XP with 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.

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)

解决方案

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

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

this will only work if last_name is a string, i.e.

Dim last_name as String

appears in the caller somewhere.

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

Two fixes:

1) Change your function to Public Function ProcessString(ByVal input_string As String) As String

2) put Dim last_name As String in the caller before you use it.

(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.

这篇关于在Excel VBA中的ByRef参数类型不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！