Visual Basic Expert Solutions

book coverQUE

Chapter 22

VB Versus VBA

by Jon Oelschlaeger


This chapter takes a brief look at some of the similarities and differences between Visual Basic 4 and Visual Basic for Applications. You will explores the impact that these differences might have on the design and development of your Visual Basic application code.

This chapter investigates the following differences between Visual Basic 4 and Visual Basic for Applications:

Comparing the Differences Between Visual Basic 4 and VBA

First the good news and then the bad news. The good news is that with the introduction of Visual Basic 4, the degree of conformance between the Visual Basic language syntax and that of Visual Basic for Applications (VBA) has been greatly improved. In addition, the code editors supporting Visual Basic 4 and contemporary VBA implementations have been essentially unified. For those of you that have previously tried to exploit OLE 2 Automation programming using Visual Basic 3, you will notice the following improvements:

• Programmers porting code between Visual Basic, and say Microsoft Excel 7 VBA or Microsoft Project 4 VBA will find that the job is much easier. Bi-directional code reuse or migration between Visual Basic 4 and either of these two Visual Basic for Applications programming environments is much easier, and less prone to require adapting language modifications.

• Many syntax language elements used in VBA that previously conflicted with restricted keywords and structures within Visual Basic 3 no longer conflict. This eliminates the need to enclose conflicting keywords in [...] when coding OLE 2 Automation features within Visual Basic using other VBA-compliant application object libraries.

• Object collection manipulation syntax such as For Each...Next, and object manipulation syntax such as With...End With which were quite efficient in source code and performance when programming within VBA, specifically using Excel 5, Excel 7, or Project 4, are now also supported in Visual Basic 4.

• Code organization and code editing techniques are now nearly identical between Visual Basic and VBA, as are the supporting tools and commands for editing operations.

• The scope of variables and code procedures, and the syntax for declaring variable and procedure scope are now used consistently between Visual Basic and VBA.

• Visual Basic 4 and VBA-supporting applications, such as Excel 7 and Project 4, all provide capabilities for menu bar and toolbar negotiations for embedded OLE 2 objects. This now allows both Visual Basic applications and VBA-hosted applications to equally implement OLE 2's edit-in-place capabilities.

• Visual Basic 4 and VBA both provide named argument capabilities in procedure calling conventions. Arguments can be passed using call-by-name techniques, and not just by using position dependent arguments. In a related change to the argument passing conventions, Visual Basic, like VBA, can make use of optional argument specifications when declaring Sub and Function procedures.

• Code modules, class code, and form-level procedures created in Visual Basic 4 and VBA can now be browsed and managed in a consistent integrated fashion. Previously, Visual Basic 3 code libraries could not be viewed and accessed using the Object Browser facility provided with Excel 7 and Project 4.

These are the major areas of welcome good news. However, there are still some differences between Visual Basic 4 and VBA applications code. That's sort of the bad news.

Many of the differences are a result of the different programming audiences and level of application detail that the two similar language facilities address. Some of the major differences between Visual Basic 4 and VBA that are likely to impact your applications planning, design, development, and porting are listed below.

• VBA objects, in particular user interface controls (such as text edit boxes, combo boxes, and list boxes), typically only provide a single event (or a limited repertoire of events). This is unlike Visual Basic, where each control provides numerous events, thereby affording detailed program-controlled interaction with the object. VBA controls, in most cases, provide only the equivalent of Visual Basic's Click event.

• Visual Basic objects provide the Windows Handle (hWnd) and, if appropriate, the Device Context Handle (hDC) properties which are crucial to programming with Windows API calls. VBA objects do not provide the hWnd and hDC properties. It is possible, however, to determine the hWnd and hDC for VBA controls, but the coding required to do this is not necessary when using Visual Basic.

• VBA windows control objects typically have a more restricted set of properties that can be retrieved or set than their functionally equivalent Visual Basic counterparts.

• Visual Basic enables programmers to directly create several generic classes of windows (for example, MDI Parent, MDI Child, and ordinary windows), and in a variety of styles (like fixed border and sizable border). In addition to creating windows, Visual Basic programmers can create and manipulate the usual window components, such as menu bars and custom toolbars. VBA does not directly provide for creating windows from generic classes, but rather enables the programmer to create windows only from the specialized classes and styles that are supported by the application context within which VBA runs. Additionally, VBA programmers are limited to manipulating the hosting application's menu bars and toolbars.

• Visual Basic 4 programs can be designed to define their own Classes and also create multiple instances of Objects of these custom Classes; VBA presently cannot.

• Visual Basic 4 programmers can design and create OLE 2 Automation Servers which can be utilized by other Visual Basic and VBA applications as components of larger applications. This capability to create OLE 2 Automation Servers is not available within VBA.

Summarizing Syntax Differences

Previous to the introduction of Visual Basic 4 , there were quite noticeable syntactical differences between Visual Basic 3 and subsequently released applications—such as Microsoft Excel 7, Microsoft Access 2, and Microsoft Project 4 —which supported variants of the Visual Basic language (known collectively hereafter as Visual Basic for Applications, or just simply as VBA).

Note: Although Microsoft Access versions 1 and 2 also host a programming facility with extensive similarities in concept and syntax to Visual Basic 3, this language facility is not, strictly speaking, VBA compliant. Instead, the Access 1 and 2 built-in programming language facilities are known as Access Basic. Presumably, the forthcoming release of the Microsoft Access 7 will show greater conformance to the VBA language model. However, for the purposes of limiting the scope of discussions here, we will focus mainly on Excel 7 and Project 4 VBA and Visual Basic 4 features when making comparisons.

With the recent introduction of Microsoft Excel 7 and Microsoft Project 4, the syntax differences between VBA and Visual Basic 4 are quite small.

Understanding Common Syntax

For those Microsoft products that are not fully VBA-compliant as of the date of publication of this book, the keywords that reflect the language syntax have for some time been a fairly consistent set, and also include restricted keywords specified by Microsoft for eventual future expansion and compatibility purposes.

As a first step in understanding the common syntax and keyword similarities, you should closely compare the language facilities of all Microsoft products which support the Visual Basic language. Such a comparison quickly becomes important when you are contemplating the development of a Visual Basic application that is to be migrated or ported across the listed development or runtime environments.

Analyzing Word 7 Syntax Differences

Word 7 implements the least consistent and compliant version of Basic. In fact, Word 7 is not really a Visual Basic for Applications (VBA) language host facility at all.

Word 7 supports a variation of the Basic language which is known as Word Basic. The focus of Word Basic is to provide a language programming facility within Word 7 that is highly tailored to the manipulation of word processing documents, and which operates by itself in a stand-alone setting.

Unlike the other Microsoft products, Word 7 cannot participate as a client or controller in OLE 2 Automation operations, and therefore cannot access and manipulate another application's objects. This state of affairs is most visible in the fact that Word 7 does not support the VBA CreateObject and GetObject functions which are central to all OLE 2 Automation operations. Word 7 does not support the Debug object, the Me object, or the Nothing object. Finally, the For Each... and With... constructs which are the hallmark of VBA for efficient collection and object manipulation are also absent in Word Basic.

In fact, Word 7 is not really object-oriented. Word Basic instead relies on cursor positioning and selection-oriented techniques, combined with menu command-equivalent language syntax elements to accomplish its programming purposes. The area of greatest Basic language consistency between Visual Basic, VBA, and Word Basic lies in the realm of string processing functions and variable types. This is pretty much what you would expect, because document content manipulation relies to a significant degree on string processing.

