First of all, let me note that your code looks good already. There are only a few things I would change:
It might be a good idea to turn the (Public) Sub into a Private Sub parameterized by
arrForbidden, which then is called by a new Public Sub passing in your special values. This would separate the configuration from the actual algorithm.
The name of the sub and its parameters could be improved by replacing them with something more expressive, like
RemoveAllForbiddenCharacters instead of
removalColumn instead of
TEST_COLUMN. (Well, the last one is not very good, but nothing better came to my mind immediately.)
I am personally not a fan of declaring multiple variables on one line or initializing on the same line because it often hurts readability. However, here it does not really hurt.
Now, I would like to present a number of different approaches to your problem. Some of these can even be combined for better results.
Using an Array to Query and Write Data
Instead of directly iterating over the cells in the collumn and updating every cell, you can read in the entire range into a two-dimensional (Variant) array using the
Value property of the range, or even better the
Value2 property, which is faster and usually safer. (See this blog post.)
Then, you can iterate over the array and finally paste it back by assigning to the
Value property of the range.
This approach is faster then updating each cell because accessing the cells in a worksheet is rather slow.
Using Regular Expressions
Instead of looping over your array, you could use a regular expression. The VBA support for these is in the library 'Microsoft VBScript Regular Expressions 5.5'. An extensive description on how to use them can be found in the answer to this question on Stack Overflow.
Basically, what you would do is set up the regex matcher with a pattern string—in your case,
"[\\/:%'*?<>|"".]". Next, you would use the
Replace function of the regex object to replace all occurrences of the forbidden characters in one go.
I do not know whether this is really much faster than the simple loop in your case. That pretty much depends on how well optimized the regex
Replace procedure is. But either way, it is a little more expressive and you could reuse the code to do all sorts of more elaborate substitutions.
Using User-Defined Functions
Instead of using a sub, you could write a function just for replacing all forbidden characters in a cell. Then, you could use this in some new column in the worksheets to automatically produce the cleaned-up version of your column in that new column. However, this would probably require you to actually reformat the worksheets before uploading to ALM (assuming ALM requires a fixed sheet layout for the import).
Personally, I do not really like this approach as it pollutes the worksheets with helper columns. However, if the need arises to remove the forbidden characters in more places, writing such a function might be the way to go.
Using Excel Functions
If you do not want to use VBA at all, you can modify the previous approach to use a suitable Excel workbook function. The base function for this approach is
SUBSTITUTE, which replaces occurrences of one string in another by a specified value.
A simple approach would be to nest calls to
SUBSTITUTE, one for each character to replace. However, this very easily becomes completely unreadable. Moreover, there is an upper limit on how long a formula is allowed to be. (OK, this Microsoft page says the limit is now over 8000 characters, so this should not really be a problem.)
There might be a nicer way to reduce the nested formulas into one array formula, but I personally don't know how to do it.