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.