' 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

Labels: excel, noteToSelf, vba