Creating Custom Function - A Little Help Please!
 

Have been trying to write my first custom function, to determine if a year is
a leap year, using the following formula provided by Microsoft:

=IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)< 0)),"Leap Year", "NOT a
Leap Year")

In my worksheet, for example, I plan to put the following formula in cell
B1: =if(LeapYear(A1) = "leap",c209/t13,b23*U14). I cannot get the custom
function (below) to work properly; it either comes back with an error,
returns 0, or the wrong answer. So far, I've got:

Public Function LeapYear(MyRange As Range) As Integer
Dim ActiveCell As Range
On Error GoTo LeapYearErr
Application.Volatile
LeapYear =
"=IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)< 0)),""leap"",""not"")"
LeapYearErr:
Exit Function
End Function

Can someone advise what I'm doing wrong here?

Creating Custom Function - A Little Help Please!
 

Here is a function that should work for you. It returns a True or False

Public Function LeapYear(Cell As Range) As Boolean
If Cell.Value Mod 400 = 0 Or (Cell.Value Mod 4 = 0 And Cell.Value Mod
100 < 0) Then
LeapYear = True
Else
LeapYear = False
End If
End Function

So in B1 you could use
=if(LeapYear(A1) ,c209/t13,b23*U14)

--
HTH...

Jim Thomlinson


"Paige" wrote:

Have been trying to write my first custom function, to determine if a year is
a leap year, using the following formula provided by Microsoft:

=IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)< 0)),"Leap Year", "NOT a
Leap Year")

In my worksheet, for example, I plan to put the following formula in cell
B1: =if(LeapYear(A1) = "leap",c209/t13,b23*U14). I cannot get the custom
function (below) to work properly; it either comes back with an error,
returns 0, or the wrong answer. So far, I've got:

Public Function LeapYear(MyRange As Range) As Integer
Dim ActiveCell As Range
On Error GoTo LeapYearErr
Application.Volatile
LeapYear =
"=IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)< 0)),""leap"",""not"")"
LeapYearErr:
Exit Function
End Function

Can someone advise what I'm doing wrong here?

Creating Custom Function - A Little Help Please!
 

Public Function LeapYear(yr as Long) as Boolean
LeapYear = (month(date(yr,2,29)) = 2)
End Sub

--
Regards,
Tom Ogilvy


"Paige" wrote:

Have been trying to write my first custom function, to determine if a year is
a leap year, using the following formula provided by Microsoft:

=IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)< 0)),"Leap Year", "NOT a
Leap Year")

In my worksheet, for example, I plan to put the following formula in cell
B1: =if(LeapYear(A1) = "leap",c209/t13,b23*U14). I cannot get the custom
function (below) to work properly; it either comes back with an error,
returns 0, or the wrong answer. So far, I've got:

Public Function LeapYear(MyRange As Range) As Integer
Dim ActiveCell As Range
On Error GoTo LeapYearErr
Application.Volatile
LeapYear =
"=IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)< 0)),""leap"",""not"")"
LeapYearErr:
Exit Function
End Function

Can someone advise what I'm doing wrong here?

Creating Custom Function - A Little Help Please!
 

Tom Ogilvy wrote:
Public Function LeapYear(yr as Long) as Boolean
LeapYear = (month(date(yr,2,29)) = 2)
End Sub

I was going to offer something similar (subtract 1 from 3/1 of the year
to see if the day is 28 or 29). However, basing the leap year decision
on whether the year has a 2/29 date appears to incorrectly identify
1900 as a leap year? EXCEL's date function claims there is a date of
2/29/1900.

Plus, it only works back to 1900. Which may or may not be a problem,
depending on how it is being used.

Creating Custom Function - A Little Help Please!
 

Thanks, guys; will give these a try. Appreciate your help!

"Randy Harmelink" wrote:

Tom Ogilvy wrote:
Public Function LeapYear(yr as Long) as Boolean
LeapYear = (month(date(yr,2,29)) = 2)
End Sub

I was going to offer something similar (subtract 1 from 3/1 of the year
to see if the day is 28 or 29). However, basing the leap year decision
on whether the year has a 2/29 date appears to incorrectly identify
1900 as a leap year? EXCEL's date function claims there is a date of
2/29/1900.

Plus, it only works back to 1900. Which may or may not be a problem,
depending on how it is being used.

Creating Custom Function - A Little Help Please!
 

What you say about Excel is true. But I am not using Excel. I am using
VBA, so it does not have these limitations.

--
Regards,
Tom Ogilvy


"Randy Harmelink" wrote:

Tom Ogilvy wrote:
Public Function LeapYear(yr as Long) as Boolean
LeapYear = (month(date(yr,2,29)) = 2)
End Sub

I was going to offer something similar (subtract 1 from 3/1 of the year
to see if the day is 28 or 29). However, basing the leap year decision
on whether the year has a 2/29 date appears to incorrectly identify
1900 as a leap year? EXCEL's date function claims there is a date of
2/29/1900.

Plus, it only works back to 1900. Which may or may not be a problem,
depending on how it is being used.

Creating Custom Function - A Little Help Please!
 

The 1900 error was deliberately included for compatibility with Lotus's
previous error.
Excel's worksheet will only work back to 1900, but VBA Dates are valid back
the year 100.
However, given the number of changes to the calendars since then, I would
not put much faith in reality v. calculation before the 1800s.

NickHK

"Randy Harmelink" wrote in message
oups.com...
Tom Ogilvy wrote:
Public Function LeapYear(yr as Long) as Boolean
LeapYear = (month(date(yr,2,29)) = 2)
End Sub

I was going to offer something similar (subtract 1 from 3/1 of the year
to see if the day is 28 or 29). However, basing the leap year decision
on whether the year has a 2/29 date appears to incorrectly identify
1900 as a leap year? EXCEL's date function claims there is a date of
2/29/1900.

Plus, it only works back to 1900. Which may or may not be a problem,
depending on how it is being used.

Creating Custom Function - A Little Help Please!
 

<not put much faith in reality v. calculation before the 1800s.

The meaning of the date also depends on its location. In Russia, for example, the Gregorian calendar was adopted as late as 1917,
and there are many other examples of other calendars being used in rather recent centuries.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"NickHK" wrote in message ...
| The 1900 error was deliberately included for compatibility with Lotus's
| previous error.
| Excel's worksheet will only work back to 1900, but VBA Dates are valid back
| the year 100.
| However, given the number of changes to the calendars since then, I would
| not put much faith in reality v. calculation before the 1800s.
|
| NickHK
|
| "Randy Harmelink" wrote in message
| oups.com...
| Tom Ogilvy wrote:
| Public Function LeapYear(yr as Long) as Boolean
| LeapYear = (month(date(yr,2,29)) = 2)
| End Sub
|
| I was going to offer something similar (subtract 1 from 3/1 of the year
| to see if the day is 28 or 29). However, basing the leap year decision
| on whether the year has a 2/29 date appears to incorrectly identify
| 1900 as a leap year? EXCEL's date function claims there is a date of
| 2/29/1900.
|
| Plus, it only works back to 1900. Which may or may not be a problem,
| depending on how it is being used.
|
|
|