VB6/VBA 14, Components
One of the advantages VBA is that has most of the facilities of full Visual Basic. But that advantage can also be a bain as we shall shortly see in the case of providing an OpenFileDialog. In effect, we have too many choices of how to implement the open file dialog. And in order to be careful that you are making the right choice you have to spend some time deciding which is the best way to implement the OpenFileDialog. This tutorial is based on a real world development for a client and some of the trade offs that had to be decided in implementing some VBA programs.

It all started in the case where I was developing some VBA code using the

Figure 1 - References in VBA

Tidestone Formula 1 spreadsheet component (see the Reference in Figure 1, fifth from the top) . This commercial ActiveX component allows users to create Excel-like
spreadsheets. Now you might ask, "why use a Tidestone Spreadsheet component when Excel's VBA exposes all the power of Excel's spreadsheets directly ?". Well, the problem is that the customer is actually using Visio on all their desktops but not Microsoft Office. To further complicate things, the version of Office available on each desktop varies from Office 95 to Office 2000. So to avoid the problem of not having the right libraries loaded on each machine the client wanted to use the Tidestone spreadsheet control which they already had done some development work with.

The Dilemma

The dilemma arose when we discovered that Formula 1 had a special OpenFileDialog called SSOpenFileDlg. At first glance it appeared to provide all the functionality of OpenFileDialog. I had been using the Microsoft Common File Dialog control but that added an additional 149KB to the distributable file; so the client suggested I work with the Formula 1's SSOpenFileDlg.

So I setup a test routine to tryout SSOpenFileDlg in comparison with OpenFileDialog (see Figure 2 below). At first it looked like we had a winner - there was no problem getting the Formula 1 Open File dialog to appear and for users to either select a file or cancel the selection. And since it looked and worked like the "real thing" our suspicion was that Tidestone just called the Windows API function directly.


However, we ran into a problem. The routine needed to identify the dialog with a special title, "Enter Definition File", and it also had to apply some file filters - *.def for Definition files and *.* for all files in the directory just in case the user wanted to check that the file had not been renamed or moved from the directory being searched. However, to our surprise, Tidestone did not supply any way to set these properties of the standard OpenFileDialog (and as it turned out 4-5 other properties were omitted as well).

So one of the members of the team suggested following the Tidestone lead and just use the Windows kernel function directly by adding a reference to it in the .bas module we already were using for other file operations. The declaration is simple:

' WinAPI Declarations
Private Declare Function GetOpenFileName% Lib "COMDLG32" _

And the resulting code (triggered by the ReadFileRAW button in Figure 2) is a bit more complex because for filters you have to separate each portion of the filter string with the null string and be sure to terminate the whole thing with two Nulls - Chr(0) & Chr(0) as shown just below.

Opend.CustomFilter = "Def file" & Chr(0) & "*.def" & Chr(0) & _
"Text files" & Chr(0) & "*.txt" & Chr(0) & "All files" & Chr(0) _
& "*.*" & Chr(0) & Chr(0)
Opend.DefaultExt = "*.def"
Opend.InitialDir = App.Path
Opend.Title = "Open using RAW Win API"
filname = modFileDialog.WinFileDialog(Opend, 1)

Well as it turn out, although using the WinAPI is free (there is no additional overhead- the functions are always available); our coders were making enough mistakes using the API functions to cause fits. Twice coders forgot to put the double Nulls at the end of a filter string. The result was some serious debug time.
So we decided to try one more option, using the FSO-FileSystemObject of the Microsoft Scripting Runtime (see Figure 1 where it is highlighted in the References dialog). We checked the size of the SCRUN.DLL  and at 137KB it turned out to be marginally smaller than the Common File Dialog Control. So the ReadfileFSO command button triggers the FSO routine. However, we discovered a problem - although FSO allows for extensive file manipulation (open file, parse filename, move a file, find a file, delete a file, and many others); it does not support an open file dialog. Back to square one.

Figure 2 - Test UserForm for VBA OpenFile.Dialog

As it turned out, during our testing an ever expanding UI-User Interface required us to also save a file and print results. These UI needs are matched by the Common File Dialog control which supports the standard Windows Open, Save, Print, Font and Color dialogs. In addition, the team felt that the Common Dialog offered/exposed the best set of control properties for the Open and Save dialogs as listed below:
CancelError - signals that the user clicked the dialog's Cancel button
DefaultExt - sets the Default file extension, *.def in our case
DialogTitle - sets what will appear in the Dialog's title bar
Filename - sets what default filename to be used, if any
Filter - set the file filters to be applied
Font... - allows a whole range of font styling to be set
Help... - allows several Help file settings to be initialized
InitDir - sets which directory the Open or Save dialog starts in
Position... - parameters allow setting the exact start up position of the dialog
DialogTitle, Filter, and InitDir turned out to be the most often used properties.

So we came full circle back to using the Common File Dialog control. Its extra size of 149KB was outweighed by the additional properties and dialog types it supported. This is a cautionary tale. When we begin to examine database connections in VB and VBA the number of options increases from 2 or 3 to nearly a dozen different access methods where there are even more complicated trade-offs to be made. Already we have covered one of the options - the popular and easy to use DAO access method. So this is the strength and challenge of VB and VBA, inevitably there is a say to get just about any Windows task programmed; and many times there are several choices - the problem arises in choosing the best way.

References: Wrox Press Peter Wright's Beginning VB6 discusses the Open File Dialog and other common controls on pages 335-357 while the Coriolis Press VB6 Black Book covers the same topics with a more programmatic view point from pages 342-368.

Top of Page  Tutorials Home