Paul Lomax's Top 15 VB Tips and Tricks

by Paul Lomax
10/01/1998

1. Use vbCrLf in Embedded SQL Scripts

Visual Basic contains quite a few intrinsic constants -- that is, constants that are now part of the VBA language but that you used to have to define in your code either explicitly or by adding the Constant.Bas file to your project. One of these is vbCrLf, which equates to the carriage return/line feed -- or Chr$(13) & Chr$(10) - character combination.

But why would I suggest that you include this constant in an embedded SQL script? After all, the code means nothing to SQL Server; it could care less that you want a carriage return and line feed at the end of each line. The answer: debugging, plain and simple. Quite often, you'll find yourself creating embedded SQL scripts that run to 10, 20 or more lines. For example,

     sSQL = "SELECT * FROM anytable" _
            & " WHERE userid = " & lUser _
            & " AND color = '" & sColor "'" 

If you define the SQL statement in this way, the SQL script is readable when viewed in the procedure, but what about when you want use the Immediate window to see the value of the sSQL variable at run time?

You enter

? sSQL

in the Immediate window, and a long, unbroken string is shown, running miles off into the distance.

However, if you simply suffix each line with vbCrLf, like this:

     sSQL = "SELECT * FROM anytable" & vbCrLf _
            & "WHERE userid = " & lUser & vbCrLf _
            & "AND color = '" & sColor "'" 

then you can quickly and easily read your completed SQL code to find the problem.

The previous example illustrates another benefit of vbCrLf: if you don't use it, you must remember to start (or end) each line with a space; otherwise,

     sSQL = "SELECT * FROM anytable" & _
            & "WHERE userid = " & lUser

becomes

"SELECT * FROM anytableWHERE userid = 1"

and your code will generate a SQL syntax error.

2. Use #If ccDebug to View Hidden Automation Servers

One of the problems with testing and debugging a remote automation server is that the server application typically remains hidden, doing its work in the background. Hence, when something goes wrong, you frequently can't tell what has happened or where in your code your application is being derailed.

Consequently, when you're developing, testing, and debugging your application, it's a good idea to make sure that otherwise hidden automation server remain visible. You can do this by using code like the following to make sure that a new instance of an automation server (in this case Microsoft Word) is visible:

     Dim objWord as Word.Application
     Set objWord = New Application
     
     #If ccDebug Then
     objWord.Visible = True
     #End If

You can define the project-level conditional compiler constant ccDebug in either of two ways:

• By including the line

  #Const ccDebug = 1

  in the declarations section of a form or code module.

• By using the Make tab of the Project Properties dialog (select the Project Properties option from the VB Project menu) and entering the following in the Conditional Compilation Arguments text box:

  ccDebug = 1

Then, whenever your code runs in the design-time environment, you automation server will be visible. When you are ready to create the final executable, you should either set the ccDebug conditional compilation constant to 0 or, if you've used the Conditional Compilation Arguments text box, remove its definition.

3. Implement an IsSaved (or IsDirty) Property

I often find this technique very useful, but it takes a little extra work when you're creating your object classes. On balance, despite this up front investment of time, it saves a good deal of programming time by making it easy to determining when an object has changed and therefore needs to be saved.

To implement an IsSaved or IsDirty property, each Property Let procedure of a particular object must contain a line of code to determine if the value to be assigned to the property is different than its current value (which should be stored in a private member variable). If it is different, then the private member variable that represents the IsSaved property is set to False. For example:

     Property Let CustStreetAddr(sVal as String)
        If msCustStreetAddr <> sVal Then
            msCustStreetAddr = sVal
            mbIsSaved = False
        End If
     End Property

(Of course, you can also implement this the other way round by having an IsDirty property that returns True when the object needs to be saved.)

Back at the client end you can check to see if the object needs saving quickly and easily as follows:

     If Not myObject.IsSaved Then           
           SaveTheObject
     End If

