Allen Browne has an interesting article on using public types at http://allenbrowne.com/ser-16.html
Based on his info, I've created a couple of functions:
1) To replace a single character or word in a string. (Useful in A97, which doesn't have a Replace() function)
2) To replace multiple characters or words in a string. (Something that I've read isn't possible without running multiple iterations of a function)
Place in a new module. Follow the comments to test.
Hope this may provide some 'food for thought'.
Bob
Thank you Allen for your insight.
Based on his info, I've created a couple of functions:
1) To replace a single character or word in a string. (Useful in A97, which doesn't have a Replace() function)
2) To replace multiple characters or words in a string. (Something that I've read isn't possible without running multiple iterations of a function)
Place in a new module. Follow the comments to test.
Hope this may provide some 'food for thought'.
Bob
Code:
p.s. There's a little repulsive person that want to get his 'two-cents' in at the end of this response. Sorry 'bout that.
Bob
Public Type xSec
LeftS As String
MidS As String
RightS As String
Total As String
End Type
Function GetXSec(ByVal pStr As String, ByVal pDelim As String) As xSec
GetXSec.LeftS = Left(pStr, InStr(pStr, pDelim) - 1)
GetXSec.MidS = Mid(pStr, InStr(pStr, pDelim), Len(pDelim))
GetXSec.RightS = Mid(pStr, InStr(pStr, pDelim) + Len(pDelim))
GetXSec.Total = GetXSec.LeftS + GetXSec.MidS + GetXSec.RightS
End Function
Function xReplaceIt(ByVal xpstr As String, xpdelim As String, xnew As String) As String
'*******************************************
'Purpose: Test of using user-defined types
' as described by Allen Browne at
' http://allenbrowne.com/ser-16.html
'Coded by: raskew
'Inputs: ? xreplaceit("The quick brown fox jumped over the dog", "o", "~")
'Output: The quick br~wn f~x jumped ~ver the d~g
***********************************
Dim MyStr As String
MyStr = xpstr
Do While InStr(MyStr, xpdelim) > 0
MyStr = GetXSec(MyStr, xpdelim).LeftS + xnew + GetXSec(MyStr, xpdelim).RightS
Loop
xReplaceIt = GetXSec(MyStr, xnew).Total
End Function
Function yReplaceIt(ByVal ypstr As String, ParamArray varmyvals() As Variant) As String
'*******************************************
'Purpose: Replace multiple characters or
' words in a string using Allen
' Browne's discussion of user-
' defined types at
' http://allenbrowne.com/ser-16.html
'Coded by: raskew
'Inputs: ? yreplaceit("The quick brown fox jumped over the dog", "quick", "muddy","fox", "stream", "jumped", "plunged", "dog", "waterfall")
'Output: The muddy brown stream plunged over the waterfall
'*******************************************
Dim MyStr As String
Dim i As Integer
Dim idx As Long
MyStr = ypstr
i = UBound(varmyvals)
For idx = 0 To UBound(varmyvals()) Step 2
Do While InStr(MyStr, varmyvals(idx)) > 0
MyStr = GetXSec(MyStr, varmyvals(idx)).LeftS + varmyvals(idx + 1) + GetXSec(MyStr, varmyvals(idx)).RightS
Loop
Next idx
yReplaceIt = MyStr
End Function
Thank you Allen for your insight.
Last edited: