Basically Visual

June/July 1996

by Peter G. Aitken 1996

Originally published in Visual Developer magazine

Beware the Variant

As most of you have probably already discovered, one of Visual Basic's handiest tools is the Variant data type. To my knowledge, Variant - or anything like it - is unique to Visual Basic. It can be thought of as a "typeless" data type, even though that may seem like a contradiction in terms. A type Variant variable is not limited to holding a single type of data, but can hold any of Basic's native data types (with the sole exception of fixed-length strings). You can put just about anything you like in a Variant - Byte, Integer, Long, Float, Double, Currency, Date, or String - and Basic will take care of the details. Variants can eliminate many of the type conversion and overflow problems that sometimes plague programs. This convenience has led to some programmers using Variant for all or most of the data in their programs, regardless of whether it is really warranted. Microsoft unintentionally encourages this by making Variant the default variable data type. This is unfortunate because there are some potentially significant drawbacks to the use of Variants.

The first has to do with memory use. It would not surprise anyone to learn that something as flexible as a Variant takes up a bit more memory that a plain old Integer or Float variable, but just how much? Take a peek at the Data Types Summary page in Visual Basic on-line Help and you'll see. When holding a numeric value, a type Variant always occupies 16 bytes regardless of the native type of the data. The extra memory overhead for using a Variant ranges from a minimum of 8 bytes when the Variant is holding a type Date, Currency, or Double value, to a maximum of 15 bytes for a type Byte value. For string data, a Variant requires 22 bytes plus the string length, 12 bytes more than the standard String type. If you have a few, or even a few hundred, Variants kicking around, it's unlikely to have any practical impact on system resources. With a big array or two, however, it's a different story.

The second drawback associated with use of the Variant data type relates to performance - that is, program speed. Since the Visual Basic documentation has no information on this topic, we have to investigate for ourselves. Is code that uses Variants slower, and if so, by how much? This brings up another question: how can we time program execution? The answer is surprisingly simple, but it doesn't involve Basic's Timer() function as you may be thinking.

Why not use Timer()? Visual Basic Help tells us that Timer() returns the number of seconds since midnight, and we certainly want more accuracy that a second. Actually, Timer() returns a floating point number, and calling it repeatedly indicates that its true resolution is on the order of 0.06 or 0.05 seconds. This is a lot better than a full second, but we can still do a lot better with almost no extra effort. We'll use the TimeGetTime() function, which is accurate to one millisecond, or 0.001 second, and is much better suited to our timing needs.

TimeGetTime() is not a Visual Basic function but is part of the Windows API (32 bit only). To call it from your program you must include its declaration in your program:

Private Declare Function TimeGetTime Lib "winmm.dll" () As Long

This line should be placed in the Declarations section of the form module from which the function will be called. If you'll be calling it in multiple forms, you can either place a function declaration in each form module or, the preferred way, add a Basic module to the project and place the function declaration in its Declarations section. In this case, you must omit the Private keyword.

With the function declaration in place, you can call TimeGetTime() just like one of Basic's built-in functions. To time the duration of a process, you would use it like this:

start = TimeGetTime()

' Do stuff here.

duration = TimeGetTime() - start

Remember that the resolution of this function is one millisecond, and for today's fast processors a millisecond is a long time - a Pentium 133, for example, will execute 133,000 cycles in a millisecond! To get meaningful results for most statements it is necessary to execute them many times in a loop. If 100,000 repetitions of a particular program statement requires 600 milliseconds, and the same number of repetitions of another statement requires 300 milliseconds, it's pretty clear that there's a significant speed difference between them!

Can we say, however, that the second statement is exactly twice as fast as the first one? No, because the times reported include not only the execution of the statement(s) being tested but also of the loop statements. We can correct for this by timing the execution of an empty loop and subtracting that value from the timing for the test loop. The difference should give use the execution time for just the statement(s) within the test loop. Of course, both loops must have the same number of repetitions and the same counter variable data type.

Yes, I am eventually going to get to the point! So just how slow are Variants? For standard data operations I timed three things: a combination of multiplication and division, a combination of addition and subtraction, and string concatenation. For the first two comparisons I compared Variant with type Double, and for the last I compared Variant with Basic's standard String type. The results? (the envelope, please!):

Multiplication/division: Type Double is 72.1% faster than Variant

Addition/subtraction: Type Double is 78.1% faster than Variant

Concatenation: Type String is 26.6% faster than Variant

But wait, there's more. As you may know, Visual Basic now has two version of most of its string manipulation functions: the old version, whose name ends in $, which takes a type String argument and returns a type String, and a new version with no $ in the name that takes a type Variant argument and returns a type Variant. Thus we have Mid$() and Mid(), Left$() and Left(), and so on. Are there performance differences between the two versions of these function? You bet your banana - and pretty big differences in some cases. Here are my test results.

Function String version time Variant version time String version faster by
Mid 510 2036 299%
Left 161 1718 967%
Right 172 1702 890%
Chr 226 427 89%
Str 4092 4270 4.4%
Trim 294 1841 526%
Hex 366 559 53%

There's quite a range of results here. Str() exacts only a 4.4% time penalty in its Variant form, while Left() costs you a whopping 90.6%. Looking at the numbers another way, the Variant version of Str() is over 10 times slower than the String version. For the occasional isolated use it's nothing to worry about, but for programs that perform a lot of heavy-duty string processing it can make a real difference. I am certainly not advising you to abandon Variants altogether – they are way too handy for that – but you do need to be aware of the costs involved.

Cracking Quicken

An enormous number of people out there use Quicken to manage their finances – yours truly included. For developers, however, the potentially huge Quicken aftermarket has long been closed because of Intuit's proprietary database format. No longer, thanks to a company called Spinoza Limited. They have recently released VBXchange, an add-on that provides full access to Quicken database files. VBXchange is not a custom control. Rather, it is an Open Database Connectivity driver for Quicken 5.0 for Windows files. Once this driver is installed, you use your normal arsenal of data-aware controls to get at the data, just like working with any other ODBC source. This provides you with the maximum flexibility, including the possibility of using third party data-aware controls in addition to those provided with Visual Basic. The driver is "intelligent," freeing the developer from worrying about the various constraints that Quicken places on its data files and automatically performing actions required to maintain data file integrity, such as adding the matching transaction for a transfer of funds between accounts.. The driver also works with other ODBC-compliant development tools, such as Access and Delphi, as well.

I think that VBXchange opens a large window of opportunity for Visual Basic developers. As good as Quicken is, there are lots of things it can't do or does poorly. There's a potential customer base of several million just waiting for your creations! VBXchange lists for $349. You can reach Spinoza at (800) 700-2217

FOR EACH and Collections to the Rescue

I think that one of the coolest things in Visual Basic is the FOR EACH ... NEXT construct. It lets you loop through all of the elements in a group without having to know the details of exactly what's in the group. The group can be a data array, but this is a relatively uninteresting use of FOR EACH because the same result can be obtained with the LBOUND() and UBOUND() functions. Thus, to set all elements of array MyArray to the value 1 you could write

For Each I in MyArray

MyArray(I) = 1


or you could write

For I = LBound(MyArray) To UBound(MyArray)

MyArray(I) = 1


These two examples do exactly the same thing; the second method is a lot faster. But this isn't all that interesting. It's when you use FOR EACH with collections that it becomes a uniquely powerful tool. A collection is a method of organizing items used in the program. Visual Basic provides the Collection object that you use to keep related items in a group. There are also several so-called internal collections that Visual Basic maintains itself. We'll take a look at some of the useful things you can do with both types of collection.

A user-defined collection can hold just about anything you like: any type of data – string, number, logical – and objects as well. What's more, the items in a given collection do not have to all be the same type. You might think that so far, a collection sounds pretty much like an array of type Variant. Similarities exist between the two, but there are differences, the most important two being (1) A collection automatically changes size, growing as items are added and shrinking as items are removed, and (2) Items in a collection can be identified not only by a numerical index, but also by an alphanumeric key.

You create a collection with the following syntax:

Dim CollectionName As New Collection

A collection starts out empty. You add items to it with the Add method:

