Chapter 26: Manipulating Visual Basic Components

In This Chapter

• Getting an overview of VBA Integrated Development Environment (IDE) and its object model

• Using VBA to add and remove modules from a project

• Writing VBA code that creates more VBA code

• Using VBA to help create UserForms

• Creating a UserForm on the fly

Introducing IDE

This chapter covers a topic that some readers might find extremely useful: writing VBA code that manipulates components in a VBA project. VBA Integrated Development Environment (IDE) contains an object model that exposes key elements of your VBA projects, including Visual Basic Editor (VBE) itself. This object model enables you to write VBA code that adds or removes modules, generates other VBA code, or even creates UserForms on the fly.

IDE is essentially an Object Linking and Embedding (OLE) automation interface for Visual Basic Editor. After you establish a reference to the object, you have access to all VBE objects, properties, and methods, and you can also declare objects from IDE member classes.

Use the VBE Tools⇒References command to display the References dialog box, where you can add a reference to Microsoft Visual Basic for Applications Extensibility Library (see Figure 26-1). This gives you access to an object called VBIDE. Creating a reference to VBIDE enables you to declare object variables contained in VBIDE and also gives you access to a number of predefined constants that relate to IDE. Actually, you can access the objects in IDE without creating a reference, but you won't be able to use the constants in your code, nor will you be able to declare specific objects that refer to IDE components.

Figure 26-1: Adding a reference to Microsoft Visual Basic for Applications Extensibility Library.

After you understand how the IDE object model works, you can write code to perform a variety of operations, including the following:

• Adding and removing VBA modules

• Inserting VBA code

• Creating UserForms

• Adding controls to a UserForm

The IDE Object Model

Programming the IDE requires an understanding of its object model. The top object in the object hierarchy is VBE (Visual Basic Environment). As with Excel's object model, VBE contains other objects. A simplified version of the IDE object hierarchy follows:

VBE

    VBProject

      VBComponent

          CodeModule

          Designer

          Property

      Reference

    Window

    CommandBar

This chapter ignores the Extensibility Library Windows collection and CommandBars collection, which aren't useful for Excel developers. Rather, the chapter focuses on the VBProject object, which can be very useful for developers — but make sure that you read the “An important security note” sidebar.

The VBProjects collection

Every open workbook or add-in is represented by a VBProject object. To access the VBProject object for a workbook using early binding, make sure that you've established a reference to Microsoft Visual Basic for Applications Extensibility Library (see the section “Introducing the IDE,” earlier in this chapter).

The VBProject property of the Workbook object returns a VBProject object. The following instructions, for example, create an object variable that represents the VBProject object for the active workbook:

Dim VBP As VBProject

Set VBP = ActiveWorkbook.VBProject

If you get an error message when VBA encounters the Dim statement, make sure that you've added a reference to Microsoft Visual Basic for Applications Extensibility Library.

Each VBProject object contains a collection of the VBA component objects in the project (UserForms, modules, class modules, and document modules). Not surprisingly, this collection is called VBComponents. A VBProject object also contains a References collection for the project, representing the libraries being referenced currently by the project.

You can't add a new member to the VBProjects collection directly. Rather, you do so indirectly by opening or creating a new workbook in Excel. Doing so automatically adds a new member to the VBProjects collection. Similarly, you can't remove a VBProject object directly; closing a workbook removes the VBProject object from the collection.

The VBComponents collection

To access a member of the VBComponents collection, use the VBComponents property with an index number or name as its argument. The following instructions demonstrate the two ways to access a VBA component and create an object variable:

Set VBC = ThisWorkbook.VBProject.VBComponents(1)

Set VBC = ThisWorkbook.VBProject.VBComponents(“Module1”)

The References collection

Every VBA project in Excel contains a number of references. You can view, add, or delete the references for a project by choosing the Tools⇒References command. (Refer to Figure 26-1 to see the References dialog box.) Every project contains some references (such as VBA itself, Excel, OLE Automation, and the Office object library), and you can add more references to a project as needed.

