Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home





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.


Check out our Excel VBA Resources

Re: Need the REPLACE string function in Excel VBA!

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


Re: Need the REPLACE string function in Excel VBA!

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


Re: Need the REPLACE string function in Excel VBA!

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




This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.