My Favourite Functions - Part 3
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