Replace function while using EXCELVBA

jibinpt used Ask the Experts™
 I am trying to use a replace function to replace a field's "," character with spaces. It gives me a compilation error saying Replace function not defined. is there any specific reference that I need to add for using replace functions?. I am using TRIM without any trouble. Can someone suggest any better alternative

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try specifying the VBA library as well, e.g.,

try this out:

ThisWorkbook.Worksheets(1).Range("A1:J10").Replace ",", " ", xlPart, xlByRows

hope that helps!


This is my code
ReplacedDesc = Replace(lsProcessSheet.Cells(lrow, glDescCol), ",", " ")..  VBA.Replace is not working
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

When you type in VBA and then hit the dot (.) does the list of available function calls come up?  Is Replace listed as one of them?
You need to add .Value
ReplacedDesc = Replace(lsProcessSheet.Cells(lrow, glDescCol).Value, ",", " ")



When I type in VBA the dot (.) does come up but Replace is not one among them. RESET  is the only one which comes up with RE.
Replace(...) by itself isn't a valid function in VBA for Office 97... below is an excerpt from the Microsoft Excel Visual Basic help file:


expression.Replace(What, Replacement, LookAt, SearchOrder, MatchCase, MatchByte)

expression   Required. An expression that returns a Range object.

Thus, "expression" is required, and generally you'll use this function as in my previous post. If you want to just replace values in a normal string without using Ranges and whatnot, you'll have to write your own little Replace function, as below:

Public Function replaceStr(ByVal str1 As String, ByVal repThis As String, ByVal withThis As String) As String
    While Left(str1, Len(repThis)) = repThis
        str1 = withThis & Mid(str1, Len(repThis) + 1)
    While InStr(2, str1, repThis) > 0
        i = InStr(2, str1, repThis)
        str1 = Left(str1, i - 1) & withThis & Mid(str1, i + Len(repThis))
    replaceStr = str1
End Function

hope that helps!
Oh, is this VBA 97?  Replace didnt come around until VB6, so I guess that would mean VBA 2000?
Are you getting an error on the replace line or is it just not working?


thanks for the points and the A! time to go home now... happy programming, people!


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial