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

Nelus picture Nelus · Jul 18, 2015 · Viewed 11.9k times · Source

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

Answer

Apokralipsa picture Apokralipsa · Jul 18, 2015

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.