VBScript - InStrRev and Format

Wow, big day for posts. Guess I am just waiting on a couple things; waiting to hear from the bigger boss about some more work for my transition (2 weeks left!) and waiting to hear from my love to see how her job interview went (could still be going on). But do you really care about any of that? I do, but I doubt you do. So I'm gonna post something about vbscript, something I love and think that more people should get into as it is quite flexible.

A very simple post to start out. For the most part, vbs can use the same methods and functions as vba/vb6, with slight modifications. The two biggest things missing, in my opinion, are Format() and InStrRev(). You can make a substitute function in VBS for the latter, however the former needs a bit more work and usually specific to your task at hand.

I'll get the easier one out of the way. I only use the two main arguments for this, but you can easily add something for the start position or even comparison type. If you want to use it, simply put this somewhere in your vbs file and call it as normal:

Function InStrRev(ByVal vStringCheck, ByVal vStringMatch)
 Dim i, iLen
 iLen = Len(vStringMatch)
 For i = Len(vStringCheck) To 1 Step -1
  If Mid(vStringCheck, i, iLen) = vStringMatch Then Exit For
 Next
 InStrRev = i
 Set i = Nothing
 Set iLen = Nothing
End Function

Simple, huh?
Next is using Format in VBS. As I said, you can't just use the function like Format(7, "000") as you can in VBA. What you can do, however, is manipulate strings to get it done easily (and use a function for it if you're going to do it multiple times).

To see a one-shot version of it, here is how you would write the above Format statement in VBS. Note that you would probably never go through this much code to write "007", but it should give you an idea of how it works. In essence, if you want it to be 3 digits, you're concatenating "000" to the value "7", then taking the right 3 digits of it:

 Dim TheValue, NumDigits, PaddedValue
 TheValue = 7
 NumDigits = 3
 PaddedValue = Right(String(NumDigits, "0") & TheValue, NumDigits)
 MsgBox PaddedValue

If you plan to do this multiple times, heres an example of how to format the current date in mm/dd/yyyy formation:

 MsgBox FormatPaddedZeroes(Month(Now), 2) & "/" & FormatPaddedZeroes(Day(Now), 2) & _
  "/" & FormatPaddedZeroes(Year(Now), 4)

Function FormatPaddedZeroes(ByVal TheValue, ByVal NumDigits)
 FormatPaddedZeroes = Right(String(NumDigits, "0") & TheValue, NumDigits)
End Function

I could write a post on all the similarities between VBA and VBS, but other than a few things they are very similar (just remove any types in VBS, and the 'main' subroutine doesn't need a Sub and End Sub).

VBA/VB6/VBS word replace - Regular expressions

Did you ever wish you could replace entire words within a string? Maybe you're changing names within a document and want to change "Mark" to "Horace", but don't want to have "market" turn into "Horaceet". Or maybe you want to insert a word, but only in relation to another specific word? Then use regular expressions!

Make sure to escape any regexp special characters, and use the \b word boundaries around the 'Find what' portion. Occasionally I'll even use my "rxPatt = ..." line in other routines when I need to escape a search string for regexp. Works well, though I find myself using this less and less nowadays. Unless you have long strings within cells, it isn't that useful for Excel unfortunately. I have found a couple uses here and there, but not too many.

Anyways.. on to the code!

Function RegExWordReplace(ByVal rxFull As String, ByVal rxWhat As String, ByVal _
  rxRepl As String, Optional ByVal rxIgnoreCase As Boolean = True) As String
 'rxFull = Full string that you will be doing your find/replace within
 'rxWhat = What to find within the full string
 'rxRepl = What to replace the found string with
 'rxIgnoreCase = You can make the search case-sensitive by specifying this = false
 Dim RegEx As Object, rxPatt As String
 Set RegEx = CreateObject("vbscript.regexp")
 rxPatt = Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace( _
  Replace(Replace(Replace(Replace(rxWhat, "\", "\\"), "^", "\^"), "$", "\$"), "*", _
  "\*"), "+", "\+"), "?", "\?"), ".", "\."), "(", "\("), ")", "\)"), "|", "\|"), _
  "{", "\{"), "}", "\}"), ",", "\,")
 With RegEx
  .Pattern = "\b" & rxPatt & "\b"
  .Global = True
  .IgnoreCase = rxIgnoreCase
  .MultiLine = True
 End With
 If RegEx.Test(rxFull) Then rxFull = RegEx.Replace(rxFull, rxRepl)
 RegExWordReplace = rxFull
 Set RegEx = Nothing
End Function

And to test it:

Sub WordReplaceExample()
 Dim OrigStr As String, NewStr As String
 OrigStr = "Our normal products were found to have no side effects."
 NewStr = RegExWordReplace(OrigStr, "no", "no adverse")
 MsgBox OrigStr & vbCrLf & vbCrLf & NewStr
End Sub