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  amp  temp string         End If     Next i     return string   Mid return string  1   Len return string  - 1       ProcessString   return string  amp       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

User · Accepted Answer

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

User · Answer

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

User · Answer

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  amp  temp string         End If     Next i     return string   Mid return string  1   Len return string  - 1       ProcessString   return string  amp       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

User · Answer

Something is wrong with that string try like this   Worksheets data sheet  Range  C2   Value   ProcessString CStr last name

User · Answer

It looks like ByRef needs to know the size of the parameter  A declaration of Dim last name as string doesn t specify the size of the string so it takes it as an error  Before using Worksheets data sheet  Range  quot C2 quot   Value   ProcessString last name  The last name has to be declared as Dim  last name as string  10   size of string is up to you but must be a fix length No need to change the function  Function doesn t take a fix length declaration

User · Answer

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

[vba] ByRef argument type mismatch in Excel VBA

Two fixes:

Examples related to vba

Examples related to excel