Excel VBA: Advanced Topics. Excel VBA 2 Lesson 21

This lesson covers topics that didn’t fit well in earlier lessons, and introduces two handy controls.

Digital Signatures and Certificates

Professional programmers digitally sign their work.  Signatures ensure that that the document is authentic, that the creator of the document stands behind the work, and that the signature can be taken as a trusted source.  Signatures are encrypted to prevent easy forgery.  With the prevalence of viruses today, many applications such as Excel require signed macros to run.  There are two levels, the self-created signature, and the industry registered certificate.  The Microsoft Internet document http://msdn.microsoft.com/en-us/library/ms995347.aspx lists 27 commercial certification authorities that do code signing.  Look for a Yes in the fifth column of the table.

An self-created signature may be created using SELFCERT.EXE from Microsoft.  It has grown larger over the years, as more functionality is added, and newer versions of Windows are accommodated.  The 1999 version was 68 KB in size, the 2007 version was 348 KB, and the 2010 version is 508 KB.  SELFCERT.EXE may not be in the default Windows install.

When SELFCERT is run, a name must be supplied.  Since the signature is specific to one PC, and is good only for a certain period of time, it is useful to include something in the name to identify the developer, the PC, and the creation date.  The date can be supplied in YYMMDD form to avoid issues with language and locale.

Besides the warm fuzzy feeling awarded the user, the signature protects the developer from being blamed for problems caused by another person’s modifications.  Any changes to the macros on any PC other than the original PC will result in the signature being deleted. 

 

Creating and Removing Controls at Runtime

Sometimes a control is not appropriate on a UserForm until the user has made a certain selection.  If the controls are disabled or hidden, the layout must include the space for them.  If there are a lot of them, it may be better to add the control while UserForm is running.  The TabStrip is one case where the number of tabs needed is not known when the UserForm is opened, or changes while it is open.

Add Method

The Add method is used to add controls to the Controls Collection after the UserForm is open.  Since it is never enough to just create a control, there must be a way to refer to it so properties such as size and location may be set.  There are several ways to do this.  The first does not give a name, but relies on the fact that the new control will be at the end of the Controls collection.  Remember that the collection index starts at 0, so the last item in the collection is one less than the collection count.

Me.Controls.Add ProgID

With Controls(Controls.Count – 1)

    ' change properties here

End With

The second supplies a Name, which is then used directly.

Me.Controls.Add ProgID, Name

With Name

    ' change properties here

End With

The third way sets a variable to the new object.  It requires a little more work up front, but is handy if the control has many references in subsequent code.

Dim var

Set var = Me.Controls.Add(ProgID [, Name [, Visible]])

With var

    ' change properties here

End With
' after some code

' var is used again

Of the three arguments of the Add method, only ProgID is mandatory.  Since the new control only exists until UserForm is closed, a fancy naming scheme is not necessary.  Visible defaults to True, and only need to be set to False if there is a reason to conceal its presence from the user until a later time.  The table below shows the ProgIDs for each control:

While a TabStrip can be added to a UserForm with the above syntax, the adding of tabs to a TabStrip requires a different technique.  This is because TabStrip is a container for a Tabs collection.  The Tabs collection is not directly available, but is accessed through the Object property.  It is more easily shown than explained, so study the code for UserForm_Lesson10b explained in the DEMO section below.

Adding a control to a UserForm does not do much good without code to go with it.  There are ways to add code programmatically, but they are difficult to master and not generally recommended.  It’s probably more efficient to only add controls that have links to cells via ControlSource, or whose functions are covered by collections such as TabStrip or MultiPage.

Most often, it is better to add the control at design time, and set the Visible property or the Enable property to False.  When the control is needed, toggle the property and continue.

If there is an overriding need to create code when the UserForm is already open, programming the VBE environment is possible.  This link is a reasonable place to begin:

http://www.cpearson.com/excel/vbe.aspx

Remove Method

The Remove method is used to delete a control from an open UserForm.  The syntax is:

Me.Controls.Remove collectionindex

where collectionindex may be either a number (the sequence number in the Controls collection) or a string (the control Name).

DEMO

UserForm_Lesson10b demonstrates the simple adding and deleting of a button, and the more complex case of adding Tabs to a TabStrip.

After the button is created with the Add a Button button, it is sized to match the other buttons, and is left-aligned with Add a Button button and top-aligned with Close.  Note that without code, the added button is useless.

The Add a Tab button will allow adding tabs up to the length of the list beginning at cell B17 on sheet Lesson 10.  As an exercise, add six items to the list and Add a Tab for all of them, to see that when too many tabs have been added to fit on TabStrip, a horizontal ScrollBar will be added to navigate to those not showing.  Note that the TabStrip code handles the added tabs.

Finding the Active Control

When there are many controls of the same type on a UserForm, the initial coding effort repeats the code for each control.  If a change is made to the common part of the code, then all the macros need the same change.  If the current active control can be identified, then a single set of code can be used for all the similar controls.

ActiveControl identifies the active control.

This example shows how to set up common code to handle Change events for four ComboBoxes. 

Private Sub DoComboAction()

    Dim iRow as Integer

    ' toggle active control Bold property

    iRow = ActiveControl.ListIndex + 1

    If iRow > 0 Then

        With Range(ActiveControl.RowSource).Cells(iRow, 1)

            .Font.Bold = Not .Font.Bold

        End With

    End If

    iRow = 0

End Sub

 

Private Sub Level_1_Change()

    DoComboAction

End Sub

Private Sub Level_2_Change()

    DoComboAction

End Sub

Private Sub Level_3_Change()

    DoComboAction

End Sub

Private Sub Level_4_Change()

    DoComboAction

End Sub

Creating a Template Control

A distinctive look and feel may be developed for a project.  Since it is time-consuming to format controls, it is efficient to drag such controls to the Toolbox where they may be used again.

EXERCISE

Open UserForm Control Demos.xls.  Open VBE.  Edit UserForm_Lesson10a and select the UserForm.  If the Toolbox does not appear, View>Toolbox to activate it.  Right-click the Controls tab and click New Page.  Right-click the New Page tab and click Rename.  Pick a name that is descriptive, such as a project name.  For this exercise, rename it to myControls.

Drag the Close button to myControls.  Hover the cursor over it, and the tooltip box will read New CommandButton.  Right-click it and click Customize New Command Button.  Change the tooltip to Close CommandButton, and hover again to check the tooltip change.  An optional step is to point to a new picture.  It will only be for quick identification of the control on myControls, and will not affect how the control looks on a UserForm.  Note that large files will not be loaded.  This can be a time-consuming effort with modest reward, so will not be pursued in these lessons.

Repeat for Cancel button.

Right-click in VBAProject and click Insert>UserForm.  Select each of the Close and Cancel template controls and create a control on the new UserForm.

Delete the new UserForm.

In some cases it will be convenient to have starter code available for the template controls.  Since the Toolbox does not carry with it any code, the best way to attach code is to create a ControlsTemplate UserForm, place all the myControls controls on it, and copy the started code into the Template UserForm code module.  Create or identify a folder that will be easy to find.  Right-click ControlsTemplate in VBAProject, click Export File, navigate to the folder, and click Save.  It will now be available for importing into other Excel files.

Another good place to store ControlsTemplate is in VBAProject PERSONAL.XLS.

Two Additional Controls

There are hundreds of additional controls available on the Internet.  The curious student need only search the Internet for ocx files to see.  Just two additional controls will be covered here, RefEdit and Date and Time Picker.  Both should be available in all versions of Windows.

There are two actions to use an additional control.  The first is to add it to Toolbox, so that it can be added to UserForms as with any other control.  The second is to add it to the VBE References.  This references the object library so that the properties and methods of the control will be known during development.

	RefEdit	Date and Time Picker
Add to Toolbox	RefEdit.Ctrl	Microsoft Date and Time Picker Control 6.0 (SP4)
Add to References	Ref Edit.Ctrl	Microsoft Windows Common Controls-2 6.0 (SP4)

EXERCISE

Open UserForm Control Demos.xls.  Open VBE.  Edit UserForm_Lesson10 and select the UserForm.  If the Toolbox does not appear, View>Toolbox to activate it.  Select the myControls new page added above, right-click any control, and click Additional Controls.  Find and check Microsoft Date and Time Picker Control 6.0 (SP4) and RefEdit.Ctrl. Click OK.  Both controls are now available on myControls.

