Tuesday, March 11, 2014

Need to pad a file or name (like Slide1) to have zero placeholders? Here is a quick REGEXP routine for VBA to PAD numbers in a string. Works in Excel and would work in a VBS as well.

Private Sub Test_Regexp_pad()
   Debug.Print Regexp_pad("9")
   Debug.Print Regexp_pad("12345")
End Sub
Private Function Regexp_pad(strFP As String) As String 'vALUE As String, Optional Pad As Integer) 'As String
  Dim r                                      '''As RegExp '''include "microsoft vbscript regular expressions 5.5" if debugging...
  Set r = CreateObject("vbscript.regexp")    '''Create Regexp
  If Pad = 0 Then Pad = 4                    '''If pad not specified they get 4
  r.Pattern = "(.*?)(\d+)(.)(.*)"                '''Items in () are returned in result set, objects without are dropped
  r.IgnoreCase = True
  Dim m                                      '''As MatchCollection   '''Holds the results. Nested (foo*(Bar*)) searches cause multiple m.item()
  Set m = r.Execute(strFP)
  On Error GoTo REGEXP_PAD_ERR               ''on bomb - return same value

   Dim i As Integer
   Dim intMyVal As Integer
   For i = 0 To m.Item(0).SubMatches.Count - 1
      If i <> 1 Then
         Regexp_pad = Regexp_pad & m.Item(0).SubMatches.Item(i)
         intMyVal = Int(m.Item(0).SubMatches.Item(i))
         'If Len(Trim(intMyVal)) > Pad Then Pad = Len(Trim(intMyVal))             ''if numbers exceed padding AND truncate
         Regexp_pad = Regexp_pad & Format(intMyVal, String(Pad, "0"))
      End If
   Next i
Exit Function
   On Error Resume Next
   Regexp_pad = strFP
   'Err.Raise 1, , "Unsupported number padding type or type without number"
End Function

No comments:

Post a Comment