Shorten Code for repeated function use

[mel]

I use the following command to replace characters in a
string which are not allowed. I use this command up to 30
times to remove all of the unwanted characters. Is there
a way to set up an array and get the function to work
with one value after another in the array rather than
having a separate statement for each occurrence?

ReplNameChar = Application.WorksheetFunction.Substitute
(ReplNameChar, "&", "_and_") ' remove &

TIA

 

[Bob Phillips]

Do you mean like

myArray = Array ("&","/","\","^")

For i = LBound(myArray) To UBound(myArray)
ReplNameChar = Application.WorksheetFunction.Substitute
(ReplNameChar, myArray(i), "_and_") ' remove &
Next i

 

[David McRitchie]

Actually I think he means more like two character strings
of individual character substitutions, such usage has been
around in mainframe computing 1960's to present. Never
seen anything similar posted in VBA. PL/I and REXX it is Translate,
expect it to be same in SAS, in Assembler it is XLAT,
in COBOL it is TRANSFORM. You can actually specify only
the "to" string in REXX and the "from" string is the characters
x'00 through x'FF (Helps to translate between EBCDIC and
ASCII except that characters are not actually two way reversible).
http://mitvma.mit.edu/cmshelp.cgi?REXX TRANSLAT (ALL
http://www.scoug.com/OPENHOUSE/REXXINTRO/RXBISTTR1.4.HTML

You would have to write your own User Defined Function.

newString = translate(oldString, toString, fromString)

Function translate(oldString As String, toString As String, _
fromString As String) As String
'David McRitchie, 2005-03-08 programing
' http://groups.google.com/[email protected]
'limited to equal length from and to strings for now
'ref. http://mitvma.mit.edu/cmshelp.cgi?REXX TRANSLAT (ALL
Dim i As Long, pos As Long, str As String
str = ""
For i = 1 To Len(oldString)
'-- InStr([start, ]haystack, needle[, compare])
pos = InStr(1, fromString, Mid(oldString, i, 1), vbBinaryCompare)
If pos = 0 Then
str = str & Mid(oldString, i, 1)
Else
str = str & Mid(toString, i, 1)
End If
Next i
translate = str
End Function

 

[David McRitchie]

sorry code correction is:

Function translate(oldString As String, toString As String, _
fromString As String) As String
'David McRitchie, 2005-03-08 programing
' http://groups.google.com/[email protected]
'limited to equal length from and to strings for now
'ref. http://mitvma.mit.edu/cmshelp.cgi?REXX TRANSLAT (ALL
Dim i As Long, pos As Long, str As String
str = ""
For i = 1 To Len(oldString)
'InStr([start, ]haystack, needle[, compare])
pos = InStr(1, fromString, Mid(oldString, i, 1), vbBinaryCompare)
If pos = 0 Then
str = str & Mid(oldString, i, 1)
Else
str = str & Mid(toString, pos, 1) '-- corrected
End If
Next i
translate = str
End Function

 

[David McRitchie]

Seeing that the archived copy of the macro code is messed up on
Groups-beta-google.com I have the created a webpage for the macro.

TRANSLATE User Defined Function
"Simulate a mainframe type TRANSLATE function"
http://www.mvps.org/dmcritchie/excel/translate.htm


I have also put in some comments relating to the mess created by
groups-beta-google back in Nov. and how it keeps getting worse. The
code structure appears okay if you look at the original text in google
groups or in the actual thread in your newsbrowser, but it messed up
for those that look at Google Groups.