Skip to content
This repository

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Browse code

Added Regex Replace function, and created example use script in comments

  • Loading branch information...
commit 4a977388729a6e36fa135ae2b098d567eba7360e 1 parent 930e162
Andy Shapiro authored November 29, 2011

Showing 1 changed file with 57 additions and 5 deletions. Show diff stats Hide diff stats

  1. 62  VBA/z_Strings.bas
62  VBA/z_Strings.bas
@@ -129,15 +129,15 @@ Function Regex(SearchString As String, RegExPattern As String, Optional CaseSens
129 129
 'Example function call would return "ty1234"
130 130
 'MsgBox RegEx("qwerty123456uiops123456", "[a-z][A-Z][0-9][0-9][0-9][0-9]", False)
131 131
 '
132  
-    Dim RE As Object, REMatches As Object
133  
-    Set RE = CreateObject("vbscript.regexp")
134  
-    With RE
135  
-        .MultiLine = False
  132
+    Dim re As Object, REMatches As Object
  133
+    Set re = CreateObject("vbscript.regexp")
  134
+    With re
  135
+        .Multiline = False
136 136
         .Global = False
137 137
         .IgnoreCase = Not (CaseSensitive)
138 138
         .Pattern = RegExPattern
139 139
     End With
140  
-    Set REMatches = RE.Execute(SearchString)
  140
+    Set REMatches = re.Execute(SearchString)
141 141
     If REMatches.Count > 0 Then
142 142
         Regex = REMatches(0)
143 143
     Else
@@ -145,6 +145,58 @@ Function Regex(SearchString As String, RegExPattern As String, Optional CaseSens
145 145
     End If
146 146
 End Function
147 147
 
  148
+Public Function RegexReplace(ByVal Pattern As String, _
  149
+                             ByVal Replacement As String, _
  150
+                             ByVal SourceString As String, _
  151
+                             Optional ByVal IgnoreCase As Boolean = False, _
  152
+                             Optional ByVal Glbl As Boolean = True, _
  153
+                             Optional ByVal Multiline As Boolean = False _
  154
+                             ) As String
  155
+'Perform a regular expression replacement
  156
+'
  157
+'Parameters
  158
+'----------
  159
+'  Pattern:
  160
+'    The regular expression pattern to search for
  161
+'  Replacement:
  162
+'    The string or pattern to be used as a replacement
  163
+'  SourceString:
  164
+'    The string to search within
  165
+'  IgnoreCase (default=True):
  166
+'    Whether to be case-insensitive
  167
+'  Glbl (default=True):
  168
+'    Whether to replace all matches in `SourceString` (otherwise,
  169
+'    stop after the first replacement)
  170
+'  Multiline (default=False):
  171
+'    Whether to activate multiline mode. This makes the carat (^) and
  172
+'    dollar ($) match at the beginning and end of each line, rather
  173
+'    than the beggining and end of the entire string.
  174
+'
  175
+'Returns
  176
+'-------
  177
+'  String, with `Pattern` replaced with `Replacement`, if found
  178
+'  in `SourceString`. If `Pattern` is not found, returns `SourceString`
  179
+'  as-is.
  180
+'
  181
+'Example function call
  182
+'---------------------
  183
+'   Dim Pattern As String: Pattern = "(:\$[A-Z]+\$)([0-9]+)" '2 groups, first keep, 2nd replace
  184
+'   Dim Replace As String: Replace = "$1" & 10     'keep $1, replace second with max row
  185
+'   Dim TestVal As String: TestVal = "$B$2:$C$4"
  186
+'   Debug.Print RegexReplace(Pattern, Replace, TestVal, True, False, False)
  187
+
  188
+    Dim re As Object
  189
+    Set re = CreateObject("vbscript.regexp")
  190
+    With re
  191
+        .Pattern = Pattern
  192
+        .IgnoreCase = IgnoreCase
  193
+        .Global = Glbl
  194
+        .Multiline = Multiline
  195
+    End With
  196
+    
  197
+    RegexReplace = re.Replace(SourceString, Replacement)
  198
+End Function
  199
+
148 200
 Function Printf(ByVal FormatWithPercentSign As String, ParamArray InsertArray()) As String
149 201
 'http://www.freevbcode.com/ShowCode.asp?ID=9342
150 202
     Dim ResultString As String

0 notes on commit 4a97738

Please sign in to comment.
Something went wrong with that request. Please try again.