len limit of 255 of function. any workaround?

  • Thread starter Thread starter Mctabish
  • Start date Start date
M

Mctabish

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
 
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
 
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.
 
Thanks! Thank did it!
Mc

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
Click to expand...
 
Dave,

I am not calling for any closed wb.
clean() did do what I wanted though!

Thanks
Mc
 
Back
Top