Word 7 does, however, provide support for OLE 2 Automation as a server. That is, Visual Basic and the other truly VBA-compliant host application programming facilities can use Word 7 as an OLE 2 Automation server.

Applications that fully support OLE Automation make their "documents" available as objects. However, with Word 7, you cannot use OLE Automation to directly manipulate a Word 7 document as an object. That is to say, you cannot use the GetObject function in Visual Basic to access a Word 7 document. Moreover, the Word Basic language does not support properties and methods or the syntax structure which would allow for their specification.

Word 7 does offer, as an OLE Automation server, an object called the Word.Basic object. This object essentially presents all of the programming language elements of Word Basic for use in manipulating the Word 7 application, and any Word documents which Word 7 currently contains. Most of these language elements are statements, or "commands" which manipulate Word document components.

A major difference between using the Word.Basic object, and the objects within other VBA compliant applications like Excel 7 and Project 4, is that the language syntax of Word Basic is such that the "thing" to be acted upon is specified along with the action to be taken within a single line of Word Basic code. The concepts of properties to modify the attributes of objects, and methods to perform some action on objects is absent from Word Basic.

Interestingly, the Word.Basic object and its syntax does support one of the key features of the broader VBA syntax; the capability of position-independent named arguments (call-by-name arguments), and the omission of optional arguments in function and statement argument lists.

It is likely that at some point in the not to distant future, Microsoft will revamp Word for Windows to:

• Fully support OLE 2 Automation with Word functioning as an object-oriented server. Word would offer objects, properties, and methods that could be used by other VBA client code, much as Excel 7 now offers its complete panoply of objects in an object library for external reference.

• Integrate a new VBA-compliant language facility into Word. This would provide Word with the capability to act as a OLE 2 Automation client in relationship to other OLE 2 Automation servers.

Of course one of the key considerations in achieving this advancement would be to maintain backward compatibility with the current Word Basic language syntax, and the Word.Basic OLE Automation object. This compatibility would be needed to support existing Word 7 macros written in Word Basic, or Visual Basic or VBA applications currently using the Word.Basic object. Until this revision takes place, the Word 7 product is sort of an odd-man-out in the world of VBA programming in Microsoft's application product lineup.

Analyzing Microsoft Access 2 Syntax Differences

The Basic language variation supported by Microsoft Access 2 is called Access Basic. Access Basic is nearly fully compliant with VBA, but not quite. Again, it is highly likely that Microsoft will implement full VBA compliance with the next release of the Access product, but this is currently not the case. Areas of non-compliance which are likely to have a significant cross-application programming impact include:

• Does not support the use of named arguments and omission of optional arguments in function and statement invocations.

• Does not support the use of the VBA construct With...End With for modifying multiple properties, or invoking multiple methods for a single object.

• Does not support the use of the VBA construct For Each...Next for looping through the members of a collection, or the elements of a Variant array.

When designing Visual Basic code which might be ported later to Access 2, you can employ specific programming strategies to eliminate problems with these non-compliance areas. Here's how to deal with:

Argument differences—Do not use named arguments or omit optional arguments. Specify all arguments, and make sure that they are specified in the correct positional order. This will work correctly with Visual Basic, and all variations of VBA, including Access Basic, and the Word.Basic object.

• With...End With—Do not use this approach to multiple specification of properties or invocation of methods for a single object. Instead, spell out each code lines' object name and property or method.

• For Each...Next—Use ordinary For loops instead. This requires declaration of an appropriate loop variable, and the range of values of the loop. Usually, the ending value of this loop will be the name of the collection object and the count property of the collection object (collection.count).

Another area where Access Basic is not fully VBA compliant is in the area of default declaration specifications. This is a programming area that, for most typical programs, will have less impact. The specifications for default declaration which are not supported by Access Basic are

• CDecl—Specifying the use of C language argument conventions in external code resource function declarations; applicable only to the Macintosh

• DefBool—Default variable letter range automatically defined of the type boolean

• DefByte—Default variable letter range automatically defined of the type byte

• DefDate—Default variable letter range automatically defined of the type date

• DefObj—Default variable letter range automatically defined of the type object

These declaration-related statements can usually be easily avoided. All variables can be declared explicitly, without the use of the default specification mechanism.

Finally, for compatibility with Macintosh versions of your applications, the following functions are not supported by Access Basic, but are supported in VBA:

• MacID—Converts a four character constant to a value to be used with the Dir, Kill, Shell and AppActivate functions when executing a VBA application on a Macintosh

• MacScript—Executes a named Macintosh Script file

Clearly, these areas of non-conformance are not presently a problem, because no implementation of Access 2 exists for the Apple Macintosh. Furthermore, it must be assumed that should an implementation of Access 2 (or higher version) be offered for the Macintosh, then this issue would be resolved by Microsoft.

These points where Access Basic and VBA syntax are not perfectly synchronized represent, in most cases, only minor limitations within Access Basic. The code should function without serious errors at worst and reveal subtle errors at least.

Access 2 itself does not offer OLE 2 Automation library capabilities for external use. That is, Access 2 cannot act as an OLE 2 Automation server. If you were to check the Windows Registry, you would find Access 2 absent. This can most easily be observed by starting up the Windows Registry Editor application REGEDIT.EXE (found in the WINDOWS directory).

If you start the REGEDIT and have the Access 2 product installed on your computer, you will find that Access 2 has a registered association between the file name extensions it supports such as MDB and MDA, but does not register to offer any object libraries.

However, Access 2 can function as an OLE 2 Automation client, using and manipulating the objects offered by other OLE 2 Automation server libraries such as those provided by Excel 7, Project 4, and the Word Basic object offered by Word 7, as well as OLE 2 Automation Server Class libraries that are created and registered using Visual Basic.

Access 2 is unlike other VBA-compliant applications in other respects. VBA-compliant products such as Visual Basic itself, Excel 7, and Project 4 have both the capability to reference-link to external OLE 2 Automation server libraries, and have an Object Browser to view the contents of such libraries. Access 2 does not have the capability to reference-link to OLE libraries, nor does it include an Object Browser.

This requires that you know the objects, methods, and properties of any OLE 2 Automation Server libraries that you choose to use from inside of Access 2. Using the Object Browser of some other application like Excel 5 or Visual Basic 4 to get the information you need is about the only way to bridge this lack in Access 2.

Analyzing Excel 7 and Project 4 Differences

When comparing Visual Basic 4 and the core syntax of Excel 7 and Project 4, the level of correspondence between all three of these products is almost complete. That is, Visual Basic 4, Excel 7, and Project 4 all implement the same core language syntax.

It is pretty clear that code which involves only the fundamental core syntax and that has been developed in any of these three environments can be freely transported between any of the others.

What remains unique to each of these three programming environments are the objects, and the objects' attendant properties and methods.

Understanding Visual Basic 4 Extensions

Visual Basic includes some extensions to the core VBA language capabilities which are unique to the Visual Basic product. Some of these Visual Basic extensions involve syntax elements, while others involve capabilities and objects that are not present in any other VBA implementations. These differences are primarily found the Command keyword, device contexts, and object references.

Using the Command Keyword

