ByRef argument type mismatch in Excel VBA

Question

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)

Answer 1

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) Force ByVal -- Change your function to pass variable ByVal:
Public Function ProcessString(ByVal input_string As String) As String, or

2) Dim varname -- 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.

Answer 2

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.

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

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

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 

Answer 3

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

Answer 4

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:

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