' from http://support.microsoft.com/kb/188007
' for running on Excel on Mac, where the VBA engine doesn't
' have VB6 String functions
Public Function Replace(sIn As String, sFind As String, _
sReplaceWith As String, Optional nStart As Long = 1, _
Optional lngMaxReplacementsToMake As Long = -1) As String
Dim lngNumReplacementsDeja As Long
Dim nPos As Integer
Dim sOut As String
Dim bContinue As Boolean
bContinue = True
sOut = sIn
nPos = InStr(nStart, sOut, sFind, vbBinaryCompare)
While (nPos > 0) And bContinue
lngNumReplacementsDeja = lngNumReplacementsDeja + 1
sOut = Left(sOut, nPos - 1) &_
sReplaceWith & Mid(sOut, nPos + Len(sFind))
nPos = nPos + Len(sReplaceWith)
If (lngMaxReplacementsToMake <> -1) And _
(lngNumReplacementsDeja >= lngMaxReplacementsToMake) Then
bContinue = False
End If
nPos = InStr(nPos, sOut, sFind, vbBinaryCompare)
If nPos > 10000 Then
' I put a breakpoint here when testing
Debug.Print nPos & " :: " & sOut & " :: " & sFind
End If
Wend
EndFn:
Replace = sOut
End Function
One feller's views on the state of everyday computer science & its application (and now, OTHER STUFF) who isn't rich enough to shell out for www.myfreakinfirst-andlast-name.com
Pages
▼
Sunday, August 30, 2009
Putting VB6 Replace into Excel 2004 for Mac
Update 20100216: Now it even works!