Solved

Replacing Characters - A more elegant way to do this in VBA?

Posted on 2013-06-19
3
307 Views
Last Modified: 2013-06-19
Hi All

I have a block of code that cleans up a string.  It uses 4 Do Loops, looking at it I just can't help but cringe - is there a better way to do this?
        Do While InStr(1, strTest, "/")
          strTest = Replace(strTest, "/", "")
        Loop
        Do While InStr(1, strTest, " ")
          strTest = Replace(strTest, " ", "")
        Loop
        Do While InStr(1, strTest, "-")
          strTest = Replace(strTest, "-", "")
        Loop
        Do While InStr(1, strTest, ",")
          strTest = Replace(strTest, ",", "")
        Loop

Open in new window

Kind regards,
0
Comment
Question by:DrTribos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 95

Accepted Solution

by:
Lee W, MVP earned 500 total points
ID: 39261331
Drop the loops.  They seem pointless to me.  The way they read, you're checking to see if the string contains the character.  If you just execute the replace, it should work the same way.

Indeed, you COULD combine it like this:

strTest = Replace(Replace(Replace(Replace(strTest, "/", ""), " ", ""), "-", ""), ",", "")
0
 
LVL 15

Author Comment

by:DrTribos
ID: 39261347
Hi leew

Thanks for the suggestion.  I thought replace(strTest, "/", "") would only replace the first instance... I will give that a go.
0
 
LVL 95

Expert Comment

by:Lee W, MVP
ID: 39261350
No, replace replaces ALL instances of a character (or characters) in the string.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question