My Favourite Functions – Part 3

Welcome to the third and final part of our Favourite Functions feature!

Our supercool gang of elite readers have really put a lot of effort into compiling this last instalment of their favourite Visual Basic code snippets. And they’re all either intrinsic VB features or prewritten, copy-and-paste functions.

It couldn’t be easier.

What’s in store this week? How to reset a form, check for a leap year, close databases and recordsets with one line of code – plus how to do the Splits in Visual Basic v6, with supporting code for v5, too.

Well, that’s enough of me telling you about it. Let’s dive straight into the content!

Note: If you’ve missed part one of this series, check it out here. And for part two, click here.

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

Check for Whole Numbers

Here’s a groovy little one liner I wrote which checks whether a number is an integer – it returns True for a number such as 5, yet False for 2.5

Assuming the text you’re wanting to analyse is residing in a text box called ‘txtBox1’, here’s the code:

binteger = (txtBox1.Text - CInt(txtBox1.Text)) = 0

— The Mysterious Martinus Pardede

Choose It!

There’s a nice little-known function in Visual Basic called Choose(). It’s great when you need to do comparisons but can’t be bothered writing an entire Select Case statement.

Here’s an example:

MsgBox Choose(2, "One", "Two", "Three")

Here, I’m passing Choose() the number 2, followed by three ‘choices’. In this instance, Choose() returns the second option – ‘Two’. You can have as many options as you wish.

Here’s the official Choose() function format:

Destination = Choose(index, choice-1[, choice-2, ...[, choice-n]])

— Janina Barrister, Mexico

Pad Out a String

If you’ve ever needed to pad out a filename (or other string) to a set number of characters, you might be interested in this Fill function I wrote. It works with virtually all versions of Visual Basic, and is particularly useful for those without the luxurious frills of version 6. Here’s how it works:

x = Fill("world.txt", 8, "0") '----returns------> 000world.txtx = Fill("world.txt", 8) '----returns------> 752world.txt

In the first example, we pass it a fill character of 0. In the second, we don’t specify anything, so it fills with random numbers. Have a go with it yourself to test.

Here’s the code you’ll need to add to your form or module for it to work:

Public Function Fill(ByVal sFln As _String, iQty As Integer, _Optional sChar As String) As StringDim SF As StringDim srnd As StringSF = sFlnsrnd = sCharDim iSize As IntegeriSize = Len(SF)If iSize > iQty ThenFill = sFlnElseIf iSize = iQty ThenFill = SFElseFor i = 1 To iQty - iSize    If sChar = "" Then   srnd = Fix(Rnd() * 10)End IfPrefix = Prefix & srndNext iFill = Prefix & SFEnd IfEnd Function

You might also be interested in the Space() function that bundles with Visual Basic it returns a certain number of spaces, also useful for padding out a string, or maybe displaying information that requires a certain number of spaces between words. It also useful when you need to ‘make room’ for variables you’re passing to the API. Check it out in the help if you’re interested.

— Jose Alonso Yocupicio Zazueta

<Ed pauses for breath, then continues to page three…>

VB6 Split Function (VB6 Only)

You can use the Split() function to parse out a string depending on a delimiter.

In other words, using Split(), the string "hello:my:friend" can be broken down into three separate parts and thrown into an array.

Here’s an example of its usage:

Dim strTextArray() As StringstrTextArray() = Split("hello:my:friend", ":")MsgBox strTextArray(0)

The last message box displays the first element (0) of the array – "hello". The second and third elements will contain the "my" and "friend" values.

So to use the Split() function, follow this syntax:

ArrayToPutInto() = Split(DelimitedString, Delimiter)

Here are a couple of other functions that might interest readers. Look them up in the help if you’re interested:

• Join() – this function does the opposite of Split(). It takes an array and puts it all together into one big string, separated by a specified delimiter
• Filter() – returns a subset of array information, dependant on criteria

Non-VB6 Split Function

One of my favourite functions in Visual Basic 6 is Split (see above). But when I moved to my new job in Cape Town, we moved down to version 5, which doesn’t support it. So here’s my own mini-version of Split:

Private Sub Command1_Click()Dim x() As Variantx() = WordSplit("Hello:my:friend", ":")For i = LBound(x) To UBound(x)MsgBox x(i)Next iEnd SubPublic Function WordSplit(sWords As String, sDelim As String) As VariantDim sTemp As StringDim vWords() As VariantDim n, nCount As IntegernCount = 0sTemp = sWordsDon = InStr(1, sTemp, sDelim)ReDim Preserve vWords(nCount)If n = 0 Then  vWords(nCount) = sTempElse  vWords(nCount) = Left$(sTemp, n)  If Right(vWords(nCount), 1) = sDelim Then    vWords(nCount) = Mid(vWords(nCount), 1, _    Len(vWords(nCount)) - 1)  End If  sTemp = Mid$(sTemp, n + 1)End IfnCount = nCount + 1Loop Until n = 0WordSplit = vWordsEnd Function

— African Abdul, Cape Town.

Static Variables

Few programmers seem to know what static variables are. Look at this code:

Private Sub Command1_Click()   Dim intCount As Integer   intCount = intCount + 1   MsgBox intCountEnd Sub

If you run this on a form with a command button called ‘Command1’, you’ll find that every time you hit the button, the message box displays 1. That’s because the intCount variables is killed off when the sub finishes running – and is recreated the next time you run it.

But if you change the ‘Dim’ part to ‘Static’, you’ll notice it doesn’t disappear – and the number keeps increasing every time you hit the command button. That’s a static variable – it’s rather like a public variable in that it remains ‘alive’ even though the sub has finished working – though no other bits of code can see it.

Cool, eh?

— Julian Versace, Italy

The IIf Function

The IIf function is a very cool way of evaluating a bit of text without a dozen If-Then-Else-EndIf statements. Here’s an example:

Dim blnTest As BooleanblnTest = TrueMsgBox IIf(blnTest = True, "Hello Mark!", "Hello John!")

Here, the IIf function asks if the blnTest variable is equal to true. If it is, it would returns the first string ("Hello Mark!") – if not, it returns the second ("Hello John!")

So you use IIf like this:

x = IIf (ExpressionToEvaluate, TruePart, FalsePart)

This can be very useful. I also noticed an article on VB-World.net about it here.

— Ken Carbrook, Yorkshire

Phew! That concludes our epic three part tips series. For more top Visual Basic code snippets, please check out the comprehensive tips library here.