none
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

Answers

  • 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 Bill Hardin 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 Bill Hardin 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 Bill Hardin 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 Bill Hardin 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 Bill Hardin 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 Bill Hardin 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 Bill Hardin 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 Bill Hardin Wednesday, February 16, 2011 10:14 PM
    Tuesday, February 15, 2011 2:27 AM