How to develop an Excel Macro that contains regula

Question

I have developed an Excel 2010 VBA Macro that makes use of VBScript.RegExp. One of my users is apparently using Excel Mac 2011, which supports VBA, but no VBScript.RegExp.

I have seen several posts that mention it is possible to create your own RegEx functions using AppleScript that could be called by the VBA Macro. However, it seems as though there would have to be a Mac version of the Excel file and a Windows version. This is less than ideal.

Is there another way to implement regular expressions in VBA that would be compatible with both Windows and Mac?

Answer 1

Unfortunately no solution for this has yet been found — the current workaround is to simply replace regular expressions with a series of Replace calls (or other required operation).

Answer 2

I hit this problem trying to use the regexp library when trying to strip html tags from a cell.

I know this wasn't noted within the question as the desired use but, in case it is or future visitors are trying to replace a regex function to remove HTML from tags within a cell within Mac Excel 2011, this user defined function will work. Apologies to the original author, i found it only but can no longer find the source i'm afraid.

Public Function StripHTML(zDataIn As String) As String

  Dim iStart As Integer
  Dim iEnd   As Integer
  Dim iLen   As Integer

  Do While InStr(zDataIn, "<") > 0
    iStart = InStr(zDataIn, "<")
    iEnd = InStr(iStart, zDataIn, ">")
    iLen = Len(zDataIn)
    If iStart = 1 Then
      zDataIn = Right(zDataIn, iLen - iEnd)
    Else
      If iLen = iEnd Then
        zDataIn = Left(zDataIn, iStart - 1)
      Else
        zDataIn = Mid(zDataIn, 1, iStart - 1) & _
                  Right(zDataIn, iLen - iEnd)
      End If
    End If
  Loop

  StripHTML = zDataIn

End Function

Answer 3

If you don't mind paying US$99, there is Aivosto RegExpr. Apparently this is a pure VBA solution, that should run on both PC and Mac VBA.

Alternatively, write separate solutions for both PC and Mac, then use compiler directives to separate which bit of code runs on each platform