Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

Why Use VB6 ActiveX DLLs in Excel VBA Projects

Although the core VBA language contained in VB6 is exactly the same as that used by Excel, VB6 supports a number of additional features that are not available in a pure Excel application. These features include very strong code protection due to its fully compiled nature and a more powerful forms package with the ability to use third-party ActiveX controls, control arrays and with enhanced data-binding capabilities. VB6 also provides better support for object-oriented programming, the ability to create resource files and other useful features, including the Clipboard, Printer and Screen objects.

Code Protection

A frequent problem that comes up in the context of Excel applications is the complete inability of VBA to protect your code from prying eyes. Sure you can protect your VBA project with a password. But VBA project password breaking programs that can remove this password instantaneously are widely available. Because VBA code is never truly compiled, it will never be possible to prevent other people from gaining access to it.

For run-of-the-mill applications this is not a very significant problem. There are a number of applications, however, that rely on valuable proprietary algorithms. The only way to protect this proprietary code is to store it in a truly compiled state like that provided by a VB6 DLL. Note that the compiled machine code produced by VB6 can be decompiled to a very limited extent. The result of decompiling a VB6 DLL, however, is little more than assembly language, which so few people understand that code compiled in VB6 is effectively invulnerable.

Taking Advantage of VB6 Forms

The forms package used by VB6 is very different from the one used by VBA. As mentioned in Chapter 10 Userform Design and Best Practices, the forms package used to create userform objects in Excel applications is called MSForms. The forms package used to create form objects in VB6 is known as Ruby Forms.

There are superficial similarities between the two. Both provide a blank canvas onto which you can drag and drop controls to visually construct your form. Both types of form have event models and both allow you to program controls by responding to events raised by those controls.Chapter 10 Userform Design and Best Practices we showed in detail how to modify the window styles of userforms using API calls to achieve effects that were not directly supported by userforms. All of these window styles are directly supported by VB6 forms and it requires nothing more than setting the appropriate form properties in the Properties window to implement them. VB6 forms also provide a wide variety of built-in shapes and drawing methods that allow you to create sophisticated form designs without ever having to resort to the Windows API.

Where API techniques are still required to achieve a certain effect, they are much easier to implement in VB6 forms because VB6 forms expose their window handles and device contexts as native properties, obviating the need for API calls to obtain them. And in contrast to the lightweight, windowless controls provided with VBA userforms, VB6 form controls all have windows and they all expose their window handles as native properties. This allows them to be manipulated in ways that are simply impossible with windowless userform controls. We discuss additional differences between VBA userforms and VB6 forms that are of particular interest to the Excel programmer in the sections that follow.

Better ActiveX Control Support

Not only do VB6 forms provide better support than userforms for a wide variety of built-in Windows controls, they can also host hundreds of third-party ActiveX controls that are completely unavailable to userforms. The reason for this is that almost all third-party ActiveX controls come in two versions. When building your project the version you use is called the design-time version of the control. When your project is distributed and running in a compiled state it uses the runtime version of the control.

When you purchase a third-party ActiveX control, what you are really buying is a license to build your VB6 forms using the design-time version of the control. The runtime version of the control, required by your VB6 form after it is compiled, can be redistributed without restrictions in most cases. This is because it cannot be used in the design-time environment and therefore cannot be used to build new projects.

By contrast, VBA userforms always operate in design mode, so runtime versions of ActiveX controls will not work with them. If you were to distribute the design-time version of a control to get it to run on an uncompiled VBA userform, you would essentially be giving that control away to other users for free. Anyone who has the design-time version of a control installed on their computer can use it in their own projects, whether they purchased it or not.

As you can imagine, there are very few ActiveX control vendors who will allow you to redistribute the design-time versions of their controls. This makes the use of those controls effectively off-limits to all VBA userform-based projects.

NOTE

It is possible to wrap a third-party ActiveX control inside a custom VB6 ActiveX control project. The wrapper project would need to duplicate all the required properties, methods and events of the control it wraps. Once compiled, however, it uses the runtime version of the third-party control. Userforms can host custom VB6 ActiveX controls, so this is a way to get around the limitation described above. However, this is difficult and time-consuming to implement, probably a violation of the license agreements of most third-party controls and, anyway, beyond the scope of this chapter.

Control Arrays

Control arrays are one of the most useful features provided by VB6 forms. There is simply nothing like them supported by VBA userforms. Using control arrays, you can declare a set of controls to be an array. Each control in the array is given the same name but a unique Index property value. VB6 then treats all the controls in the array almost as if they were a single control. For example, a single event procedure of each type is fired in response to activity from any of the controls within the array. This event procedure passes the Index value of the specific control that fired the event, allowing you to respond appropriately.

Not only are all controls in a control array treated as a single control, but you can easily add or remove controls dynamically from the array as required at runtime. Adding or removing controls from the array physically adds or removes actual controls on the form, a capability that greatly simplifies the creation of dynamically configured forms.

We demonstrate a very simple example of a control array below. This example is available on the CD in the \Concepts\Ch20Combining Excel and Visual Basic 6\ControlArrays folder. The plumbing required to display the VB6 form in Excel is identical to that shown in the third iteration of our Hello World example above, so we do not rehash any of that material. Instead we concentrate on how to create and program a control array on a VB6 form.

Our control array demo will consist of a VB6 form that will eventually contain six OptionButton controls along with a ListBox control. The option button selected by the user will determine the contents of the ListBox control. Rather than having to respond to a separate Click event from each option button, we create the six option buttons as a control array so that we can manage them from a single Click event.

The initial form containing a list box and a single nonarray option button is displayed side by side with the Properties window showing the properties for the option button in Figure 20-13. The Properties window is shown with its Categorized tab active and all categories except Misc collapsed. The Misc properties are the ones we want to focus on when it comes to creating control arrays.

Figure 20-13. The Initial Control Array Demo Form

[View full size image]

Note that we have given our option button the name optType and that the Index property selected in the Properties window is empty. This identifies optType as a standard control. The next step is to add the five additional option buttons to our form. Because we have already drawn one OptionButton control on our form that we want to duplicate, there's no reason to add five more option buttons from scratch. Instead we'll just copy our first option button and paste new copies of it back on to the form.

To do this, we just select our existing option button, right-click over it and choose Copy from the shortcut menu. We then select an empty area on our form, right-click over it and choose Paste from the shortcut menu. As soon as we attempt to paste a copy of our existing option button onto the same form where it originated, VB6 displays the message shown in Figure 20-14.

Figure 20-14. VB6 Helps us Create a Control Array

[View full size image]

When you attempt to paste a control that has the same name as an existing control onto a form, VB6 assumes you want to create a control array and it offers to help you do so. Click the Yes button in the message box and let's see what happens. We have reselected our original option button and displayed the result in Figure 20-15.

Figure 20-15. A Control Array Created by the Copy/Paste Method

[View full size image]

The copy of the option button that we pasted appears in the upper left corner of the form. What is intriguing is to compare the previous property values for our first option button from Figure 20-13 with its new property values shown in Figure 20-15. The first thing to note is that the name of our original option button is now optType(0) instead of optType. We allowed VBA to create a control array for us when we pasted the copy of this option button onto our form, and the original option button is now the first element in the array. Control arrays begin at zero by default.

The second thing to notice is that the formerly empty Index property now also has a value of zero. This property is what really determines whether a control is part of an array or not. If the Index property is blank, the control is not part of an array. If the Index property contains a number, the control is part of an array and the Index property value is its position within the array.

We now position the copied option button control (which has an Index of 1) below the original option button and set its caption. Then we'll paste four more copies of the original option button onto the form and do the same. The results are shown in Figure 20-16.

Figure 20-16. The Completed Control Array Demo Form

