UDF runs in 2K, don't in 97

[CLR]

Hi All......\

I'm using this UDF in XL2k and it works just fine.......when I try the same
thing in XL97 it don't seem to work.......just returns #VALUE!
***********************************
Function KillText(cellinput) As String
Set cellinput = cellinput(1)
CharVal = ""
For i = 1 To Len(cellinput)
Select Case Mid(cellinput, i, 1)
Case 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, " ", "%"
CharVal = CharVal & Mid(cellinput, i, 1)
End Select
Next i
KillText = CharVal
End Function
********************************************

What I'm really trying to do is to delete the text characters out of a
selected column, leaving only the selected numerical characters
indicated........is there a better way?

TIA
Vaya con Dios,
Chuck, CABGx3

 

[Tom Ogilvy]

Nothing leaps off the page as being incompatible with xl97. Copy the
function to notepad. Then close and reopen xl97. In the new workbook,
insert a module and put in the code copied from notepad.

Use it in a cell.

Does it work?

 

[Jim Cone]

Tom, Chuck,

It would not work for me either in XL 97.
However, changing the return value of the Mid function from Variant to String, solved the problem...

Change from Mid to Mid$

Also, the variables CharVal and i should be declared.

Regards,
Jim Cone
San Francisco, USA

 

[CLR]

Hi Tom......

I copied the function to Notepad, deleted the module in that workbook and
closed it. I re-opened that workbook and inserted a new module and put that
function in it and it still did not work.

I then opened a new workbook with nothing in it at all and inserted a new
module and put the function in it. I put the =KillText(A1) in B1 and copied
down. In column A , I put various things,
In A1 I have nothing.....B1 displays blank
In A2 I have text only....B2 is #VALUE!
In A3 I have numbers only...B3 is the same numbers, but left aligned
IN A4 I have mixed string, numbers and text...B4 is #VALUE!

If this don't suggest anything to you, I'll try it at home tonight. I only
have 97 here at work and it's on a somewhat flaky machine, at home I have
both 97 and 2k on a good machine, so I'll bounce it back and forth and try
your suggestion there.....

Thanks,
Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Hi Jim.........

Thanks, I changed MID to MID$ both places in the code and it appears to work
in my test book. I have not tried it yet in my main book. I do not know how
to "declare the variables CharVal and i "......could you elaborate please?

Vaya con Dios,
Chuck, CABGx3

 

[Tom Ogilvy]

I guess it is a conversion problem - xl97 is pickier about that. Changing to
Mid$ is the easiest fix, but you could also make the Case statement like

Case "1","2","3" etc

Untested, but it should have the same effect. ( I don't have xl97 handy
right now).

 

[Tom Ogilvy]

***********************************
Function KillText(cellinput) As String
Dim i as long, CharVal as String
Set cellinput = cellinput(1)
CharVal = ""
For i = 1 To Len(cellinput)
Select Case Mid$(cellinput, i, 1)
Case 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, " ", "%"
CharVal = CharVal & Mid(cellinput, i, 1)
End Select
Next i
KillText = CharVal
End Function
********************************************

 

[Jim Cone]

Hello Chuck,

The first three lines would read...

Function KillText(cellinput) As String
Dim CharVal As String
Dim i As Long
...

Also,it is good practice to put
"Option Explicit" (no quote marks) at the top of every module.
You can go to Tools | Options | Editor (tab) and checkmark
"Require Variable Declaration" to have it done for you.

Regards,
Jim Cone

 

[Harlan Grove]

CLR wrote...

I'm using this UDF in XL2k and it works just fine.......when I try the same
thing in XL97 it don't seem to work.......just returns #VALUE!
***********************************
Function KillText(cellinput) As String
Set cellinput = cellinput(1)
CharVal = ""
For i = 1 To Len(cellinput)
Select Case Mid(cellinput, i, 1)
Case 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, " ", "%"
CharVal = CharVal & Mid(cellinput, i, 1)
End Select
Next i
KillText = CharVal
End Function
********************************************

....

