 |
Excel VBA Discuss using VBA for Excel programming. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|

March 6th, 2004, 07:40 AM
|
Authorized User
|
|
Join Date: Sep 2003
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Testing for integer
Hi, im am doing some coursework in my ICT lesson, i am trying to add some Excel VBA which i am learning from reading macros i have made and i also know a bit as some is the same as Access VBA which i learning from a book.
I want to be able to work out whether the current year is a leap year by dividing the current year by four and if it is a whole number then it is. My question is how do i test the number to see if it is a whole number?
Thanks for any help.
Matthew Leek
__________________
Thanks,
Matthew
|

March 6th, 2004, 12:05 PM
|
 |
Wrox Author
|
|
Join Date: Jun 2003
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
|
|
If your two digit year is contained in iYear then:
[code]
Dim dblQuarter as Double
dblQuarter = iYear/4
If dblQuarter = CInt(dblQuarter) Then
'Result is integer
Else
'Result is not integer
End If
[code]
Of course this test is not valid as 1900 was not a leap but 2000 was. If you trust Microsoft's date processing you could just see if the 29th February was a valid date:
Code:
Function IsLeapYear(iSomeYear As Long) As Boolean
IsLeapYear = Month(DateSerial(iSomeYear, 2, 29)) = 2
End Function
This constructs a date based on 29th February and tests whether the month is still February or has moved to March.
--
Joe
|

March 6th, 2004, 12:46 PM
|
Authorized User
|
|
Join Date: Sep 2003
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Hi, i hate to be a pain, but i checked in my Access book and couldn't find the DateSerial command. Could anyone tell me how it works?
Matthew Leek
|

March 6th, 2004, 02:52 PM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
If the only purpose is to determine whether CURRENT year is a leap year, isn't it sufficient to use this:
Sub LeapYear()
If Year(Now) Mod 4 = 0 Then
MsgBox ("It's a leap year")
Else
MsgBox ("Nope")
End If
End Sub
|

March 6th, 2004, 03:09 PM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
It might be your book doesn't mention DateSerial, but if you use the help provided by Access you'll find an explanation.
|

March 6th, 2004, 03:27 PM
|
 |
Wrox Author
|
|
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
|
|
Nope, it's not
It's a leap year once every four year, unless the year can be divided by 100. And this last rule only applies when the year cannot be divided by 400. So, 2000 was a leap year, 1900 wasn't.
Cheers,
Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
|

March 6th, 2004, 03:35 PM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Oh yeah!
If you're going to use Year(Now), then who's interested in year 2000? Or 1900, for that sake!
If he's using an explicit year, he can disregard year 2000 within an If sentence or use Select Case to disregard that year.
|

March 6th, 2004, 03:52 PM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Could it be that the ICT -word implicates there should be special considerations when answering such question? I can see the it's some sort of interactive education!
|

March 7th, 2004, 09:15 AM
|
 |
Wrox Author
|
|
Join Date: Jun 2003
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
|
|
Quote:
quote:Originally posted by MattLeek
Hi, i hate to be a pain, but i checked in my Access book and couldn't find the DateSerial command. Could anyone tell me how it works?
Matthew Leek
|
It's not an Access function, it's part of VBA runtime library so can be used across all Microsoft Office apps. Open the help from inside Access VBA IDE and search there.
--
Joe
|
|
 |