Feedback on this code as a Find/Replace function for VBA Strings 1

[ordendelfai]

Access 97

I was looking for a quick and easy function in Access VBA (like excels Replace() function) that will look at a string value I pass to it, and replace all searched for values with another value. I couldn't find anything, so hacked the below code together real quick. It does the job, but I am wondering if there is a more efficient way and perhaps less code to achieve the same results. The below code should cut/paste into a module and can be run.

RoyVidar, your example in this Thread gave me the idea to put this together (just to give credit).

Sub ReplaceText()
Dim strFindThis 'Holds the Variable of what to Find
Dim strFindThis_Size 'Holds the size of the variable to find
Dim strInThis 'Holds the object to search in
Dim strInThis_Temp 'Holds the original, and then new looped string value of the object to search in
Dim strFoundIn As Long 'Holds the numerical value of where the value being searched was found
Dim strToThis 'Holds the variable of what to change the variable found to
Dim strToThis_Size

strFindThis = "Bob"
strFindThis_Size = Len(strFindThis)

strInThis = "His name is Bob Smith. Bob and his friend carpool to work."
strInThis_Temp = strInThis

strToThis = "Jeremy"
strToThis_Size = Len(strToThis)

MsgBox strInThis 'Displays the original text of the object to search in

Do Until InStr(strInThis, strFindThis) = 0
    strFoundIn = InStr(strInThis, strFindThis)
    strInThis = Mid$(strInThis, 1, InStr(strInThis, strFindThis) - 1) & strToThis
    strInThis = strInThis & Mid$(strInThis_Temp, strFoundIn + strFindThis_Size)
    strInThis_Temp = strInThis 'Change the temp full string to the changed string to prepare for next search
    MsgBox strInThis 'Shows the changes as they occur
Loop
End Sub

~Joel
As common courtesy and to help other members who might find your thread helpful in the future, please respond if the advice given resolved your issue.

 

[EBGreen]

You could probably do this sleaker with regular expressions.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[ordendelfai]

Hmm, good point, but I haven't learned regular expressions yet (need to do that soon...)

~Joel
As common courtesy and to help other members who might find your thread helpful in the future, please respond if the advice given resolved your issue.

 

[PHV]

If Access 2k or above you may use the Replace VBA function:
newString = Replace(oldString, "Bob", "Jeremy")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ordendelfai]

Unfortunately, it's Access 97

Though it is good to know that Replace does work in Access 2k

~Joel
As common courtesy and to help other members who might find your thread helpful in the future, please respond if the advice given resolved your issue.

 

[PHV]

Take a look in the FAQ.

http://www.tek-tips.com/faqs.cfm?fid=4342

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ordendelfai]

That is helpful, thanks a lot ;-)

~Joel
As common courtesy and to help other members who might find your thread helpful in the future, please respond if the advice given resolved your issue.

 

[cwarner]

Hi,

I created this code to take out leading zero's and alpha's and other noise characters out of numeric codes like invoice #s, tax #s, etc.

Enjoy!
cwarner.

Public Function filtercode(code As String) As String

Dim i As Integer
Dim firstrealnum As Integer
Dim tempstr As String
tempstr = " "
firstrealnum = 0

For i = 1 To Len(code)
If Mid(code, i, 1) <> 0 And IsNumeric(Mid(code, i, 1)) = True Then firstrealnum = 1
If firstrealnum = 1 And IsNumeric(Mid(code, i, 1)) = True Then tempstr = tempstr + Mid(code, i, 1)
Next

filtercode = LTrim(tempstr)

End Function