Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shorten Code for repeated function use
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shorten Code for repeated function use
Do you mean like
myArray = Array ("&","/","\","^") For i = LBound(myArray) To UBound(myArray) ReplNameChar = Application.WorksheetFunction.Substitute (ReplNameChar, myArray(i), "_and_") ' remove & Next i -- HTH Bob Phillips "mel" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shorten Code for repeated function use
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?RE...RANSLAT%20(ALL http://www.scoug.com/OPENHOUSE/REXXI...BISTTR1.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/groups?thre...0a% 40phx.gbl 'limited to equal length from and to strings for now 'ref. http://mitvma.mit.edu/cmshelp.cgi?RE...RANSLAT%20(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 -- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Bob Phillips" wrote in message ... Do you mean like myArray = Array ("&","/","\","^") For i = LBound(myArray) To UBound(myArray) ReplNameChar = Application.WorksheetFunction.Substitute (ReplNameChar, myArray(i), "_and_") ' remove & Next i -- HTH Bob Phillips "mel" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shorten Code for repeated function use
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/groups?thre...0a% 40phx.gbl 'limited to equal length from and to strings for now 'ref. http://mitvma.mit.edu/cmshelp.cgi?RE...RANSLAT%20(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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shorten Code for repeated function use
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. --- David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Which function will shorten IF-THEN-ELSE ? | Excel Worksheet Functions | |||
Which function will shorten IF-THEN-ELSE ? | Excel Worksheet Functions | |||
function for repeated cells | Excel Worksheet Functions | |||
Printing text in a repeated cell/row that is longer than repeated | Excel Discussion (Misc queries) | |||
Shorten Code? | Excel Programming |