You can also manipulate the references for a project by using VBA. The References collection contains Reference objects, and these objects have properties and methods. The following procedure, for example, displays a message box that lists the Name, Description, and FullPath property for each Reference object in the active workbook's project:

Sub ListReferences()

    Dim Ref As Reference

    Msg = “”

    For Each Ref In ActiveWorkbook.VBProject.References

        Msg = Msg & Ref.Name & vbNewLine

        Msg = Msg & Ref.Description & vbNewLine

        Msg = Msg & Ref.FullPath & vbNewLine & vbNewLine

    Next Ref

    MsgBox Msg

End Sub

Figure 26-2 shows the result of running this procedure when a workbook that contains six references is active.

Figure 26-2: This message box displays information about the references for a project.

Because it declares an object variable of type Reference, the ListReferences procedure requires a reference to VBA Extensibility Library. If you declare Ref as a generic Object, the VBA Extensibility Library reference is not needed.

You can also add a reference programmatically by using either of two methods of the Reference class. The AddFromFile method adds a reference if you know its filename and path. AddFromGuid adds a reference if you know the reference's globally unique identifier, or GUID. Refer to the Help system for more information.

Displaying All Components in a VBA Project

The ShowComponents procedure, which follows, loops through each VBA component in the active workbook and writes the following information to a worksheet:

• The component's name

• The component's type

• The number of lines of code in the code module for the component

Sub ShowComponents()

    Dim VBP As VBIDE.VBProject

    Dim VBC As VBComponent

    Dim row As Long

    

    Set VBP = ActiveWorkbook.VBProject

    

‘   Write headers

    Cells.ClearContents

    Range(“A1:C1”) = Array(“Name”, “Type”, “Code Lines”)

    Range(“A1:C1”).Font.Bold = True

    row = 1

    

‘   Loop through the VB Components

    For Each VBC In VBP.VBComponents

        row = row + 1

‘       Name

        Cells(row, 1) = VBC.Name

‘       Type

        Select Case VBC.Type

            Case vbext_ct_StdModule

                Cells(row, 2) = “Module”

            Case vbext_ct_ClassModule

                Cells(row, 2) = “Class Module”

            Case vbext_ct_MSForm

                Cells(row, 2) = “UserForm”

            Case vbext_ct_Document

                Cells(row, 2) = “Document Module”

        End Select

‘       Lines of code

        Cells(row, 3) = VBC.CodeModule.CountOfLines

    Next VBC

End Sub

Note that I used built-in constants (for example, vbext_ct_StdModule) to determine the component type. These constants aren't defined unless you've established a reference to Microsoft Visual Basic for Applications Extensibility Library.

Figure 26-3 shows the result of running the ShowComponents procedure. In this case, the VBA project contained six components, and only one of them had an empty code module.

Figure 26-3: The result of executing the ShowComponents procedure.

This code is available on the book's website in a workbook named list VB components.xlsm. The workbook includes an enhancement that lets you choose a project from all open VB projects.

Listing All VBA Procedures in a Workbook

The ListProcedures macro in this section creates a list (in a message box) of all VBA procedures in the active workbook:

Sub ListProcedures()

    Dim VBP As VBIDE.VBProject

    Dim VBC As VBComponent

    Dim CM As CodeModule

    Dim StartLine As Long

    Dim Msg As String

    Dim ProcName As String

    

‘   Use the active workbook

    Set VBP = ActiveWorkbook.VBProject

    

‘   Loop through the VB components

    For Each VBC In VBP.VBComponents

        Set CM = VBC.CodeModule

        Msg = Msg & vbNewLine

        StartLine = CM.CountOfDeclarationLines + 1

        Do Until StartLine >= CM.CountOfLines

            Msg = Msg & VBC.Name & “: “ & _

              CM.ProcOfLine(StartLine, vbext_pk_Proc) & vbNewLine

            StartLine = StartLine + CM.ProcCountLines _

              (CM.ProcOfLine(StartLine, vbext_pk_Proc), _

               vbext_pk_Proc)

        Loop

    Next VBC

    MsgBox Msg

End Sub

Figure 26-4 shows the result for a workbook that has nine procedures.

Figure 26-4: The message box lists all procedures in the active workbook.

This example, named list all procedures.xlsm, is available on the book's website.

Replacing a Module with an Updated Version

The example in this section demonstrates how to replace a VBA module with a different VBA module. Besides demonstrating three VBComponent methods (Export, Remove, and Import), the procedure also has a practical use. For example, you might distribute a workbook to a group of users and then later discover that a macro contains an error or needs to be updated. Because the users could have added data to the workbook, replacing the entire workbook isn't practical. The solution is to distribute another workbook that contains a macro that replaces the VBA module with an updated version stored in a file.

This example consists of two workbooks:

• UserBook.xlsm: Contains a module (Module1) that needs to be replaced.

• UpdateUserBook.xlsm: Contains VBA procedures to replace Module1 in UserBook.xlsm with a later version of Module1 (which is stored in UpdateUserBook.xlsm).

The BeginUpdate procedure follows. This macro is contained in the UpdateUserBook.xlsm workbook, which would be distributed to users of UserBook.xlsm. This procedure ensures that UserBook.xlsm is open. It then displays the message shown in Figure 26-5 to inform the user of what is about to happen.

Figure 26-5: This message box informs the user that a module will be replaced.

Sub BeginUpdate()

    Dim Filename As String

    Dim Msg As String

    Filename = “UserBook.xlsm”

    

‘   Activate workbook

    On Error Resume Next

    Workbooks(Filename).Activate

    If Err <> 0 Then

        MsgBox Filename & “ must be open.”, vbCritical

        Exit Sub

    End If

    

    

    Msg = “This macro will replace Module1 in UserBook.xlsm “

    Msg = Msg & “with an updated Module.” & vbCrLf & vbCrLf

    Msg = Msg & “Click OK to continue.”

    If MsgBox(Msg, vbInformation + vbOKCancel) = vbOK Then

        Call ReplaceModule

    Else

        MsgBox “Module not replaced,”, vbCritical

    End If

End Sub

When the user clicks OK to confirm the replacement, the ReplaceModule procedure is called. This procedure replaces Module1 in the UserBook.xlsm with the copy of Module1 in the UpdateUserBook.xlsm file:

Sub ReplaceModule()

    Dim ModuleFile As String

    Dim VBP As VBIDE.VBProject

    

‘   Export Module1 from this workbook

    ModuleFile = Application.DefaultFilePath & “ empmodxxx.bas”

    ThisWorkbook.VBProject.VBComponents(“Module1”) _

      .Export ModuleFile

      

‘   Replace Module1 in UserBook

    Set VBP = Workbooks(“UserBook.xlsm”).VBProject

    On Error GoTo ErrHandle

    With VBP.VBComponents

        .Remove VBP.VBComponents(“Module1”)

        .Import ModuleFile

    End With

    

‘   Delete the temporary module file

    Kill ModuleFile

    MsgBox “The module has been replaced.”, vbInformation

    Exit Sub

    

ErrHandle:

‘   Did an error occur?

    MsgBox “ERROR. The module may not have been replaced.”, _

      vbCritical

End Sub

This procedure performs the following actions:

1. Exports Module1 (the updated module) to a file.

The file has an unusual name to reduce the likelihood of overwriting an existing file.

2. Removes Module1 (the old module) from UserBook.xlsm, using the Remove method of the VBComponents collection.

3. Imports the module (saved in Step 1) to UserBook.xlsm.

4. Deletes the file saved in Step 1.

5. Reports the action to the user.

General error handling is used to inform the user that an error occurred.

This example is available on the book's website. The demo uses two workbooks: UserBook.xlsm and UpdateUserBook.xlsm.

Using VBA to Write VBA Code

The example in this section demonstrates how you can write VBA code that writes more VBA code. The AddButtonAndCode procedure does the following:

1. Inserts a new worksheet.

2. Adds an ActiveX CommandButton control to the worksheet.

3. Adjusts the position, size, and caption of the CommandButton.

4. Activates Sheet1 by inserting an event-handler procedure for the CommandButton named CommandButton1_Click in the sheet's code module.

The AddButtonAndCode procedure follows.

Sub AddButtonAndCode()

    Dim NewSheet As Worksheet

    Dim NewButton As OLEObject

    

‘   Add the sheet

    Set NewSheet = Sheets.Add

    

‘   Add a CommandButton

    Set NewButton = NewSheet.OLEObjects.Add _

        (“Forms.CommandButton.1”)

    With NewButton

        .Left = 4

        .Top = 4

        .Width = 100

        .Height = 24

        .Object.Caption = “Return to Sheet1”

    End With

    

‘   Add the event handler code

    Code = “Sub CommandButton1_Click()” & vbCrLf

    Code = Code & “    On Error Resume Next” & vbCrLf

    Code = Code & “    Sheets(“”Sheet1””).Activate” & vbCrLf

    Code = Code & “    If Err <> 0 Then” & vbCrLf

    Code = Code & “      MsgBox “”Cannot activate Sheet1.””” _

     & vbCrLf

    Code = Code & “    End If” & vbCrLf

    Code = Code & “End Sub”

    

    With ActiveWorkbook.VBProject. _

      VBComponents(NewSheet.Name).CodeModule

        NextLine = .CountOfLines + 1

        .InsertLines NextLine, Code

    End With

End Sub

Figure 26-6 shows the worksheet and the CommandButton control that were added by the AddButtonAndCode procedure.

Figure 26-6: This sheet, the CommandButton, and its event handler were added by using VBA.

This example is available on the book's website in the add button and code.xlsm file.

The tricky part of this procedure is inserting the VBA code in the code module for the new worksheet. The code is stored in a String variable named Code, with each instruction separated by a return and linefeed sequence. The InsertLines method adds the code to the code module for the inserted worksheet.

The NextLine variable stores the number of existing lines in the module incremented by 1. This ensures that the procedure is added to the end of the module. If you simply insert the code beginning at line 1, it causes an error if the user's system is set up to add an Option Explicit statement to each module automatically.

Figure 26-7 shows the procedure created by the AddButtonAndCode procedure in its new home in the code window.

Figure 26-7: VBA generated this event-handler procedure.

Adding Controls to a UserForm at Design Time

If you've spent any time developing UserForms, you probably know that adding and adjusting the controls so that they're aligned and sized consistently can be tedious. Even if you take full advantage of the VBE formatting commands, getting the controls to look just right can take a long time.

The UserForm shown in Figure 26-8 contains 100 CommandButtons, all of which are identical in size and positioned precisely on the form. Furthermore, each CommandButton has its own event-handler procedure. Adding these buttons manually and creating their event handlers would take lots of time. Adding them automatically at design time by using a VBA procedure takes less than a second.

Figure 26-8: A VBA procedure adds the CommandButtons on this UserForm and writes the event-handler procedures.

Design-time versus runtime UserForm manipulations

It's important to understand the distinction between manipulating UserForms or controls at design time and manipulating these objects at runtime. Runtime manipulations are apparent when the UserForm is shown, but the changes made aren't permanent. For example, you might write code that changes the Caption property of the UserForm before the form is displayed. The new caption appears when the UserForm is shown, but when you return to VBE, the UserForm displays its original caption. Runtime manipulation is common, and Part III contains many code examples that perform runtime manipulation of UserForms and controls.

