I made a calendar file on Excel at a computer with Windows XP and a version
of Excel earlier than the 2007 version designed for use with windows vista.
When I transferred the file to my home computer which uses these later
Microsoft applications (Vista and Office 2007), a user function I had made to
display the ordinal form of cardinal numbers gave me the useless and
ubiquitous error of #NAME? in every cell I had used it. The function is...

Function Ordinal(CardNum) As String
OrdEnd = "th": CardNum = Int(CardNum)
If CardNum Mod 10 = 1 Then OrdEnd = "st"
If CardNum Mod 10 = 2 Then OrdEnd = "nd"
If CardNum Mod 10 = 3 Then OrdEnd = "rd"
Teens = CardNum Mod 100
If Teens 10 And Teens < 14 Then OrdEnd = "th"
Ordinal = Format(CardNum) + OrdEnd
End Function

This function works just fine in its file of origin. But the file I brought
home fails to, well, function with the very same macro procedure. I am quite
perturbed. Help!
--
If it ain''t broke, don''t fix it!

What in the Samm Dickens,

Just guessing (I don't have Vista or XL12), but try declaring all variables...
Function Ordinal(ByRef CardNum As Double) As String
Dim OrdEnd As String
Dim Teens As Double
'--
Also, make sure the code is in a standard module.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Samm Dickens"
wrote in message
I made a calendar file on Excel at a computer with Windows XP and a version
of Excel earlier than the 2007 version designed for use with windows vista.
When I transferred the file to my home computer which uses these later
Microsoft applications (Vista and Office 2007), a user function I had made to
display the ordinal form of cardinal numbers gave me the useless and
ubiquitous error of #NAME? in every cell I had used it. The function is...

Function Ordinal(CardNum) As String
OrdEnd = "th": CardNum = Int(CardNum)
If CardNum Mod 10 = 1 Then OrdEnd = "st"
If CardNum Mod 10 = 2 Then OrdEnd = "nd"
If CardNum Mod 10 = 3 Then OrdEnd = "rd"
Teens = CardNum Mod 100
If Teens 10 And Teens < 14 Then OrdEnd = "th"
Ordinal = Format(CardNum) + OrdEnd
End Function

This function works just fine in its file of origin. But the file I brought
home fails to, well, function with the very same macro procedure. I am quite
perturbed. Help!
--
If it ain''t broke, don''t fix it!

I tried declaring all the variables, without luck. I think its in a standard
module, but then I don't know what a nonstandard module is. Thanks for the
suggestions.
--
If it ain''''t broke, don''''t fix it!


"Jim Cone" wrote:

What in the Samm Dickens,

Just guessing (I don't have Vista or XL12), but try declaring all variables...
Function Ordinal(ByRef CardNum As Double) As String
Dim OrdEnd As String
Dim Teens As Double
'--
Also, make sure the code is in a standard module.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Samm Dickens"
wrote in message
I made a calendar file on Excel at a computer with Windows XP and a version
of Excel earlier than the 2007 version designed for use with windows vista.
When I transferred the file to my home computer which uses these later
Microsoft applications (Vista and Office 2007), a user function I had made to
display the ordinal form of cardinal numbers gave me the useless and
ubiquitous error of #NAME? in every cell I had used it. The function is...

Function Ordinal(CardNum) As String
OrdEnd = "th": CardNum = Int(CardNum)
If CardNum Mod 10 = 1 Then OrdEnd = "st"
If CardNum Mod 10 = 2 Then OrdEnd = "nd"
If CardNum Mod 10 = 3 Then OrdEnd = "rd"
Teens = CardNum Mod 100
If Teens 10 And Teens < 14 Then OrdEnd = "th"
Ordinal = Format(CardNum) + OrdEnd
End Function

This function works just fine in its file of origin. But the file I brought
home fails to, well, function with the very same macro procedure. I am quite
perturbed. Help!
--
If it ain''t broke, don''t fix it!

In the Visual Basic Editor (VBE), on the Menu bar, click Insert then Module.
What you get is a standard/regular module. Everything else is a Class module.
Class modules belong to objects (worksheets, workbooks, forms, custom objects).
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Samm Dickens" wrote in message
I tried declaring all the variables, without luck. I think its in a standard
module, but then I don't know what a nonstandard module is. Thanks for the
suggestions.
--
If it ain''''t broke, don''''t fix it!


"Jim Cone" wrote:
What in the Samm Dickens,
Just guessing (I don't have Vista or XL12), but try declaring all variables...
Function Ordinal(ByRef CardNum As Double) As String
Dim OrdEnd As String
Dim Teens As Double
'--
Also, make sure the code is in a standard module.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

The first place to look is in ToolsReferences in the VBIDE, and see if any
of the references are tagged as MISSING. If so, uncheck them.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Samm Dickens" wrote in message
...
I made a calendar file on Excel at a computer with Windows XP and a version
of Excel earlier than the 2007 version designed for use with windows
vista.
When I transferred the file to my home computer which uses these later
Microsoft applications (Vista and Office 2007), a user function I had made
to
display the ordinal form of cardinal numbers gave me the useless and
ubiquitous error of #NAME? in every cell I had used it. The function
is...

Function Ordinal(CardNum) As String
OrdEnd = "th": CardNum = Int(CardNum)
If CardNum Mod 10 = 1 Then OrdEnd = "st"
If CardNum Mod 10 = 2 Then OrdEnd = "nd"
If CardNum Mod 10 = 3 Then OrdEnd = "rd"
Teens = CardNum Mod 100
If Teens 10 And Teens < 14 Then OrdEnd = "th"
Ordinal = Format(CardNum) + OrdEnd
End Function

This function works just fine in its file of origin. But the file I
brought
home fails to, well, function with the very same macro procedure. I am
quite
perturbed. Help!
--
If it ain''t broke, don''t fix it!

In the Visual Basic IDE, the references under tools all look okay, none of
them are visibly identified as "missing". Thanks for that idea.
--
If it ain''''t broke, don''''t fix it!


"Bob Phillips" wrote:

The first place to look is in ToolsReferences in the VBIDE, and see if any
of the references are tagged as MISSING. If so, uncheck them.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Samm Dickens" wrote in message
...
I made a calendar file on Excel at a computer with Windows XP and a version
of Excel earlier than the 2007 version designed for use with windows
vista.
When I transferred the file to my home computer which uses these later
Microsoft applications (Vista and Office 2007), a user function I had made
to
display the ordinal form of cardinal numbers gave me the useless and
ubiquitous error of #NAME? in every cell I had used it. The function
is...

Function Ordinal(CardNum) As String
OrdEnd = "th": CardNum = Int(CardNum)
If CardNum Mod 10 = 1 Then OrdEnd = "st"
If CardNum Mod 10 = 2 Then OrdEnd = "nd"
If CardNum Mod 10 = 3 Then OrdEnd = "rd"
Teens = CardNum Mod 100
If Teens 10 And Teens < 14 Then OrdEnd = "th"
Ordinal = Format(CardNum) + OrdEnd
End Function

This function works just fine in its file of origin. But the file I
brought
home fails to, well, function with the very same macro procedure. I am
quite
perturbed. Help!
--
If it ain''t broke, don''t fix it!

Just so you know, the function you posted works fine in my copy of XL2007.
So, I am guessing one of the comments Jim or Bob gave you applies... my bet
is that you didn't put it in a Standard Module.

By the way, you might find this one-liner Ordinal function I developed back
in the compiled VB world (modified for use in Excel) interesting...

Function Ordinal(Cell As Range) As String
Ordinal = 0 + Cell.Value & Mid$("thstndrdthththththth", _
1 - 2 * ((Cell.Value) Mod 10) * _
(Abs((Cell.Value) Mod 100 - 12) 1), 2)
End Function

Rick


"Samm Dickens" wrote in message
...
I made a calendar file on Excel at a computer with Windows XP and a version
of Excel earlier than the 2007 version designed for use with windows
vista.
When I transferred the file to my home computer which uses these later
Microsoft applications (Vista and Office 2007), a user function I had made
to
display the ordinal form of cardinal numbers gave me the useless and
ubiquitous error of #NAME? in every cell I had used it. The function
is...

Function Ordinal(CardNum) As String
OrdEnd = "th": CardNum = Int(CardNum)
If CardNum Mod 10 = 1 Then OrdEnd = "st"
If CardNum Mod 10 = 2 Then OrdEnd = "nd"
If CardNum Mod 10 = 3 Then OrdEnd = "rd"
Teens = CardNum Mod 100
If Teens 10 And Teens < 14 Then OrdEnd = "th"
Ordinal = Format(CardNum) + OrdEnd
End Function

This function works just fine in its file of origin. But the file I
brought
home fails to, well, function with the very same macro procedure. I am
quite
perturbed. Help!
--
If it ain''t broke, don''t fix it!

That's an amazing formula to me. That kind of formula is so eloquent (says a
lot with a little) and escapes my abilities thus far. I'll use it instead of
mine if you don't mind. Can't get my function working yet with the other
ideas; think I'll erase the entire module and start from scratch. Obviously,
moving the file from one computer to another has caused,probably, a security
glitch. My home computer has let loose the white cells to fight this strange
invader. But I want to retain some security over unwelcomed macros. I know
whatever is wrong has got to be some little thing working the way its
supposed to work with a situation someone should have anticipated, but
didn't. Thanks for your response!
--
If it ain''''t broke, don''''t fix it!


"Rick Rothstein (MVP - VB)" wrote:

Just so you know, the function you posted works fine in my copy of XL2007.
So, I am guessing one of the comments Jim or Bob gave you applies... my bet
is that you didn't put it in a Standard Module.

By the way, you might find this one-liner Ordinal function I developed back
in the compiled VB world (modified for use in Excel) interesting...

Function Ordinal(Cell As Range) As String
Ordinal = 0 + Cell.Value & Mid$("thstndrdthththththth", _
1 - 2 * ((Cell.Value) Mod 10) * _
(Abs((Cell.Value) Mod 100 - 12) 1), 2)
End Function

Rick


"Samm Dickens" wrote in message
...
I made a calendar file on Excel at a computer with Windows XP and a version
of Excel earlier than the 2007 version designed for use with windows
vista.
When I transferred the file to my home computer which uses these later
Microsoft applications (Vista and Office 2007), a user function I had made
to
display the ordinal form of cardinal numbers gave me the useless and
ubiquitous error of #NAME? in every cell I had used it. The function
is...

Function Ordinal(CardNum) As String
OrdEnd = "th": CardNum = Int(CardNum)
If CardNum Mod 10 = 1 Then OrdEnd = "st"
If CardNum Mod 10 = 2 Then OrdEnd = "nd"
If CardNum Mod 10 = 3 Then OrdEnd = "rd"
Teens = CardNum Mod 100
If Teens 10 And Teens < 14 Then OrdEnd = "th"
Ordinal = Format(CardNum) + OrdEnd
End Function

This function works just fine in its file of origin. But the file I
brought
home fails to, well, function with the very same macro procedure. I am
quite
perturbed. Help!
--
If it ain''t broke, don''t fix it!

By the way, you might find this one-liner Ordinal function I developed
back
in the compiled VB world (modified for use in Excel) interesting...

Function Ordinal(Cell As Range) As String
Ordinal = 0 + Cell.Value & Mid$("thstndrdthththththth", _
1 - 2 * ((Cell.Value) Mod 10) * _
(Abs((Cell.Value) Mod 100 - 12) 1), 2)
End Function

That's an amazing formula to me. That kind of formula is so eloquent
(says a
lot with a little) and escapes my abilities thus far.

I'm not so sure about the eloquent part, but thank you for the nice words.
Let's see if we can short-circuit your search for why my function works. At
its most basic level, the one-liner breaks down to this...

Ordinal = 0 + Cell.Value & Mid$("thstndrdthththththth", X, 2)

where X is what I'll call "the 'ugly' looking expression" from now on. Since
concatenation has a lower precedence than addition, zero will be added to
the value from the cell first (this forces a blank cell to zero, which is
what your function does) and then the results of the Mid function call will
be concatenated onto that. The Mid function call is straight forward
enough... whatever value X takes on is used to find the starting location
for a substring within that supplied String constant and the 2, of course,
says the returned substring should be two characters long. Notice that the
String constant is made up of the ordinal suffixes for the numbers zero
through nine lumped together in order.

Okay, so how does the expression X works. First, lets isolate the expression
so we can examine it...

1 - 2 * ((Cell.Value) Mod 10) * (Abs((Cell.Value) Mod 100 - 12) 1)

Because the ordinal suffixes are each two characters long, we need a
mathematical expression that returns 1, 3, 5, 7, etc. (the starting position
in the String constant) for the numerical values 0, 1, 2, 3, etc. (the last
digit in the number from the cell). That mathematical expression is this...

Position = 1 + 2 * Digit

If Digit equals 0, Position equates to 1; if Digit equals 1, Position
equates to 3; if Digit equals 2, Position equates to 5, etc.

Okay, so we can see where the 1 and the 2 come from in our ugly expression,
but why the minus sign and not a plus sign? Well, one of the terms being
multiplied in our ugly expression is a logical expression (returns True or
False) and, in VB/VBA, True values equate to -1 (False values to 0)... in
order to get our plus sign when the logical expression is True, we need a
minus sign to multiply the returned -1 (True) value by in order to make it a
plus value.

So, that explains the 1, the minus sign and the 2... let's now look at the
two terms the 2 is being muliplied by. First, is this....

((Cell.Value) Mod 10)

You alread know what this does because you used it in your own function. For
those reading this response from the archive, the above Mod operator divides
the Cell.Value by 10 and returns the remainder form that division. That
means everything is stripped off of the Cell.Value except for its last
digit. Notice those remainders are 0, 1, 2, 3, etc. These are the same
values I discused a couple of paragraphs ago when we talked about finding
the starting position of the substring. (That number multiplied by 2 and
added to 1 gives the starting position for the substring in the String
constant of suffixes.) This last digit is the correct positional value for
17 of the first 20 numbers... the problem three numbers being 11, 12 and 13.
These numbers do not take the same ordinal suffixes as do 1, 2 and 3, so
when the Cell.Value's last two digits are 11, 12 or 13, we need to modify
the number we multiply the 2 by in order to get the correct suffix. Also
note that within every block of 100 number numbers, those ending with 11, 12
and 13 are the only ones where the suffix "breaks the rules".

Okay, this brings us to the second expression which the 2 is being
multiplied by. This expression is the logical expression we talked about
earlier...

(Abs((Cell.Value) Mod 100 - 12) 1)

In the same way Mod 10 returned the last digit from the Cell.Value, Mod 100
returns the last 2 digits. Whatever value that is, we subtract 12 from it
and then take the Absolute value of that result (the Abs function simply
removes minus signs from negative values). We then check that result for
being greater than 1. Why? Think about it, of the 100 possible last two
digits of a number, only 11, 12 and 13 will be 1 or less if 12 is subtracted
from them... all other last two digits, when 12 is subtracted from them,
will have an absolute difference greater than 1... and in those cases, the
last digit takes a normal ordinal suffix; hence, we test the subtraction
against 1. If the difference is less than or equal to 1, the logical
expression evaluates to 0 (False) so that the product of the numbers with
the 2 is zero... and, thus, our ugly expression evaluates to 1 for last two
digits of 11, 12 and 13. This means these numbers use the same ordinal
suffix as 0 does... which, for them, is the correct suffix for them.


I'll use it instead of mine if you don't mind.

Of course I don't mind... anything I post in a newsgroup is free for the
reader to use.


Can't get my function working yet with the other ideas

I'm still surprised by this. As I said, your originally posted code works
perfectly for me when place in a Standard Module, and when I comment out my
Option Explicit statement<g (you should really consider using variable
declaration statements and not relying on default values for Objects; such
as the Value property of the Range statement).


Rick

Try jumpstarting the functions. Replace "=" with "=".

--
Tim Zych
SF, CA

"Samm Dickens" wrote in message
...
I made a calendar file on Excel at a computer with Windows XP and a version
of Excel earlier than the 2007 version designed for use with windows
vista.
When I transferred the file to my home computer which uses these later
Microsoft applications (Vista and Office 2007), a user function I had made
to
display the ordinal form of cardinal numbers gave me the useless and
ubiquitous error of #NAME? in every cell I had used it. The function
is...

Function Ordinal(CardNum) As String
OrdEnd = "th": CardNum = Int(CardNum)
If CardNum Mod 10 = 1 Then OrdEnd = "st"
If CardNum Mod 10 = 2 Then OrdEnd = "nd"
If CardNum Mod 10 = 3 Then OrdEnd = "rd"
Teens = CardNum Mod 100
If Teens 10 And Teens < 14 Then OrdEnd = "th"
Ordinal = Format(CardNum) + OrdEnd
End Function

This function works just fine in its file of origin. But the file I
brought
home fails to, well, function with the very same macro procedure. I am
quite
perturbed. Help!
--
If it ain''t broke, don''t fix it!

I tried this without success, but thanks for the idea.
--
If it ain''''t broke, don''''t fix it!


"Tim Zych" wrote:

Try jumpstarting the functions. Replace "=" with "=".

--
Tim Zych
SF, CA

"Samm Dickens" wrote in message
...
I made a calendar file on Excel at a computer with Windows XP and a version
of Excel earlier than the 2007 version designed for use with windows
vista.
When I transferred the file to my home computer which uses these later
Microsoft applications (Vista and Office 2007), a user function I had made
to
display the ordinal form of cardinal numbers gave me the useless and
ubiquitous error of #NAME? in every cell I had used it. The function
is...

Function Ordinal(CardNum) As String
OrdEnd = "th": CardNum = Int(CardNum)
If CardNum Mod 10 = 1 Then OrdEnd = "st"
If CardNum Mod 10 = 2 Then OrdEnd = "nd"
If CardNum Mod 10 = 3 Then OrdEnd = "rd"
Teens = CardNum Mod 100
If Teens 10 And Teens < 14 Then OrdEnd = "th"
Ordinal = Format(CardNum) + OrdEnd
End Function

This function works just fine in its file of origin. But the file I
brought
home fails to, well, function with the very same macro procedure. I am
quite
perturbed. Help!
--
If it ain''t broke, don''t fix it!

On Sun, 23 Dec 2007 06:58:00 -0800, Samm Dickens wrote:

I made a calendar file on Excel at a computer with Windows XP and a version
of Excel earlier than the 2007 version designed for use with windows vista.
When I transferred the file to my home computer which uses these later
Microsoft applications (Vista and Office 2007), a user function I had made to
display the ordinal form of cardinal numbers gave me the useless and
ubiquitous error of #NAME? in every cell I had used it. The function is...

Function Ordinal(CardNum) As String
OrdEnd = "th": CardNum = Int(CardNum)
If CardNum Mod 10 = 1 Then OrdEnd = "st"
If CardNum Mod 10 = 2 Then OrdEnd = "nd"
If CardNum Mod 10 = 3 Then OrdEnd = "rd"
Teens = CardNum Mod 100
If Teens 10 And Teens < 14 Then OrdEnd = "th"
Ordinal = Format(CardNum) + OrdEnd
End Function

This function works just fine in its file of origin. But the file I brought
home fails to, well, function with the very same macro procedure. I am quite
perturbed. Help!


Is it possible that there is another file on this new computer that has a
function with the same name? That will sometimes confuse XL.
--ron