Strange issue with range variable substitution

[RIchavalues]

Hi All,


I tried my best to fix the issue myself and couldn't figure out for hours and hope I get some help here.


Am trying to pass a variable to a range through another subroutine. If argument is of same datatype, it works. But if multiple datatype, then getting "Type mismatch error 13". Am posting both methods below:


Method1: Below method works fine

Sub GoMainFunction(var1 As String)


ThisWorkbook.Worksheets("TestData").Select


    With ThisWorkbook.Worksheets("TestData")
        Range(var1).Select
    End With


End Sub




Sub SelectRange()


    Call GoMainFunction("A1")
    
    Call GoMainFunction("A5")


End Sub

Method2:

Function MainFunction(Fruits As Long, var1 As String) As Variant


ThisWorkbook.Worksheets("TestData").Select


Select Case Fruits
    Case "APPLE"
        With ThisWorkbook.Worksheets("TestData")
            Range(var1).Select
        End With
    
    Case "ORANGE"
        With ThisWorkbook.Worksheets("TestData")
            Range(var1).Select
        End With
    End Select
    
End Function




Sub SelectRange()


    Call MainFunction("APPLE", "A1")
    
    Call MainFunction("ORANGE", "A5")


End Sub

I have fruits as an example but in real time I have larger value and hence choosing Long datatype.


I really appreciate any help provided.


Thanks,
Richa

 

[Trebor76]

Hi Richa,

In your MainFunction UDF you have defined Fruits as long so the function is expecting a number ranging in value from -2,147,483,648 to 2,147,483,647 but you are passing a string to it i.e. "APPLE" so Excel returns the "Type mismatch error 13" error message.

Have a look at this great web page on variables by the late Dave Hawley.

Regards,

Robert

 

[RIchavalues]

Thank you so much for the help Robert. I really appreciate it and the code working fine now by changing Long to String. All these days I was thinking Long data type accepts strings, my bad.

Thanks for pointing me in right direction and also for the reference web page. It's great website, hope I found it earlier. Am now changing all my code to implement this best practice and also naming convention.

Regards,
Richa