Design-time manipulations, on the other hand, are permanent — just as if you made the changes manually by using the tools in VBE. Normally, you perform design-time manipulations to automate the tedious chores in designing a UserForm. to make design-time manipulations, you access the Designer object for the UserForm.

To demonstrate the difference between design-time and runtime manipulations, I developed two simple procedures that add a CommandButton to a UserForm. One procedure adds the button at runtime; the other adds it at design time.

The following RunTimeButton procedure is straightforward. When used in a general (non-UserForm) module, it adds a CommandButton to the UserForm, changes a few of the CommandButton's properties, and then displays the UserForm. The CommandButton appears on the form when the form is shown but isn't there when you view the form in VBE.

Sub RunTimeButton()

‘   Adds a button at runtime

    Dim Butn As CommandButton

    Set Butn = UserForm1.Controls.Add(“Forms.CommandButton.1”)

    With Butn

        .Caption = “Added at runtime”

        .Width = 100

        .Top = 10

    End With

    UserForm1.Show

End Sub

Following is the DesignTimeButton procedure. Unlike the preceding example, this procedure uses the Designer object, which is contained in the VBComponent object. Specifically, it uses the Add method to add the CommandButton control. Because the Designer object was used, the CommandButton is added to the UserForm just as if you did it manually in VBE.

Sub DesignTimeButton()

‘   Adds a button at design time

    Dim Butn As CommandButton

    Set Butn = ThisWorkbook.VBProject. _

      VBComponents(“UserForm1”) _

      .Designer.Controls.Add(“Forms.CommandButton.1”)

    With Butn

        .Caption = “Added at design time”

        .Width = 120

        .Top = 40

    End With

End Sub

Adding 100 CommandButtons at design time

The example in this section demonstrates how to take advantage of the Designer object to help you design a UserForm. In this case, the code adds 100 CommandButtons (perfectly spaced and aligned), sets the Caption property for each CommandButton, and also creates 100 event-handler procedures (one for each CommandButton).

Sub Add100Buttons()

  Dim UFvbc As VBComponent

  Dim CMod As CodeModule

  Dim ctl As Control

  Dim cb As CommandButton

  Dim n As Long, c As Long, r As Long

  Dim code As String

  

  Set UFvbc = ThisWorkbook.VBProject.VBComponents(“UserForm1”)

    

‘ Delete all controls, if any

  For Each ctl In UFvbc.Designer.Controls

    UFvbc.Designer.Controls.Remove ctl.Name

  Next ctl

    

‘ Delete all VBA code

  UFvbc.CodeModule.DeleteLines 1, UFvbc.CodeModule.CountOfLines

    

‘ Add 100 CommandButtons

  n = 1

  For r = 1 To 10

    For c = 1 To 10

      Set cb = UFvbc.Designer. _      

        Controls.Add(“Forms.CommandButton.1”)

      With cb

        .Width = 22

        .Height = 22

        .Left = (c * 26) - 16

        .Top = (r * 26) - 16

        .Caption = n

      End With

    

‘     Add the event handler code

      With UFvbc.CodeModule

        code = “”

        code = code & “Private Sub CommandButton” & n & _

         “_Click” & vbCr

        code = code & “Msgbox “”This is CommandButton” & n & _

          “””” & vbCr

        code = code & “End Sub”

        .InsertLines .CountOfLines + 1, code

      End With

      n = n + 1

    Next c

  Next r

End Sub

This example is available on the book's website in the add 100 buttons.xlsm file.

The Add100Buttons procedure requires a UserForm named UserForm1. You'll need to make the UserForm a bit larger than its default size so that the buttons will fit. The procedure starts by deleting all controls on the form by using the Remove method of the Controls collection and then deleting all code in the code module by using the DeleteLines method of the CodeModule object. Next, the CommandButtons are added, and the event-handler procedures are created in two For-Next loops. These event handlers are simple. Here's an example of such a procedure for CommandButton1:

