Excel for Mac 2011: UBound() Not Working

Question

I'm working on making an existing macro-enabled spreadsheet functional on Excel for Mac 2011.

I have a function (Source) that searches arrays for a specified value:

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

It works perfectly in Excel 2013, but on Excel for Mac 2011, I receive the error:

Runtime error '9': Subscript out of range

I broke it apart and found that the UBound call is what's causing the error.

I'd like to change as little as possible for maintainability. How can I fix this error for the Mac version?

Thanks in advance for any replies!

Edit: @Siddharth Rout's solution is spot on, but since I was searching arrays within a loop, I had to modify the loop to reset the array between each iteration as follows (in case anyone else runs into the same issue!):

' --- START Reset Array for OS X ---
Dim OS_X_Hack(99) As String

For intIndex = 0 To 99
    OS_X_Hack(intIndex) = Original(intIndex)
Next

Erase Original()
ReDim Original(0 To 99) As String

For intIndex = 0 To 99
    Original(intIndex) = OS_X_Hack(intIndex)
Next

Erase OS_X_Hack()
' --- END Reset Array for OS X ---

Just tested it in Excel 2011. It works for me absolutely fine. Hope you are passing an Array to a function? Can you show me how are you calling this function? — Siddharth Rout, Apr 26 '13 at 17:03

Siddharth Rout · Accepted Answer · 2013-04-26T17:29:51.633

Ok This is my observation. If you call the function once in a procedure then it will work fine. For Example

Sub Sample()
    Dim a As Variant
    Dim s As String
    Dim strTemp As String

    s = "CC"
    strTemp = "A,B,C,D"

    a = Split(strTemp, ",")

    Debug.Print IsInArray(s, a)
End Sub

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
    IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

enter image description here

However if you call it twice in the procedure then you will get an error Runtime error '9': Subscript out of range. Maybe it is an Excel 2011 Bug?

Sub Sample()
    Dim a As Variant
    Dim s As String
    Dim strTemp As String

    s = "CC"
    strTemp = "A,B,C,D"

    a = Split(strTemp, ",")

    Debug.Print IsInArray(s, a)

    s = "A"
    Debug.Print IsInArray(s, a)
End Sub

enter image description here

Solution

Recreate the array. See this example.

Sub Sample()
    Dim a As Variant
    Dim s As String
    Dim strTemp As String

    s = "CC"
    strTemp = "A,B,C,D"

    a = Split(strTemp, ",")
    Debug.Print IsInArray(s, a)

    s = "A"
    a = Split(strTemp, ",")
    Debug.Print IsInArray(s, a)
End Sub

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
    IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

enter image description here

Awesome; thanks so much! The array I was searching was in a loop, so I added in a method for resetting it (edited into the original question). — Cameron Sumpter, Apr 26 '13 at 21:26
@CameronSumpter I think your issue may have been that you defined your array as a string array ("Dim a() As String") instead of as a variant ("Dim a As Variant"). When I changed to variant, Siddharth's solution worked for me—i.e., without using Erase() and ReDim(). — litturt, Sep 11 '15 at 17:44

score 0 · Answer 2 · edited May 23 '17 at 11:52

0

Credit for this solution goes to this answer by Brian Hinchey. Using the code below, I'm able to call IsInArray within a loop in Excel for Mac 2011.

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = Not IsError(Application.Match(stringToBeFound, arr, 0))
End Function

edited May 23 '17 at 11:52

Community

1
1

answered Feb 06 '16 at 13:19

Jeremy Caron

123
1
1
9

Excel for Mac 2011: UBound() Not Working

2 Answers2