Where's the REPLACE function in Excel VBA?

miktek
03-30-2001, 09:44 PM
Wow. I got so used to using the nifty REPLACE string function in VB6.0 and then tried to use it in EXCEL VBA and the compiler said"function not defined"! (I'm using Excel 97). I tried TOOLS-REFERENCES to add some VBA extensions? but it doesn't help. I really need to parse some strings I get back from a device on the serial port and I'm missing this function big time. I thought EXCEL VBA had everything VB6 had. Has this happened to anyone else? here's my line of code that it doessn't like.
Instring = Replace(Instring, "=>", "")

Thanks for any help.

Mike O

PWNettle
04-02-2001, 09:50 AM
I swear I replied to this from home! Anyways, there is no Replace function in VBA (for 97 versions at least). You can use a custom replace function. I use this code for Replace in Access97:
<PRE>Public Function Replace(StringIn As String, TargetString As String, ReplaceString As String) As String
Dim strReturn As String
Dim strLeftOver As String

strLeftOver = StringIn

Do While InStr(strLeftOver, TargetString) > 0
strReturn = strReturn & Mid$(strLeftOver, 1, InStr(strLeftOver, TargetString) - 1) & ReplaceString
strLeftOver = Mid$(strLeftOver, InStr(strLeftOver, TargetString) + Len(TargetString))
Loop

strReturn = strReturn & strLeftOver

Replace = strReturn
End Function</PRE>Paul

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum