Add regular expression reference before running the macro vba

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?

Microsoft VBScript Regular Expressions

Tools > References

Use late binding instead: Set RE = CreateObject("vbscript.regexp"). See here
– Pᴇʜ
Jun 29 at 10:00

Set RE = CreateObject("vbscript.regexp")

@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 at 10:04

Set RE = New RegExp

Set RE = CreateObject("vbscript.regexp")

Dim RE As RegExp

User-defined type not defined

Dim RE As Object as shown in my link.
– Pᴇʜ
Jun 29 at 10:05

Dim RE As Object

1 Answer
1

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.

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 at 10:09

You can accept an answer in 3 minutes

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.
– Jeeped
Jun 29 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 at 10:11

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.