I'd suggest using a different approach entirely.


Function KillText(cellinput As Range) As String
Dim i As Long, v As String, c As String * 1
v = cellinput(1).Value
For i = 1 To Len(v)
c = Mid(v, i, 1)
If c Like "[0-9 %]" Then KillText = KillText & c
Next i
End Function


works (tested) in XL97 and should work in later versions too. Then
again, I'd generalize it to take the set of characters to retain as an
argument.

 

[CLR]

Many thanks Tom and Jim...............
It all works well now in 97 and 2k..........life is good.
I really appreciate your time and patience in coming to my assistance, and
seeing me through 'til it worked. You guys are great, and really make this
Newsgroup system a wonderful thing. Thanks for being there............

Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Harlan..........

Thank you very much for your version. I've tested it in my situation using
both 97 and 2k and it came through with flying colors as well. It will
definately be added to my arsenal.

One of the beauties of this Newsgroup system is of course the opportunity
for the OP's to see different ways of doing the same thing, and hopefully
learning from studying them. To that end, I don't really understand what to
do about your closing sentence, "Then again, I'd generalize it to take the
set of characters to retain as an argument."............if you would be so
kind as to elaborate.....

Thanks again,
Vaya con Dios,
Chuck, CABGx3

CLR wrote...

I'm using this UDF in XL2k and it works just fine.......when I try the same
thing in XL97 it don't seem to work.......just returns #VALUE!
***********************************
Function KillText(cellinput) As String
Set cellinput = cellinput(1)
CharVal = ""
For i = 1 To Len(cellinput)
Select Case Mid(cellinput, i, 1)
Case 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, " ", "%"
CharVal = CharVal & Mid(cellinput, i, 1)
End Select
Next i
KillText = CharVal
End Function
********************************************

...

I'd suggest using a different approach entirely.


Function KillText(cellinput As Range) As String
Dim i As Long, v As String, c As String * 1
v = cellinput(1).Value
For i = 1 To Len(v)
c = Mid(v, i, 1)
If c Like "[0-9 %]" Then KillText = KillText & c
Next i
End Function


works (tested) in XL97 and should work in later versions too. Then
again, I'd generalize it to take the set of characters to retain as an
argument.

 

[Tom Ogilvy]

usage
=KillText(A1,"0-9 %")

Function KillText(cellinput As Range, sCrit as String) As String
Dim i As Long, v As String, c As String * 1
v = cellinput(1).Value
For i = 1 To Len(v)
c = Mid(v, i, 1)
If c Like "[" & sCrit & "]" Then KillText = KillText & c
Next i
End Function

--
Regards,
Tom Ogilvy

Harlan..........

Thank you very much for your version. I've tested it in my situation using
both 97 and 2k and it came through with flying colors as well. It will
definately be added to my arsenal.

One of the beauties of this Newsgroup system is of course the opportunity
for the OP's to see different ways of doing the same thing, and hopefully
learning from studying them. To that end, I don't really understand what to
do about your closing sentence, "Then again, I'd generalize it to take the
set of characters to retain as an argument."............if you would be so
kind as to elaborate.....

Thanks again,
Vaya con Dios,
Chuck, CABGx3

CLR wrote...

I'm using this UDF in XL2k and it works just fine.......when I try the same
thing in XL97 it don't seem to work.......just returns #VALUE!
***********************************
Function KillText(cellinput) As String
Set cellinput = cellinput(1)
CharVal = ""
For i = 1 To Len(cellinput)
Select Case Mid(cellinput, i, 1)
Case 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, " ", "%"
CharVal = CharVal & Mid(cellinput, i, 1)
End Select
Next i
KillText = CharVal
End Function
********************************************

...

I'd suggest using a different approach entirely.


Function KillText(cellinput As Range) As String
Dim i As Long, v As String, c As String * 1
v = cellinput(1).Value
For i = 1 To Len(v)
c = Mid(v, i, 1)
If c Like "[0-9 %]" Then KillText = KillText & c
Next i
End Function


