Error with Character Replacement VBA Function

Question

Consider the following VBA Function:

Sub RemoveLetters()
    Application.ScreenUpdating = False

    Dim str As String: str = "abcdefghijklmnopqrstuvwxyz0123456789 "
    Dim ltr As String

    For i = 1 To 37

        ltr = Mid(str, i, 1)

        Sheet9.Range("A2:A1800").Replace _
            What:=ltr, Replacement:="", MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

    Next
    Application.ScreenUpdating = True

End Sub

This function gets rid of all letter and number characters in a given string. However, the error I encounter is that whenever a string begins with a non-letter, non-number string it fails to execute on that string and does not execute on any further strings. That is, running into such a string stops the execution. Is there an obvious reason why this is happening? How might I modify this code to fix it?

Just tried it with $asd and it worked. What are examples of these strings that "begins with a non-letter, non-number"? — WGS
– WGS, Commented Feb 18, 2014 at 15:33
For example, @gmail.com would halt the macro. It is the only thing I can think of that is causing a problem as in every case this fails @ seems to pop up at the start. — 114
– 114, Commented Feb 18, 2014 at 15:42
Just tested with [email protected]. The macro left '@. on my cell, which should be correct, as @ functions the same way as =, -, etc in that they can start formulas, so Excel forces them to string format by adding '. (Try @SUM. It's the same as =SUM.) I don't see why this should throw an error, really, on your end. What happens if you add On Error Resume Next before the For i=... line? — WGS
– WGS, Commented Feb 18, 2014 at 15:48
@BK201 Same error it seems. Did you try a case where @ is the first character in a cell? It does seem to work when I add a space in front of @ string cells. Is there a way to prevent Excel reading @ in the same way as =? I tried changing to text format without success. — 114
– 114, Commented Feb 18, 2014 at 16:02
As I said, it's pretty impossible to start a cell in Excel with @ as it reads as a formula. Check the formula bar while this cell on your end is highlighted. What does it show? Just @string or '@string? I bet it starts with '. That will stop the macro, based on my tests. — WGS
– WGS, Commented Feb 18, 2014 at 16:04

WGS · Accepted Answer · 2014-02-18 16:22:41Z

2

For matching patterns, the best option is regular expressions or more commonly known as regex. By defining a set pattern to follow, you can extract or replace almost anything you want.

To replace all non-number and non-letter characters as well as spaces, a small function like the following works:

Function NoNormalChar(StrTarget As String) As String
    Dim oRegEx As Object
    Set oRegEx = CreateObject("vbscript.regexp")
    With oRegEx
        .Pattern = "[a-zA-Z0-9\s]+"
        .Global = True
        NoNormalChar = .Replace(StrTarget, "")
    End With
End Function

Calling it inside a sub is simple enough:

Sub RemoveLetters()
    Dim Rng As Range, Cell As Range
    Application.ScreenUpdating = False
    Set Rng = Sheet9.Range("A2:A1800")
    For Each Cell In Rng
        Cell.Value = NoNormalChar(Cell.Value)
    Next Cell
    Application.ScreenUpdating = True
End Sub

Copy and paste both to a single module. Run on a back-up copy and let us know of the results.

answered Feb 18, 2014 at 16:22

WGS

14.2k5 gold badges50 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

114 Over a year ago

That worked perfectly, thanks! If you happen to come up with another idea as to why @ was giving me an issue before please do let me know.

114 Over a year ago

As a follow up, would it be possible for you to explain how the function you defined works? I am not sure I fully understand that part, though it does work wonderfully.

WGS Over a year ago

Gladly. Basically, regular expressions works on a pattern. The learning curve is very steep, since it mainly relies on your creativity after learning it and it's very easy to create a bad pattern. As said over and over, you need a pattern. The pattern above is [a-zA-Z0-9\s]+. The way it's read is: all characters from a-z, A-Z, 0-9, and spaces (\s), in any grouping ([]) and of an indeterminate length (+). When the pattern is locked in, We call on .Replace to "hit" every character that fits the pattern and replace them with "". Say you want to remove * as well...

WGS Over a year ago

... It's as easy as changing the pattern to [a-zA-Z0-9\s\*]. The backslash escapes the * as * is a special character in RegEx. I highly suggest checking out tutorials online. There's a lot. My personal favorite has to be regex.learncodethehardway.org. Check out the free HTML version. :)

Collectives™ on Stack Overflow

Error with Character Replacement VBA Function

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related