Results 1 to 6 of 6
  1. #1
    Star Lounger PStepanas's Avatar
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    90
    Thanks
    4
    Thanked 2 Times in 2 Posts

    Question Excel VBA exits on function call

    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.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Star Lounger PStepanas's Avatar
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    90
    Thanks
    4
    Thanked 2 Times in 2 Posts
    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.

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    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

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 650 Times in 592 Posts
    PStephanas,

    Declare your variable like this:

    Code:
    Public Sub Mysub()
        Dim s
        s = Split(Range("A1"), " ")
        MsgBox s(0)
        MsgBox s(1)
    End Sub

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •