Jeff Webb, Mike McKelvy, Ronald Martinsen, Taylor Maxwell, Michael Regelski September 1995 Special Edition Using Visual Basic 4 - Chapter 21 1-56529-998-1 Computer Programming computer programming Visual Basic OLE database applications ODBC VB VBA API This book is an all-in-one reference that provides extensive coverage of every topic and technique for creating optimized and customized applications with Visual Basic.

Chapter 21

Advanced OLE Programming Techniques

Advanced topics should always be slightly shocking. Fortunately, Visual Basic enables you to do many startling things with objects. The topics in this chapter range from the esoteric (extending data types) to the downright practical (extending Excel). All, however, are eye-openers.

In this chapter, you learn about the following:

Creating Extended Data Types

Objects are a type of data, so you can use them just like any other data type in Visual Basic. This can lead to some interesting and surprising applications. For example, many DLLs return flag settings as bytes within a long integer. To extract the information from the returned long integer, you must read the various parts of the integer, as shown in figure 21.1.

Fig. 21.1

Reading the bytes from a long integer.

The procedural approach to this problem is to create four functions that return the various parts of the long integer: HiWord, LoWord, HiByte, and LoByte. But you can also tackle this problem by using objects. By creating classes for Long and Integer types, you can have the object parse itself by using its own methods. The TYPES.VBP sample defines two classes, as shown in table 21.1.

Table 21.1 TYPES.VBP Class Module Property Settings

Class Name Creatable Public
clsLong True True
clsInteger True True

The clsLong Object

TYPES.VBP defines its clsLong object in the clsLong class (LONG.CLS). The clsLong object has the following property and methods:

Property/Method Description
Value property Sets or returns the value that the object contains. (Read/write.)
HiWord method Returns a clsInteger object that contains the value of the high word of the clsLong object.
LoWord method Returns a clsInteger object that contains the value of the low word of the clsLong object.

The HiWord and LoWord methods return an object rather than a value, so you can easily use the subsequent methods in clsInteger to get the high and low bytes. Listing 21.1 shows the clsLong class that contains the Value property and HiWord and LoWord methods.

Listing 21.1 The clsLong Class

' Long class -- LONG.CLS
' Parses Long integers into integers (words)
'
' Properties
' Value
'
' Methods
' LoWord
' HiWord
'
Option Explicit
' Value property (read/write)
Public Value As Long
' HiWord method.
Function HiWord() As clsInteger
' Create a new object to return.
Dim intReturn As New clsInteger
' Get the high word and set the inReturn object's
' Value property.
intReturn.Value = (Me.Value And &hffff0000) \ &h10000
' Return the intReturn object as the result.
' Note that you must use Set, since this is object
' assignment.
Set HiWord = intReturn
End Function
' LoWord method.
Function LoWord() As clsInteger
' Create a new object to return.
Dim intReturn As New clsInteger
' Check if the high word is zero (avoids divide by 0 errors).
If Me.Value And &hffff0000 Then
' Get the low word and set the inReturn object's
' Value property.
intReturn.Value = Me.Value Mod (Me.Value And &hffff0000)
Else
' Get the low word and set the inReturn object's
' Value property.
intReturn.Value = Me.Value
End If
' Return the intReturn object as the result.
' Note that you must use Set, since this is object
' assignment.
Set LoWord = intReturn
End Function

The clsInteger Object

The clsInteger class (LONG.CLS) defines TYPES.VBP’s clsInteger object. The object has the following property and methods:

Property/Method Description
Value property Sets or returns the value that the object contains. (Read/write.)
HiByte method Returns a Byte that contains the value of the high byte of the clsInteger object.
LoByte method Returns a Byte that contains the value of the low byte of the clsInteger object.

The HiByte and LoByte methods return a fundamental type (Byte) rather than an object. You can easily extend this example to return a bits collection that contains the eight bits of the byte. Listing 21.2 shows the clsInteger class that contains the Value property and HiByte and LoByte methods.

Listing 21.2 The clsInteger Class

' clsInteger class -- INTEGER.CLS
' Parses Integers (words) into bytes.
'
' Properties:
' Value
'
' Methods:
' LoByte
' HiByte
'
Option Explicit
' Value property.
Public Value As Integer
' HiByte method.
Function HiByte() As Byte
' Return the high byte from an integer.
HiByte = (Me.Value And &hff00) \ &h100
End Function
' LoByte method.
Function LoByte() As Byte
' Check if the high byte is zero (avoids
' divide by zero errors).
If Me.Value And &hff00 Then
' Return the high byte from an integer.
LoByte = Me.Value Mod (Me.Value And &hff00)
Else
' Return the high byte from an integer.
LoByte = Me.Value
End If
End Function

Using clsLong and clsInteger Objects

Using the clsLong and clsInteger objects in code is the same as using any other object. First, declare a variable as a new instance of the object; then, use the object’s properties and methods. The following code demonstrates how to use the two objects to parse a long integer:

Sub Main()
' Create an object variable.
Dim lngValue As New clsLong
' Set the object's Value property.
lngValue.Value = &h1020304
' Display the value of
' the least significant byte.
MsgBox lngValue.LoWord.LoByte
End Sub

You cannot create default properties for objects in Visual Basic, so you must remember to use a Value property when assigning a value to an object of an extended type.

Creating Recursive Data Types

One of the most interesting applications of an extended data type is a recursive type. Recursive types contain elements of their own type, creating a tree of data. You can use tree structures to solve many programming problems, such as sorting data, storing directory structures, and creating outlines.

The OUTLINE.VBP sample provides a Topic object that you can use as a general data type to store any tree-structured information. The Topic object provides an AddSubtopic method to create more instances of itself, as shown in figure 21.2.

You can create the same type of structure by using user-defined data types (Type...End Type) and arrays, but objects give you the advantage of automatic garbage-collection. If you delete Topic3 from the structure shown in figure 21.2, you also automatically destroy TopicA and TopicB because their reference (contained in Topic3) no longer exists.

The OUTLINE.VBP sample defines a single class, as shown in table 21.2.

Fig. 21.2

The Topic object can create subordinate objects of its own type to store tree-structured data.

Table 21.2 TYPES.VBP Class Module Property Setting

Class Name Creatable Public
Topic True True

Most of the properties and methods of the Topic object are navigational; that is, they deal with the creation and placement of the object in a hierarchy (tree) of objects. Only the Value property is functional. It sets or returns the actual data that you want to store in the hierarchy. Of course, you can expand the Topic object to contain more functional methods and properties. Methods for printing the data or storing it to disk are some enhancements that you might consider. Table 21.3 describes the properties and methods that Topic provides.

Table 21.3 Topic Object Properties and Methods

Property/Method Description
Parent property Returns the creator of this object. For the top-level object, returns itself (which is an OLE convention). (Read/write once.)
Index property Returns a numeric index that stores the object in its containing collection. The Delete method uses this property to remove the object. (Read/write once.)
Value property Sets or returns the value that the object contains. The Value property can receive any data type, so it has Property Get, Let, and Set procedures. (Read/write.)
AddSubtopic method Creates a new, subordinate Topic object and adds it to the current object’s collection. AddSubtopic sets the new Topic object’s Index and Parent properties for the first (and only) time.
Delete method Deletes the Topic object from its Parent object’s collection, destroying the current object and all of its contained objects (subtopics).
SubTopics method Returns the collection of Topic objects that this object contains.

Module-Level Private Variables

The Topic class uses Private variables to store the settings of Parent, Index, and Value properties. The Property procedures for these properties control access to the variables. Topic also uses a Private collection variable, colSubTopics, to contain the object’s subtopics. The AddSubtopics and Delete methods maintain the colSubTopics variable, and the Topics method returns the variable. The following lines show the module-level comments and declarations for the Topic class:

' Topic class -- TOPIC.CLS
' Recursive data type -- stores any type of
' tree-structured information.
'
' Properties
' Parent
' Index
' Value
'
' Methods
' AddSubtopic
' Delete
' Topics
'
Option Explicit
' Internal variables used to store information
' used by various methods and properties.
Private mParent As Topic
Private mIndex As Integer
Private mValue As Variant
Private colSubTopics As New Collection

The Parent and Index Properties

The Parent and Index properties are the primary navigational properties for the Topic class. The AddSubtopic method initializes both properties once; thereafter, the properties are read-only. They demonstrate how to implement write-once, read-always properties, which are useful in a variety of contexts.

You use the Parent property to move up in the object hierarchy. The Delete method uses the property to get the collection object from which to remove the object. Listing 21.3 is the definition of the Parent property for the Topic class.

Listing 21.3 Definition of the Parent Property for the Topic Class

' Parent property (write-once, read-always).
Public Property Get Parent() As Topic
' If parent is not set, then the object
' is the top-level topic, so Parent is Me.
If TypeName(mParent) = "Nothing" Then
Set Parent = Me
End If
' Return the parent object.
Set Parent = mParent
End Property
Public Property Set Parent(objSetting As Topic)
' Initialize this property only once. Afterward, return
' an error.
If TypeName(mParent) = "Nothing" Then
Set mParent = objSetting
Else
' Can't reset.
Err.Raise 383, "Topic object", _
"Parent property is read-only."
End If
End Property

You use the Index property to select an item from the collection that the Topics method returns. The Delete method uses this property to identify which object to delete from the Parent object’s collection. Usually, objects that belong to a collection must provide some kind of Index property to enable specific object’s to be selected and deleted. Listing 21.4 shows the definition of the Index property for the Topic class.

Listing 21.4 Definition of the Index Property for the Topic Class

' Index property (write-once, read-always).
Public Property Get Index() As Integer
' Return the internal Index variable
' intialized when Name is first set.
Index = mIndex
End Property
Public Property Let Index(iSetting As Integer)
' Check if property has already been set.
If mIndex = 0 Then
' Set index on first call.
mIndex = iSetting
Else
' Can't reset.
Err.Raise 383, "Topic object", _
"Index property is read-only."
End If
End Property

The Value Property

The Value property is an example of a property that can receive or return any type of value—fundamental type or object reference. The property has all three types of Property procedure: Get, Let, and Set. The property invokes the appropriate procedure, depending on the data that Value stores. The Property Let procedure is polymorphic—that is, it returns a value if the stored data is a fundamental type; it returns an object reference using Set if the stored data is an object. Listing 21.5 is the definition of the Value property for the Topic class.

Listing 21.5 Definition of the Value Property for the Topic Class

' Value property (read/write).
' May contain object data or fundamental type
' (get/let/set).
Public Property Get Value() As Variant
' If the data is an object, use Set.
' Otherwise, use regular assignment.
If IsObject(mValue) Then
' Return the internal Data variable.
Set Value = mValue
Else
' Return the internal Data variable.
Value = mValue
End If
End Property
Public Property Let Value(vSetting As Variant)
' Update the internal Data variable.
mValue = vSetting
End Property
Public Property Set Value(objSetting As Object)
' Update the internal Data variable.
Set mValue = objSetting
End Property

The AddSubtopic Method

The AddSubtopic method adds a Topic object to the current object’s colSubtopics collection. This behavior makes the Topic object recursive—that is, you can add as many levels to the object hierarchy as you want, using just one class. AddSubtopic initializes the new object’s write-once properties, Index and Parent, ensuring that the user cannot change them and disrupt the hierarchy. Listing 21.6 is the definition of the AddSubtopic method for the Topic class.

Listing 21.6 Definition of the AddSubtopic Method for the Topic Class

' AddSubTopic method.
Public Function AddSubtopic() As Topic
' Create a new subtopic.
Dim NewTopic As New Topic
' Use a static Index to create a unique key
' for each subtopic to add to the collection.
Static Index As Integer
Index = Index + 1
NewTopic.Index = Index
' Set Parent property (creator is parent).
Set NewTopic.Parent = Me
' Add the topic to the collection of
' subtopics (NewTopic.Index is unique).
colSubTopics.Add NewTopic, Str(NewTopic.Index)
' Return this object as the result of function.
Set AddSubtopic = NewTopic
End Function

The Delete Method

The Delete method deletes the current object by calling the Remove method on the Parent object’s collection. Remove takes a numeric index or string key value as its argument. Because a collection’s order can change, you must use the object’ Index property to delete the appropriate object. Delete requires special code to handle being called on the hierarchy’s top-level object. In this case, Delete clears the object’s collection when called on the top-level object. Listing 21.7 is the definition of the Delete method for the Topic class.

Listing 21.7 Definition of the Delete Method for the Topic Class

Public Sub Delete()
' If this is the top-level object, then
' clear the collection, destroying all
' subtopics.
If (Me Is Me.Parent) Then
' Remove first item from the collection
' until it is empty
Do Until colSubTopics.Count = 0
colSubTopics.Remove 1
Loop
' This object isn't the first topic, so
' remove it from its parent's collection.
Else
Me.Parent.Topics.Remove Me.Index
' Subtopics under this topic are automatically
' destroyed when they go out of scope.
End If
End Sub

The Topics Method

The Topics method returns the collection of Topic objects contained in the private colSubtopics variable. You use the Topics method to navigate down in the object hierarchy. Listing 21.8 is the definition of the Topics method for the Topic class.

Listing 21.8 Definition of the Topics Property for the Topic Class

Public Function Topics() As Collection
Set Topics = colSubTopics
End Function

Using the Topics Object

You traverse recursive data structures by using recursive procedures. A recursive procedure is one that calls itself to solve a problem. Listing 21.9 is a Main procedure uses two recursive procedures: AddLevel creates new Topic objects from tab-delineated lines of text, and SearchTree traverses the object hierarchy to find a Topic object with a specific Value setting.

Listing 21.9 A Main Procedure with Two Recursive Procedures

Option Explicit
' Create a new Topic object variable.
Dim Outline As New Topic
Sub Main()
Dim strLine As String, strName As String
Dim Topic As Topic
Open "org.txt" For Input As #1
Do Until EOF(1)
Line Input #1, strLine
AddLevel strLine, Outline
Loop
Close 1
strName = InputBox("Enter your name")
Set Topic = SearchTree(strName, Outline)
MsgBox "Your boss is " & Topic.Parent.Value
Set Outline = Nothing
End
End Sub

The AddLevel procedure adds topics to the object hierarchy based on a passed-in string argument, strLine. AddLevel uses the number of tab characters in the string to determine the level in the hierarchy for the new object. Listing 21.10 shows the AddLevel procedure.

Listing 21.10 The AddLevel Procedure

Sub AddLevel(strLine As String, objTopic As Topic)
' If the line starts with a tab...
If Mid(strLine, 1, 1) = Chr$(9) Then
' Trim off the character and call again.
AddLevel Right(strLine, Len(strLine) - 1), _
objTopic.Topics.Item(objTopic.Topics.Count)
Else
objTopic.AddSubtopic.Value = strLine
End If
End Sub

The SearchTree procedure searches down each branch of the object hierarchy, checking for a match between strName and the passed-in object’s Value property. SearchTree keeps searching downward until it finds the bottom-level object in each branch (when Topics.Count = 0). If the procedure finds a match before reaching the bottom, it returns that object. Otherwise, SearchTree bubbles back up and searches down the next branch. Listing 21.11 shows the SearchTree procedure.

Listing 21.11 The SearchTree Procedure

Function SearchTree(strName As String, objTopic As Topic) As Topic
Dim Item As Topic
If objTopic.Topics.Count > 0 Then
For Each Item In objTopic.Topics
If Item.Value = strName Then
Set SearchTree = Item
Else
Set SearchTree = SearchTree(strName, Item)
End If
Next Item
End If
End Function

Using Object Libraries during Development

You can use extended data types like clsLong and Topic within an application by copying their class modules to the application’s project, or you can compile them and use them as object libraries across multiple applications.

If you use these objects across applications, you incur some overhead from using OLE objects. Sometimes, this overhead might not be a problem. However, if you use many extended fundamental types, such as clsLong, you might consider developing your application by using the class as an OLE .DLL and then creating your release-version application with the class compiled into the project’s .EXE file.

This solution enables you to distribute the development of your application among programmers, but optimizes the performance in your released .EXE file. By using object libraries during development, you restrict the changes to those libraries and draw clearer lines of responsibility among developers. Using object libraries in this way is similar to using .LIB files when developing applications in the C programming language. Figure 21.3 illustrates this approach to developing projects.

Compiling an object library as a .DLL rather than an .EXE improves the performance of applications that use the object library. Access a method or property from a .DLL is 10 to 100 times faster than accessing a method or property from an .EXE.

Fig. 21.3

Use compiled object libraries during development, then optimize by compiling the classes into the release .EXE file.

Creating Object Libraries for Other Applications

Any application that includes Visual Basic Applications Edition (VBA) can use object libraries that you compile in Visual Basic. Currently, only two applications fit this description: Microsoft Excel and Microsoft Project. The next release of Microsoft Access is supposed to include VBA, and Microsoft Word 7.0 probably will provide access to VBA objects through its language, WordBasic.

Other companies, such as Borland International, are working on support for OLE objects in their applications and development tools. But currently it is unclear what depth those features will have.

For now, the primary client for Visual Basic object libraries are Microsoft Excel and applications created in Visual Basic. The process of creating an object library is the same regardless of the application for which you intend it. Chapter 18, “Creating Objects,” demonstrates how to build an .EXE file that provides objects to other applications.

Using an object library from another application varies slightly from application to application. For instance, VBA in Excel and Project does not provide a New keyword, so you cannot declare a variable as a new object (Dim obVar As New Class), as you can in Visual Basic 4.0.

VBA itself is an object library, but you can’t “switch out” the VBA 1.0 library in Excel or Project from the VBA 2.0 library. Therefore, when writing code within Excel or Project, you must limit yourself to language supported in VBA 1.0. Table 21.4 lists the VBA 2.0 features unavailable from within VBA in Excel or Project.

Table 21.4 Language Features Unavailable in Microsft Excel and Project

VBA 2.0 Feature VBA 1.0 Substitute
Dim...As New... Use the CreateObject function.
Specific object types Use generic Object data type for objects created in Visual Basic 4.0.
Collection objects Pass Excel or Project collections to VBA 2.0 procedures. You cannot create new collections in VBA 1.0.
Class modules Pass Excel or Project objects to VBA 2.0 procedures. You cannot create new classes of objects in VBA 2.0.

Using Visual Basic Object Libraries from Excel

You can use object libraries created in Visual Basic 4.0 to extend Excel’s features. These extensions take two general forms:

The following sections describe how to add to the Microsoft Excel application some features created in Visual Basic 4.0. Because these sections deal with VBA in Excel, the code examples show you how to write the code in a VBA code module within Excel. You can actually run these lines in either Excel or Visual Basic 4.0 (VB4). However, in VB4, you must first establish a reference to the Excel object, because this reference is implicit from within Excel.

The following code lines demonstrate how to initialize commonly used Excel object references from within VB4:

' Microsoft Excel commonly used object variables.
Public Application As Object
Public ActiveWorkbook As Object
Public ActiveSheet As Object
Sub Main()
' Initialize objects.
' Starts Microsoft Excel and establishes a reference to the
' Excel application object.
Set Application = CreateObject("Excel.Application")
' Excel starts invisibly, so make it visible as a first step.
Application.Visible = True
' Microsoft Excel starts with no loaded workbook,
‘ so create one.
Application.Workbooks.Add
Set ActiveWorkbook = Application.ActiveWorkbook
Set ActiveSheet = Application.ActiveSheet
End Sub

Adding New Objects to VBA in Excel

Object libraries that you create in VB4 are automatically available from VBA in Excel if they meet the following criteria:

From VBA in Excel, use the CreateObject or GetObject functions to establish a reference to an object in a VB4 object library. The following Microsoft Excel VBA code line establishes a reference to the WinAPI application object in the SYSTEM.VBP sample included on the companion CD:

Set WinAPI = CreateObject(“WinAPI.Application”)

After establishing a reference to the object, you can use the object’s methods and properties just as you can within VB4. The following code line uses the preceding WinAPI object to get the path of the Windows directory:

strWinDir = WinAPI.Information.WindowsDirectory

Browsing VB4 Object Libraries in Excel

You don’t have to establish a reference to a VB4 object library to use it from Excel. However, establishing a reference enables you to view the object library using Excel’s Object Browser, which is identical to the VB4 Object Browser.

To establish a reference to a VB4 object library in Excel, follow these steps:

  1. Display a code module in Excel. Choose Tools, References. Excel displays the References dialog box (see fig. 21.4).

Fig. 21.4

Excel’s References dialog box.

  1. The References dialog box’s Available References list displays all the object libraries registered with the system registration database. If the list does not display the library that you want, choose the Browse button. Excel displays the Object Browser (fig. 21.5), in which you can select a VB4 .EXE file that contains the object library.

Fig. 21.5

Excel’s Object Browser.

  1. Select a file to reference and click Close. Excel adds the file to the References dialog box and marks with an X the check box to the file name’s left. This mark indicates that you have a reference to that object library.

After you have a reference to a VB4 object library, you can use the Excel Object Browser to view its Public object, methods, and properties.

A Code-Only Object Library

The SYSTEM.VBP sample on the companion CD is a good example of a code-only object library that you might want to use from VBA in Excel or any other VBA client. SYSTEM.VBP repackages some popular Windows API calls as objects. Although you can call Windows DLLs directly from VBA in Excel, getting the declarations right is often difficult, and the argument lists are long and complicated.

SYSTEM.VBP simplifies calling some the Window API functions by providing them as methods and properties of various objects. Table 21.5 lists the objects, properties, and methods provided in SYSTEM.VBP.

Table 21.5 The SYSTEM.VBP Sample's Object, Properties, and Methods

ObjectProperty/MethodDescription
Application FileIO Returns the FileIO object that this object library provides.
Help Returns the Help object that this object library provides.
Information Returns the Information object that this object library provides.
Keyboard Returns the Keyboard object that this object library provides.
Registration Returns the Registration object that this object library provides.
Version Returns the Version object that this object library provides.
FileIO WindowsDirectory Returns the path for the Windows directory.
SystemDirectory Returns the path for the Windows system directory.
TempFileName Returns the path and file name for a new temporary file.
Help hWnd Sets the window handle of the application that controls the Help window.
FileName Sets the name of the Help file to open.
Context Sets the context ID for which to search the Help file.
Keyword Sets the keyword for which to search the Help file.
Show Displays Help for the appropriate keyword or context ID.
OnTop Displays the Help window on top of all other windows.
Information Memory Returns the amount of physical memory installed.
Processor Returns the series number of the installed microprocessor (386 or 486).
GetTasks Returns a list of the running Windows applications, including those that are not visible on-screen.
IsRunning Checks whether an application is currently running.
MakeVisible Makes a hidden application window visible.
FindWindow Finds the handle of a given window.
WindowsVersion Returns the version number of the running Windows operating system.
Keyboard NumLock Sets or returns the Num Lock key state.
Registration CheckRegistrationEntry Checks whether an application is correctly registered.
GetRegisteredList Returns the system registration database's list of applications.
CheckInstalled Checks whether an application is registered in the system registration database.
Version InstallFile Installs the latest version of a file on the system.

Adding Menu Items in Excel

Use Excel's Menu Editor to modify the menus Excel displays. To start the Excel Menu Editor, follow these steps:

  1. Select or add a VBA code module sheet.
  2. Choose Tools, Menu Editor. Excel displays the Menu Editor only when a module sheet is active, as shown in figure 21.6.

Fig. 21.6

Excel’s Menu Editor.

Excel menus have four parts, as shown in figure 21.7.

Fig. 21.7

Parts of an Excel menu (menu objects).

Menu bars change depending on Excel's current context. While a worksheet is active, Excel displays the Worksheet menu bar; when you switch to a module, the Visual Basic Module menu bar replaces the Worksheet menu bar; and so on.

Menu bars contain menus. In Excel, menus organize related tasks, which the menu items indicate. Each menu item performs a specific action when the user selects it. The action might be a task, such as sorting, or a step toward a task, such as displaying a dialog box.

Submenu items subordinate a task one more level. For example, Record Macro presents the submenu choices of recording a new macro, using relative references, or recording at a mark. Applications display submenu items when a dialog box would be obtrusive to a task.

Attaching Procedures to Menu Items

You can assign VBA procedures to run when a user clicks a menu item. To display a VB4 object after an Excel menu click, you must create a VBA procedure to call that object. For example, the following VBA procedure starts the VB4 CDPlayer application when the user clicks on the menu item assigned to CDPlay:

Sub CDPlay()
‘ Create an object reference to the VB4 object library.
Dim CD As Object
Set CD = CreateObject(“CDPlayer.Application”)
‘ Invoke the Play method on the VB4 object.
CD.Play
End Sub

You can assign procedures only to menu items and submenu items that you have added. You cannot attach procedures to menus or to built-in menu items.

To attach a procedure to a custom menu item, follow these steps:

  1. From the Menu Editor, select the menu item by clicking its name in the Menu Items or Submenu Items list box.
  2. In the Macro list box, type the name of the procedure to attach. Then click OK.

Saving and Distributing Menus

Menu changes are saved with files. The changes last while the file is open. When you save a menu with a workbook, the menu is local to that workbook; that is, the menu goes away as soon as you close the workbook. To make the changes available to all workbooks, save the changes to a workbook template.

Excel defines two types of templates: the default template BOOK.XLT, and custom templates, which can have any base name other than CHART and end with .XLT. To make templates available automatically, you must save them in Excel's startup directory (\EXCEL\XLSTART).

To create a template, follow these steps:

  1. Choose File, New.
  2. Enter data, formats, and menu changes as appropriate.
  3. Choose File, Save As. Select Template from the Save File as Type list box and select Excel's startup directory from the Directories list box (usually \EXCEL\XLSTARTUP).
  4. Type the file’s base name in the File Name text box. New files that you create from this template will use the base name as the default name. For example, if you specify BOOK as the default template, the default names will be BOOK1.XLS, BOOK2.XLS, and so on.
  5. Click OK to save the file.

The new template is not automatically available until you restart Excel. After you restart Excel, it displays the New dialog box (see fig. 21.8) and lists the template types if you have multiple template files in the startup directory.

Fig. 21.8

Excel’s New dialog box.

To distribute a template to other users, simply copy the template file to their startup directory and restart Excel on their machine.

Creating and Displaying Menu Bars Dynamically

Excel changes its menu bars when you activate various types of sheets and windows. The Excel MenuBars collection contains all these built-in menu bars. Use the constants in table 21.6 to get to a specific menu. (Shortcut menus use strings rather than constants.)

Table 21.6 Excel’s Built-In Menu Bar Constants

Constant Menu Bar Returned
xlWorksheet Worksheet, macro sheet and dialog sheet
xlChart Chart
xlModule Visual Basic module
xlNoDocuments No documents open
xlInfo Info Window
"Shortcut Menus 1" General worksheet, module, and toolbar shortcut
"Shortcut Menus 2" Drawing object and dialog sheet shortcut
"Shortcut Menus 3" Charting shortcut
xlInfo Info window
xlInfo Info window
xlWorksheet4 Excel 4.0 worksheet
xlChart4 Excel 4.0 chart
xlWorksheetShort Excel 3.0 short worksheet
xlChartShort Excel 3.0 short chart

The following line returns a reference to the worksheet menu bar:

Set mnubrWorksheet = Application.MenuBars(xlWorksheet)

Once you have a reference to a menu bar, you can get the menus that the menu bar contains or add new menus to the menu bar. For instructions on adding menus to a menu bar, see the section "Creating and Displaying Menus Dynamically," later in this chapter.

Excel also enables you to create your own menu bars in code. The following line creates a new menu bar:

Application.MenuBars.Add("New Menu Bar")

To display a menu bar, use the Excel MenuBar object’s Activate method. The following line displays the new menu bar:

Application.MenuBars("New Menu Bar").Activate

If you just ran the preceding line without looking ahead, you might have been surprised that you just wiped out the menu bar. The new menu bar is blank and it replaced the built-in menu bar when you activated it.

While Excel displays the Debug window, you cannot see the changes that you make to the menu bar. You cannot modify the Debug menu bar.

To restore the built-in menu bar, activate it. The following line activates the module menu bar:

Application.MenuBars(xlModule).Activate

You cannot activate a built-in menu bar if it conflicts with the current context. For example, you cannot activate the worksheet menu bar while a module sheet is active.

Creating and Displaying Menus Dynamically

You can use Visual Basic to create Excel menus dynamically at run time. This capability is useful when installing and removing add-in components and when creating systems that make menus available at the appropriate times within a workbook.

Use the Excel MenuBar object’s Add method to add menus to a menu bar. The following line adds a Procedures menu to the Worksheet menu bar:

Application.MenuBars(xlWorksheet).Menus.Add "&Procedures", _
"&Window"

Use the Excel MenuBar object’s Delete method to remove menus from a menu bar. The following line deletes the Help menu:

Application.MenuBars(xlWorksheet).Menus("Help").Delete

Use the Excel MenuBar object’s Reset method to restore the default settings of a built-in menu. The following line restores the Help menu, previously deleted from the menu bar:

Application.MenuBars(xlWorksheet).Reset

Adding Items to Menus

You can use Visual Basic to create menus items dynamically at run time. This capability is useful when updating lists on a menu. Excel's Window list is an example of a use for dynamic menu items.

Use the Excel MenuItems object’s Add method to add menu items to a menu. The following line adds an item to the Window menu:

Application.MenuBars(xlWorksheet).Menus("Window").MenuItems.Add "_
&Locator Map"

Use the Add method with a hyphen to add a separator bar to a menu. This line adds a separator bar to the Window menu:

Application.MenuBars(xlWorksheet).Menus("Window").MenuItems.Add _
"-"

Use the Excel MenuItems object’s Delete method to remove items from a menu. The following line deletes the menu item that the previous line added:

Application.MenuBars(xlWorksheet).Menus("Window"). _
MenuItems("Locator Map").Delete

Use the Excel MenuItems object’s Reset method to restore the default settings of a built-in menu. The following line restores the Window menu:

Application.MenuBars(xlWorksheet).Menus("Window").Reset

Creating and Editing Toolbars

You can customize the Excel workspace by creating new toolbars and by changing existing, built-in toolbars. Changes are saved with your system and remain in effect until you delete or reset the toolbars. Figure 21.9 displays the Excel toolbar objects.

Fig. 21.9

Excel’s toolbar objects.

From within Excel, you can do the following:

To display a VB4 object after an Excel toolbar button click, you must create a VBA procedure to call that object. For example, the following VBA procedure starts the VB4 CDPlayer application when the user clicks on the menu item assigned to CDPlay:

Sub CDPlay()
‘ Create an object reference to the VB4 object library.
Dim CD As Object
Set CD = CreateObject(“CDPlayer.Application”)
‘ Invoke the Play method on the VB4 object.
CD.Play
End Sub

The following sections describe how to perform these tasks manually from within Excel.

Creating New Toolbars

To create a new toolbar in Excel, follow these steps:

  1. Choose View, Toolbars. Excel displays the Toolbars dialog box (see fig. 21.10).

Fig. 21.10

Excel’s Toolbars dialog box.

  1. Type the name of the toolbar to create in the Toolbar Name text box.
  2. Click the New button. Excel displays the Customize dialog box (see fig. 21.11).

Fig. 21.11

Excel’s Customize dialog box.

Adding Buttons to Toolbars

To add a button a toolbar, follow these steps:

  1. Drag a button from the Customize dialog box’s Buttons palette to the destination toolbar.
  2. If the button is from the Custom category, Excel displays the Assign Macro dialog box (see fig. 21.12).

Fig. 21.12

Excel’s Assign Macro dialog box.

  1. In the Macro Name/Reference text box, type the name of a procedure to run when the user clicks the button. Click OK.

Changing the Procedure Assigned to a Button

To change the assigned procedure name, follow these steps:

  1. Display the Customize dialog box.
  2. Choose Tools, Assign Macro. (The Tools and shortcut menus display Assign Macro only while the Customize dialog box is open.) Excel displays the Assign Macro dialog box.
  3. Type the new procedure name in the the Macro Name/Reference text box and click OK.

Changing a Button Image

To edit a button image, follow these steps:

  1. Display the Customize dialog box.
  2. Left-click on the button that you want to edit. Excel displays a shortcut menu.
  3. Choose Edit Button Image. (The shortcut menu displays Edit Button Image only while the Customize dialog box is open.) Excel then displays the Button Editor (see fig. 21.13).

Fig. 21.13

Excel’s Button Editor.

  1. Edit the image and click OK.

Distributing Toolbars with Excel Files

You can distribute custom toolbars to other users by attaching them to a file. When they open the file, the toolbar is installed on their system. A user has to open the file only once to install the toolbar.

To attach a toolbar to a file, follow these steps:

  1. Open or create the file to which to attach the toolbar.
  2. Display a module sheet.
  3. Choose Tools, Attach Toolbars. Excel displays the Attach Toolbars dialog box (see fig. 21.14).

Fig. 21.14

Excel’s Attach Toolbars dialog box.

  1. Choose the toolbar to attach and the choose Copy. You can attach to a file as many toolbars as you want.
  2. Click OK and save the file. Excel defines the .XLB file extension for toolbars, but you can use any file extension that you want.

If the buttons on the toolbar run Visual Basic procedures, you must ensure that the file containing those procedures is on the user's path or in Excel's library directory (\EXCEL\LIBRARY by default).

Creating and Displaying Toolbars Dynamically

You can use Visual Basic to create toolbars dynamically at run time. This capability is useful when installing and removing add-in components and when creating systems that make tools available at the appropriate times (Excel itself creates toolbars dynamically in that the menus change as you switch between contexts.)

Use the Add method of the Toolbars collection to add toolbars dynamically. The following line adds a new, custom toolbar:

Application.Toolbars.Add "Budget Tools"

Adding a toolbar automatically displays it. You can hide the toolbar by using its Visible property. The following line adds a toolbar, but does not display it:

Application.Toolbars.Add ("Invoicing Tools").Visible = False

The Position, Top, and Left properties controls a toolbar’s position. Position determines whether the toolbar floats or is attached to the window’s edge. The following line makes the Standard toolbar a floating toolbar:

Application.Toolbars("Standard").Position = xlFloatingToolbar

You remove a custom toolbar by using the Delete method. You cannot delete built-in toolbars, but you can restore their default setting by using the Reset method.

Using VB4 Objects with Excel Add-ins

Add-ins extend Excel's capabilities by adding features, menus, and toolbars as if they were built in. Add-ins are a good way to distribute VB4 object libraries written for Excel, because the add-in can contain all the menus, toolbars, and VBA procedures that call the VB4 object library, as shown in figure 21.15.

As shown in figure 21.15, the Excel add-in contains a compiled version of the VBA “wrapper” procedures that access the VB4 object library. The add-in can also include toolbars and menu bars, so making the new features available in the user interface is easy.

Fig. 21.15

Create Excel add-ins when distributing VB4 object libraries written for Excel.

Loading Add-Ins

You must load an add-in in Excel before you can use the functions that the add-in provides.

To load an add-in in Excel, follow these steps:

  1. Choose Tools, Add-Ins. Excel displays the Add-Ins dialog box (see fig. 21.16).

Fig. 21.16

Excel’s Add-Ins dialog box.

  1. If the Add-Ins Available list box lists the add-in, click the check box beside the add-in name to load the add-in. When an add-in is loaded, the corresponding check box is marked.
  2. If the list box does not list the add-in, choose Browse. Excel displays the Browse dialog box (see fig. 21.17).

Fig. 21.17

Excel’s Browse dialog box.

  1. Double-click the file name of the add-in to load. Excel adds the add-in to the Add-Ins dialog box’s Add-Ins Available list box and loads the add-in.

To make an add-ins procedures available to other Visual Basic procedure, you must add a reference to the add-in. To add a reference to an add-in, follow these steps:

  1. Display a VBA code module.
  2. Choose Tools, References. Excel displays the References dialog box.
  3. In the Available References list, click the check box beside the add-in name to establish a reference to that add-in. Then click OK to close the dialog box.

Each add-in consumes memory and takes time to load. Loading more than three or four add-ins can cause significant problems running Excel.

Creating Add-Ins in Excel

Add-ins created from workbook files (.XLS) do not create a new workbook when loaded. However, add-ins created from template files (.XLT) create a new workbook based on the source template when the add-in is loaded. SLIDES.XLT is an example of a template add-in.

To create an add-in from a workbook or template file, follow these steps:

  1. Open the file in Excel and display a VBA code module.
  2. Choose Tools, Make Add-In. Excel displays the Make Add-In dialog box (see fig. 21.18).

Fig. 21.18

Excel’s Make Add-In dialog box.

  1. Click OK.
  2. Save the file as the source for the add-in. You cannot edit .XLA files, so you must keep the .XLS or .XLT file if you want to modify the add-in in the future.

Distributing Add-In Files

When you distribute add-ins to other users, you must copy the files to the user's machine and load the add-in in Excel. If you are providing functions that will be called from VBA, you also must add a reference to the add-in.

The procedures that you follow to install an add-in differ depending on whether the add-in consists of a single file or multiple files.

Single-File Add-Ins

Single-file add-ins can contain VBA procedures, toolbars, menus, and templates. They do not call VB4 object libraries, DLLs, or other add-ins.

To install a single-file add-in on a machine, follow these steps:

  1. Copy the add-in to the user's machine. The following line uses the Excel Addins object’s Add method to copy an add-in to a local machine from a network location:

    Application.Addins.Add _
    "\\public\tools\excel\addins\demo.xla", True

    Add copies the add-in file to Excel's \LIBRARY directory and adds the add-in's title to the add-in list.

  2. Load the add-in in Excel. The following line loads the add-in:

    Application.Addins("Demo").Installed = True

  3. If the add-in must be available to procedures in Visual Basic, add a reference to the add-in. You cannot add this reference directly in code. You must instruct users how to do this manually, or provide a template file with a reference to the add-in.

Listing 21.12 demonstrates how to install an add-in and install a template with a reference to the add-in. sPath can be a network drive or a floppy drive.

Listing 21.12 Installing an Add-In and a Template with a Reference to the Add-In

' Installs an add-in.
Sub InstallAddin(sPath As String, sAddinName As String)
' Copy file to the \EXCEL\LIBRARY directory.
With Application.Addins.Add(sPath & sAddinName, True)
' Load the add-in.
.Installed = True
End With
End Sub
' Copies a Template file to the user's XLSTART directory.
' The Template file must be created manually and
‘ have a reference to the add-in.
Sub InstallTemplate(sPath As String, sTemplateName As String)
' Copy file to the directory where EXCEL.EXE is installed.
' Save the file as a template in the user's
‘ Excel startup directory.
FileCopy sPath & sTemplateName, Application.StartupPath
' Set make the template read/write.
SetAttr Application.StartupPath & "\" & sTemplateName, _
vbNormal
End Sub