Even though the option buttons have completely different captions, they have exactly the same name with a unique Index value to identify them. Therefore we now have option buttons optType(0) through optType(5). Note that when you paste a control that is already part of a control array onto a form, VB no longer prompts you with the question shown in Figure 20-16. It just adds the control to the next available index position within the existing control array.

Now that we have completed the visible part of our Control Array Demo form, let's have a look at the code required to manage it. Our form requires only two properties and three event procedures to perform all of its operations:

The code behind our Control Array Demo form is shown in Listing 20-8.

Listing 20-8. The Control Array Demo Form-Specific Code




Option Explicit
' ************************************************************
' Form Property Procedures Follow
' ************************************************************
Public Property Get OptionSelected() As Long
Dim lIndex As Long
Dim sOption As String
For lIndex = optType.LBound To optType.UBound
If optType(lIndex).Value Then Exit For
Next lIndex
Select Case lIndex
Case 0
sOption = "Population"
Case 1
sOption = "Demand"
Case 2
sOption = "Price"
Case 3
sOption = "Turnover"
Case 4
sOption = "Additional Capital"
Case 5
sOption = "Additional Expense"
End Select
OptionSelected = sOption
End Property
Public Property Get ListSelection() As Double
ListSelection = CDbl(lstValue.Text)
End Property
' ************************************************************
' Form Event Procedures Follow
' ************************************************************
Private Sub optType_Click(Index As Integer)
Dim vItem As Variant
Dim vaList As Variant
lstValue.Clear
Select Case Index
Case 0  ' Population
vaList = Array(500, 1000, 100000, 100000)
Case 1  ' Demand
vaList = Array(50, 100, 1000, 10000)
Case 2  ' Price
vaList = Array(9.99, 19.99, 29.99, 39.99)
Case 3  ' Turnover
vaList = Array(0.01, 0.015, 0.02, 0.025, 0.03)
Case 4  ' Additional Capital
vaList = Array(1000, 2000, 3000, 4000)
Case 5  ' Additional Expense
vaList = Array(500, 1000, 1500, 2000)
End Select
For Each vItem In vaList
lstValue.AddItem vItem
Next vItem
lstValue.ListIndex = -1
End Sub
Private Sub cmdOK_Click()
Dim bOptionSelected As Boolean
Dim lIndex As Long
' Do not allow the user to continue unless an option button
' has been selected and a list item has been selected
For lIndex = optType.LBound To optType.UBound
If optType(lIndex).Value Then
bOptionSelected = True
Exit For
End If
Next lIndex
If Not bOptionSelected Or lstValue.ListIndex = -1 Then
MsgBox "You must select an option and a list item."
Else
Me.Hide
End If
End Sub
Private Sub Form_QueryUnload(Cancel As Integer, _
UnloadMode As Integer)
' Route the x-close button through the
' cmdOK_Click event procedure.
If UnloadMode = vbFormControlMenu Then
Cancel = True
cmdOK_Click
End If
End Sub

The OptionSelected property procedure returns a string identifying the option button within the control array that the user selected. It does this by just looping the control array until a control with a value of True is located. After this control is located, the loop is exited and the index value converted into its corresponding string description. The ListSelection property does nothing more than return the Text property of the list box (whose values are constrained by the option button selection).

The optType_Click event procedure is something that we have never seen before. This event procedure behaves exactly like the Click event for any option button except that it fires when any option button in the control array is clicked. This is why it has an Index argument. The Index argument will contain the value of the Index property of the option button that fired the event. We use this event procedure to load the list box with values that correspond to the options selected. Look at the structure of this procedure and imagine how easy it would be to add or remove options.

The cmdOK_Click event demonstrates some very simple validation code. When the form is first displayed, no option button is selected and there is nothing in the list. If this were a real application we would write code to disable the cmdOK button until all the appropriate selections had been made. For the purposes of this demo, however, we just check the status of the option button array and the list when the cmdOK button is clicked. If everything is in order, we hide the form and continue. Otherwise we display a message to the user indicating what they need to do.

The Form_QueryUnload event is used to trap cases where the user tries to close the form with the X-Close button rather than the OK button. In a real application, we would have cancel logic coded into the form that would be activated by this button, but because the only route out of the form that we have provided for this example is through the cmdOK button, we cancel any clicks on the X-Close button and reroute them through the cmdOK_Click event procedure.

As mentioned in the final section of our Hello World example, VB6 forms cannot be accessed directly by outside applications. Therefore we have created a public CDialogHandler class module to expose the Control Array Demo form and the selections the user has made in it to our Excel application. The method used to expose the form is shown in Listing 20-9.

Listing 20-9. The CDialogHandler ShowVB6Form Method




Public Sub ShowVB6Form(ByRef sOption As String, _
ByRef dValue As Double)
Dim frmCtrlArrays As FControlArrays
Set frmCtrlArrays = New FControlArrays
Load frmCtrlArrays
' Parent the Form window to the Excel Application window.
SetWindowLongA frmCtrlArrays.hWnd, GWL_HWNDPARENT, mlXLhWnd
frmCtrlArrays.Show vbModal
sOption = frmCtrlArrays.OptionSelected
dValue = frmCtrlArrays.ListSelection
Unload frmCtrlArrays
Set frmCtrlArrays = Nothing
End Sub

This ShowVB6Form method procedure is almost identical to the method we used to display the form in our Hello World application. The only difference is that in the Hello World application the form did not return any value to the calling procedure, while in this example the form returns the option selected by the user and the item they selected in the list box. The additional code you see in this procedure is used to retrieve these two values and pass them back to the calling Excel application. This is accomplished by the two ByRef arguments to the ShowVB6Form method

In a real-world application, we would typically have much more data to transfer back to Excel and therefore we would design a much more efficient method for doing so, creating a global user-defined type to hold all the information transferred out of the form and passing this UDT directly from the DLL to the Excel application for example. We have not done so here so as not to draw attention away from the main point, which is to demonstrate the use of control arrays.

Better Support for Object-Oriented Programming




More Class Instancing Types




The Instancing property of a class determines its visibility with respect to the application within which it is located. Excel VBA classes are limited to the Instancing types Private and PublicNotCreatable. A class with its Instancing property set to Private is invisible to all outside applications. A class with its Instancing property set to PublicNotCreatable can be seen by outside applications but it can only be used by an outside application if your project creates and exposes an instance of the class through a publicly accessible property, method or procedure first.

What this means for Excel VBA projects is that no outside applications (or even other VBA projects within the same instance of Excel) can create instances of classes that exist within an Excel VBA project. As shown in Figure 20-17, however, a VB6 ActiveX DLL has two additional Instancing types, both of which allow classes of those types to be created and used directly by other applications.

Figure 20-17. VB6 ActiveX DLL Class Types






MultiUse




MultiUse is the default instancing type for class modules added to a VB6 ActiveX DLL project. This is the instancing type that has been used by all the class modules that we have used in this chapter so far. After you have created a class with the MultiUse instancing type, any application that references your DLL can create and use new instances of that class.

The MultiUse instancing type is the one you want to use in cases where it is important for you to require that your public classes are explicitly created before their features are used. This is most frequently the case when your VB6 ActiveX DLL contains an object model that implements the business logic layer of an application, for example.

GlobalMultiUse




Class modules with the instancing type GlobalMultiUse don't need to be explicitly created. Sometimes referred to as auto-instancing classes, this type of class module will be instantiated automatically as soon as any reference is made to one of its publicly exposed components. This type of class is most frequently used for creating libraries of user-defined types, enumeration constants and functions that will be used by multiple files in an application. They are also a good place to define interfaces that you will be implementing in multiple files.

