VBA Automation or Using other applications Libraries VBScript Regular Expressions
Example
Set createVBScriptRegExObject = CreateObject("vbscript.RegExp")
Tools> References> Microsoft VBScript Regular Expressions #.#
Associated DLL: VBScript.dll
Source: Internet Explorer 1.0 and 5.5
- MSDN-Microsoft Beefs Up VBScript with Regular Expressions
- MSDN-Regular Expression Syntax (Scripting)
- experts-exchange - Using Regular Expressions in Visual Basic for Applications and Visual Basic 6
- How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops on SO.
- regular-expressions.info/vbscript
- regular-expressions.info/vbscriptexample
- WIKI-Regular expression
Code
You can use this functions to get RegEx results, concatenate all matches (if more than 1) into 1 string, and display result in excel cell.
Public Function getRegExResult(ByVal SourceString As String, Optional ByVal RegExPattern As String = "\d+", _
Optional ByVal isGlobalSearch As Boolean = True, Optional ByVal isCaseSensitive As Boolean = False, Optional ByVal Delimiter As String = ";") As String
Static RegExObject As Object
If RegExObject Is Nothing Then
Set RegExObject = createVBScriptRegExObject
End If
getRegExResult = removeLeadingDelimiter(concatObjectItems(getRegExMatches(RegExObject, SourceString, RegExPattern, isGlobalSearch, isCaseSensitive), Delimiter), Delimiter)
End Function
Private Function getRegExMatches(ByRef RegExObj As Object, _
ByVal SourceString As String, ByVal RegExPattern As String, ByVal isGlobalSearch As Boolean, ByVal isCaseSensitive As Boolean) As Object
With RegExObj
.Global = isGlobalSearch
.IgnoreCase = Not (isCaseSensitive) 'it is more user friendly to use positive meaning of argument, like isCaseSensitive, than to use negative IgnoreCase
.Pattern = RegExPattern
Set getRegExMatches = .Execute(SourceString)
End With
End Function
Private Function concatObjectItems(ByRef Obj As Object, Optional ByVal DelimiterCustom As String = ";") As String
Dim ObjElement As Variant
For Each ObjElement In Obj
concatObjectItems = concatObjectItems & DelimiterCustom & ObjElement.Value
Next
End Function
Public Function removeLeadingDelimiter(ByVal SourceString As String, ByVal Delimiter As String) As String
If Left$(SourceString, Len(Delimiter)) = Delimiter Then
removeLeadingDelimiter = Mid$(SourceString, Len(Delimiter) + 1)
End If
End Function
Private Function createVBScriptRegExObject() As Object
Set createVBScriptRegExObject = CreateObject("vbscript.RegExp") 'ex.: createVBScriptRegExObject.Pattern
End Function
