Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 6th, 2004, 07:40 AM
Authorized User
 
Join Date: Sep 2003
Location: Ipswich UK, , United Kingdom.
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to MattLeek
Default 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
Reply With Quote
  #2 (permalink)  
Old March 6th, 2004, 12:05 PM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old March 6th, 2004, 12:46 PM
Authorized User
 
Join Date: Sep 2003
Location: Ipswich UK, , United Kingdom.
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to MattLeek
Default

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
Reply With Quote
  #4 (permalink)  
Old March 6th, 2004, 02:52 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , Denmark.
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #5 (permalink)  
Old March 6th, 2004, 03:09 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , Denmark.
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
Default

It might be your book doesn't mention DateSerial, but if you use the help provided by Access you'll find an explanation.
Reply With Quote
  #6 (permalink)  
Old March 6th, 2004, 03:27 PM
Imar's Avatar
Wrox Author
Points: 72,022, Level: 100
Points: 72,022, Level: 100 Points: 72,022, Level: 100 Points: 72,022, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,076
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

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.
Reply With Quote
  #7 (permalink)  
Old March 6th, 2004, 03:35 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , Denmark.
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
Reply With Quote
  #8 (permalink)  
Old March 6th, 2004, 03:52 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , Denmark.
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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!
Reply With Quote
  #9 (permalink)  
Old March 7th, 2004, 09:15 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Integer I/O in assembly kaycg BOOK: Professional Assembly Language 2 May 11th, 2007 05:00 AM
text vs integer paul20091968 Access VBA 2 February 13th, 2007 10:50 PM
How knowing it is an integer? rtr1900 Classic ASP Basics 1 October 5th, 2006 07:40 AM
testing integer rjonk XSLT 2 July 27th, 2006 03:11 PM
integer overflow partaola Beginning VB 6 2 March 17th, 2004 09:01 AM



All times are GMT -4. The time now is 10:47 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.