Visual Basic as well as Access 2 makes use of the keyword Command, whereas other VBA-compliant applications do not. The keyword Command is a function which returns a string containing a command line argument. This function enables you to retrieve and subsequently interpret a string of characters that follows the executable program name which starts a Windows application—in this case a Visual Basic (or Access 2) application.

Excel 7 and Project 4 allow the specification of a startup command line argument string, but the interpretation how the command line argument string should be used is a fixed built-in capability of the application itself, and is therefore not available to any VBA code module which may be associated with an Excel 7 or Project 4 "document" file.

As a practical programming consideration it is therefore advisable not to make use of the Command function in code that is used across Visual Basic and the various VBA contexts. Generally, this does not present a serious programming restriction or limitation.

Using Device Contexts in Visual Basic

Another area where Visual Basic and VBA differs is Visual Basic's ability to manipulate Windows device contexts. A Windows device context is an abstract graphical area and the data which characterize the area, such as its dimensions, scaling, aspect ratio, pens, brushes, color palette and so forth. Visual Basic affords you the capability to programmatically modify, via the Windows API, the device contexts associated with the following objects:

• Printer Object—Where the device context reflects the usable printing area and graphical capabilities of the default Windows printer

• PictureBox Object—Where the device context is formed by the interior area within the outer border of the PictureBox object

Form Objects—Where the device context consists of the interior area within the form, excluding the border and title bar areas. All styles of Visual Basic forms (regular forms, MDI Parent forms, and MDI Child forms) have associated device contexts

Visual Basic provides corresponding properties, methods and functions of these device contexts which enables you to manipulate them. All of the methods are graphical methods, since they render some graphical modification of the objects device context. The attendant Visual Basic methods, properties and functions of a device context are listed below.

• LoadPicture(filename)—A function that loads a device context with a bitmap (BMP or RLE file), metafile (WMF file), or icon (ICO file) obtained from disk.

• SavePicture Object.Picture, filename—A statement that copies the graphical contents (Picture property) of a device context into a bitmap, metafile, or icon file to be saved on disk. You can also use this function to save the device context contents of OLE Container objects, consisting of the graphical rendering within the OLE Container's bounding box.

• PaintPicture Object.Picture, X1, Y1, width1, height1, X2, Y2, width2, height2, opcode—A method that draws (renders) the contents of one device context onto the another device context, making specified modifications during the drawing process. The Picture parameter is the source device context for the drawing, typically specified as the Picture property of the object acting as the source device context. The object upon which the PaintPicture method operates is used as the destination device context. The X and Y coordinates specify offsets from the upper left-hand corner of the destination device context and source device context respectively. This allows clipping and offsetting in the copy operation. width and height parameters specify compression or stretching of the device context when copied. Finally, the opcode parameter specifies the type of copy operation to be performed when copying bitmap graphics. The opcode parameter is as specified in the Windows API function call for the BitBlt function.

• PSet [Step] (X,Y), color—A graphical method that draws a point at the specified X and Y location (or offset from the CurrentX and CurrentY position, if the optional Step parameter is used). The point drawn is of the color, specified by the color parameter. The size of the point is determined by the DrawWidth property then current for the device context.

• Line [Step] (X1, Y1) - [Step] (X2, Y2), color, [B][F]— A graphical method which draws a line between the point-pair X1,Y1 and X2,Y2 (or offset from the CurrentX and CurrentY position, if the optional Step parameter is used). The line drawn is of the color specified by the color parameter. The width of the line is determined by the DrawWidth property, the current for the device context. If the optional B parameter is used, the coordinates are taken to specify the diagonally opposite corners of a rectangle to be drawn. If the optional F parameter is used, the rectangle is filled with color.

• Circle [Step] (X,Y), radius, color, start, end, aspect—A graphical method that draws a circle or arc segment. The X and Y coordinates specify the center of the circle or arc (or offset from the CurrentX and CurrentY position, if the optional Step parameter is used). The radius parameter specifies the radius of the circle or arc, and color specifies the color of the circle or arc. The start and end parameters specify the start and end angles of an arc to be formed (in radians). Finally, the parameter aspect specifies the aspect ratio of the circle. If aspect is not 1.0, then an ellipse is formed.

• Scale (X1, Y1) - (X2, Y2)—A method that modifies the metrics and coordinate system of a device context. The point-pair coordinates specify the dimensions of a diagonal of the device context (that is, the upper left-hand corner and lower right-hand corner).

• Point(X,Y)—A method that returns the RGB color number of the point in the device context specified by the coordinates X and Y.

• Print outputlist—A method that renders printed output in a device context. The type of printing performed is determined by the various Font-related properties of the device context, and the ForeColor property. The outputlist parameter is any valid printing expression.

• CurrentX—A property that sets or gets the current X location of the pen position relative to the upper left-hand corner of the coordinate system origin within the device context. Printing or drawing methods applied to the device context also modify the CurrentX property.

• CurrentY—A property that sets or gets the current Y location of the pen position relative to the upper left-hand corner of the coordinate system origin within the device context. Printing or drawing methods applied to the device context also modify the CurrentY property.

• Picture—A property that sets or gets the graphical contents of the device context.

• DrawWidth—A property that sets the drawing pen width to be used in the device context. Specifies the pen width used by the various drawing methods.

• ScaleMode—A property that sets or gets the scaling and coordinate system used within the device context. Also the properties ScaleTop, ScaleLeft, ScaleWidth and ScaleHeight are related to the ScaleMode property, and the Scale method when defining custom coordinate systems and scales for a device context.

Additionally, these Visual Basic objects also have a property—hDC—the handle of a device context, which enables you to identify the device contexts by their Windows assigned handles. As a consequence, you can invoke most of the Windows API function calls of the GDI.DLL (Graphics Device Interface) library. Declaring and using Windows GDI function calls within your Visual Basic program gives you the opportunity to perform sophisticated transformations and manipulations of the device contents in these objects.

In this regard, it is possible to access a device context by invoking the Windows API function GetDC(hWnd), which returns a handle to a device context (i.e., hDC). Of course, unlike the special properties, methods, and functions discussed above for Visual Basic manipulation of device contexts, you would have to thereafter exclusively use other Windows API calls to use the device context referenced in this manner.

A related consideration is that only Visual Basic objects have a hWnd property—the handle of a window, which enables you to identify the windows handle of all screen windows objects. Again, to invoke most Windows API function calls of the USER.DLL, and some of the function calls of the GDI.DLL libraries, you need to provide the windows handle as one or more of the calling arguments. For example, to send a message to a Windows window, you need to provide the hWnd as a calling parameter in the Windows API SendMessage(hWnd, Msg, wParam, lParam) function, or the PostMessage(hWnd, Msg, wParam, lParam)function. Note also, that in order to invoke functions calls to a device context, you must first obtain its hDC using the GetDC(hWnd) function, and the GetDC function needs the window handle as a calling parameter.

It is of course possible to use the Windows API function GetActiveWindow() from within a VBA application to obtain the windows handle (hWnd) of whichever window is active within a VBA application, and then proceed from there. Some amount of additional coding work would have to be done in VBA code —activating the desired window—prior to making the GetActiveWindow() function call that would not have to be done if you would do the same coding in Visual Basic.

Because Visual Basic alone provides these capabilities, you must carefully consider several issues when developing an application. First, any application that must directly manipulate a device context is best developed as a Visual Basic application, rather than a VBA application. Second, Visual Basic code which manipulates the above listed device contexts cannot be ported into an equivalent VBA application code module.

As a practical matter, it would probably afford the maximum flexibility to develop a set of Class modules in Visual Basic 4 that both create and manipulate the device contexts that your applications might need. Because the Class modules can be registered as OLE Automation servers, both Visual Basic applications and VBA applications can create the objects they need from these classes, along with appropriate methods and properties for the object. The Visual Basic and VBA code that creates instances of objects (that is, instantiates the object) from these classes—using the CreateObject(registered.classname) function—can successfully execute unmodified in both the Visual Basic and the VBA contexts.

Using Object References in Visual Basic

Another area of difference between Visual Basic and VBA is in object referencing. Some of the capabilities of Visual Basic to reference and allocate storage for objects is not present in VBA.

Specifically, the following keywords—which are part of Visual Basic's repertoire—are absent within VBA:

• Me—Acts as an implicitly defined global object variable within the runtime environment of Visual Basic. It is an object variable that references (provides the base memory address of) the object whose code stream is executing at any instant. Various VBA-host environments provide a similar concept. For example, in Excel 7 VBA, the method ThisWorkbook returns an object variable which references the workbook object within which the code stream is currently executing.

• New—A keyword that is used in the syntax of object variable declaration (for example, Dim objectvar As New class, where objectvar is an object variable, and class refers to a class of Visual Basic or externally creatable objects). The keyword New invokes the class constructor, thereby creating an instance of an object of the class, and assigns the reference of the object to the object variable.

• By invoking the class constructor, the New keyword also has the effect of dynamically allocating storage for the member variables of the class (the object's properties), and loading of the member functions of the class (the code library elements which embody the object's methods). The classes used by the New keyword can be any Visual Basic intrinsic class (such as controls, forms, and collections), and any externally creatable classes, such as Visual Basic Class module classes, or externally creatable OLE Automation classes.

• In VBA-host environments, dynamic memory allocation for object storage is usually handled someplace implicitly buried within a function for an object class. For example, in Excel 7 VBA, the constructors which create an instance of objects of a class are incorporated into an object called a collection. Each class has a corresponding collection—like Workbooks is the collection for Workbook objects. Within each collection is a function that creates the object( similar to a C++ constructor)—a method of the collection called Add. It is this function that actually does the memory allocation.

• TypeOf objectname Is classname—This is a logical expression that determines if the objectname object is an instance of the classname class. Use of this syntax is only permitted in If[e1]Then[e1]Else expressions as the logical If test.

• Although no such syntax exists in VBA, there are alternative mechanisms to accomplish much the same thing within VBA-host versions. For example, in Excel 7 VBA, there is function called TypeName(object), which returns the class name of an object as a string. You can then test the class name using typical logical comparisons.

The primary consequences of these differences between Visual Basic and VBA are strongly influenced by whether the application you are developing is dependent on the capabilities provided by these unique syntax elements.

Again, if your application needs to perform object-class tests, determine within which object the code stream is currently executing, or be able to perform sophisticated instantiation of objects, then such code should be written in Visual Basic. Converting it to another VBA-host environment would require rethinking the code, and then making modifications.

Conversely, if you want to develop and test parts of an application within Visual Basic, and later port the code into a VBA execution environment, you should avoid the use of the above syntax elements, because they are not supported in VBA.

Comparing Environment Differences

One of the key differences that impacts not only language syntax but also behavioral characteristics of Visual Basic programs as compared to VBA programs is the ability of Visual Basic to create its own generic window objects. The consequences and considerations which these differences entail will be investigated in the subsections that follow.

A second major difference that has a significant impact on the characteristics of Visual Basic when compared to VBA is that Visual Basic controls generally have more extensive event capabilities (that is, a greater number and variety of windows messages are decoded and presented as Visual Basic events) than do corresponding functionality VBA controls. In this regard, the comparison is drawn between the Visual Basic controls of the controls toolbox, and the similar controls of VBA's controls toolbox. Also, many of the properties of functionally comparable controls in Visual Basic and VBA are different. These differences are addressed in following subsections.

Reviewing Object Differences

In the previous major section you focused on language syntax differences between Visual Basic 4 and various implementations of VBA. In this section you will shift your attention to differences among the objects that are part of the Visual Basic and VBA environments.

To accomplish this comparison let's first subdivide the comparison into two major parts—window objects and control objects.

Comparing Visual Basic and VBA Window Objects

One of the chief distinctions between Visual Basic 4 and VBA is just what constitutes a "window" in these different environments. Consider for a moment that in Visual Basic you have a fair degree of control over defining your own generic windows, and that in VBA the windows you can use are more-or-less predefined for you as some part of the VBA host application's object hierarchy.

To clarify this issue of windows a bit you should recall that Visual Basic can statically and dynamically (during design and during execution) create windows of fairly general classes and styles. Visual Basic programs can include general Forms, MDI Parent Forms, and MDI Child Forms. Objects of these fundamental window classes allow the greatest generalized flexibility in defining placement and use of other child windows of these Form-class windows, such as controls, and in determining how the windows themselves can be used.

This is in contrast to VBA windows classes and styles, and how they are specified and used. In a VBA environment, the classes and styles of windows are pre-ordained by the VBA host application itself. That is to say that they are built into the overall fabric of the VBA host application's user interface.

Take as an example VBA as hosted within Excel 7, where there are several classes of user interface windows which are pre-defined as being within Excel's object hierarchy. The following classes and styles of pre-defined windows are part of Excel 7:

• Worksheet Class—Window class and style, acting as an MDI Child window, that itself includes a very complex set of child window classes which are contained within its object hierarchy. Examples of child windows are PivotTable, Range, and Drawing Objects.

• DialogSheet Class—Window class and style, also acting as an MDI Child window, that itself incorporates a different set of child window classes, such as DialogFrame and Drawing Objects.

• Chart Class—Window class and style, again acting as an MDI Child window, that includes yet another set of child window classes as part of its object hierarchy. A representative list of these child windows includes ChartArea, PlotArea, Axis, and Legend Objects.

• Workbook Class—Window class and style that acts as a type of MDI Parent window for Worksheet and Chart child windows during runtime. In design-time mode, the Workbook window also acts as an MDI Parent window for DialogSheet, Module, and Excel 4 MacroSheet window classes which act as MDI Child windows.

A cursory analysis of other VBA host environments such as Project 4 and Access 2 reveals that this same application-customized assortment of user interface windows are provided intrinsically by all host applications. As other Microsoft applications are brought into VBA compliance, the diversity of application-unique window classes and styles will continue to grow.

So, you may well ask, what are some of the consequences of these different window classes and styles when comparing Visual Basic and VBA? Well, the foremost consequence is that if your application requires use of the generalized window styles afforded by Visual Basic 4, then you must design a Visual Basic-centric method for accomplishing your objectives.

You can design a Visual Basic 4 Class that includes the desired generalized windows, and then register the Class. Armed with this registered Class, you can incorporate generalized Forms (windows) within both a Visual Basic application, and any VBA application that can act as an OLE 2 Automation controller (or "client"). Referencing your Visual Basic 4 defined Class enables you to port the code which instantiates the window objects within the Class to any suitable VBA (or of course Visual Basic) host application.

Comparing Visual Basic and VBA Control Objects

Another key area of difference between Visual Basic and VBA pertains to the characteristics of controls and menus. For the purposes of the following discussion, the illustrations again principally reference Excel 7 but are equally applicable to other VBA-hosting applications.