On the server object, this is implemented as a simple Property Get procedure:

          Property Get IsSaved() As Boolean
              IsSaved = mbIsSaved
          End Property

Another neat addition to this is to define an object event called something like ObjectChanged. Then the event can be fired whenever some attribute of the object changes:

     Property Let CustStreetAddr(sVal As String)
        If msCustStreetAddr <> sVal Then
            msCustStreetAddr = sVal
            mbIsSaved = False
            RaiseEvent ObjectChanged()
        End If
     End Property

On the client form, you can then implement an event handler for the ObjectChanged event that enables the Save button when the object needs to be saved:

     Sub MyObject_ObjectChanged()
        cmdSave.Enabled = Not myObject.IsSaved
     End Sub

This code enables the Save button when the object is not saved and disables the button when the object has been changed.

I should add a major qualification to this tip: don't update your object property based on the Change event handler of a text box. The Change event is fired for each keystroke that the text box receives. Therefore typing a word like "Stupid" into the text box will fire off 6 Change events - and the final result is that the text box could contain the same word that it originally started with, so that in fact its contents haven't changed at all despite the firing of six unnecessary events.

4. Implement a For Each...Next Statement against a Collection Class

Most of the time, we take for granted the For Each...Next loop, which iterates the members of an array or a collection. It's the fastest, most efficient method of visiting all the members of the collection or array, and we could care less that, as it enumerates the collection, the unseen code is actually generating new references to members of the collection with each iteration of the loop. However, as the provider of a collection class, it is up to you to provide an interface that the For Each...Next statement can work with.

This may sound a little daunting, but you'll be pleasantly surprised how easy it is to implement a property that enumerates members of the collection within your class. First of all, you must create a Property Get procedure called NewEnum with the type of IUnknown. Its syntax is always the same:

     Public Property Get NewEnum() As IUnknown
        Set NewEnum = mCol.[_NewEnum]
     End Property

where mCol is the name of your private collection object variable.

Second, set the Procedure ID for this Property Get procedure to -4. To do this, select the Procedure Attributes option from the Tools menu, then click the Advanced button on the Procedure Attributes dialog. Enter a Procedure ID of -4. You should also check the "Hide this member" option to prevent the property from appearing in the IntelliSense drop down.

5. Implement an Exists Method within a Collection Class

One of my long standing gripes about the Collection object is the complete absence lack of an easy method to determine whether the member you're looking for exists within the collection. Therefore, when I'm writing a wrapper class for a collection, I always include my own.

However, I add a little more to the method than simply determining if the member exists in the collection. If the member is not found within the collection, I attempt to add it to the collection. This way, I can simplify the code at the client end by always calling the Exists method prior to assigning the member to a local object variable. Therefore, I know that if the Exists method returns true, I can safely go on to assign the member to the local object variable.

The code for a typical Exists method is shown below:

     Public Function Exists(sDomainName As String) As Boolean
   
        On Error GoTo Exists_Err
   
        Dim oTemp As Domain
        Set oTemp = mcolDomains.Item(sDomainName)
        Exists = True
        Set oTemp = Nothing
        Exit Function
    
     TrytoGet:
         If Not LoadDomain(sDomainName) Then
             Exit Function
         Else
            Exists = True
         End If
         Exit Function
    
     Exists_Err:
         If Err.Number = 5 Then
             Resume TrytoGet
         Else
         'further error handling here for other error types
         End If
        
     End Function

As you can see, the idea is to test for the presence of a particular member of the collection by attempting to assign it to a temporary local object variable. If the member is not present, then error 5 ("Invalid Procedure Call or Argument") is raised. Trapping this, program flow proceeds to the LoadDomain function, which attempts to load the member into the collection.

The new VB6 Dictionary object (found in the Scripting Runtime Library) contains its own built-in Exists property. The custom Exists method for the collection object can therefore be cut down dramatically but still achieve the same results, as the following method illustrates:

     Public Function Exists(sDomainName As String) As Boolean 
         If mdicWebsites.Exists(sDomainName) Then
             Exists = True
         Else
             Exists = GetWebsite(sDomainName)
         End If
     End Function