works (tested) in XL97 and should work in later versions too. Then
again, I'd generalize it to take the set of characters to retain as an
argument.

 

[CLR]

Thank you kind Sir.........it would have been a very long time before "that"
would have dawned on me...........if ever.........

Say, just as a point of interest........while going through this exercize, I
also discovered that "Data > TextToColumns recordings" done in XL2k, do not
work in XL97 either.........it seems while recording in 2k it adds a final
element to the sequence that 97 does not like......"
TrailingMinusNumbers:=True".........it's been a tough couple of days <g>

Thanks again Tom,
Vaya con Dios,
Chuck, CABGx3

usage
=KillText(A1,"0-9 %")

Function KillText(cellinput As Range, sCrit as String) As String
Dim i As Long, v As String, c As String * 1
v = cellinput(1).Value
For i = 1 To Len(v)
c = Mid(v, i, 1)
If c Like "[" & sCrit & "]" Then KillText = KillText & c
Next i
End Function

--
Regards,
Tom Ogilvy

Harlan..........

Thank you very much for your version. I've tested it in my situation using
both 97 and 2k and it came through with flying colors as well. It will
definately be added to my arsenal.

One of the beauties of this Newsgroup system is of course the opportunity
for the OP's to see different ways of doing the same thing, and hopefully
learning from studying them. To that end, I don't really understand

what

to
do about your closing sentence, "Then again, I'd generalize it to take the
set of characters to retain as an argument."............if you would be so
kind as to elaborate.....

Thanks again,
Vaya con Dios,
Chuck, CABGx3

CLR wrote...
I'm using this UDF in XL2k and it works just fine.......when I try the
same
thing in XL97 it don't seem to work.......just returns #VALUE!
***********************************
Function KillText(cellinput) As String
Set cellinput = cellinput(1)
CharVal = ""
For i = 1 To Len(cellinput)
Select Case Mid(cellinput, i, 1)
Case 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, " ", "%"
CharVal = CharVal & Mid(cellinput, i, 1)
End Select
Next i
KillText = CharVal
End Function
********************************************
...

I'd suggest using a different approach entirely.


Function KillText(cellinput As Range) As String
Dim i As Long, v As String, c As String * 1
v = cellinput(1).Value
For i = 1 To Len(v)
c = Mid(v, i, 1)
If c Like "[0-9 %]" Then KillText = KillText & c
Next i
End Function


works (tested) in XL97 and should work in later versions too. Then
again, I'd generalize it to take the set of characters to retain as an
argument.

 

[Tom Ogilvy]

You will find a lot of that. If you just use the default setting, then
those new parts can be removed and it will work in xl97 and later versions.
Of course, it is easier to record in xl97/develop in xl97 and you will
usually find forward compatibility easier.

--
Regards,
Tom Ogilvy

Thank you kind Sir.........it would have been a very long time before "that"
would have dawned on me...........if ever.........

Say, just as a point of interest........while going through this exercize, I
also discovered that "Data > TextToColumns recordings" done in XL2k, do not
work in XL97 either.........it seems while recording in 2k it adds a final
element to the sequence that 97 does not like......"
TrailingMinusNumbers:=True".........it's been a tough couple of days <g>

Thanks again Tom,
Vaya con Dios,
Chuck, CABGx3

usage
=KillText(A1,"0-9 %")

Function KillText(cellinput As Range, sCrit as String) As String
Dim i As Long, v As String, c As String * 1
v = cellinput(1).Value
For i = 1 To Len(v)
c = Mid(v, i, 1)
If c Like "[" & sCrit & "]" Then KillText = KillText & c
Next i
End Function

--
Regards,
Tom Ogilvy

Harlan..........

Thank you very much for your version. I've tested it in my situation using
both 97 and 2k and it came through with flying colors as well. It will
definately be added to my arsenal.

One of the beauties of this Newsgroup system is of course the opportunity
for the OP's to see different ways of doing the same thing, and hopefully
learning from studying them. To that end, I don't really understand

what