The controls and menus have different object names, and other differences in Visual Basic and across the various VBA implementations. However, at this point you should concentrate on only one characteristic of these objects—their event capabilities.

Evaluating Control Event Differences

As you may or may not realize, the events triggered user interaction with these controls, and menus arise as the result of decoding Windows messages. As users interact with these objects, messages are queued by the Windows operating system and sent to the windows message decoding code which constitutes the message processing loop for these control's windows.

Within the Visual Basic 4 runtime environment, almost all windows messages for controls and menus are decoded and transformed into events. Typically, most of the code in a Visual Basic program consists of event procedures which execute upon the control or menu receiving a windows message.

By way of contrast, most VBA functionally equivalent user interface controls and menus only support a single (or in some cases a restricted number of) events. Again, using the Excel 7 VBA environment as an example, you will notice that the controls offer only the equivalent of the Visual Basic Click event.

Controls in VBA environments do not support the same programming style and automatic construction of event procedure "code stubs" that are present in Visual Basic. Instead, VBA programming environments reflect the "decoding of windows messages" for controls as a property of the control object. It is usually quite apparent which property represents the effect of windows message decoding, since, by convention in VBA, they all have the form "OnXyyyyy". For example in Excel 7, if the user interacts with a Text Box control, the corresponding property is OnAction.

The scheme implemented in VBA to invoke execution of the related application code stream when an "event" takes place consists of assigning the name of a code procedure (a Function or Sub procedure) to this OnAction property. When the "event" occurs, execution control is then transferred to the code procedure that responds to the "event."

The sense of the "event" being decoded by VBA with the OnAction property oftentimes does not correspond directly to what you might think of as the Visual Basic event. The VBA OnAction property in Excel 5 is not like Visual Basic's Click event. However, only in the context of Excel 5 VBA, the OnAction property is generally otherwise equivalent to "Click".

On the surface of it, for simple control uses, this seems pretty straightforward—and oftentimes it is. If you are just dealing with Visual Basic menu objects everything works just fine, because menu objects only have a single event—the Click event. However, what do you do about Visual Basic code where a given control may have several event procedures? Or, worse yet, what do you do when the logical sense of what constitutes an "event" is different between Visual Basic and VBA? For example, the Visual Basic Text Box control supports the multitude of events listed in Table 22.1.

Table 22.1 The Equivalent Visual and VBA Events

Event

Excel VBA Equivalent

Access VBAEquivalent

Change

OnAction

OnChange

Click

(none)

OnClick

DblClick

(none)

OnDblClick

DragDrop

(none)

(none)

DragOver

(none)

(none)

GotFocus

(none)

OnGotFocus

KeyDown

(none)

OnKeyDown

KeyPress

(none)

OnKeyPress

KeyUp

(none)

OnKeyUp

LinkClose

(none)

(none)

LinkError

(none)

(none)

LinkNotify

(none)

(none)

LinkOpen

(none)

(none)

LostFocus

(none)

OnLostFocus

MouseDown

(none)

OnMouseDown

MouseMove

(none)

OnMouseMove

MouseUp

(none)

OnMouseUp

While investigating the correspondence (or lack thereof) between events in Visual Basic and VBA, it should be noted that there is a great degree of variation in the number of events (or event equivalent properties) among the various VBA-host environments.

First, the events of the Visual Basic 4 controls, and the event equivalent properties of the Access 2 control are nearly the same. The two consistent areas where there are differences are:

• Visual Basic provides DragOver and DragDrop message decoding for its controls, whereas Access 2 does not. Clearly, Access 2 does not support drag-and-drop programming with its controls.

• Visual Basic provide LinkOpen, LinkClose, LinkError and LinkNotify message decoding for its controls that can participate in DDE Link operations (Label and Text Box controls), and Access 2 does not.

Secondly, notice that all Excel 7 equivalent controls provide only a single event equivalent property for all decoded messages. Furthermore, the decoded message that provokes the event action varies with each of the controls in Excel 7.

Finally, notice that although Word 7 (in Word Basic) does not have any event (or event equivalent property), it does support the usual controls. In Word 7 the standard controls are defined during the design phase using the Word Dialog Editor. This is a utility applet that installs with Word 7. The Word Dialog Editor is a specialized Windows applet that provides for the layout and design of dialog forms. Using the Dialog Editor, you can create a form with the listed controls, and set the default design-time properties of the controls. The results of the Dialog Editor are left as a set of Word Basic code on the Clipboard. This automatically generated code can then be pasted into your Word Basic application. If you have knowledge of the Windows API, some functions are accessible via WordBasic.

For example, take a look at figure 22.1, which shows a sample Word dialog box that uses an assortment of controls.

Fig. 22.1 A custom Word dialog box can be created with the Word Dialog Editor using assorted VBA controls.

The resulting Word Basic code reflecting the dialog box is as follows:

Begin Dialog UserDialog 446, 234, "Microsoft Word"
OptionGroup .OptionGroup1
OptionButton 10, 6, 133, 16, "Option Button", _
.OptionButton1
CheckBox 11, 32, 111, 16, "Check Box", .CheckBox1
Text 12, 69, 43, 13, "Label", .Text1
TextBox 12, 85, 203, 18, .TextBox1
Picture 12, 109, 204, 108, "PictureFileName", 0, _
.Picture1
PushButton 237, 10, 171, 21, "Command Button", .Push1
ListBox 225, 36, 208, 62, ListBox1$(), .ListBox1
DropListBox 235, 122, 200, 108, DropListBox1$(), _
.DropListBox1
GroupBox 230, 153, 204, 64, "Frame"
End Dialog

The Word Dialog Editor applet just provides a simplified visually-oriented way to design dialog boxes with controls, and automatically generate the Word Basic code required for the execution of the dialog box.

None of the controls used provides any external (programmable) events. Instead, the controls are used within the dialog form to obtain values and inputs from the user. Following dismissal of the dialog box upon which the controls are used, you can use Word Basic code to retrieve the values (properties) of the controls as they might have been manipulated and modified by the user. Notice that this treatment of control objects with Word Basic is quite different from that used in Visual Basic or other VBA-supporting applications.

In many respects, the way in which Excel 7 treats controls is quite similar to the way they are used in Word Basic. Predominantly, controls in Excel 7, as in Word 7, are used within the context of a DialogSheet as child windows of the DialogSheet's DialogFrame window. However, in Excel 7's use of controls, each control has one event (actually event property equivalent). This allows for dynamic programming within the scope of Dialog execution. This can be accomplished because this single "event" per control can be used to trigger the execution of VBA code streams, which in turn can modify the properties of other controls within the dialog box.

Evaluating Control Property and Method Differences

Although your first concern is probably to understand the event characteristics of controls, your next consideration is typically to note that there are also variations in properties and methods associated with these controls. Differences do indeed exist for the properties and methods of controls when comparing the Visual Basic and the various VBA implementations.

Because each control, in each different environment (Visual Basic and multiple VBA variations), has a different number of properties, the best approach to begin with may be to identify those properties that are consistent. Also because of the number of different controls, let's first restrict our comparison to the Text Box control.

The Visual Basic 4 Text Box control is most nearly like the Access 2 Text Box control. You will find this higher degree of similarity for all controls when you compare the Visual Basic and the Access versions.

There are few other general observations that can be made when comparing the Visual Basic 4 Text Box control's properties to those of its VBA counterparts:

1. Properties that are completely unique to the capabilities of Visual Basic 4 are as follows:

2. DataXxxx: Used for recordset-bound data controls
DragXxxx: Used for drag-and-drop operations
LinkXxxx: Used for Dynamic Data Exchange (DDE)

3. These specialized capabilities of only the Visual Basic 4 controls should not be used if you hope to make your programs portable across environments.

4. Properties that deal with dimensions and location (Height, Width, Top, and Left) are consistent.

5. Properties that deal with fonts and font characteristics (such as FontName, FontSize, FontBold, FontItalic, and FontUnderline) are used fairly consistently. Any small variations (noted in the table above) can usually be accommodated easily. Some VBA versions of the controls assign default values to these properties and are therefore not programmable. Once again, notice that Word Basic is an exception—using "macro-style" selection and function call techniques to achieve the same effect as Visual Basic's object.property approach

6. Properties that deal with borders and colors (BorderStyle, BackColor, ForeColor, and so forth) are used consistently. Some VBA versions of the controls assign default values to these properties and are therefore not programmable. Again, Word Basic enables you control of most of the "properties" but using its "macro-style" approach.

7. Properties that deal with control status (Visible and Enabled) are used consistently, with the exception again of Word Basic.

Remaining properties that don't compare well across Visual Basic and the VBA implementations should be avoided if you hope to port your code between environments. And, of course, if you must use control in Word Basic dialogs, count on a lot of back-and-forth conversions. You can accomplish the same effect in most cases with the controls, but only at the expense of major programming conversion efforts.

There are a few observations that can be made from comparing Visual Basic 4 Text Box control's methods to those of its VBA counterparts:

1. Unique methods of Visual Basic 4 dealing with DDE Link or drag-and-drop operations are Drag, LinkExecute, LinkPoke, LinkRequest, Move, Refresh, SetFocus, and ZOrder.

2. Excel 7 uses two differently named methods—SendToBack and BringToFront—to accomplish what Visual Basic accomplishes with a single method—ZOrder—and a parameter specifiying order.

3. The only consistent method is the SetFocus method. However, you will also notice that Word Basic uses a different technique—a language function call that moves the focus to a named object. Again, Word Basic is still "macro language" oriented and not yet VBA oriented.

Note: Similar comparisons can be made for those controls that are common to all programming environments. It would take a great deal of space in this book to exhaustively make such comparison. This process is left as an exercise for the reader—or until Microsoft comes up with a comparative database. It's on my "wish list" of helpful utilities to be added to the Microsoft Office Developers Kit. For the moment though, comparisons can be made quite easily using the Visual Basic and various products' VBA Help systems information.

Without going to this detailed comparison, as a first approximation you can generally apply the following rules-of-thumb concerning the properties and methods of functionally equivalent controls, and be assured that you have identified shared properties and methods:

Properties—If a property deals with dimension and location, fonts, borders, colors, status, or enumeration it is highly likely that the property is common to both the Visual Basic and VBA implementations. As you can notice from the Text Box control comparison above, it is equally likely that Access 2 will be the most nearly comparable to the Visual Basic control, followed by Excel 7, and then Word Basic at a far distant third is.

Methods—If a method deals with an intrinsic behavior of the control, then it is highly likely that the method is common to both the Visual Basic and VBA implementations. Examples of such intrinsic behaviors would be as follows:

Manipulating Lists: AddItem and RemoveItem methods
Setting Focus: SetFocus method

Word Basic—Has its own "macro-style" selection and function call approach to accomplishing the same ends. Conversion or porting between VBA into and out of Word 7 is like moving to a completely different programming paradigm and coding environment. Similar control and control capabilities are present, but the mechanization of programming is quite different.

Microsoft Project—Notice in the properties and the methods comparison tables above that Project 4 does not provide any of the standard windows controls "widgets" as part of its object libraries. The moral of the story is that if you need to use customized dialog boxes, or any of these usual controls, you need to develop either a Visual Basic 4 Class which can be used from Project, or some other VBA Object library that includes these controls.

As a final piece of practical advice, be careful to analyze any porting or conversion efforts, especially across these various VBA host environments. There is some greater or less degree of consistency among them, but there is no substitute for careful study of whether or not your particular applications will convert easily or with great difficulty. Hopefully, some of the analysis and investigation included in this section will point the way to your own considerations for any specific application you have in mind.

Noticing Database Access Differences

For the majority of Visual Basic and VBA applications there is at least some reliance on the use of databases. In fact, many Visual Basic and VBA applications are almost entirely developed to act as customized "front-ends" for existing (or newly contemplated) corporate database resources.

As in previous sections of this chapter, our goal will be to analyze not only the differing capabilities, but to evaluate the consequences as they influence Visual Basic or VBA software design and implementation.

Using ODBC Facilities

For the greatest generality and breadth of kinds of database access, nothing surpasses using the Microsoft ODBC (Open Database Connectivity) architecture. There are ODBC database drivers available for just about any database ever used by mortal man.

Visual Basic 4 and VBA applications can all access ODBC databases—but the degree of difficulty and the capabilities that are available is strongly dependent on which specific application is acting as the VBA host environment. A summary of these facilities (or lack thereof) is presented in Table 22.2.

Table 22.2 ODBC Facilities

VBA Environment

ODBC Facilities Provided

Visual Basic 4

The Visual Basic 4 built-in database engine (JET Version 2.5) incorporates a direct interface to the ODBC facilities; language syntax features make using this capability quite direct and efficient. The effect is that ODBC access is tightly integrated with Visual Basic, and acts like a native language facility.

Excel 5 VBA

ODBC facility can be obtained by referencing the Visual Basic 4 DAO Model Object library.

Access 2 VBA

Microsoft Access 2 also incorporates a built-in database engine; the Jet 2 Database Engine. The ODBC facilities of Access 2 are most like those found in Visual Basic 4, in that the language syntax of Access Basic includes native language syntax faciliites for ODBC database access. However, at the present time, beware of the subtle differences that exist between the Jet 2.5 Database Engine in Visual Basic 4 and the Jet 2 Database Engine found in Access 2.

Project 4 VBA

Microsoft Project 4 doesn't really support any database (except for dBASE III/IV compatible tables' import and export) facilities. To use any ODBC facilities in VBA in Project you have to declare and use the ODBC API functions, and perform API-level programming, or reference the Visual Basic 4 DAO model Object library.

Word 7 VBA

Microsoft Word for Windows 7 and its Word Basic facilities surprisingly do support a useful form of ODBC database access. The form that this takes is the InsertDatabase function, which can be accessed by choosing Insert, Database. The InsertDatabase function provides parameters for specifying a ODBC "connect string" and suitable SQL language statements that create a query result set from an ODBC database. The result set is treated like a Word Table to be formatted within a document. This bears little or no resemblance to the Visual Basic 4 or Access 2 language mechanisms for accessing an ODBC database and manipulating the result set. Furthermore, modifying and updating an ODBC database is not supported, because the InsertDatabase function is a query results oriented capability.

From a careful reading of the table you can rightly conclude that ODBC database-related code compatibility between Visual Basic 4 and Access 2 VBA environments is very good. Your conclusions would indeed be correct. However, within all of the other VBA variations, there is a different—and non-Visual Basic compatible—approach to ODBC databases.