Whether you're using the (now old fashioned) Collection object or the (new and fast) Dictionary object, your client code is identical, as the following fragment shows:

     Private Sub cboDomainName_Click()
         If moWebsites.Exists(cboDomainName.Text) Then
             Set moWebsite = moWebsites.Website(cboDomainName.Text)
         End If
     End Sub

6. Implement a Fast Reset Method within a Form

There are many occasions where you need to reset the controls on a form, either in readiness for a new record, or perhaps when a record has been displayed and the user clicks the button to enter a new record.

You could, of course, reference each control on the form individually to reset its display. However, I tend to whip through every control on the form and perform the same function on each. Of course, not all controls have the same properties, so I preface my procedure with an On Error Resume Next statement that tells my code to ignore any errors and continue with the next line of code:

     On Error Resume Next
     For Each oControl In Controls
        oControl.Text = "" 'mainly for text box controls
        oControl.ListIndex = -1 'reset combo and list boxes.
     Next

7. Beware O'Reilly, O'Malley, and It's

You've spent the last 17 years writing your new application and it works like a dream. You've got error handling coming out of your ears, belts, and braces. You've tested the application till you were blue in the face. Then, first day out in the field, a user contacts you and says, "It won't let me save this record."

Huh? What could be wrong? It can't be your code. No way!

You hunt through the code, and eventually find out from the user that the error message was a SQL syntax error that only reared its ugly head when the customers name was O'SomethingOrOther. Ahha!

Look at this embedded SQL Script:

     sSQL = "UPDATE Customers Set CustName = '" & sCustName & _
            "' WHERE CustID = " & custID"

Given a customer name of "Lomax" and an ID of 100, then the string sSQL will contain a perfectly valid

"UPDATE Customers Set CustName = 'Lomax' WHERE CustID = 100"

However, change the customer name to "O'Reilly" and you generate a SQL syntax error:

"UPDATE Customers Set CustName = 'O'Reilly' WHERE CustID = 100"

The "O" is the string variable for CustName, but "Reilly" is a string that has no beginning (or is it the "O" that has no end? Hmmm..). So what's the answer? Well, there are a few ways of sorting out this little problem. One of the easiest is to replace all single quotation marks ("'") within strings with an apostrophe ("`" ). There are several ways to do this, but here's my favorite (for pre-VB6 code):

Mid(CustName, Instr(CustName, 1, "'"), 1) = "`"

This code fragment makes unusual use of Mid on the left side of the statement. It replaces the single character "'" with the "`" character. However, this single line of code can only replace the first instance of a single quotation mark ("'") in a string. To replace all instances, you'd have to implement a Do While loop like the following:

     Do While Instr(CustName,1,"'") > 1
         Mid(CustName, Instr(CustName, 1, "'"), 1) = "`"
     Loop

If you're using VB6, you can use the new Replace function to substitute a single line of code for the code block:

CustName = Replace(CustName, "'", "`")

You'll also find that providing users with large, multi-line text boxes for entering free format text provides opportunities for them to enter words like "It's", "They're", and other apostrophe-containing, SQL script-breaking words. So be sure to remove the apostrophes here, too.

8. Populate Classes in One Go

I've called this a mass assignation function. What's a mass assignation function? Let's say you have a collection class that contains 20 properties, which you populate by reading data from a database. You have a procedure that opens the database, creates a recordset, and then assigns the values from the recordset to each of the relevant properties in the class, something like this:

          oClass.EmployeeNo = rsRecordset!EmpNo
          oClass.FirstName = rsRecordset!FirstName
          Etc...

