VB6/VBA 12, Overview 3

Figure 1A
Visual Basic 6 Textbox Properties

VB Tutorial 12 - VBA III

Our last tutorial provided an overview of the importance of VBA as Windows macro language. In addition a VBA dialog was developed for initializing headers and footers within Word. In the process of developing our own Word command, it became evident that:
a)VBA developers are working with a subset of all VB features;
b)there are tricks to getting your VBA dialogs to run from a toolbar or menus;
c)each application (AutoCAD, Excel, Word, Visio, etc) has its own distinct commands best learned from recording macros and the Object Browser.
So in this session we return to creating another VBA command dialog. But it will be done in a way so that the code can be exported to other VBA enabled programs and even to VB itself. Finally we will use a very useful Windows GUI feature - tabbed forms in our dialog.

Figure 1B
VBA Textbox Properties

But let us return to the first point for a moment - VBA is a subset of VB. In fact, VBA is only a somewhat close subset of Visual Basic. Look at Figures 1A and 1B. 1A shows the Property Sheet for a text box in Visual Basic 6. Note the differences with the textbox properties shown in Figure 1B for VBA in Office 2000 Excel. First, VB6's Property Sheet has a descriptor for each property highlighted at the bottom of the sheet; VBA does not. Second, VB6 has such textbox properties as Alignment, Appearance, DragMode, DataField, DataFormat, DataMember, Index, LinkItem, LinkMode, LinkTimeout, LinkTopic, and several others which VBA does not have. Likewise, VBA has textbox properties such as Autosize, AutoTab, AutoWordSelect, BackStyle, BorderColor, ControlSource, ControlTipText, DragBehavior, EnterField Behavior and several others that are distinct to VBA. Now readers will rightly point out that some of these properties overlap and that is true; however I have yet to find a Microsoft source that reconciles the differences.

So the bottom line on compatibility between VB and VBA is the following:
1)for the common controls, their properties, methods, and events are fairly close but by no means identically the same;
2)for the command syntax (For..Next; Select Case, Format(), etc) VBA is a direct subset of VB;
3)for COM objects, classes and the hierarchy of object collections every version of VBA has its unique hierarchy - so to an extent one must learn Visio VBA as distinct from Excel VBA;
4)but you can import VBA forms into VB6 at the price of a larger runtime;
5)most VB6 forms cannot be imported into VBA; but VB6 COM components can be imported into VBA projects.
This close-but-not-quite compatibility means that users and developers will have to invest extra time in two ways: first, users will have to learn more to master VBA even given strong knowledge of VB6; depending on the application (Excel, Word, Visio, or others) between 20-50% more. Second, developers will have to spend 10-15% of their time resolving runtime differences between VB and VBA when sharing forms and modules. In summary, all one can say is that close and good enough have been winning strategies for Microsoft for quite some time and VBA appears to be the defining case.
Figure 2 shows an Excel VBA editor working on our Message and Meeting Form. This will be a useful exercise because the tab control used in this form work in a very similar fashion in VB6 as well. In effect, a tab or multi-page control (as in VBA lingo) saves screen space by fitting two or more forms into one dialog box. In our Message and Meeting dialog we will build three forms - the "Message To" tab/page in which an Excel user can choose which colleagues to send a message to. Then in the next tutorial we will complete the project by adding the "Contents" tab/page which allows the subject and body of the message to be filled out plus a third tab/page, "Meeting Options", which notifies team members and support staff of any meeting needs.

In VB Tutorial 10 , we used two comboxes to transfer field name from in the DynaCube to out and back. In a

Figure 2 - VBA Form using Tabbed Pages

similar fashion we use two lisboxes to transfer "Group Members" to the "Message To" list (just off screen in Figure 2). However, in our form we are using command buttons(Add>, All>>, <Remove, <<All) to move items from one list to another. This type of list manipulation occurs quite frequently in GUI interface programs - so we shall spend some extra time describing the programming of this tab/page of our 3 part Message and Meeting form.

Start up Excel (97 or 2000 or any other VBA enabled app if you don't have Excel) and then choose Tools | Macros | Visual Basic Editor. When VBA comes up choose Insert | UserForm and a blank form will appear on screen along with the Toolbox. From the ToolBox select the MultiPage control (3 row, second icon not the TabStrip) and draw it so it fills 4/5ths of the form. Two tabs labeled Page1 and Page2 will appear. Click on Page1 and change its Caption to Message To. Click on Page2 and change its Caption to Contents. Right mouse click either of the tabs and select New Page from the pop up menu. Change the Caption from Page3 to Meeting Options - note that the tab automatically widens to fit the label in. We have now created our basic multi-page form.

Next click on the Message To tab and add two listboxes of equal size on opposite sides of the page - change their Name in the Property Sheet to Lb1 and Lb2 respectively. Add four command buttons between the two listboxes (as shown in Figure 2). Remember to change their Captions to Add>, All>>, <Remove, <<All. If you want, change the names of the command buttons to something more meaningful - I used Badd, Baddall, Brem, and Bremall.

Now add two more command buttons at the bottom strip and to the right (make sure the command buttons are not on the multi-page part of the form. Change the command buttons Captions to Send and Cancel respectively. Double click on the Cancel button and when the source code editor comes up add the following line of code:

Unload me

Do the same for the Send Button. Later, we will add successively much more code to this Sub Send_Click() procedure. Now we will add the snippets of code to move names to and from the Group members-Lb1 list box and the Message To-Lb2 listbox. So double click on the Add> command button and insert the following code::

Private Sub Badd_Click()
If Lb1.ListIndex >= 0 Then
Lb2.AddItem Lb1.Text
Lb1.RemoveItem Lb1.ListIndex
End If
End Sub

Note we check first that the list is not empty then we add the item to Lb2 list box(Message To list) before removing the item from the Group Members list Lb1. For the All>> command button the code is as follows:

Private Sub Baddall_Click()
Do While Lb1.ListCount > 0
Lb2.AddItem Lb1.List(0)
Lb1.RemoveItem (0)
End Sub

Note that the Do While loop will not start if the Lb1 listbox is empty; which is exatly what we want. The loop transfers all the Group Members starting at the top over to Lb2, the Message To listbox. As you might guess the <Rem and <<All code is quite similar. Download the vbtut12.zip file from my website at www.inforamp.net/~jbsurv to see the details plus some additional features. Finally we have to add some Group Member names to Lb1 with the following code:

Private Sub UserForm_Initialize()
With Lb1
.AddItem "Dave"
.AddItem "Don"
.AddItem "Dirk"
.AddItem "Darryl"
.AddItem "Dom"
.AddItem "Donna"
.AddItem "Debra"
.AddItem "Dan"
.AddItem "Dieter"
End With
End Sub

Note the With clause saves having to type Lb1 before each .AddItem. Now to check out the dialog box, save the results (File | Save) and then place the cursor in any one of the UserForm Subs and press the F5 key or click Run | Run Sub/UserForm. Highlight a name in the Group Member list and then press the Add> button. Try the other buttons. If the program does not work, use the debug features of VBA by setting a breakpoint in the subroutine that is not working. make sure the debug toolbar is available - right mouse click anywhere on the standard toolbar and choose Debug from the popup menu. To start debugging click on Debug | Step Into. With this we have finished the first part of our UserForm. In the next tutorial we will complete the Contents and Meeting Options pages. For more reading on VBA, there are some excellent sites on the Web including www.zdnet.com, www.techweb.com, and www.idgnet.com (punch VBA into Search engines).

Jacques Surveyer is a consultant; let me know at jbsurv@theopensourcery.com your favorite VB and VBA websites and I will publish a list of the best in an upcoming tutorial .
Top of Page  Tutorials Home