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 — Pᴇʜ
– Pᴇʜ, Commented Jun 29, 2018 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
– Aman Devrath, Commented Jun 29, 2018 at 10:04

score 4 · Accepted Answer · 2018-06-29 10:27:31Z

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, 2018 at 10:27

answered Jun 29, 2018 at 10:03

user4039065

Sign up to request clarification or add additional context in comments.

3 Comments

Aman Devrath Over a year ago

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

user4039065 Over a year ago

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.

Aman Devrath Over a year ago

Thank you for the explanation. I'll research for it more, didn't fully understand it though. :P

Collectives™ on Stack Overflow

Add regular expression reference before running the macro vba

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related