Using this method, you are calling the Property Let procedure of each property. If there is validation code within the Property Let procedure, this must execute too, most likely on data that has been validated before being saved in the database. A more efficient method of population is to create a function within the collection class, like this:

     Friend Function Initialize(sEmpNo as String, _
                     sFirstName as String ...etc) As Boolean
     msEmpNo = sEmpNo
     msFirstName = sFirstName
     ...Etc...

This single function assigns all the values for the object in one go by assigning the values directly to the local variables, thus bypassing the Property Let procedures and the redundant validation code. You can therefore pass all the values to populate the object in one go:

     If oClass.Initialise( rsRecordset!EmpNo, _
                           rsRecordset!FirstName, _
                           etc...) Then

Of course you should only use this method within a class module - never from outside, and you should only employ this against data that you're certain has already been validated. You will find that a mass assignation function will dramatically improve the performance of your collection classes.

9. Using API Calls to Create a Software Timer Class

You can create your own timer class without needing a form and a Timer control present. This solution is ideal for a remote server application where you don't want to be cluttering up the server with forms. This example also shows how callback functions and the AddressOf operator are used.

The following code forms two separate projects. The first is the automation server; this consists of a class module (clsRemTimer) and a code module. The code module is necessary to provide a callback procedure for the API functions used to initiate and destroy the Windows Timer.

TimerServer.vbp - clsRemTimer.cls

     Option Explicit

     Public Event Timer()
     Private blnEnabled As Boolean
     Private lTimerID   As Long
     Private lInterval  As Long

     Public Property Let Interval(lVal As Long)
         lInterval = lVal
     End Property
     Public Property Get Interval() As Long
         Interval = lInterval
     End Property
     Public Property Let Enabled(blnVal As Boolean)
         If blnVal = False Then
             StopTimer
         Else
             StartTimer
         End If
     End Property

     Private Function StartTimer() As Boolean

         If Not blnEnabled Then
             lTimerID = TimerStart(Me, lInterval)
             If lTimerID = 0 Then
               Err.Raise 60000 + vbObjectError, "clsTimer", "Could not start Timer"
             End If
             blnEnabled = True
         End If
         StartTimer = True
    
     End Function
    
     Private Function StopTimer() As Boolean

         If blnEnabled Then
             lTimerID = TimerStop
             If lTimerID = 0 Then
               Err.Raise 60001 + vbObjectError, "clsTimer", "Could not stop Timer"
             End If
             blnEnabled = False
         End If
         StopTimer = False

     End Function

     Friend Function RaiseTimerEvent()

        RaiseEvent Timer

     End Function

     Private Sub Class_Terminate()
         Call TimerStop
     End Sub

TimerServer.vbp - modTimer.bas

     Option Explicit

     Private oTimer   As clsRemTimer
     Private lTimerID As Long

     Declare Function SetTimer Lib "user32" _
           (ByVal hwnd As Long, _
           ByVal nIDEvent As Long, _
           ByVal uElapse As Long, _
           ByVal lpTimerFunc As Long) As Long

     Declare Function KillTimer Lib "user32" _
           (ByVal hwnd As Long, _
           ByVal nIDEvent As Long) As Long
    
     Public Sub TimerCallBack(ByVal hwnd As Long, _
                               ByVal uMsg As Long, _
                               ByVal idEvent As Long, _
                               ByVal dwTime As Long)

        oTimer.RaiseTimerEvent
    
     End Sub

     Public Function TimerStart(ByRef oTmr As clsRemTimer, _
                                lInterval As Long) As Long
         Set oTimer = oTmr
         lTimerID = SetTimer(0, 0, lInterval, AddressOf TimerCallBack)
         TimerStart = lTimerID

     End Function

     Public Function TimerStop() As Long
         TimerStop = KillTimer(0, lTimerID)
         Set oTimer = Nothing
     End Function

TimerClient.vbp - frmTimerClient.frm

