# ByRef argument type mismatch

### Question

• I have written a user defined function as follows:

Function SpiralArc(dblThickness As Double, dblTheta As Double) As Double

SpiralArc = dblThickness * (dblTheta * Sqr(1 + dblTheta ^ 2) + HArcsin(dblTheta)) / 2
End Function

I have two variables declared as doubles,

dblTapethickness and dblThetaInner

I am passing them to SpiralArc as follows.

dblLoopLengthInner = SpiralArc(dblTapethickness, dblThetaInner)

I get a compile error of argument type mismatch, and the first variable is highlighted.

Any thoughts?

Thanks!

Bill Hardin
Thursday, February 10, 2011 12:11 AM

• I have written a user defined function as follows:

Function SpiralArc(dblThickness As Double, dblTheta As Double) As Double

SpiralArc = dblThickness * (dblTheta * Sqr(1 + dblTheta ^ 2) + HArcsin(dblTheta)) / 2
End Function

I have two variables declared as doubles,

dblTapethickness and dblThetaInner

I am passing them to SpiralArc as follows.

dblLoopLengthInner = SpiralArc(dblTapethickness, dblThetaInner)

I get a compile error of argument type mismatch, and the first variable is highlighted.

Any thoughts?

Thanks!

Bill Hardin

Check that the value for dblTapethickness is actually a double.

I would also use ByVal in the function's arguments like this:

Function SpiralArc(ByVal dblThickness As Double, ByVal dblTheta As Double) As Double

Jeanette Cunningham
• Marked as answer by Tuesday, February 15, 2011 12:24 AM
Thursday, February 10, 2011 3:11 AM
• Jeanette is mostly correct: it would be much better to declare the function arguments using ByVal. Why? Because it explicitly tells the user of the function you have no intention of changing the value on the caller. I wish this was the VBA default, but alas it is not.

However, the actual reason you're getting the error is because of a common mistake with declaring variables. You used this:

Dim dblTapethickness, dblThetaInner As Double

The error is in your assumption that dblTapeThickness is now declared as a Double. It is not. It's a Variant.

That's why I always declare one variable per line:
Dim dblTapethickness As Double
Dim dblThetaInner As Double

Or, if you must:
Dim dblTapethickness As Double, dblThetaInner As Double

In the ByRef case, VBA's type coercion is not strong enough, resulting in a compile error. In the ByVal case, type coercion does what most users would expect.

Not to weird you out, but this would have worked with your original code:
dblLoopLengthInner = SpiralArc((dblTapethickness), dblThetaInner)
Per the help file the extra parentheses cause the variable to be evaluated and 'promoted' to a double in a temporary variable of the precise type.

-Tom. Microsoft Access MVP
• Marked as answer by Tuesday, February 15, 2011 12:24 AM
Thursday, February 10, 2011 4:45 AM
• Hi Bill

_VAR_iable data _TYP_e can be checked with the function VARTYPE() ;-)

Henry
is there a way to check a variables data type while the code is running?
Bill Hardin
• Marked as answer by Wednesday, February 16, 2011 10:14 PM
Tuesday, February 15, 2011 2:18 AM
• Dim dblTapethickness, dblThetaInner As Double

The above gives dblThetaInner as a double and dblTapethickness as a Variant.

To make them both a double:

Dim dblTapethickness As Double
Dim dblThetaInner As Double

I like to put each dim statement on a separate line for easier reading and understanding while debugging.

To test if you get a double while the code is running, I use the immediate window and a debug statement like this:

Debug.Print "dblTapethickness", dblTapethickness , "dblThetaInner ", dblThetaInner

When you open the immediate window, you will see the values of each variable and you can check if it is a double.

Jeanette Cunningham
• Marked as answer by Wednesday, February 16, 2011 10:14 PM
Tuesday, February 15, 2011 2:27 AM

### All replies

• I have written a user defined function as follows:

Function SpiralArc(dblThickness As Double, dblTheta As Double) As Double

SpiralArc = dblThickness * (dblTheta * Sqr(1 + dblTheta ^ 2) + HArcsin(dblTheta)) / 2
End Function

I have two variables declared as doubles,

dblTapethickness and dblThetaInner

I am passing them to SpiralArc as follows.

dblLoopLengthInner = SpiralArc(dblTapethickness, dblThetaInner)

I get a compile error of argument type mismatch, and the first variable is highlighted.

Any thoughts?

Thanks!

Bill Hardin

Check that the value for dblTapethickness is actually a double.

I would also use ByVal in the function's arguments like this:

Function SpiralArc(ByVal dblThickness As Double, ByVal dblTheta As Double) As Double

Jeanette Cunningham
• Marked as answer by Tuesday, February 15, 2011 12:24 AM
Thursday, February 10, 2011 3:11 AM
• Jeanette is mostly correct: it would be much better to declare the function arguments using ByVal. Why? Because it explicitly tells the user of the function you have no intention of changing the value on the caller. I wish this was the VBA default, but alas it is not.

However, the actual reason you're getting the error is because of a common mistake with declaring variables. You used this:

Dim dblTapethickness, dblThetaInner As Double

The error is in your assumption that dblTapeThickness is now declared as a Double. It is not. It's a Variant.

That's why I always declare one variable per line:
Dim dblTapethickness As Double
Dim dblThetaInner As Double

Or, if you must:
Dim dblTapethickness As Double, dblThetaInner As Double

In the ByRef case, VBA's type coercion is not strong enough, resulting in a compile error. In the ByVal case, type coercion does what most users would expect.

Not to weird you out, but this would have worked with your original code:
dblLoopLengthInner = SpiralArc((dblTapethickness), dblThetaInner)
Per the help file the extra parentheses cause the variable to be evaluated and 'promoted' to a double in a temporary variable of the precise type.

-Tom. Microsoft Access MVP
• Marked as answer by Tuesday, February 15, 2011 12:24 AM
Thursday, February 10, 2011 4:45 AM
• Hi Bill
Can you please show, how you declared these two "double" variables. I think, something went wrong here. Please Copy/Paste your declaration.
Henry

I have two variables declared as doubles,

dblTapethickness and dblThetaInner

Thursday, February 10, 2011 4:49 AM
• I dimensioned the variables as follows,

Dim dblTapethickness, dblThetaInner As Double

as Tom pointed out.  I had no idea that this wouldn't declare them both as doubles...

Is at least the second one a double, or is neither of them?

Bill

Bill Hardin
Tuesday, February 15, 2011 12:28 AM
• is there a way to check a variables data type while the code is running?
Bill Hardin
Tuesday, February 15, 2011 12:30 AM
• Hi Bill

_VAR_iable data _TYP_e can be checked with the function VARTYPE() ;-)

Henry
is there a way to check a variables data type while the code is running?
Bill Hardin
• Marked as answer by Wednesday, February 16, 2011 10:14 PM
Tuesday, February 15, 2011 2:18 AM
• Dim dblTapethickness, dblThetaInner As Double

The above gives dblThetaInner as a double and dblTapethickness as a Variant.

To make them both a double:

Dim dblTapethickness As Double
Dim dblThetaInner As Double

I like to put each dim statement on a separate line for easier reading and understanding while debugging.

To test if you get a double while the code is running, I use the immediate window and a debug statement like this:

Debug.Print "dblTapethickness", dblTapethickness , "dblThetaInner ", dblThetaInner

When you open the immediate window, you will see the values of each variable and you can check if it is a double.

Jeanette Cunningham
• Marked as answer by Wednesday, February 16, 2011 10:14 PM
Tuesday, February 15, 2011 2:27 AM