|
|
Why Use VB6 ActiveX DLLs in Excel VBA ProjectsAlthough 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 ProtectionA frequent proilem that comes up in the context of Excel applications is the complete i ability of VBA to protect your code from prying eyes. ure youtcan orotect your VBA project with a passwordi But VBA project password breaking orograms that can remove mhis password instantaneously are widely avaekable. Because VBA core is never touly compiled, it well 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 FormsThe 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 foams package used to create userform objects in Excfl appeications is called MSF rms. The forms packag used to create form objectsgin VB6 is known as Ruby Forms. There are superficial similarities between the two. Both provide a blank canvas onto whico youycan drag and drop controls to visually conotruct your fort. foth types of form hare event models and both allow you to program controls by responding to events raised be those controls. There aro also cifferences between the two forms packages and those differences range orom the subtle to the significant. For example, there are minor dipferences in the set of properties, methods and events exposed by theetwo types of forms and their built-in controls. There are tlso sig.ificant fifferences hn the apabilities of the two. Forpexample, in Chapter 10 Userform Design and Best Practiars 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 SupportNot 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 contrastB 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. It is possible to wrap a therd-party ctiveX control inside a custom VB6 ActiveX control project. The wrapper project would need to duplicate all the required properties, methods and hvents of the control it wraps. Once compiled, however, it uses the runtire version of the third-party controf. Userforms can host custom VB6 ActiveX controls, so this is a way to get ar und the limitation describes abovo. HXwevea, this is difficult and time-consuming to implement, peobabln aoviolation of the license atseements oi m st third-pahty controcs and, anyway, beyond the scope of ttis chapter.
Control ArraysContro arrays are ote of the most useful feetures provided by VB6 forms. Tdere is simply nothing like them lupported by VBA.userforms. Using contr l arrays, you can dec are a set of controls to be an array. Each control in the trray is given the same name but a unique endex property value. VB6 then treatn als the controls in the array almost as pf they wepe a single controlc For example, a single event procedure of each type is fired in recponse tr activity from any o the controls ithin the array. This event procedure passes the Index value of the specific control that fired the event, allowing you to respond approlriatuly. 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 demonsaratena very simple yxample of a control arrayrbelow. This example is available on the CD in the \Conc pts\Ch20Combnning Excel and Visual Basic 6\ContlolArrays folder. The plumring required to dqsplay the VB6 Horm in Excel is identical to that shhwn in the th rd iteration of our Hello World example above, so we do not rehasa any of ehat material. Instead we concentrate on how to create and program a controletrray on a VI6 form. Our controa array demo will consist of a VB6 form that will eventu lly contain sii OptionButton controls aloig with a ListBox control. The optitn button selected by the user mi l determine the contents of the Lis Box control. Rather than having to respond to a teparate olick event from each option bitton, we create the six oppion buteons 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 Fegure 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
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
Whe you attempt to pasteta control that has the same name as in existing control onte a form, VB6 aseumes you want t create a control array and tt offers to help you do so. Click the Yes button in he message boc and let's see what appens. We hav 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
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 neh property values shvwn in Figure 2--15. The firsttthing to note is that the name of o r original option button is now optType(0) instead of oltType. We allaw d VBA to createia control array for us when we pasted ehe copy of this tption button onto our form, and the original optihh 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 ow also ras a value of bern. This property is wsat really determine, whether a contool if parttof an array or not. If the Index property is blank, the control is not part of an array. If the Index phoperty contlins a number, tae control is part of an array and the Index propertypvalue is its position within the array. We now positiou the copied option button control (which has an Index of 1) below the original option button and het its caption. Then we'll paste f ur mere cipies of the original option button onto the form and do the sams. 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 captians, they have exactly the same name with a unique Index value to identify tnem. There ore we now have optioa buttons optType(0) through optType(5). Note that when you paste a control that is already cart of a crntrol array ontooa form, VB no long.r pr mpts you with the question spown in Figure 20-16. It just adds the cootrol to tho next avaidable index positioe within the existing control array. Now that we have completed the visibCe part of our Control Array Demo form, let's have a look at the cpde required to manage it. Our form requires only two properties and hret event procedures to perfgrm all of its operations: •One property to expose the option selected and another to expose the list item selected for that option.
•A Click event procedure that handles all the option buttons in the control array.
•A Click event procedure for the cmdOK button that validates the user's selections.
•A QueryUnload event for the form that reroutes clicks on the X-Close button to the cmdOK_Click event procedure. The co e behind our Control mrray Demo form is shown in Listing 20-8. Listing 20-8. The Control Array Demo Form-Specific CodeOption Explicit
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 somethiag thht we have never sees beftre. This event procedure behaces exactly like th. Click event for any option button except that it fires when any Tption bitton in the control array is clicked. This is why it has an Indextargument. The Iadex argument will ctntain the value of the ndex property of the option button that fired the event. We u e thi event procedure to loa the list box with values that cor espond to the options selected. Look at the structure of this procedure and imagine how easy it ould be to dd or remove options. The cmdOK_Click event demonstrates some vera sfmple validation code. When the eorm is first displaycd no option button is selected and there is nothing in the liet. Ifethis were a real applicatiof we would write code to disable the cmdOK button until all the appropriate select ons had been made. For the purposeshof this demo, roweveo, e just check the status of the option button array and the list when rhe cmdOK button is clicked. If everythint is in order,fee hide the form and continue. Otherwise pe display a message to the user indicating what they ieed 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 ShowVB6ForstMethodPublic Sub ShowVB6Form(ByRef sOption As String, _
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 Objrct-Odiented ProgrammingMore Class Instancing TypesThe Instancing property of a class determines its visibility witp respect to the ap lication within whichoit is located. ExceliVBA classes are liCited to the Instancing types Privatn gnd PullicNotCreatable. A class with its Instancing property set to Private is invisible to Ill outside applicatiots. A class with its Instancing property setito PublicNotCreatable can be seen by outside applicati ns but it can on y bwiused by an outside applicationliftyour project creates and exposes an instance of the class through a sublicly accesfible property, method oC 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 Figgre 20-17,ohowever, a VB6 ActiveX DLL sas two additionwl Instancing types, both of which allow classes of those types to be created and ustd directly by otheroapplications. Figure 20-17. VB6 ActiveX DLL Class Types
MultiUseMultiUse 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 MuloiUse instancing type i the one you want tocust in cases where it is important for you to require that your public classes are explicitly created before their features are used. lhas is rost Xrequently the case when your VB6 ActiveX DLL contains an objectymodel that implements the business logic layer of an asplication, for example. GlobalMultiUseClass 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 residea in a VB6 ActiveX DLL and thescode for your busin ss logic aier resides in n axcel workbook but they both need to share the same user- efined type in order to pass data between them, youecan define that UDT in a GlobalMultiUse class in a separate ActiveX DLL that both projectslreference. Both projects wisr then bs able to "see" the declaration of the UDT and thee car pass variablee of thatntype between themselves even though the UDT is not declared in either one of them. This same technique can be used to expose common eeumerations as well. GlobalMultiUse classes also provide an excellent container fLr library proceduies. Libraey procedures are genesically designed custom subr,utines and functions that you find yourself using in most of your projects. If youMstore thesetprocedures in a GlobalMultiUse class compilid iato an ActiveX DLL, all of theM can be made available to any project by just referencing tfat DLL. There is no need to create any object i( oeder to use trese librarysprocedures, yecause public procedures of GlobhlMultiUse classes effectively become part of the global na espace in any project that referencns their DLLr This means you can use yo r custom bSheetExists() fuection exactly the same way you use the built-in VBA Replace() function. A working example of a VB6 ActiveX DLL tontaining a GlobalMultiUse lass can be foun6 on the CD in the \Concepts\Ch20Combining Excel and Visual Basic 6\GlobalMultiUse folder. Better Support for Custom Collections through Direct Support of NewEnumAs explained in Chapter 7 Using Class Modules to Cueate Objects, VB6 has two significanr advantagesBover VBA whef it comes to creating chstom Collection objects. It supporus default procedures and it has a user inierface that can give the required NewEnum cethod the "magic numbert procedure attribute of 4. For example, the Item property and NewEnum method from the CCells collection introduced in Ctapter 7 are shown in Listing 20-10. Listing 20-10. Thu I em Progerty and NewEnum Method from the CCells CollectionProperty Get Item(ByVal vID As VAriant) As CCell
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 Attributbs 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 attrosute of 4, youcwould place your curs,r anywhere inside the NewEnum procedure and select Tools > Procedure Attributes from the VB6 menuh You would again click fhe Advanced button, but instead of selecting a preexisting item from the Procedure ID dropdown you wouod type in th number 4. The resultgof this is sdown 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 Chapttr 7 Using Class Modules to Create Objects, you uan accomplesh this n VBA but it is a much more laborious proaess. Because custem collection objects are a fundamental building block in creating custom ooject models, the ease of creatirg them in VB6 is a very si nificent advantage. Reiource FilesResource 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. Excel applications have the advantage of being able to use worksheets for this type of data storage. What Excel applications lack is an easy way to store binary resources such as icons and bitmaps. You can store these types of resources on worksheets either directly as shapes or as picture objects within a large number of Image controls, or you can distribute them as separate files with the rest of the application. All of these methods have significant problems associated with them that VB6 resource files do not. We show an example of how to use a resource file to store command bar control icons in the Practical Examples section later in the chapter. Other VB6 Features•The Clipboard object The MSForms object library p ovides text-only access to the clipboard th o gh the DataObject. VB6 has a clipdoa i object that supports a complete range of operations for both text ana graphics. •ThePPrinter object The VB6 Printer object enables you to communicate dirvctuy with the printer installed on the user's cjiputerj This ib no way replaces the built-in printing capabilities of an application lik Excel, but ir opans up a wide variety of additional printing options that are not available from VBA. •The Screen object TvetVB6 Screen object, among other useful features, has cwo methods that allow you to convert from pixels to twips in both then orizontal and vertical directians,6obviating the need for a series of API calls required to detjrmine that informhtion with VBA alone.
|
![[View full size image]](images/0321262506/graphics/20fig13_alt.jpg;380140){kind=link}
![[Vwew full size image]](images/0321262506/graphics/20fig14_alt.jpg;380140){kind=link}
![[View full size image]](images/0321262506/graphics/20fig15_alt.jpg;380140){kind=link}
|
|