Visual Basic

[John E. McLoughlin]

I am hopeful that a member who is familiar with IsNumeric will see this posting and will be able to provide a solution to my problem for which I would be most grateful.

For reasons that I will not labour, I have transferred my focus from Generations to Reunion. To that end, I now have a Mac Mini, Reunion and Office 2004 for Mac, as well as my Windows PC. Incidentally, it is pertinent that Excel 2004 for Mac is said to be compatible with Excel 2003 for Windows.

For some time now, I have transcribed pertinent entries from census pages into Excel spreadsheets and, to calculate the estimated year of birth, I wrote a procedure function. In certain UK Censuses, the ages of men and women are recorded in separate columns and the ages of infants less than one year are given in months (7 m), weeks (3 w) or days (5 d). With this in mind, the following code depicts the area of my function that is problematic:

Function yob71(m_age, f_age) ***************** ‘Year of Birth – UK 1871 Census
If IsNumeric(m_age) = True And m_age > 0 Then
*** yob71 = 1871 - m_age – 1 ***************** ‘Where m_age is age of male
ElseIf IsNumeric(f_age) = True And f_age > 0 Then
*** yob71 = 1871 - f_age – 1 ****************** ‘Where f_age is age of female
ElseIf IsNumeric(m_age) = False Then *********** ‘Where m-age < 1 (9 m, 6 d or the like
*** yob71 = "Baby Boy" *********************** ‘Baby Boy represents a Select Case procedure
ElseIf IsNumeric(f_age) = False Then************* ‘Where m-age < 1 (9 m, 6 d or the like
*** yob71 = "Baby Girl" *********************** ‘Baby Girl represents a Select Case procedure
Else: yob71 = ""
End If
End Function

Whilst this function works on my PC under Windows it fails on my Mac Mini. The problem seems to lie with the fact that, in Excel 2004 for Mac, a non-numeric variable passed to IsNumeric generates a vbError (10), which is manifest by a #Value error and an explanation that “A value used in the formula is of the wrong data type”.

I thought, perhaps, a solution might be found through the CVErr function but I cannot see a way of harnessing it to that end.

[Legolas]

Could you please provide me with a few samples and tell exactly what error message occurs and when it occurs ? May be I can help you.

Alain

[John E. McLoughlin]

Firstly, I would like to thank Alain for his help with this problem.

It transpires that when using IsNumeric to test if an expression is numeric or not, the Macintosh version of Excel 2003 considers it wrong to pass a non-numeric expression to Isnumeric and thus returns an “Value” error. The Windows version of Excel 2003 does not consider it wrong to do so and correctly returns “False”. The solution is to avoid passing an non-numeric expression to an “IsNumeric = True” test. Incidentally, no such error is returned if a numeric expression is passed to an “IsNumeric = False” test.

[John E. McLoughlin]

In my last posting, I said “the Macintosh version of Excel 2003 considers it wrong to pass a non-numeric expression to Isnumeric and thus returns an “Value” error”.
Apparently, this incorrect and I should have said “the Macintosh version of Excel 2003 considers it wrong to pass a non-numeric expression to such a test as “If IsNumeric(m_age) = True And m_age > 0 Then” and thus returns an “Value” error”. In this instance, I am told, the “Value” error is due to performing two tests in the same expression.
By accepting this phenomenon, I have achieved a solution that meets my requirements satisfactorily.

[netty]

This is totally non-Reunion related, so please contact me off-forum if you have any additionaly information you could share.

I'm looking to learn how to program in Visual Basic & so I can teach a class in it next year (I'm a high school teacher). Haven't had much luck in finding how I can do this on a Mac. What ever recommendations you could provide about how I might accomplish this would be extremely helpful. Additionally, if you provide a few brief examples of the types of things I can do with Visual Basic on a Mac (and what limitations I will run up against as compaired to programming on a PC in VB), that would be most helpful as well.

Most of my 'programming' experience comes from web development (HTML, JavaScript, ASP, ActionScript (Flash), etc.) which I've been doing for nearly ten years. Been a very avid Mac user for longer.

Thanks in advance for any help/advice.

Rick
netty@wi.rr.com