Other alternatives are not too pleasant, and entail a lot of ODBC API programming. That is, you could declare the requisite ODBC API functions within your VBA program, and thereafter make the direct API function calls to obtain the desired ODBC database access. While this is certainly quite possible, it requires a pretty comprehensive and in-depth knowledge of how to correctly use the ODBC API functions; this is the subject for another whole book.

Using DAO Model Facilities

Only two of the products—Visual Basic 4 and Access 2 —include built-in Data Access Object Models. Previously, Visual Basic 3 had a quite different—and much simpler—DAO model than that present in Access 2. However, with the release of Visual Basic 4, the DAO models in Visual Basic and in Access 2 are nearly the same.

The consequence of this change in Visual Basic 4 is that Visual Basic and Access VBA code using the DAO model are now pretty much interchangeable. There are few exceptions within Visual Basic 4's Data Access Object model that are not present in Access 2's DAO model. The top-level differences are summarized in the following table, which lists the Visual Basic 4 unique differences:

VB 4 Objects

Summary Description

Columns

Used to store column information for DBGrid

Errors

Information on database object errors

SelBookmarks

Set of selected rows in the DBGrid object

This compatibility is not the major source of good news for VBA programmers. With the introduction of Visual Basic 4, the DAO 2.5 Model is packaged as an OLE 2 Automation server object library. This means that any VBA-host application that can create "references" to the DAO 2.5 Model libraries can also support database access via the DAO 2.5 Object library. Furthermore, the language syntax and programming techniques are identical in all VBA-host environments, because all VBA environments can use the same Object Library.

Referencing the DAO 2.5 Library in VBA

Any VBA-host environment that supports object library references can make use of the Visual Basic 4 DAO 2.5 Object Library, and therefore can use any of the database techniques available in Visual Basic 4. It is quite easy to determine if a particular VBA-host environment supports external library "references." To check for external library reference support, choose Tools, References.

Note: In Excel 7, you must be in a Module sheet to find the References item on the Tools menu. While in a worksheet, the item disappears from this menu.

When you choose References, the application displays a dialog box like that shown in figure 22.2. The dialog box displays a series of external reference object libraries whose functions can be linked into the VBA environment of the host application. In the case illustrated by the two figures, reference linkages are made to Excel 7's VBA environment, the Excel 5.0 Object Library and the Office 95 Document Properties Type Library.

Fig 22.2 The References dialog box shows the available external object libraries.

Selecting one or more of the available reference libraries is accomplished just by clicking on the desired library—setting the check box to indicate selection.

Once the DAO 2.5 library is referenced, all of the objects, functions, methods and properties within the library are available within the VBA-host application environment.

Testing DAO 2.5 Object Library References

Some simple examples of DAO 2.5 code that serve to illustrate the consistency of database access in Microsoft Project 4, Excel 5, and Visual Basic 4 are shown in code Listings 22.1, 22.2, 22.3 respectively.

The example code just opens a database, and uses some of the objects, collections, and properties of the DAO 2.5 Object Library to enumerate all of the tables within the opened database, and also to determine the number of records in each table in the database.

Listing 22.1 Code Example in Project 4

Option Explicit
Dim db As Object 'Module level database object variable
Dim td As Object 'Module level tabledefs object variable
Sub NWindOpen()
Set db = DBEngine.Workspaces(0).OpenDatabase_
("d:\access\sampapps\nwind.mdb")
MsgBox "Database NWIND.MDB is OPEN"
EnumTables 'Invoke Enumerate tables
End
Sub EnumTables() 'Enumerate tables in a database
Dim inx As Integer
Set td = db.TableDefs()
For inx = 0 To (td.Count - 1)
MsgBox "Table [" & td(inx).Name & "] with " _
& td(inx).RecordCount & " records"
Next inx
End Sub

Listing 22.2 Code Example in Excel 7

Option Explicit
Dim db As Object 'Module level database object variable
Dim td As Object 'Module level tabledefs object variable
Sub NWindOpen()
Set db = DBEngine.Workspaces(0).OpenDatabase_
("d:\access\sampapps\nwind.mdb")
MsgBox "Database NWIND.MDB is OPEN"
EnumTables 'Invoke Enumerate tables
End
Sub EnumTables() 'Enumerate tables in a database
Dim inx As Integer
Set td = db.TableDefs()
For inx = 0 To (td.Count - 1)
MsgBox "Table [" & td(inx).Name & "] with " _
& td(inx).RecordCount & " records"
Next inx
End Sub

Listing 22.3 Code Example in Visual Basic 4

Option Explicit
Dim db As Object 'Module level database object variable
Dim td As Object 'Module level tabledefs object variable
Private Sub Command1_Click()
NWindOpen 'Invoke NWindOpen
Sub NWindOpen()
Set db = DBEngine.Workspaces(0).OpenDatabase_
("d:\access\sampapps\nwind.mdb")
MsgBox "Database NWIND.MDB is OPEN"
EnumTables 'Invoke Enumerate tables
End
Sub EnumTables() 'Enumerate tables in a database
Dim inx As Integer
Set td = db.TableDefs()
For inx = 0 To (td.Count - 1)
MsgBox "Table [" & td(inx).Name & "] with " _
& td(inx).RecordCount & " records"
Next inx
End Sub

Admittedly, the three exact duplicate code examples in these three VBA compliant environments does not in any way pass as an "acid test," but you will find that any database application code that was written in any of the three example VBA environments can be ported between any of the others. This really illustrates the power of the OLE 2 Automation object library concepts in practice.

Using OLE Custom Controls

This section examines controls that are implemented as OLE 2 Object Libraries—much like the DAO 2.5 Object Library discussed in the previous section—and the consequences of using such controls.

The Object Library controls are called OLE Custom Controls, and are a specialized type of OLE 2 object dubbed a "mini-server." These OLE Custom Controls are contained in files with the OCX filename extensions. They are created using C++, normally with the Visual C++ Control Developer's Toolkit. Several third-party development kits have begun to appear on the market.

One of the chief advantages of these OLE Custom Controls is that they can be used interchangeably in Visual Basic and any VBA environments that supports their use. Like other OLE 2 Objects, the OLE Custom Controls can only be accessed following their registration in the Registry Database (REG.DAT).

If you have already installed Visual Basic 4 on your computer, all of the OLE Custom Controls that are shipped as part of the product are automatically registered in the Registry Database during installation.

In addition to these OLE Custom Controls, there are three more that are shipped by Microsoft as part of the Access 2 Office Development Kit:

Calendar—Monthly calendar page

Scrollbar—Horizontal and vertical scroll bars

Data Outline—Database-bound outline list box

Because these OLE Custom Controls (OCX) are intended by Microsoft to replace the earlier controls (VBX) you'll be seeing more OLE Custom Controls from Microsoft and other Visual Basic product add-on vendors.

Registering OLE Custom Controls

Prior to using OLE Custom Controls you must make sure that they are registered in the registry database (REG.DAT). Usually, registration is handled by the Windows setup program for a product during the installation process. This is the case for the OLE Custom Controls listed above.

However, it is also possible to manually register OCX's using one of several techniques. If you have a copy of Access 2 you can use it to update the registry for new OLE Custom Controls. To update the registry, follow these steps:

1. Open any Access 2 Form in Design mode.

2. Choose Edit, Insert Object.

3. Choose the Insert Control option from the dialog box.

4. Click the Add Control button in the dialog box.

5. Use the dialog box to locate the OCX file.

6. Select your desired OCX file.

