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.
