len limit of 255 of function. any workaround?

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
 
P

paul.robinson

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
 
D

Dave Peterson

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.
 
M

Mctabish

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
 
M

Mctabish

Dave,

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

Thanks
Mc
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top