Need my Macro to replace words with abbreviations

[ahetzel10]

Hi all,

I am running a macro to delete and hide some columns of weekly data to quickly create a sheet small enough to print out. I want the macro to automatically replace a couple phrases such as "single family" with "SF", and "new construction" with "NC". How can I incorporate this into my Macro?

Thanks!

 

[codeliftsleep]

Uggh...it churns my stomach everytime I see macro generated code.

It was a great idea so people can quickly figure out how to do stuff that is unclear, but its a terrible idea to actually use the macro generated code as is in production...its exceptionally slow, and it prevents people from learning how to program properly. Basically, I view it as a crutch.

To give an example, at my current job, when I got there, they had a huge macro that was obviously recorded and left as is, that copied and pasted a bunch of different data from cell to cell and from sheet to sheet. It was taking 30 minutes just to run this small part of the code everyday. I went in there and rewrote it properly using variant arrays and the code ran in under 30 seconds. It also chopped out about 200 lines of code.

People need to stop using recorded macros and learn how to program properly to get excel to run efficiently. You may not think its needed at this time because what you are doing is small, but you will need it at some point and its always easier to learn properly rather than learn it wrong and then have to re-learn it.

This is coming from a professional programmer who just happens to have to code a lot in VBA due to the bank I work for using Excel for a lot of stuff(I try and steer them away from it as much as possible for large scale projects). On a daily basis I code in C#, C++, PHP, VBA and javascript and deal with 3 or 4 different databases. If you use macros all the time you will not progress anywhere with learning how to program and/or do things the proper way, it will significantly limit your ability to accomplish things and will also run very inefficiently.

VBA is a nice easy language to learn....when I started at the bank never having really programmed in VBA I considered it a toy compared to the other languages, similar to a matchbox car. I have to admit I am pretty impressed with what you can do in VBA, especially when you think outside the box, but I still consider it a toy, albeit a high end toy---like one of those electric riding cars kid have these days instead of like a matchbox car.

 

[Joe4]

People need to stop using recorded macros and learn how to program properly to get excel to run efficiently. You may not think its needed at this time because what you are doing is small, but you will need it at some point and its always easier to learn properly rather than learn it wrong and then have to re-learn it.

This is coming from a professional programmer who just happens to have to code a lot in VBA due to the bank I work for using Excel for a lot of stuff

I think you are painting with too broad a brush here. Not every situation or person is the same. Not everyone here is a professional programmer, nor do many of them have any desire to be. Dare say, many would even qualify themselves as non-technical people. So to expect everyone here to learn VBA is really not all that feasible.

The Macro Recorder can be a:
- stepping stone for those just learning to write code
- a good tool to get certain code snippets
- a satisfactory tool to automate some simple tasks

No one will dispute that (well written) VBA code is better than recorded code. All things equal, that is the preferable way to go. But not everyone here is a programmer or the desire to learn to code. And that is OK. We all have different skills and interests. That is what makes the world go round!