Open References with Tools>References, and find and check Ref Edit Control and Microsoft Windows Common Controls-2 6.0 (SP4).  Click OK.  Both controls are now checked on References.

Control	Short Description	Default Property	Default Event
RefEdit	Displays address	Value	BeforeDragOver
Date and Time Picker	Displays a date	Value	Change

 RefEdit Control

RefEdit is not a normal ActiveX control found in an OCX file.  It is a special control just for UserForms.  Normally, a UserForm retains focus, and to allow focus to leave the UserForm to go another place in the Excel application, it must be started as vbModeless   RefEdit on a UserForm causes it to be modeless, and it must not be started with Show vbModeless.  Doing so causes the application to lock up.

This control is only usable on UserForm, so if the project is to result in a .COM module, don’t use it.

The purpose for RefEdit is to return a range.  It may be typed in or selected with the cursor.  Normal usage is to select the range with the cursor.  Select a small range directly by clicking a corner and dragging the cursor to the opposite corner.  To select a large range, click a corner cell and use arrows and the keyboard navigation keys such as arrows, End, and <Ctrl> to define the range.  Because the UserForm is modeless, it can be moved out of the way to access a cell that happens to be behind it.

Using the direct dragging method, it is possible for an unusual requirement to define multiple ranges by selecting one and then pressing <Ctrl> to select another.  The ranges will be separated by a comma.  It probably would be more clear if multiple RefEdit controls were used.

The Value returned by RefEdit is always a string.  It can only be a range address and not a Range object.  Note that the Worksheet name is included in the returned string.

When a range is selected with the cursor rather than by typing, RefEdit will always return a valid range address, and so will not need to be validated.  If there are special requirements, such as the inclusion of a blank row above or below or a blank column to the right or left, then code can check that the special requirement is met.

When the address is typed, use the isAddress function from Lesson 9 to validate it.

Date and Time Picker Control

Date and Time Picker Control 6.0 (SP4)

Can also display time.  It will be less confusing to use a second control for time, and combine the two when using.

Utilities

Utility procedures are small procedures performing one function, that can be re-used.  A good place to store them is in VBAProject PERSONAL.XLS.

chgDirIfNeeded

The chgDirIfNeeded utility changes the drive letter and directory (aka folder) to match that of the current workbook.  This allows files to be saved to the same directory with relative path instead of the much longer full path.  The optional bMsg argument, if set to False, suppresses the notice that the drive and/or directory is changing.

Sub chgDirIfNeeded(Optional bMsg As Boolean = True)

    ' Version 1.3 8/12/05 - exit if network drive

    If Left(ThisWorkbook.Path, 1) = "\" Then Exit Sub

    If Left(CurDir, 1) <> Left(ActiveWorkbook.Path, 1) Then

        If bMsg = 1 Then MsgBox "Changing" & vbCr & _

            "  current drive " & Left(CurDir, 1) & vbCr & _

            "       to drive " & Left(ActiveWorkbook.Path, 1)

        ChDrive Left(ActiveWorkbook.Path, 1)

    End If

    If CurDir <> ActiveWorkbook.Path Then

        If bMsg Then MsgBox _

            "Changing Current directory " & CurDir & vbCr & _

            "to " & ActiveWorkbook.Path

        ChDir ActiveWorkbook.Path

    End If

End Sub         ' chgDirIfNeeded

Asynchronous Hardware Actions

Sometimes a problem will be seen where code appears to work, but the results are is if it didn’t.  This can happen if commands are issued to the hardware, where the hardware immediately signals that the action completed, when in fact the hardware is still processing.

If the PC has an independent disk controller, it can signal that the file write is complete, when in fact it is still completing the write from buffered data.  If a file is saved, then immediately closed, then the source of the data to be written may be removed before the write can happen.

If the PC has an independent video controller, it can signal that the display is updated, when in fact it is still completing the update from buffered data.  If a subsequent code step clears the buffer, the video processor may not have completed the update.

One of the hallmarks of this type of problem is that when the code is stepped through, the problem does not occur.  A step can be inserted to wait a few seconds before continuing.  This step pauses for 60 seconds.

Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 60)

Insert this line immediately after the file save or screen update as a diagnostic step.  If it works, then reduced delay times can be tried until the failure occurs.  Often a 1 second delay is sufficient.

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid.com is in no way associated with Microsoft.