ByRef argument type mismatch in Excel VBA
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.
And I use this function like this
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.
Name already in use
VBA-Docs / Language / Reference / User-Interface-Help / byref-argument-type-mismatch.md
- Go to file T
- Go to line L
- Copy path
- Copy permalink
- Open with Desktop
- View raw
- Copy raw contents Copy raw contents
Copy raw contents
Copy raw contents
ByRef argument type mismatch
An argument passed ByRef (by reference), the default, must have the precise data type expected in the procedure. This error has the following cause and solution:
You passed an argument of one type that could not be coerced to the type expected.
For example, this error occurs if you try to pass an Integer variable when a Long is expected. If you want coercion to occur, even if it causes information to be lost, you can pass the argument in its own set of parentheses.
For example, to pass the Variant argument MyVar to a procedure that expects an Integer argument, you can write the call as follows:
Placing the argument in its own set of parentheses forces evaluation of it as an expression. During this evaluation, the fractional portion of the number is rounded (not truncated) to make it conform to the expected argument type. The result of the evaluation is placed in a temporary location, and a reference to the temporary location is received by the procedure. Thus, the original MyVar retains its value.
[!NOTE] If you don’t specify a type for a variable, the variable receives the default type, Variant. This isn’t always obvious. For example, the following code declares two variables, the first, MyVar , is a Variant; the second, AnotherVar , is an Integer.
For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).
Error Examples
An argument passed ByRef must have the precise data type.
If the data types are different you will see a "ByRef argument type mismatch" error message.
In this example we are passing an Integer variable when a Long is expected.
Run-Time Error — Invalid procedure
This works for positive values of X but fails for negative values.
You should use logical assignments as opposed to IF statements will also help performance (ie y = (x = 5)).
Compile Error — Invalid outside procedure
Compile Error — Invalid qualifier
Compile Error — Only user defined types defined
Compile Error — Procedure too long
Run Time Error — 429 ActiveX component can’t create object
If you are running 64-bit Office and there is a reference to a 32-bit dll.
Automation Error — The object invoked has disconnected from its clients
Automation Error — The callee (server [not server application]) is not available and disappeared; all connections are invalid. The call may have been executed
ByRef argument type mismatch in Excel VBA
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.
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.