CollectionName.Add Item, Key, Before, After

  • Item is, of course, the item being added to the collection. It can be any expression or an object name. It is the only required argument.
  • Key is a string expression that will be associated with Item. Key must be unique within the collection or an error occurs.
  • Before is an expression specifying the numeric position or the string key of an existing item in the collection. The new item will be placed immediately before the specified item. If you do not specify a position the new item is added at the end of the collection.
  • After works the same way as Before, but the new item is placed after the specified existing item. You can specify After or Before but not both.
  • To retrieve an item from a collection, use the Item method:


    where Key specifies the item's numerical position in the collection or the string key that was associated with it when it was added. Because Item is the Collection object's default method you can use the following shorthand:


    To remove an item from a collection use the Remove method:


    where Key is as described above. When you remove an item from a collection, you do not end up with an empty slot, as you would with an array. Other items "move up" to fill in. Obviously this means that you cannot count on an item's numerical index within the collection remaining constant. Note that item indexes in a collection start at 1, not at 0. The Collection object has only a single property, Count, that returns the number of items in the collection.

    What can you do with collections? Don't ask! Literally, the possibilities are endless. Here are a couple of examples. Implementing a stack is trivial with the Collection object. If you've ever dabbled in assembly language programming you know quite well what a stack is – a temporary data storage location that operates on a last in, first out basis. You put one or more data items on the stack, an operation called pushing. When you retrieve an item (popping) you get the most recent item. If you pop again, you get the previous item, and so on. Here's how to implement a stack using a Collection object.

    Dim Stack As Collection


    Sub Push(Item As Variant)

    Stack.Add Item

    End Sub


    Function Pop() As Variant

    If Stack.Count Then

    Pop = Stack(Stack.Count)


    End If

    End Function

    A stack has a multitude of uses. For example, it can help you to swap the values of two variables without creating a third variable for temporary storage::

    Push x

    x = y

    y = Pop

    More interesting things are possible with collections, too. Some of my favorite techniques involve using a collection to keep track of objects. Suppose you have defined a class named cWine to keep track of items in a wine distributor's database. You would create new entries and add them to a collection as shown here:

    Dim Wines As New Collection

    Set aWine = New cWine

    aWine.Name = "Hogue Cellars "

    aWine.Year = "1992"

    aWine.WineType = "Merlot"

    aWine.StockNo = "H191"

    aWine.Price = 12.50

    Wines.Add aWine, aWine.StockNo

    Set aWine = New cWine

    aWine.Name = "Jordan Estates"

    aWine.Year = "1991"

    aWine.WineType = "Chardonnay"

    aWine.StockNo = "J91"

    aWine.Price = 20.00

    Wines.Add aWine, aWine.StockNo

    Note that each cWine object that is added to the collection is given a string key consisting of its stock number. This provides two advantages. First, Visual Basic will generate a trappable error if you try to use a key that is already in use. Since you want each entry to have a unique stock number, this automatic checking for duplicates is a real help. Second, it is easy to obtain information about any wine as long as you know its stock number. Here's a function that returns the total price of a specified quantity of a particular wine:

    Function GetPrice (StockNumber As Variant, _

    Quantity As Integer) As Currency

    GetPrice = Quantity * Wines(StockNumber).Price

    End Function

    Many other tasks are greatly simplified by using the For Each ... Next construct to loop through all items in a collection. For example, to display a list of all wines of type "Chardonnay" you could do the following:

    Dim W

    For Each W in Wines

    If W.WineType = "Chardonnay" Then

    Print W.Name, W.Price

    End If


    These examples should give you some idea of all the neat things you can do with collections. They are really simple to use. After all, with only three methods and one property how bad can they be!

    Next, let's look at a couple of Visual Basic's internal collections, specifically the Controls and the Forms collections. There are other internal Visual Basic collections, but I will limit myself to these two for now. There is a Controls collection for each form in the project. This collection contains all of the controls on the form. You can loop through the collection to access all of the controls individually. For example, here's code to move all of a form's controls up by 50 twips:

    Dim ctrl As Control

    For Each ctrl In Controls

    ctrl.Top = ctrl.Top - 50


    Likewise, the following code will hide all of a form's Text Box controls:

    Dim ctrl As Control

    For Each ctrl In Controls

    If TypeOf ctrl is TextBox Then ctrl.Visible = False


    The Forms collection contains all of the forms in the project. To be more precise, it contains all of the forms that have been loaded, so you usually need to be sure all forms have been loaded before using the Forms collection. You can use the Forms collection for a variety of tasks. The following code sets the background color of all forms to bright red:

    Dim f as Object

    For Each f in Forms

    f.BackColor = &H000000FF&


    I'll finish by describing a real-world use for internal Visual Basic collections that saved me a whole lot of work recently. The project I was working on included several forms, each of which contained a large number of text box controls into which the user would enter a variety of information. The program would use this information to perform certain calculations, but that's not germane at present. I needed some way to let the user save their work and return to it later. In other words, I had to write the contents of all the text boxes to disk and later read the data bask and ensure that each piece of information went to the proper text box. The thought of manually coding all the necessary statements gave me the sweats. I though that there must be an easier way. Indeed there was, thanks to collections. By iterating through all of the project's forms, and then through each form's controls, it would be a simple matter to write each and every text box's data to disk, or to read the data back and out it in the proper places. Here is the subroutine that does the job of saving the data:


    Public Sub SaveData(sDataFileName As String)

    Dim iFileNum As Integer

    Dim f As Form

    iFileNum = FreeFile

    Open sDataFileName For Output As #iFileNum

    For Each f In Forms

    For Each Control In f.Controls

    If TypeOf Control Is TextBox Then

    Write #iFileNum, Control.Text

    End If



    Close iFileNum

    End Sub

    The process of loading the data follows the same approach. Here's that subroutine:

    Public Sub LoadData(sDataFileName As String)

    Dim iFileNum As Integer

    Dim f As Form, sBuf As String

    iFileNum = FreeFile

    Open sDataFileName For Input As #iFileNum

    For Each f In Forms

    For Each Control In f.Controls

    If TypeOf Control Is TextBox Then

    Input #iFileNum, sBuf

    Control.Text = sBuf

    End If



    Close iFileNum

    End Sub

    The beauty of this method is that the file writing and reading code does not need to be changed if I add more forms and/or text boxes to the project. Of course, old data files can no longer be used if such changes are made to the project, but that is not really a problem. It is important that the order of objects in the Forms and Controls collections be exactly the same each time the program is run; it doesn't matter what the order is, as long as it doesn't change. The order of controls in the Controls collection depends on the order the controls were placed on the form during program design. Once the program design is complete, this will not be subject to change. The order of forms in the Forms collection is based on the order in which the forms were loaded at run time. For this reason it is necessary to use the Load statement to ensure that all of the project's forms are loaded, and in exactly the same order, before either the Save of Load procedure is called. With these cautions, I have found the Forms and Controls collections to be terrific time-savers when writing code to save and load data.

    End the CD Shuffle

    One of my prerogatives as a columnist is to stray from the main topic once in a while to acquaint you with items of software or hardware that I find particularly useful. My favorite recent acquisition solves the increasing problem of CD shuffle. While a CD-ROM player is almost an obligatory part of any PC system these days, having more than one disc that you access regularly can lead to a lot of disc swapping. If you have deep pockets you can daisy-chain a bunch of individual 6X CD drives and have instant access to multiple discs. In the real world, most of us are more likely to opt for one of the CD changers that are now available at reasonable prices. I've seen both 4 disc internal models and 7 disc external ones, most sporting 4X speed drives. I am using the 7 disc model from NEC. Each of the 7 discs appears as its own drive letter. When a program accesses a particular drive, the appropriate disc is automatically swapped into place, a process that takes between one and two seconds. I was particularly impressed with ease of installation under Windows 95. I simply connected the unit to my BusLogic SCSI card, made sure the termination and SCSI ID switches were set correctly, and turned the computer on. That was it – when I opened My Computer all seven CD drive letters were displayed. I was impressed. This is how hardware installation should be, but all too rarely is.

    I've devoted drives F: through J: to my regularly used CD's, and left D: and E: open for transient discs like my daughter's Magic Schoolbus titles (these are definitely worth a look if you have young children). Downside? If you do something that makes Windows look at all drives, such as opening the My Computer window, there will be a bit of a delay as each CD is loaded in turn and its directory read. Otherwise I'm delighted with my new toy. I find I'm getting a lot more use out of my reference CD's now that I am free of the hassle of manually loading each one when I need it.