VBA Dialogue Box for Excel

This is not my usual sort of blog post - and of the very few people who occasionaly visit, probably all would be advised to move on. This is me sharing a technique I've developed for creating user forms in VBA - which I typically use to create Excel tools.

The problem I'm trying to solve is - how to incorporate user input into a function.

For example, let's imagine you've got a spreadsheet with a table of data containing three columns: names, ages, and addresses. You want to create a list of all the people older than age n. Not hard to do by sorting the data by age, copying the group meeting the criteria, and pasting that subset of data to another sheet. But what if your data gets updated regularly and you want to recreate this list each time? And what if you want a colleague of questionable competence to be able to do it while you're off saving the world? And what if you'd like to be able to change the value of n each time you run the process? Chances are you'll want a macro, or a function, or a program, or a script, or whatever you choose to call it, to automate this task.

If you're competent with VBA and know something of Excel's object libraries, you'll know how to create a loop to iterate through the rows in your data list and copy the data to another location where [age] > n. If you don't know how to do this, your competence with Google should lead in the
right direction. But how do you tell the function where to find n?

If you're using Excel, the simple answer is to collect the data in cells, and use sheet.Cells(row, col) to read the input. This works, but one of the reasons I use Excel a lot is its flexibility - but what you typically want with functions is control. What happens when someone inserts a new row? How does VBA know where to look for input data now? (I actually have another solution to this problem, which I might post later)

Most programs use some form of dialogue box to collect user input -at least the ones I write do. And here's how it's done ...

STEP 1 - CREATE A FORM
Insert a user form. At a minimum, you'll need:
1 label
1 input control (e.g. textbox)
2 command buttons

Here's mine ...



I've named the controls:
Text Box - txtAge
"CANCEL" Button - cmdCancel
"DONE" Button - cmdDone
(I don't care what the label is called, because I won't be referring to it in code)

STEP 2 - CREATE SOME OBJECT CLASSES
The tricky bit is passing information from the form back to the program - or at least to do it in a way that doesn't make you feel dirty and corrupted. Sure, you could write to global variables, but the form is an object and shouldn't need to know about global variables.

The way I control communication between the form and the calling function is by passing objects to the form, and having the form write to those objects.

So, what is the information we wish to pass? In this case, the program needs to know the value that is entered into the textbox, and which button was pressed to close the form.

You could create one object class with attributes for each piece of information you want to pass. I prefer to use an "OK" object with a single Boolean value to deal with the Done/Cancel information, because I can reuse this class for any other similar forms I create.

So, insert a class module or two. Here's the code from mine:


    OK Class

    Private mblnOK As Boolean

    Public Property Let Value(bln As Boolean)
        mblnOK = bln
    End Property

    Public Property Get Value() As Boolean
        Value = mblnOK
    End Property


    Age Class

    Private mintAge As Integer

    Public Property Let Value(intVal As Integer)
        mintAge = intVal
    End Property

    Public Property Get Value() As Integer
        Value = mintAge
    End Property


STEP 3 - ADD SOME PROPERTIES TO THE FORM
Now add some properties to the user form based on those object classes. Note that the Property Set functions specify that the objects are passed "ByRef". I'm pretty sure this is the default method of passing objects anyway - but it doesn't hurt to be sure. Here's the code:


    Private mAge As VBAProject.Age
    Private mOK As VBAProject.OK

    Public Property Set Age(ByRef obj As VBAProject.Age)
        Set mCalls = obj
    End Property

    Public Property Get Age() As VBAProject.Age
        Set Age = mAge
    End Property

    Public Property Set OK(ByRef OK As VBAProject.OK)
        Set mOK = OK
    End Property

    Public Property Get OK() As VBAProject.OK
        Set OK = mblnOK
    End Property


STEP 4 - LET THE FORM UPDATE THE OBJECTS
Use the Click event of the DONE button to update the object values. (The CANCEL button is a simple Unload Me)


    Private Sub cmdDone_Click()
        mAge.Value = txtAge
        mOK.Value = True
        Unload Me
    End Sub


STEP 5 - CREATE THE CODE TO LAUNCH THE FORM
With all the pieces in place, now it's simply a matter of creating some instances of the objects, loading an instance of the form, passing the objects to the form, and displaying the form. Note the use of vbModal, which tells VBA to halt processing this function until the form has been closed. I also use a Cleanup: subroutine just to be tidy. I'm pretty sure VBA is smart enough to release resources once my objects go out of scope, but it doesn't hurt to be explicit in tidying up.


    Public Sub Launch()

        Dim frm As frmAge
        Dim objAge As VBAProject.Age
        Dim objOK As VBAProject.OK

        Set frm = New frmAge
        Load frm
        Set objAge = New VBAProject.Age
        Set frm.Calls = objCalls
        Set objOK = New VBAProject.OK
        objOK.Value = False
        Set frm.OK = objOK
        frm.Show vbModal
        If Not objOK.Value Then GoTo Cleanup

            Do some stuff with the age value
            ...
            ...

    Cleanup:
        Set objOK = Nothing
        Set objCalls = Nothing
        Set frm = Nothing

    End Sub

And there you have it - my somewhat long winded but tidy implementation of a dialogue box in VBA.