Add regular expression reference before running the macro vba

Question

code :

Public Sub CallDeleteAllText(control As IRibbonControl)
Call LeaveNumbers
End Sub

Public Function PullOnly(strSrc As String, CharType As String)
Dim RE As RegExp
Dim regexpPattern As String
Set RE = New RegExp
CharType = LCase(CharType)
Select Case CharType
    Case Is = "digits":
        regexpPattern = "\D"
    Case Is = "letters":
        regexpPattern = "\d"
    Case Else:
        regexpPattern = ""
End Select
RE.Pattern = regexpPattern
RE.Global = True
PullOnly = RE.Replace(strSrc, "")
End Function

Sub LeaveNumbers()
Dim cCell As Range
For Each cCell In Selection
    If cCell <> "" Then
        cCell.Value = "'" & PullOnly(cCell.Text, "digits")
    End If
Next cCell
With Selection
    .NumberFormat = "0"
    .Value = .Value
End With
End Sub

This code removes all text from the cell and leave all the numbers. But for this code to run, the user has to manually add Microsoft VBScript Regular Expressions reference from Tools > References. Is there a way to add the reference within the code itself so that, first it adds the reference and then removes all the text?

Use late binding instead: `Set RE = CreateObject("vbscript.regexp")`. See [here](https://stackoverflow.com/questions/4556910/how-do-i-get-regex-support-in-excel-via-a-function-or-custom-function) — Pᴇʜ, Jun 29 '18 at 10:00
@Pᴇʜ I added your code. I changed `Set RE = New RegExp` to `Set RE = CreateObject("vbscript.regexp")`. Now, when I run it, it gives me error in line `Dim RE As RegExp`. The error is : `User-defined type not defined`. — Aman Devrath, Jun 29 '18 at 10:04

score 4 · Accepted Answer · 2018-06-29T10:27:31.763

4

Change these two lines of regex declaration and assignment in the PullOnly function to static late binding.

Dim RE As RegExp
...
Set RE = New RegExp

'becomes,

static RE As object
...
if re is nothing then Set RE = createobject("VBScript.RegExp")

Static vars are 'remembered' by the sub procedure or function where they are declared. Normally, RE would be 'forgotten' (and destructed) when the function was completed and exited. However, with a static RE, the second time (and all subsequent times) the function is entered it 'remembers' that it has already been set to a regex scripting object so it is unnecessary to set it again.

This does not mean that a static var is globally public; it is only available within the function or sub procedure where it was declared.

edited Jun 29 '18 at 10:27

answered Jun 29 '18 at 10:03

Yeah I am doing it, just getting a msg as `You can accept an answer in 3 minutes`. WIll wait for 3 minutes then will do it – Aman Devrath Jun 29 '18 at 10:09
2

Just a note for everyone else, the **static** natrue of RE means that it is only created once. Subsequent calls to the function skip the CreateObject call. This vastly improves run-time especially so when used in a UDF down a long column. – Jun 29 '18 at 10:10
Thank you for the explanation. I'll research for it more, didn't fully understand it though. :P – Aman Devrath Jun 29 '18 at 10:11

Add regular expression reference before running the macro vba

1 Answers1