Do you remember that old TV gameshow called "Name that Tune"? Contestants would be given notes from a song and they would bet who could identify the song with the least number of notes.

Well, I thought it would be a good discussion to ask this about VBA code. Namely, how compact and efficient do you make your code before it starts becoming "too difficult" to follow? Sometimes I read solutions to problems and the poster write these War and Peace length bits of code... and I think, "Wow, I can do the same thing in five lines." But are those five lines easy for someone who inherits your code to follow? Even if you comment the code like crazy, if the next generation coder sees it, will he or she be able to understand it? What do you? Code for "function or fashion"? Any thoughts?

Very simple example to illustrate point:
   Is simply...

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

__________________
Greg Serrano
Michigan Dept. of Environmental Quality
Air Quality Division

Well Greg, sometimes it's easier for a beginner to understand the logic when you use the longer version. I know I did in the beginning. Then as I became more experienced, I learned how to simplify the code and still understand what I was doing. Whenever I post code for someone, I generally post the longer version so they can understand the logic of what is happening and what is trying to be accomplished.

Just my $.02 worth..

Beth M

Code can never be too compact. That is where comments come into place. If my code can not be deciphered I insert a complete explanation of my code on the footer of my code page or I include an explanation in the documentation.

Something that I see a lot also is a lot of loops. Loops are slow.




Sal

Compact code is not always the fastest nor most efficient. My preference is to write the most efficient code that I can. My efficiency concerns are primarily traffic (bus or LAN), then Memory and lastly processor usage. When working in a Terminal Server environment, I may shift the emphasis a bit where there is a choice.

Being a lazy typist, all things being equal, I prefer shorter code and am reluctant to name the counter variable in a For - Next loop. The nesting should make it obvious. If there is a single If test I have been known to write:

     If blnVariable Then intX = intX + 1

rather than

     If blnVariable = True Then intX = intX = 1

or

     If blnVariable = True Then
          intX = intX = 1
     End If

How does one know which is most efficient in processor usage? I like to write a test procedure in a module headed with a declaration for the timeGetTime Win32 API at the top of a standard module:

Public Declare Function timeGetTime Lib "Winmm" () As Long

This call returns a long that uses multimedia timer that returns the number of milliseconds since the machine was booted. If you need more resolution, then you can declare the queryperformancecounter which gets to the millionths of a second. (by the way, the timeGetTime has a resolution of a thousandth of a second but on many machines, it reports to the nearest 10/thousandths). In any event, many processes can be run in a loop repeatedly if the timer resolution is not fine enough, though this will not give information about the overall function load time and the duration of other 'one time' matters such as caching a recordset. For the purposes of this discussion:

Sub SpeedTest()
Dim lngi As Long
Dim lngt As Long
Dim bln As Boolean

lngt = timeGetTime
For lngi = 0 To 100000
    If bln Then
        lngi = lngi
    End If
Next
MsgBox timeGetTime - lngt
End Sub

     yeilds 72 milliseconds
vs

Sub SpeedTest()
Dim lngi As Long
Dim lngt As Long
Dim bln As Boolean

lngt = timeGetTime
For lngi = 0 To 100000
    If bln = True Then
        lngi = lngi
    End If
Next
MsgBox timeGetTime - lngt
End Sub

     equals 90 milliseconds

and finally:

Sub SpeedTest()
Dim lngi As Long
Dim lngt As Long
Dim bln As Boolean

lngt = timeGetTime
For lngi = 0 To 100000
    If bln Then lngi = lngi
Next
MsgBox timeGetTime - lngt
End Sub

     yeilds 63 milliseconds.

As a result, the production code gets the best performing yet least obvious code.

An example of a place where compact code is slow is the IIf construct, or in certain circumstances, a one line dLookup as opposed to dimensioning, opening, writing a value to a variable, closing and setting to nothing a recordset to return a value. One place I won't scrimp on is with implicit type declaration characters (there is no measurable difference in performance between Dim lngI As Long and Dim lngI& yet I use the more verbose form).

Addressing the orginal example: The example is particularly bad because Access 97 had a bug that prevented closing of Access when one referred to a checkbox control value without explicitly checking if it was equal to True or False. The first statement:

If chkCtrl Then

     vs

If chkCtrl = True

resulted in a failure to clean up an implicit object reference to the checkbox requiring the use of task manager to shut down Access. In that case the verbose code is necessary to prevent running into the bug. (I believe you could also kill the reference by forcing an unhandled error). Microsoft was never very good at fixing old bugs and often just added features. Many bugs carried over to newer versions and although I never tested whether this one did, I continue to use a more verbose form when checking check box values.

Ciao
Jürgen Welz
Edmonton AB Canada
[email protected]