Finally, here's a sample client, which is simply a form that uses the RemTimer class. Note that for this to function correctly, the client must declare the instance of the RemTimer class WithEvents. This sample client form contains one text box (named Text1) with its Locked property set to True and one command button (named Command1).

     Option Explicit

     Private WithEvents oTimer As TimerServer.clsRemTimer
    
     Private Sub Form_Load()
   
        Set oTimer = New TimerServer.clsRemTimer
        oTimer.Interval = 1000
        Text1.Text = 0
        Command1.Caption = "Start"
   
     End Sub

     Private Sub Command1_Click()

          On Error GoTo Command1_Err
    
          If Command1.Caption = "Start" Then
              oTimer.Enabled = True
              Command1.Caption = "Stop"
          Else
               oTimer.Enabled = False
               Command1.Caption = "Start"
          End If
          Exit Sub
    
     Command1_Err:
         MsgBox Err.Description
    
     End Sub

     Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
         Set oTimer = Nothing
     End Sub

     Private Sub oTimer_Timer()

         Text1.Text = Text1.Text + 1

     End Sub

10. Use a Count Property to Populate a Class

One problem you always have when architecting a class hierarchy is implementing a method that allows the user of the class to populate the class. For example, you could implement a Load method that reads all relevant records into the class. However, I prefer to automatically populate classes using the Count property; this method produces quite elegant code within the client.

Here's an example of a Count property that automatically populates the class:

     Public Property Count() As Long
         If colMyCollection.Count = 0 Then
             LoadRecordsIntoObject
         End If
         Count = colMyCollection.Count
     End Property

Beware, though, you may need to add a filter property to reduce the number of records automatically loaded into the object. Also, bear in mind that many times these days, the person writing the objects will not be the person who writes the client-side code. Therefore, you should make your class objects as easy to use as possible.

Here's an example of some client side code that utilizes the Count property shown above:

     Set oDomains = New Domains
         oDomains.Type = 1
         If oDomains.Count > 0 Then
        sNames = oDomains.Names
         End If
     Set oDomains = Nothing

11. Never Create Custom Subs

There is no operational difference between a sub and a function. A sub is the VB equivalent of a void function in C and C++. However, if you use a function, you have the choice of using or ignoring the function's return value.

If you were about to create a Sub because you don't necessarily need a particular return value, then create a function with a Boolean return value instead. This way, you can return True if all went well, or False if some error occurred. Your code will be much more robust as a result.

For example, the following code calls two sub procedures, one after the other:

     Call DoFirstRoutine(sVal)
     Call SecondRoutine(sOther)

However, the call to the second may actually presuppose on the successful completion of the first. In this case, it is safer to implement the two routines as functions that return a Boolean value to denote the success or failure of the procedure, as follows:

     If DoFirstRoutine(sVal) Then
         SecondRouting sOther
     End If

The only time you should need to write code within a sub procedure is as an event handler; otherwise, use a function and return a value (at least a Boolean). The calling statement can then choose whether to use or ignore the return value from your function.

12. Never Assume the Unusual Won't Happen

I love this story. It's true (and very recent). One weekend (like most in the summer), we were off racing and had to stay over in a hotel, part of a major UK chain. The kids are getting a bit older now, so we decided that they could have their own room. When I'd filled out the form for the first room and signed in, the lady behind the check-in desk said, "I'm sorry, but can we put your wife down as the occupier of the second room? It's just that our system won't let us use the same surname and initial for two records on the same day. We need a different initial."

Whose bright idea was it that the primary key on the day table should be last name and initial?

OK, perhaps it's unusual, but what about father and adult son, both paying for their rooms individually but both with the same first name? I knew a family once - a mother, father, and three sons - all of whom had the same initial and last name! Nora, Norman, Nigel, Nicholas, and Nathaniel. (OK, so I made the last name up because I could remember it...)

If someone says that something is "unusual," "unlikely," or "improbable," it does not mean that it's impossible. Unusual, etc., means that it can happen. And as sure as eggs are eggs, it's going to happen, and it'll happen to your system! Sound programming is not about gambling on probabilities, its about banking on certainties!

