Geek asks and answers

Excel for Mac 2011: UBound () not working

I am working on creating an existing macroeconomic table in Excel for Mac 2011.

I have a function ( Source ) that looks for arrays for a given value:

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

It works fine in Excel 2013, but in Excel for Mac 2011 I get an error:

Runtime error '9': Subscript out of range

I broke it and found that the cause was a UBound error.

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

Thanks in advance for any answers!

Edit: The @Siddharth Rout solution is in place, but since I was looking for arrays in a loop, I had to change the loop to reset the array between each iteration as follows (in case anyone else encounters the same problem!):

' --- 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 ---
+5
2

. , .

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

, , Runtime error '9': Subscript out of range. , Excel 2011?

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

. . .

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

+4

. , IsInArray Excel Mac 2011.

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

More articles:


All Articles