VBA byref argument type mismatch when calling a function from a function being called

Question

hope someone can help. This is a simplification of a set of code I'm working with. I'm calling a function, and that function calls another function. The variables are passed from the calling sub to the first called function, from where it should be passed to the 2nd called function to return a value. However, I'm getting a "byref argument type mismatch" error on the below "Parameter" in the first function. Any suggestions? Thanks!

' Sub to call function 1
Sub TestFunctionSelect()
    Dim X As Double
    ' X should = the value of the function mShareMMTDaily as called from mFunctionSelect
    X = mFunctionSelect("mShareMMTDaily", "SOL", "2008/02/28", 12)
End Sub

' Function 1 to call function 2 and return a value to the sub
Function mFunctionSelect(FunctionName As String, CompCode As String, CurrentMonth As Date, Parameter As Double) As Double
    Select Case FunctionName
        ' Case Is = "mValue"
            ' mFunctionSelect = mValue(CompCode, CurrentMonth, Parameter)
        Case Is = "mShareMMTDaily"
            ' This function is called
            ' I get the "byref argument type mismatch" error on the below "Parameter"
            mFunctionSelect = mShareMMTDaily(CompCode, CurrentMonth, Parameter)
    End Select
End Function

Function mShareMMTDaily(Code As String, ShareDate As Date, LookBack As Integer) As Double
    ' Do Stuff
End Function

possible duplicate of ByRef arugment type mismatch in Excel VBA — GSerg
Convert the string "2008/02/28" which represents a date to Date type. More about type conversion functions here. — Daniel Dušek
Thanks for the tip @dee. Will standardise my code accordingly. — Nelus

Apokralipsa Apokralipsa · Accepted Answer · 2015-07-18T10:33:31

Your Parameter variable is declared as a Double and you are passing it to a function that expects an Integer. You should convert it, as in:

mFunctionSelect = mShareMMTDaily(CompCode, CurrentMonth, CInt(Parameter))

Btw., as @dee pointed out - you are also doing implicit conversion of a string to date. This makes your code less safe and dependent on the language settings of the host on which your code runs. You should convert the date explicitly or, better yet, work with Dates from the beginning instead of Strings.

VBA byref argument type mismatch when calling a function from a function being called

1 Answers