Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
len limit of 255 of function. any workaround?
I am getting a VALUE error when I run this function and the input string is
longer than 255. Basicaly, I am wanting to strip out all control characters. Is there a better way to do that? TIA, Mc Public Function CleanKey(vData As String) As String Dim nChar As Long Dim sChar As String * 1 Dim nCharCode As Long Dim sNewData As String For nChar = 1 To Len(vData) sChar = Mid$(vData, nChar, 1) nCharCode = Asc(sChar) If nCharCode <= Asc("~") And nCharCode = Asc(" ") _ Then sNewData = sNewData & sChar Next nChar CleanKey = sNewData End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
len limit of 255 of function. any workaround?
Hi
Could you use the CLEAN function? Application.Worksheetfunctions.Clean(vData) read the help as clean doesn't clean everything (you'll need to check the nCharCode range of values you need). regards Paul On Apr 17, 10:48 am, "Mctabish" wrote: I am getting a VALUE error when I run this function and the input string is longer than 255. Basicaly, I am wanting to strip out all control characters. Is there a better way to do that? TIA, Mc Public Function CleanKey(vData As String) As String Dim nChar As Long Dim sChar As String * 1 Dim nCharCode As Long Dim sNewData As String For nChar = 1 To Len(vData) sChar = Mid$(vData, nChar, 1) nCharCode = Asc(sChar) If nCharCode <= Asc("~") And nCharCode = Asc(" ") _ Then sNewData = sNewData & sChar Next nChar CleanKey = sNewData End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
len limit of 255 of function. any workaround?
I don't think it's your function that's having the trouble with the length of
the string. Any chance you're retrieving a long string from a closed workbook, then trying to clean that. Excel will only bring back up to 255 characters from a closed workbook. Mctabish wrote: I am getting a VALUE error when I run this function and the input string is longer than 255. Basicaly, I am wanting to strip out all control characters. Is there a better way to do that? TIA, Mc Public Function CleanKey(vData As String) As String Dim nChar As Long Dim sChar As String * 1 Dim nCharCode As Long Dim sNewData As String For nChar = 1 To Len(vData) sChar = Mid$(vData, nChar, 1) nCharCode = Asc(sChar) If nCharCode <= Asc("~") And nCharCode = Asc(" ") _ Then sNewData = sNewData & sChar Next nChar CleanKey = sNewData End Function -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
len limit of 255 of function. any workaround?
Thanks! Thank did it!
Mc wrote in message oups.com... Hi Could you use the CLEAN function? Application.Worksheetfunctions.Clean(vData) read the help as clean doesn't clean everything (you'll need to check the nCharCode range of values you need). regards Paul On Apr 17, 10:48 am, "Mctabish" wrote: I am getting a VALUE error when I run this function and the input string is longer than 255. Basicaly, I am wanting to strip out all control characters. Is there a better way to do that? TIA, Mc Public Function CleanKey(vData As String) As String Dim nChar As Long Dim sChar As String * 1 Dim nCharCode As Long Dim sNewData As String For nChar = 1 To Len(vData) sChar = Mid$(vData, nChar, 1) nCharCode = Asc(sChar) If nCharCode <= Asc("~") And nCharCode = Asc(" ") _ Then sNewData = sNewData & sChar Next nChar CleanKey = sNewData End Function |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
len limit of 255 of function. any workaround?
Dave,
I am not calling for any closed wb. clean() did do what I wanted though! Thanks Mc "Dave Peterson" wrote in message ... I don't think it's your function that's having the trouble with the length of the string. Any chance you're retrieving a long string from a closed workbook, then trying to clean that. Excel will only bring back up to 255 characters from a closed workbook. Mctabish wrote: I am getting a VALUE error when I run this function and the input string is longer than 255. Basicaly, I am wanting to strip out all control characters. Is there a better way to do that? TIA, Mc Public Function CleanKey(vData As String) As String Dim nChar As Long Dim sChar As String * 1 Dim nCharCode As Long Dim sNewData As String For nChar = 1 To Len(vData) sChar = Mid$(vData, nChar, 1) nCharCode = Asc(sChar) If nCharCode <= Asc("~") And nCharCode = Asc(" ") _ Then sNewData = sNewData & sChar Next nChar CleanKey = sNewData End Function -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Workaround for HYPERLINK argument length limit | Excel Worksheet Functions | |||
Excel text export limit - 1024 per line (not cell), workaround? | Excel Discussion (Misc queries) | |||
Limit on logical function | Excel Discussion (Misc queries) | |||
busted workaround for 255 character limit in cells | Excel Programming | |||
IF Function Help due to 7 limit | Excel Worksheet Functions |