Need the REPLACE string function in Excel VBA!

Posted by Mike O on March 30, 2001 8:36 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.

Posted by Dave Hawley on March 30, 2001 10:15 PM

Hi Mike

The Replace is part of the WorksheetFunctions, so you would need to put:

Instring = WorksheetFunction.Replace(Instring, "=>", "")

But Having said that, you have not got enough arguments included:

REPLACE(old_text,start_num,num_chars,new_text)

It looks like you are wanting the Subsitute function though:

Sub TryThis()
Dim Instring As String
Instring = "25=>35"
Instring = WorksheetFunction.Substitute(Instring, "=>", "")
MsgBox Instring
End Sub

Dave

OzGrid Business Applications

Posted by Mike O on March 31, 2001 12:30 PM

Dave that's great. Thanks a lot for the help. The Replace function syntax is right for VB6 but I need to find out more about these worksheet functions. I tried to look up Substitute and Worksheetfunction in the help index, bit it wasn't there. Any idea where I can find definitions and help on these? Again , thanks for the help.

Mike O

Posted by Dave Hawley on March 31, 2001 6:26 PM

Hi Mike

Glad to help. To find help on all functions and formulas, you will need to use the help while in Excel's interface as apposed to the VBE. In other words while in Excel push F1 and type in: Substitute.

Dave

OzGrid Business Applications