Rgx.Mid Limitation in VBA?

[G Ingram]

Operating System: Windows 10 [Version 1909 (OS Build 18363.1316)]

Excel Version and Build Number: Office365 - Version 2103 Build 13827.20004 Click-to-Run

FastExcel Build Number: 461.443

I've been using the Rgx.Mid function in VBA (since it's a LOT faster and better than the inbuilt Regular Expressions libraries), however I've bumped into a limitation where the function won't accept more than 32,767 characters.

A cell is limited to hold  32,767 characters, but a variable in VBA can hold much more. I am attempting to read a text file into a variable and then find all matches of a RegEx inside that string.

Below is the code from a module I used to this limitation as a worksheet function. 

These formulas worked as expected:

=RgxTest(32767,".{30000}")

=RgxTest(32767,".{20000}")

These formulas didn't work as expected:

=RgxTest(32768,".{30000}")

=RgxTest(32768,".{20000}")

Option Explicit

Public jFuncRgxMid As Long

Private Function genString(i As Long) As String

    genString = String$(i, "A")

End Function

Public Function RgxTest(i As Long, rx As String)

    Dim jFuncRgxMid As Long

    Dim arrTemp As Variant

    If jFuncRgxMid = 0 Then

        jFuncRgxMid = Evaluate("Rgx.Mid")

    End If

    arrTemp = Application.Run(jFuncRgxMid, genString(i), rx, 0, False)

    If UBound(arrTemp) > 0 Then

        RgxTest = Application.Transpose(arrTemp)

    End If

End Function

This might be a limitation on Application.Run() paramaters, but if it is - is there any way to get around this?

Thanks for your consideration!

[Charles Williams]

I tried running the C++ XLL in debug mode but it looks like it never even enters Rgx.Mid, so I am fairly sure that this is a limitation of Application.Run.

Sorry - I cannot see a way around this at the moment.

[G Ingram]

Thanks for the response and for looking into this issue Charles.

In case anyone else bumps into this limitation - I made a semi-functional workaround by dividing the string into chunks of no more than 32767 characters. It helped a bit in my scenario, but it won't work in other scenarios.

My function looks like this:

Function String_Chunkify(str As String, Optional iMaxStringSize As Long = 32767) As String()

    Dim strLen As Long

    Dim iChunkCount As Long

    Dim i As Long, iStart As Long

    Dim retArr() As String

    strLen = Len(str)

    iChunkCount = Ceil(strLen / iMaxStringSize)

    ReDim retArr(1 To iChunkCount)

    For i = 1 To iChunkCount

        iStart = ((i - 1) * iMaxStringSize) + 1

        'iEnd = (i * iMaxStringSize) + 1

        retArr(i) = Mid(str, iStart, iMaxStringSize)

    Next i

    String_Chunkify = retArr

End Function