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

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

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

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

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