13. Always Use the Event Log to Log Errors

One of the neat things about NT is its event log. Wherever possible (and I can't think of a situation where it isn't), you should add a line of code (or two) into your error handling routine to write a line out to the event log.

Even if your application is unfortunate enough to have to run on Windows 9x, you can still specify that an error line be written to an event log file.

Once you are documenting errors via the event log, you can sit at your computer on the network and (if you have administrator rights) keep your eye on the event logs of your users for potential problems. No more having to rely on your users giving you the exact error message that was shown on the screen.

You will soon find that, with the use of the event log, once difficult to track errors are easy to trace, and even errors that may be caused by other sometimes "unseen" causes are pinpointed quickly and accurately.

14. Navigate Effectively with SHIFT+F2 and CTRL+SHIFT+F2

One of the confusing aspects of developing a large project with Visual Basic is that code snippets tend to be in any of a variety of places, and keeping track of what's where - not to mention navigating to a particular routine when you want to see it - is often difficult. However, a little-known keyboard shortcut can help.

Simply highlight the property or procedure name in the code and press SHIFT+F2; you'll be transported as if by magic to the highlighted property or procedure. (If it's outside the project, you'll be taken to the Object Browser, where you can obtain further information about it.)

You can navigate back to where you came from just as easily. This time hit CTRL+SHIFT+F2 and you're back at your original place. This can save hours and hours of scrolling and jumping from one module to another in a large project.

15. Fast Combo Population from Objects with VB6

One of the most common tasks we have to undertake when creating a user form is to provide a dropdown list of values based on property values within one of our objects. A combo control, for example, might contain the names of all the employees in a certain division of the company.

One of the most common methods of doing this (until VB6) was to populate the collection object, then enumerate each object in the collection and assign the particular value from each object into the combo control. The code looks something like this:

     Set oEmployees = New Employees
         For i = 1 to oEmployees.Count
             If oEmployees.Exists(i) Then
                 Set oEmployee = oEmployees.Employee(i)
                      cboEmployees.AddItem oEmployee.Name
               Set oEmployee = Nothing
             End If
         Next i
     Set oEmployees = Nothing

The amount of processing and (if an object is remote) the massive amount of network traffic that this simple procedure can create is staggering! So when I found that functions can return arrays in VB6, I was delighted. This means that you can now execute a simple SQL query that returns to the client only an array containing the values of that field that you want to display in the list or combo box. Here's the server side code:

     Public Property Get Names() As String()

         ReDim sTemp(0) As String
         Dim oADORec As ADODB.Recordset
         Dim lRecCount As Long
         Dim sSQL As String
         Dim iCounter As Integer
   
             Set oUtil = New PowerUtils.DBUtils
                 sSQL = "SELECT SName from Employees"
                 Set oADORec = oUtil.GetRecordset(sSQL)
                      If oADORec.RowCount > 0 Then
                          ReDim sTemp(oADORec.RowCount)
                          Do While Not oADORec.EOF
                              sTemp(iCounter) = oADORec!SName
                              iCounter = iCounter + 1
                              oADORec.MoveNext
                          Loop
                       End If
                 Set oADORec = Nothing
             Set oUtil = Nothing
          Names = sTemp

     End Property

If you can't get the RowCount property to work with your system, it is faster to execute a SQL Query that returns the row count and then do a single ReDim of the array, rather than to use ReDim Preserve.

Here's how the Names property shown above is used at the client end:

     Dim sNames() As String
     Dim vName As Variant
     Dim oEmps As Employees

     Set oEmps = New Employees
         sNames = oEmps.Names
         For Each vName In sNames
             cboEmployees.AddItem vName
         Next
     Set oEmps = Nothing

Note that only an array of string values is passed from the server object to the client. Therefore, the amount of traffic between the two objects is negligible. Also note that to assign an array from a function in VB6, your local variable must be dimensioned as a dynamic array.