to
do about your closing sentence, "Then again, I'd generalize it to take the
set of characters to retain as an argument."............if you would

be

so

kind as to elaborate.....

Thanks again,
Vaya con Dios,
Chuck, CABGx3


CLR wrote...
I'm using this UDF in XL2k and it works just fine.......when I try the
same
thing in XL97 it don't seem to work.......just returns #VALUE!
***********************************
Function KillText(cellinput) As String
Set cellinput = cellinput(1)
CharVal = ""
For i = 1 To Len(cellinput)
Select Case Mid(cellinput, i, 1)
Case 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, " ", "%"
CharVal = CharVal & Mid(cellinput, i, 1)
End Select
Next i
KillText = CharVal
End Function
********************************************
...

I'd suggest using a different approach entirely.


Function KillText(cellinput As Range) As String
Dim i As Long, v As String, c As String * 1
v = cellinput(1).Value
For i = 1 To Len(v)
c = Mid(v, i, 1)
If c Like "[0-9 %]" Then KillText = KillText & c
Next i
End Function


works (tested) in XL97 and should work in later versions too. Then
again, I'd generalize it to take the set of characters to retain as an
argument.

 

[CLR]

I hear ya Tom..........I prefer 2k as my version of choice, but for work
with this company, I just may have to revert to 97 as you suggest to
minimize my problems.......

Thanks again for your time and advice....

Vaya con Dios,
Chuck, CABGx3

You will find a lot of that. If you just use the default setting, then
those new parts can be removed and it will work in xl97 and later versions.
Of course, it is easier to record in xl97/develop in xl97 and you will
usually find forward compatibility easier.

--
Regards,
Tom Ogilvy

Thank you kind Sir.........it would have been a very long time before "that"
would have dawned on me...........if ever.........

Say, just as a point of interest........while going through this

exercize,

I
also discovered that "Data > TextToColumns recordings" done in XL2k, do not
work in XL97 either.........it seems while recording in 2k it adds a final
element to the sequence that 97 does not like......"
TrailingMinusNumbers:=True".........it's been a tough couple of days <g>

Thanks again Tom,
Vaya con Dios,
Chuck, CABGx3

usage
=KillText(A1,"0-9 %")

Function KillText(cellinput As Range, sCrit as String) As String
Dim i As Long, v As String, c As String * 1
v = cellinput(1).Value
For i = 1 To Len(v)
c = Mid(v, i, 1)
If c Like "[" & sCrit & "]" Then KillText = KillText & c
Next i
End Function

--
Regards,
Tom Ogilvy


Harlan..........

Thank you very much for your version. I've tested it in my situation
using
both 97 and 2k and it came through with flying colors as well. It will
definately be added to my arsenal.

One of the beauties of this Newsgroup system is of course the opportunity
for the OP's to see different ways of doing the same thing, and hopefully
learning from studying them. To that end, I don't really understand what
to
do about your closing sentence, "Then again, I'd generalize it to

take
the

set of characters to retain as an argument."............if you would

be

so
kind as to elaborate.....

Thanks again,
Vaya con Dios,
Chuck, CABGx3


CLR wrote...
I'm using this UDF in XL2k and it works just fine.......when I

try
the

same
thing in XL97 it don't seem to work.......just returns #VALUE!
***********************************
Function KillText(cellinput) As String
Set cellinput = cellinput(1)
CharVal = ""
For i = 1 To Len(cellinput)
Select Case Mid(cellinput, i, 1)
Case 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, " ", "%"
CharVal = CharVal & Mid(cellinput, i, 1)
End Select
Next i
KillText = CharVal
End Function
********************************************
...

I'd suggest using a different approach entirely.


Function KillText(cellinput As Range) As String
Dim i As Long, v As String, c As String * 1
v = cellinput(1).Value
For i = 1 To Len(v)
c = Mid(v, i, 1)
If c Like "[0-9 %]" Then KillText = KillText & c
Next i
End Function


works (tested) in XL97 and should work in later versions too. Then
again, I'd generalize it to take the set of characters to retain

as

an