Custom INDEX function that can handle >255 characters

Hi,

I am trying to use the `Application.Index` function with an variant 2D Array which contains some elements having >255 characters of text. This results in Variant/Integer Type `Type Mismatch` error. I am also unable to use `Application.Transpose` because of hitting this >255 characters limit.

Has anyone made any `Custom INDEX UDFunction` that can handle >255 characters of text to overcome this limit?

e.g.
Index Type MisMatch error.PNG


The snippet code looks like this:

........
........
For j = 1 to NoOfSlides
    A = (j - 1) * (nRw * 2) + 1
    B = IIf(A >= UBound(Arr, 1), UBound(Arr, 1), (A + (nRw * 2)) - 1)
    If B > UBound(Arr, 1) Then B = UBound(Arr, 1)
    ab_Rng = Evaluate("row(" & A & ":" & B & ")")

    TmpArr(j) = Application.Index(Arr, ab_Rng, Array(1, 2))     ' Type Mismatch Error
    With oPres
        Set oSlide = .slides("Slide0_ABC").Duplicate
        oSlide.moveto toPos:=.slides.Count
        With oSlide
        ....
        End With
        
        If getDimensions(TmpArr(j))<2 Then 
            TmpArr(j) = Application.Transpose(TransposeDim(TmpArr(j)) )    ' Error
        End If
        For y = LBound(TmpArr(j), 1) To UBound(TmpArr(j), 1)
        .....
        Next y
    End With
Next j
........
........

Function getDimensions(var As Variant) As Long
    On Error GoTo Err
    
    Dim i As Long
    Dim tmp As Long
    
    i = 0
    Do While True
        i = i + 1
        tmp = UBound(var, i)
    Loop
    
Err:
    getDimensions = i - 1
    On Error GoTo 0
    Err.Clear

End Function


Function TransposeDim(v As Variant) As Variant
' Convert 1D Array to 2D Array (1 -Based)
    Dim x As Long, y As Long, Xupper As Long, Yupper As Long
    Dim tempArray As Variant

    on error resume next
    Xupper = UBound(v, 2)
    if err.number <>0 then
        Redim Preserve v(1 to ubound(v), 1 to 1)
       Xupper = UBound(v, 2)   
    endif
    on error goto 0

    Yupper = UBound(v, 1)

    ReDim tempArray(1 To Xupper, 1 To Yupper)
    For x = 1 To Xupper
        For y = 1 To Yupper
            tempArray(x, y) = v(y, x)
        Next y
    Next x

    TransposeDim = tempArray
End Function

There is no way to attach a powerpoint pptx file, so sharing the powerpoint template file from google drive.