7. Click OK.

After clicking OK, there will be a slight delay while the OCX is added to the Registry. Once registered, you can start using the OCX Control. Custom Controls can be deleted from your forms by selecting them and pressing the delete key.

When an OLE Custom Control is registered in the registry database, you can use the REGEDIT.EXE program (found in the Windows directory), to view the registry database. The view that you want to use is the "verbose" view. This can be invoked using the following command line with the /V switch:

C:\WINDOWS\REGEDIT.EXE /V

This command starts the Win95 RegEdit, which is a pure Windows Applet.

Programming With OLE Custom Controls

Programming with and using OLE Custom Controls is fairly straightforward. There are few steps that must be observed in all programming environments, including Visual Basic 4 and the various VBA implementations.

In all environments, the first step in using an OLE Custom Control is to create a Reference to the Object Library. This is accomplished by selecting the References command in the VBA host environment. Generally, this command is found under the Tools menu. Choosing the References command displays a dialog box that lists all the registered OLE 2 Object Libraries that are present in the Windows registry database.

You will notice in the References dialog box that all of the registered object libraries are shown in the list box portion of the dialog box. To create a Reference linkage between the object library and say, Excel 7, just select the appropriate option button on the left-hand side of each line.

Once a reference linkage is established, the object libraries' objects are available in the Object Browser. For example, in figure 22.3, the Excel Object Library is selected. In the left-hand list box, the Excel Objects are listed, and ScrollBar is selected. The right-hand list box accordingly shows the Methods/Properties for ScrollBar.

Note: To access the Excel 7 Object Browser, you must be in a module sheet and then choose View, Object Browser. The Object Browser cannot be accessed through a worksheet.

If you select a different object from the Objects/Modules list box of the Object Browser dialog box, the right-hand Methods/Properties list box displays all of the selected objects methods and properties.

Fig 22.3 The Excel 7 Object Browser dialog box allows the developer to examine the methods and properties of an object.

In summary, once the object is registered, you can create a reference linkage between the control's library and Visual Basic 4 and VBA applications. Once the reference linkage is defined, the OLE Custom Controls' object library exposes its objects, properties, methods, and events, which can be viewed using Object Browser. This view in the Object Browser provides you with the information you need to incorporate the Custom Control in your application. Don't forget that there are usually Help files that provide additional information about the Custom Control to assist you in your programming efforts.

Porting Code Between Visual Basic and VBA

As you have noticed thus far, there are enough differences between Visual Basic and the various incarnations of VBA environments that porting code from one to another requires some serious study.

There are a variety of conditions which you must pay close attention to in undertaking code migrations from one of these environment to another. In the subsections that follow, we will focus on a couple of the major considerations involved.

Managing Event Limitations

Previously we discussed the fact that control objects have a restricted set of events in the various VBA environments. Therefore, if you plan to use these controls in applications that will be moved across the various VBA platforms and also Visual Basic 4, you should attempt to restrict your code procedures associated with these events to a single event. Typically, this is the equivalent of the Visual Basic 4 Click event or the Change event, depending on the control.

If, however, your code porting aspirations are less generalized, you should compare the event repertoire of the controls in the actual VBA environments that you are considering. As noticed earlier, designing and developing code that is to execute in both the Visual Basic and the Access Basic environments allows the use of equivalent events in both contexts, with the exception of the following:

The conversion between the Visual Basic 4 program and the equivalent Access Basic program requires that the name of the code procedure to be executed be assigned to the OnEvent equivalent property in the Access Basic program. Otherwise you can just copy the code procedures from the Visual Basic module directly into the Access Basic code module, and then assign the name of the Visual Basic code procedure for the event to the OnXxxx property within Access code.

Converting Properties

In a like manner, there are some generalized suggestions that you might adopt for converting properties between Visual Basic and VBA environments. These are as follows:

If you bear these general rules in mind you can usually devise code that can be moved from environment to environment with ease. You may need to make minor fixes and modifications but usually nothing radical.

Using Visual Basic Class Libraries

One of the greatest enhancements of Visual Basic 4 in comparison to its predecessor Visual Basic 3 is the ability to create Class Libraries in Visual Basic 4. This capability offers the simplest and most direct mechanism to provide consistent programming approaches both in Visual Basic and from VBA in any host environment.

Developing your own custom Class Libraries (or OLE Object Servers if you prefer) in Visual Basic 4 is a powerful technique for achieving these objectives.

There are distinct advantages in using this Class Library approach that you may want to seriously consider, among which are the following:

These are significant advantages and can greatly expedite your development of "universal" applications approaches. Encapsulation of algorithms, object structures, and user interface design in Visual Basic 4 Class Libraries is a highly leveraged programming technique if properly applied.

Developing and Using Components

The key to getting the greatest benefit from Visual Basic 4's new Class Library capability is to begin designing your applications as a series of modular and reusable program components. Each modular component should encapsulate the data structures, screen objects, and code procedures that make it unique and allow it to accomplish one clearly defined functional purpose.

Although these rules of structured program design have been with us for decades now, and sometimes have been espoused as the "Holy Grail" of professional program design and development, their observation in practice has more often than not been honored in the breach—usually because of rigid interpretations of what's the best methodology.

To some degree, the basic nature of Windows programming tends to naturally motivate toward the development of more modular code, because it is an event-driven rather than procedural execution environment. As a result, most Windows code procedures tend to be fairly small and oriented around a single specific purpose. Additionally, more "modern" programming strategies, especially those associated with C++ programming practice, tend toward more modular programming results, although oftentimes older C (and other procedural language) programming practices still persist.

However, none of these really address many of the prime imperatives of modular or encapsulated programming techniques, which include:

Data Hiding—Encapsulated procedures should not directly expose Global or Public variables. They should instead externally offer programmatic function interfaces that manipulate data structures within their scope to prevent direct external modification of variables.

Code Reuse—More primitive code modules should be combined to create progressively more highly differentiable specialized function code modules. The concept is intended to prevent the proliferation of similar-function but slightly different code streams.

Self-Contained—Encapsulated procedures should be designed to be self-protective. They should check externally supplied arguments for validity. They should internally provide for their own error handling and recovery. They should be designed to eliminate reaching outside of their own scope and influencing other code components.

Singular Purpose—Encapsulated procedures should be designed to accomplish only a single functional purpose. Procedures which internally perform multiple jobs present confusing external interfaces and are typically difficult to learn and use reliably.

It is certain that other rules could be easily added to the list presented above, but that would be another whole book in its own right. Using just the basic concepts outlined above, you should be able to create Visual Basic Classes that can be used from within any Visual Basic program, or consistently from within any VBA environment.

From Here...

In this chapter, we looked at some of the similarities and differences between Visual Basic 4 and Visual Basic for Applications. You learned to understand the differences in common syntax, extensions, and object references. Along with reviewing environment differences, the chapter compared window objects and control objects.

The database access differences were investigated regarding the Data Access Object model facilities and OLE custom controls. Finally, event limitations and converting properties were discussed concerning porting code between VB and VBA.

The following is a list of chapters that you can refer to for related topics in this book:


| Previous Chapter | Next Chapter | Search | Table of Contents | Book Home Page |

| Buy This Book | Que Home Page | Digital Bookshelf | Disclaimer |


To order books from QUE, call us at 800-716-0044 or 317-361-5400.

For comments or technical support for our books and software, select Talk to Us.

© 1996, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.