My Favourite Functions - Part 3

• facebook
• twitter
• google+
• in
• Email

Leap Year Check

You can check if a year is a leap year by using the follow neat hack. Just pass the function a date (even a string date, such as "20/05/1960") - and it will return True if a leap year, False if not.

Public Function IsLeapYear(DateIn As Date) As BooleanIf IsDate("29/02/" & Format(DateIn, "yyyy")) = True Then  IsLeapYear = TrueEnd IfEnd Function

Hah, Y2K? - eat my shorts

-- John Hayman, Somewhere in the UK

Closing Recordsets, Databases

Are you bored of manually closing recordsets, setting them to nothing, then closing their parent databases and setting the object to nothing? Here's a quick function to help you out:

Public Sub CloseNothing(objToClose As Object)If IsObject(objToClose) And Not (objToClose Is Nothing) ThenobjToClose.CloseSet objToClose = NothingEnd IfEnd Sub

-- Charles Boisvert, DOJ Programmer and Supercool Family Guy, Quebec

Reset a Form

You can 'reset' a form by clearing all the text boxes, deselecting combo boxes unchecking check boxes and so on. Sound time consuming? Well, here's a piece of code to save you all the hard work.

It requires two parameters - the form you want to clear, plus a pSkipThese string. The code checks pSkipThese and skips over any controls beginning with that string. So if you pass in 'txt' as pSkipThese, it skips all the text boxes (providing the form developer followed standard naming conventions!). Try it!

Note - you could always change the code to check the Tag property of each control. If it has a certain tag, you could skip it - hence ridding of the need for a pSkipThese property.

Here's the actual code:

Public Sub ClearForm(ByRef pForm As Object, _Optional ByVal pSkipThese As String = "")Dim Ctl As ControlDim pSkipLen As LongOn Error Resume NextpSkipLen = Len(pSkipThese)For Each Ctl In pFormIf pSkipThese <> "" Then  If Left(Ctl.name, pSkipLen) <> pSkipThese ThenSelect Case TypeName(Ctl)  Case "TextBox"Ctl.Text = ""  Case "CheckBox"Ctl.Value = vbUnchecked  Case "ComboBox"Ctl.ListIndex = -1Ctl.Text = ""End Select  End IfElse  Select Case TypeName(Ctl)Case "TextBox"  Ctl.Text = ""Case "CheckBox"  Ctl.Value = vbUncheckedCase "ComboBox"  Ctl.ListIndex = -1  Ctl.Text = ""  End SelectEnd IfNextEnd Sub

-- Martin Wildam, MAY Computer GmbH & Co KG, Vienna

Page 2 of 5