Add-Ins That Use Other Files

Add-ins that use VB4 object libraries, DLLs, or other files present two problems that you don't encounter with single-file add-ins:

When you compile an add-in, Excel writes the paths of any dependent files into the add-in. When you install your add-in on other machines (or if you move the add-in on your own machine), the add-in must search for any DLLs, templates, or other files that it uses. The search follows this order:

  1. Absolute build path. Build refers to the path on which the files were installed when you compiled the add-in.
  2. Relative build path.
  3. Current directory.
  4. Windows directory.
  5. Windows system directory.
  6. DOS path.

If an add-in uses a .DLL, you should install both the .XLA file and the .DLL in the user's Windows system directory. This ensures that the add-in can find its .DLL.

Excel's LIBRARY directory typically is not on the DOS path, so you should not rely on the Addins collection’s Add method to copy multifile add-ins to new systems. Instead, use the FileCopy statement.

Listing 21.13 installs a multifile add-in in the user's Windows system directory and then loads the add-in on the user's machine.

Listing 21.13 Installing and Loading a Multifile Add-In

' Declarations
#If Win16 Then
Declare Function GetSystemDirectory Lib "KERNEL" (ByVal _
lpBuffer As String, ByVal nSize As Integer) As Integer
#ElseIf Win32 Then
Declare Function GetSystemDirectory Lib "KERNEL32" (ByVal _
lpBuffer As String, ByVal nSize As Integer) As Integer
#End If
' Demonstrates calling InstallFiles
Sub CopyDemo()
' Copy the files to the Windows System directory.
InstallFiles "b:\", Array("DEMO.XLA", "DEMO.DLL")
' Load the addin in Excel.
With Application.Addins.Add "DEMO.XLA"
.Installed = True
End With
End Sub
' Copies files to Windows System directory, compares file dates.
Sub InstallFiles(sSourceDirectory As String, _
vFileInstall As Variant)
Dim sTemp As String * 144
Dim sSysDirectory As String
Dim iCopy As Integer
' Turn on error checking (for file/drive-related errors)
On Error GoTo errInstallFiles
' Get the Windows system directory.
iWorked = GetSystemDirectory(sTemp, 144)
' If the previous API call worked,
‘ then trim the returned string.
If iWorked Then
sSysDirectory = Mid(sTemp, 1, InStr(sTemp, _
Chr(0)) - 1) & "\"
' Otherwise, return an error to the user.
Else
MsgBox _
"Couldn't get system directory. Operation cancelled."
End
End If
' For each file in the vFileInstall array.
For Each sNewFile In vFileInstall
' Check if the file exists.
If Len(Dir(sSysDirectory & sNewFile)) = 0 Then
' If it doesn't, copy the new file.
FileCopy sSourceDirectory & sNewFile, _
sSysDirectory & sNewFile
Else
' Otherwise, compare the date stamps on the files.
dtExisting = FileDateTime(sSysDirectory & sNewFile)
dtNew = FileDateTime(sSourceDirectory & sNewFile)
' If the new file is more recent,
‘ copy over the old file.
If dtExisting < dtNew Then
FileCopy sSourceDirectory & sNewFile, _
sSysDirectory & sNewFile
' Otherwise, ask the user what to do.
Else
iCopy = MsgBox ("A newer version of " _
& sNewFile & _
" exists on your system. Keep newer version?", _
vbYesNoCancel)
Select Case iCopy
Case vbYes
' Don't copy file.
Case vbNo
' Copy file anyway.
If iCopy Then FileCopy _
sSourceDirectory & sNewFile, _
sSysDirectory & sNewFile
Case vbCancel
' End this procedure.
MsgBox _
"Operation cancelled, installation is not complete."
End
End Select
End If
End If
Next sNewFile
Exit Sub
' Error handler
errInstallFiles:
Select Case Err
' Disk full
Case 61
MsgBox _
"Your disk is full. Free some space and try again."
End
' Disk drive not ready or path not found.
Case 71, 76
iCopy = MsgBox("Drive " & sSourceDirectory & _
" is not ready or could not be found. Try again?", _
vbOKCancel)
Select Case iCopy
' User chose OK, so try again.
Case vbOK
Err = 0
Resume
' User chose Cancel, so end.
Case vbCancel
' End this procedure.
MsgBox _
"Operation cancelled, installation is not complete."
End
End Select
' Unknown error.
Case Else
MsgBox "Error " & Err & _
" occurred. Installation is not complete."
End
End Select
End Sub

Working with the Excel Addin Object

The Addins collection and Addin object enable you to load, unload, and get information about add-ins. You cannot use the Addin object to create an add-in or establish a reference to an add-in. You can perform those tasks only through Excel's user interface.

To install a new add-in in Excel, use the Add method. The following line, for example, copies an add-in file from drive B to the Excel LIBRARY directory:

Application.Addins.Add "B:\DEMO.XLA", True

To load an add-in in Excel, use the Installed property. The following line loads the add-in installed in the preceding line:

Application.Addins("DEMO").Installed = True

You identify add-ins by their Title property. The Addins collection uses an add-in’s index or Title to return a specific add-in. The following line installs the Analysis Toolpak:

Application.Addins("Analysis Toolpak").Installed = True

Most Excel collections use the object's Name property to identify items. For add-ins, Name returns the add-in’s file name.

From Here...

For more information on the following topics, see the indicated chapters:

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