Private Sub CommandButton1_Click()

  MsgBox “This is CommandButton1”

End Sub

If you'd like to show the form after adding the controls at design time, you need to add the following instruction right before the End Sub statement:

  VBA.UserForms.Add(“UserForm1”).Show

It took me quite a while to figure out how to display the UserForm. When VBA generates the 100-button UserForm, it indeed exists in VBA's memory, but it isn't officially part of the project. You need the Add method to formally enroll UserForm1 into the collection of userForms. The return value of this method is a reference to the form itself, which is why the Show method can be appended to the Add method. So, as a rule, the UserForm must be added to the UserForms collection before it can be used.

Creating UserForms Programmatically

The final topic in this chapter demonstrates how to use VBA code to create UserForms at runtime. I present two examples. One is relatively simple, and the other is more complex.

A simple runtime UserForm example

The example in this section demonstrates some useful concepts. The MakeForm procedure performs several tasks:

1. Creates a temporary UserForm in the active workbook by using the Add method of the VBComponents collection.

2. Adds a CommandButton control to the UserForm by using the Designer object.

3. Adds an event-handler procedure to the UserForm's code module (CommandButton1_Click).

This procedure, when executed, simply displays a message box and then unloads the form.

4. Displays the UserForm.

5. Deletes the UserForm.

The net result is a UserForm that's created on the fly, put to use, and then deleted. This example and the one in the next section both blur the distinction between modifying forms at design time and modifying forms at runtime. The form is created by using design-time techniques, but creating the form happens at runtime.

The following shows the MakeForm procedure:

Sub MakeForm()

    Dim TempForm As Object

    Dim NewButton As Msforms.CommandButton

    Dim Line As Integer

    

    Application.VBE.MainWindow.Visible = False

    

‘   Create the UserForm

    Set TempForm = ThisWorkbook.VBProject. _

      VBComponents.Add(3) ‘vbext_ct_MSForm

    With TempForm

        .Properties(“Caption”) = “Temporary Form”

        .Properties(“Width”) = 200

        .Properties(“Height”) = 100

    End With

    

‘   Add a CommandButton

    Set NewButton = TempForm.Designer.Controls _

      .Add(“Forms.CommandButton.1”)

    With NewButton

        .Caption = “Click Me”

        .Left = 60

        .Top = 40

    End With

    

‘   Add an event-hander sub for the CommandButton

    With TempForm.CodeModule

        Line = .CountOfLines

        .InsertLines Line + 1, “Sub CommandButton1_Click()”

        .InsertLines Line + 2, “  MsgBox “”Hello!”””

        .InsertLines Line + 3, “  Unload Me”

        .InsertLines Line + 4, “End Sub”

    End With

    

‘   Show the form

    VBA.UserForms.Add(TempForm.Name).Show

‘

‘   Delete the form

    ThisWorkbook.VBProject.VBComponents.Remove TempForm

End Sub

This example, named create userform on the fly.xlsm, is available on the book's website.

The MakeForm procedure creates and displays the simple UserForm shown in Figure 26-9.

Figure 26-9: This UserForm and its underlying code were generated on the fly.

The workbook that contains the MakeForm procedure doesn't need a reference to VBA Extensibility Library because it declares TempForm as a generic Object (not specifically as a VBComponent object). Moreover, it doesn't use any built-in constants.

Note that one of the first instructions hides the VBE window by setting its Visible property to False. This eliminates the on-screen flashing that might occur while the form and code are being generated.

A useful (but not simple) dynamic UserForm example

The example in this section is both instructive and useful. It consists of a function named GetOption that displays a UserForm. Within this UserForm are a number of OptionButtons whose captions are specified as arguments to the function. The function returns a value that corresponds to the OptionButton selected by the user.

The example in this section is available on the book's website in the getoption function.xlsm file.

The GetOption function procedure follows.

Function GetOption(OpArray, Default, Title)

    Dim TempForm As Object

    Dim NewOptionButton As Msforms.OptionButton

    Dim NewCommandButton1 As Msforms.CommandButton

    Dim NewCommandButton2 As Msforms.CommandButton

    Dim i As Integer, TopPos As Integer

    Dim MaxWidth As Long

    Dim Code As String

    

‘   Hide VBE window to prevent screen flashing

    Application.VBE.MainWindow.Visible = False

    

‘   Create the UserForm

    Set TempForm = _

      ThisWorkbook.VBProject.VBComponents.Add(3)

    TempForm.Properties(“Width”) = 800

    

‘   Add the OptionButtons

    TopPos = 4

    MaxWidth = 0 ‘Stores width of widest OptionButton

    For i = LBound(OpArray) To UBound(OpArray)

        Set NewOptionButton = TempForm.Designer.Controls. _

          Add(“Forms.OptionButton.1”)

        With NewOptionButton

            .Width = 800

            .Caption = OpArray(i)

            .Height = 15

            .Accelerator = Left(.Caption, 1)

            .Left = 8

            .Top = TopPos

            .Tag = i

            .AutoSize = True

            If Default = i Then .Value = True

            If .Width > MaxWidth Then MaxWidth = .Width

        End With

        TopPos = TopPos + 15

    Next i

    

‘   Add the Cancel button

    Set NewCommandButton1 = TempForm.Designer.Controls. _

      Add(“Forms.CommandButton.1”)

    With NewCommandButton1

        .Caption = “Cancel”

        .Cancel = True

        .Height = 18

        .Width = 44

        .Left = MaxWidth + 12

        .Top = 6

    End With

    

‘   Add the OK button

    Set NewCommandButton2 = TempForm.Designer.Controls. _

      Add(“Forms.CommandButton.1”)

    With NewCommandButton2

        .Caption = “OK”

        .Default = True

        .Height = 18

        .Width = 44

        .Left = MaxWidth + 12

        .Top = 28

    End With

    

‘   Add event-hander subs for the CommandButtons

    Code = “”

    Code = Code & “Sub CommandButton1_Click()” & vbCrLf

    Code = Code & “  GETOPTION_RET_VAL=False” & vbCrLf

    Code = Code & “  Unload Me” & vbCrLf

    Code = Code & “End Sub” & vbCrLf

    Code = Code & “Sub CommandButton2_Click()” & vbCrLf

    Code = Code & “  Dim ctl” & vbCrLf

    Code = Code & “  GETOPTION_RET_VAL = False” & vbCrLf

    Code = Code & “  For Each ctl In Me.Controls” & vbCrLf

    Code = Code & “    If TypeName(ctl) = “”OptionButton””” _

       & “ Then” & vbCrLf

    Code = Code & “      If ctl Then GETOPTION_RET_VAL = “ _

       & “ctl.Tag” & vbCrLf

    Code = Code & “    End If” & vbCrLf

    Code = Code & “  Next ctl” & vbCrLf

    Code = Code & “  Unload Me” & vbCrLf

    Code = Code & “End Sub”

    

    With TempForm.CodeModule

        .InsertLines .CountOfLines + 1, Code

    End With

    

‘   Adjust the form

    With TempForm

        .Properties(“Caption”) = Title

        .Properties(“Width”) = NewCommandButton1.Left + _

           NewCommandButton1.Width + 10

        If .Properties(“Width”) < 160 Then

            .Properties(“Width”) = 160

            NewCommandButton1.Left = 106

            NewCommandButton2.Left = 106

        End If

        .Properties(“Height”) = TopPos + 24

    End With

    

‘   Show the form

    VBA.UserForms.Add(TempForm.Name).Show

    

‘   Delete the form

    ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=TempForm

    

‘   Pass the selected option back to the calling procedure

    GetOption = GETOPTION_RET_VAL

End Function

The GetOption function is remarkably fast, considering all that's going on behind the scenes. On my system, the form appears instantaneously, just like a preconstructed UserForm. The UserForm is deleted after it has served its purpose.

Using the GetOption function

The GetOption function takes three arguments:

• OpArray: A string array that holds the items to be displayed in the form as OptionButtons.

• Default: An integer that specifies the default OptionButton selected when the UserForm is displayed. If 0, no OptionButton is selected.

• Title: The text to be displayed in the title bar of the UserForm.

How GetOption works

The GetOption function performs the following operations:

1. Hides the VBE window to prevent any flashing that could occur when the UserForm is created or the code is added.

2. Creates a UserForm and assigns it to an object variable named TempForm.

3. Adds the OptionButton controls by using the array passed to the function via the OpArray argument.

It uses the Tag property of the control to store the index number. The Tag setting of the chosen option is the value that's eventually returned by the function.

4. Adds two CommandButton controls: the OK button and the Cancel button.

5. Creates an event-handler procedure for each CommandButton.

6. Does some final cleanup work.

It adjusts the position of the CommandButtons as well as the overall size of the UserForm.

7. Displays the UserForm.

When the user clicks OK, the CommandButton1_Click procedure is executed. This procedure determines which OptionButton is selected and also assigns a number to the GETOPTION_RET_VAL variable (a Public variable).

8. Deletes the UserForm after it's dismissed.

9. Returns the value of GETOPTION_RET_VAL as the function's result.

A significant advantage of creating the UserForm on the fly is that the function is self-contained in a single module and doesn't even require a reference to VBA Extensibility Library. Therefore, you can export this module (which is named modOptionsForm) and then import it into any workbook, thus giving you access to the GetOption function.

The following procedure demonstrates how to use the GetOption function. In this case, the UserForm presents five options (contained in the Ops array).

Sub TestGetOption()

    Dim Ops(1 To 5)

    Dim UserOption

    Ops(1) = “North”

    Ops(2) = “South”

    Ops(3) = “West”

    Ops(4) = “East”

    Ops(5) = “All Regions”

    UserOption = GetOption(Ops, 5, “Select a region”)

    MsgBox Ops(UserOption)

End Sub

The UserOption variable contains the index number of the option selected by the user. If the user clicks Cancel (or presses Escape), the UserOption variable is set to False.

Note that the Accelerator property is set to the first character of each option's caption, so the user can use an Alt+letter combination to make a choice. I made no attempt to avoid duplicate Accelerator keys, so the user may need to press the key combination multiple times to make a selection. However, it's certainly possible to write code that attempts to eliminate duplicate Accelerator keys.

Figure 26-10 shows the UserForm that this function generates.

Figure 26-10: The GetOption function generated this UserForm.

The UserForm adjusts its size to accommodate the number of elements in the array passed to it. Theoretically, the UserOption function can accept an array of any size. Practically speaking, however, you'll want to limit the number of options to keep the UserForm at a reasonable size. Figure 26-11 shows how the form looks when the options contain more text.

Figure 26-11: The UserForm adjusts its height and width to accommodate the number of options and the length of the text.

GetOption event-handler code

Following are the event-handler procedures for the two CommandButtons. This code is generated in the GetOption function and placed in the code module for the temporary UserForm.

Sub CommandButton1_Click()

  GETOPTION_RET_VAL = False

  Unload Me

End Sub

    

Sub CommandButton2_Click()

  Dim ctl

  GETOPTION_RET_VAL = False

  For Each ctl In Me.Controls

    If TypeName(ctl) = “OptionButton” Then

      If ctl Then GETOPTION_RET_VAL = ctl.Tag

    End If

  Next ctl

  Unload Me

End Sub

Because the UserForm is deleted after it's used, you can't see what it looks like in VBE. If you'd like to view the UserForm, convert the following instruction to a comment by typing an apostrophe (‘) in front of it:

ThisWorkbook.VBProject.VBComponents.Remove _

VBComponent:=TempForm