Excel VBA exits on function call

Topic

New Reply

I have a bizarre VBA problem in Excel, where I either get a “ByRef argument type mismatch” error, or the function simply exits with no message or warning. I could use some help understanding what’s going on.

Here’s the two functions involved (pared down to just reproduce the problem):

Function TranslateB(strItem As String, strSrcList As String, strDestList As String, Optional strSplitCharacter As Variant) As String
TranslateB = “3.5”
End Function

Function TranslateA(strInput As String, strVarname As String) As String
i = 15
strSrcList = Range(“C_InputLists”).Cells(i, 1)
strDestList = Range(“P_InputLists”).Cells(i, 1)
TranslateA = TranslateB(strInput, strSrcList.Value, strDestList.Value, “; “)
MsgBox (TranslateA)
End Function

I call the first function from an Excel cell: =TranslateA(“3.5″,”Label”)

I place a breakpoint in each function. When I press F8 to execute the call to TranslateB(), the code exits with no error. The breakpoint within TranslateB() is never reached, and MsgBox() is never executed.

If I remove the “.Value” modifiers from the end of strSrcList and strDestList, I get a “ByRef argument type mismatch” error.

I’m left with the following questions:
– Why is the code not working?
– Why is the function exiting without an error message?
– How should I be debugging this?

Thanks heaps for any help.

Reply | Quote

Replies

It’s not working because you’re trying to use the .Value property of a simple data value, and values don’t have properties. Also you haven’t declared your variables, which is why you get a ByRef error when you try without the .Value property.

Try:

Code:

Function TranslateB(strItem As String, strSrcList As String, strDestList As String, Optional strSplitCharacter As Variant) As String
    TranslateB = "3.5"
End Function

Function TranslateA(strInput As String, strVarname As String) As String
    Dim i                     As Long
    Dim strSrcList            As String
    Dim strDestList           As String
    i = 15
    strSrcList = Range("C_InputLists").Cells(i, 1)
    strDestList = Range("P_InputLists").Cells(i, 1)
    TranslateA = TranslateB(strInput, strSrcList, strDestList, "; ")
    MsgBox TranslateA
End Function

Reply | Quote

Thanks, Rory.

So if I understand correctly, I should always declare local variables. That said, how do I declare an array of unknown size? (Specifically, one to be populated by Split().)

Secondly, if I understand correctly, the reason my code was exiting without a pop-up error message is that the error was effectively being returned in-cell. If I wanted execution to stay in VBA, I need to create an error handler. That would allow me to debug the error directly. Is that right?

Thanks again.

Reply | Quote

So if I understand correctly, I should always declare local variables

The following works too..

Code:

Function TranslateB(strItem, strSrcList, strDestList, Optional strSplitCharacter)
    TranslateB = "3.5"
End Function

Function TranslateA(strInput, strVarname)
    i = 15
    strSrcList = Range("C_InputLists").Cells(i, 1)
    strDestList = Range("P_InputLists").Cells(i, 1)
    TranslateA = TranslateB(strInput, strSrcList, strDestList, "; ")
    MsgBox TranslateA
End Function

..so if you don’t want Excel to ‘coerce’ variables for you, you should, as Rory recommends, always declare your variables.

zeddy

Reply | Quote

PStephanas,

Declare your variable like this:

Code:

Public Sub Mysub()
    Dim s
    s = Split(Range(“A1″), ” “)
    MsgBox s(0)
    MsgBox s(1)
End Sub

Reply | Quote

Since Split returns a String array, I’d use:

Code:

    Dim s() As String

Yes, if you have no error handler, a function called from a cell will simply terminate.

Reply | Quote