MOCKSTACKS
EN
Questions And Answers

More Tutorials









VBA Automation or Using other applications Libraries

VBScript Regular Expressions


Set createVBScriptRegExObject = CreateObject("vbscript.RegExp")

Tools> References> Microsoft VBScript Regular Expressions #.#
Associated DLL: VBScript.dll
Source: Internet Explorer 1.0 and 5.5

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


Conclusion

In this page (written and validated by ) you learned about VBA Automation or Using other applications Libraries . What's Next? If you are interested in completing VBA tutorial, your next topic will be learning about: VBA Macro security and signing of VBA projects modules.



Incorrect info or code snippet? We take very seriously the accuracy of the information provided on our website. We also make sure to test all snippets and examples provided for each section. If you find any incorrect information, please send us an email about the issue: mockstacks@gmail.com.


Share On:


Mockstacks was launched to help beginners learn programming languages; the site is optimized with no Ads as, Ads might slow down the performance. We also don't track any personal information; we also don't collect any kind of data unless the user provided us a corrected information. Almost all examples have been tested. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. By using Mockstacks.com, you agree to have read and accepted our terms of use, cookies and privacy policy.