For example, if the code for your data access tier resides in a VB6 ActiveX DLL and the code for your business logic tier resides in an Excel workbook but they both need to share the same user-defined type in order to pass data between them, you can define that UDT in a GlobalMultiUse class in a separate ActiveX DLL that both projects reference. Both projects will then be able to "see" the declaration of the UDT and they can pass variables of that type between themselves even though the UDT is not declared in either one of them. This same technique can be used to expose common enumerations as well.

GlobalMultiUse classes also provide an excellent container for library procedures. Library procedures are generically designed custom subroutines and functions that you find yourself using in most of your projects. If you store these procedures in a GlobalMultiUse class compiled into an ActiveX DLL, all of them can be made available to any project by just referencing that DLL. There is no need to create any object in order to use these library procedures, because public procedures of GlobalMultiUse classes effectively become part of the global namespace in any project that references their DLL. This means you can use your custom bSheetExists() function exactly the same way you use the built-in VBA Replace() function. A working example of a VB6 ActiveX DLL containing a GlobalMultiUseClass can be found on the CD in the \Concepts\Ch20Combining Excel and Visual Basic 6\GlobalMultiUse folder.

Better Support for Custom Collections through Direct Support of NewEnum




As explained in Chapter 7 Using Class Modules to Create Objects, VB6 has two significant advantages over VBA when it comes to creating custom Collection objects. It supports default procedures and it has a user interface that can give the required NewEnum method the "magic number" procedure attribute of 4.Chapter 7 are shown in Listing 20-10.

Listing 20-10. The Item Property and NewEnum Method from the CCells Collection




Property Get Item(ByVal vID As Variant) As CCell
Set Item = mcolCells(vID)
End Property
Public Function NewEnum() As IUnknown
Set NewEnum = mcolCells.[_NewEnum]
End Function

If this code were located in a custom collection class in VB6, you would make the Item property the default procedure of the class by placing your mouse cursor anywhere within the procedure and selecting the Tools > Procedure Attributes menu. This would display the Procedure Attributes dialog. In the Procedure Attributes dialog you would click the Advanced button and select (Default) from the Procedure ID dropdown. The result of this is shown in Figure 20-18.

Figure 20-18. Creating a Default Procedure in VB6






Similarly, to give the NewEnum procedure the magic number procedure attribute of 4, you would place your cursor anywhere inside the NewEnum procedure and select Tools > Procedure Attributes from the VB6 menu. You would again click the Advanced button, but instead of selecting a preexisting item from the Procedure ID dropdown you would type in the number 4. The result of this is shown in Figure 20-19.

Figure 20-19. Adding the Magic Number to the NewEnum Method






With VB6 it requires very little effort to create a custom collection object that behaves exactly like a built-in collection object. As we described in Chapter 7 Using Class Modules to Create Objects, you can accomplish this in VBA but it is a much more laborious process. Because custom collection objects are a fundamental building block in creating custom object models, the ease of creating them in VB6 is a very significant advantage.

Resource Files




Resource files are a type of container provided by VB6 to store string tables and a wide variety of binary objects. These resources, as they are called, are compiled into your VB6 DLL and made available through the use of specialized VB6 functions. In traditional VB6 projects, the most common use of a resource file is to hold a string table that is used to translate the application into multiple languages. A separate string table is provided for each language under which the application needs to run, and all text displayed by the application is loaded dynamically from the string table that corresponds to the language setting on the user's computer.Practical Examples section later in the chapter.

Other VB6 Features




The Clipboard object
The MSForms object library provides text-only access to the clipboard through the DataObject. VB6 has a clipboard object that supports a complete range of operations for both text and graphics.

The Printer object
The VB6 Printer object enables you to communicate directly with the printer installed on the user's computer. This in no way replaces the built-in printing capabilities of an application like Excel, but it opens up a wide variety of additional printing options that are not available from VBA.

The Screen object
The VB6 Screen object, among other useful features, has two methods that allow you to convert from pixels to twips in both the horizontal and vertical directions, obviating the need for a series of API calls required to determine that information with VBA alone.