|
Back again, for Lesson 4. In last month’s Lesson 3 we learned about
writing our own Excel functions, as well as how to incorporate Excel built-in
functions in our VBA code. This month, we will cover the exciting topic of
creating your own forms! But first, of course, we will start with…
Solution to Last Month’s Exercise
Last month I closed
with an exercise for you to try which involved writing two functions: DayOfYear,
which represents which day of the year, in integer form, a date represents,
and ZodiacSign, which returns the zodiac sign for a particular date. You may
download the complete solution here: Excel
VBA Article 3 Example Solution.xls (in zipped format).
Let’s start with the function declaration for DayOfYear. The function
will need to take a Date as a parameter, and return an integer:
Public Function DayOfYear(SomeDate As Date) As Integer
To calculate the day of the year, we’ll first determine the month and
day of SomeDate using the VBA date functions Month() and Day(). In this case,
Day represents the day of the month, not the day of the year.
Example 1a.
Public Function DayOfYear(SomeDate As Date) As Integer
Dim iMonth As Integer
Dim iDay As Integer
iMonth = Month(SomeDate)
iDay = Day(SomeDate)
We’ll start at zero, and add the appropriate number of days for each
month which precedes our date. Then we add the number of days into the current
month (i.e. the day of the month)...
Example 1b.
Public Function DayOfYear(SomeDate As Date) As Integer
Dim iMonth As Integer
Dim iDay As Integer
iMonth = Month(SomeDate)
iDay = Day(SomeDate)
DayOfYear = 0
If iMonth > 1 Then
DayOfYear = DayOfYear + 31
End If
If iMonth > 2 Then
DayOfYear = DayOfYear + 28
End If
If iMonth > 3 Then
DayOfYear = DayOfYear + 31
End If
...
If iMonth > 11 Then
DayOfYear = DayOfYear + 30
End If
' Add number of days into the current month:
DayOfYear = DayOfYear + iDay
End Function
For the sake of brevity, I left out months 4 through 10, but you get the picture.
The last thing we need to do is account for the possibility that our date occurs
in a leap year. To do this we will use the Year() function, and the Mod operator,
which calculates the remainder of one number divided by another. I won’t
insult your intelligence by explaining that a leap year is a multiple of four,
so when divided by four, the remainer is zero. We will put this if statement
inside our if iMonth > 2 clause.
Example 1c.
If iMonth > 2 Then
DayOfYear = DayOfYear + 28
' Add one extra day if this is a leap year:
If Year(SomeDate) Mod 4 = 0 Then
DayOfYear = DayOfYear + 1
End If
End If
I gave a hint last month that you would be using a series of elseif statements,
and it certainly can be done that way, but upon further review I decided
that using separate if statements, one for each month, would be the "cleaner",
if longer, way to implement this function. If you’re interested in
seeing an alternative implementation which uses elseif, see function
DayOfYear_Alt1 in the solution download (see above). An even shorter
way which involves calling another user-defined function, DaysInMonth, is
presented in function DayOfYear_Alt2.
For the function ZodiacSign, we will use elseif statements, simply figuring
out, based on the day of the year, which sign represents that day. We also
have to adjust back one day for leap years…
Example 1d.
Public Function ZodiacSign(BirthDate As Date) As String
Dim iDayofYear As Integer
iDayofYear = DayOfYear(BirthDate)
' Adjust back one day if a leap year, and later than Feb 28...
If Year(BirthDate) Mod 4 = 0 And iDayofYear > 59 Then
iDayofYear = iDayofYear - 1
End If
If iDayofYear < 20 Then
ZodiacSign = "Capricorn"
ElseIf iDayofYear < 50 Then
ZodiacSign = "Aquarius"
ElseIf iDayofYear < 81 Then
ZodiacSign = "Pisces"
ElseIf iDayofYear < 111 Then
ZodiacSign = "Aries"
ElseIf iDayofYear < 142 Then
ZodiacSign = "Taurus"
ElseIf iDayofYear < 173 Then
ZodiacSign = "Gemini"
ElseIf iDayofYear < 205 Then
ZodiacSign = "Cancer"
ElseIf iDayofYear < 236 Then
ZodiacSign = "Leo"
ElseIf iDayofYear < 267 Then
ZodiacSign = "Virgo"
ElseIf iDayofYear < 297 Then
ZodiacSign = "Libra"
ElseIf iDayofYear < 327 Then
ZodiacSign = "Scorpio"
ElseIf iDayofYear < 357 Then
ZodiacSign = "Sagittarius"
Else
ZodiacSign = "Capricorn"
End If
End Function
Creating Your Own Forms
If you want to spruce up your humble Excel spreadsheet with a handsome user
interface, creating your own “UserForms” is the only way to go.
Keep in mind that I am a programmer by trade. This means that while I can
teach you how to create user interface, I am unable, by definition, to design
good user interface.
To create a form, bring up the Visual Basic Editor from Excel. Select Insert,
UserForm from the main menu. You will be presented with a blank form with a
caption of “UserForm1”. Your form, and each of the controls you
will place on it, has a set of properties which determine how it will look
and behave. To view these properties, click on menu item View, Properties Window
(or type F4). The left column lists each property, and the right column lists
the value of each property, which you may change. The first property listed
is (Name). You will use the Name property in the code which will bring up your
form. Try changing the Caption property, and toying with some of the other
properties. You can also resize your form using the border, and small boxes,
which appear around its outside edge. (Note that the tree structure on the
left, in the Project Explorer window, now has a folder called “Forms” and
that your UserForm1 appears beneath it.)
Next, let’s drop a few controls on our form. You will do this using
the controls “Toolbox”. Click on menu item View, Toolbox. (If it
is greyed out, try clicking on your user form first. Toolbox is disabled when
focus is in the Properties window.)
Click on the capital A button to create a Label control. A label is simply
text that appears on your form. Click and drag a box on your form. The Properties
window now shows you the properties of your first control, named Label1.
In the Caption property, type “Enter Some Data Here…” or
something slightly more clever.
Below your label, add a TextBox control by clicking on the lowercase “ab” button
in the toolbox. Change the (Name) property of the TextBox to “txtData”.
Next add two CommandButton controls (small 3-D rectangle button on the right
side of the Toolbox). For the first button, change the Name property to “cmdAdd” and
the Caption property to “Add”. For the second, change the name
to “cmdClose” and the caption to “Close”.
You should now have a form which looks something like this…
Invoking Your Form
Just as each of the Excel objects (see Lesson 2) has both properties and
methods, so does your UserForm, and each of its controls. You can think of
them as additional types of Excel object. We’ve already seen the properties
of each in the Properties window in the Visual Basic Editor. Generally, you
won’t use many UserForm or control methods, but UserForm has a method
without which you would not be able to Show your form. It’s called… Show.
To bring up our sample form, we’ll try something else new… adding
a control directly to our spreadsheet. Back in Excel itself, click on View,
Toolbars, Control Toolbox. This Toolbox is quite similar to the Toolbox in
the VB Editor. Click on the button for the CommandButton control (3-D rectangle),
then place it on your spreadsheet by clicking and dragging. Like the buttons
you drew on your form, this button also has properties. To view them, click
the button in the upper right of the Toolbox, the hand holding a card (or is
that an electic bill?). Change the name to “cmdShowForm”, and the
caption to “Show My Form”. Next, double-click on the button. This
will switch you back to the VB Editor and automatically create an empty subroutine
called cmdShowForm_Click within your Sheet1 worksheet object. As you may guess,
this is the subroutine which will be called when the user clicks the button.
Within this subroutine, enter a single line of code:
Example 2.
Private Sub cmdShowForm_Click()
UserForm1.Show
End Sub
Next, switch back to Excel and click on the Design Mode button, in the upper-left
corner of the Control Toolbox. This is the button which appears to be someone
stabbing a shark with a very stubby pencil. Not a good idea. Next click on
your “Show My Form” button and… voila! There’s your
user form. You’ll notice that while you can type information into the
textbox, neither of your buttons actually does anything when you click on them.
That’s next. For now, close your form by clicking the X button in the
upper right.
Writing Code for a User Form
In the Visual Basic Editor, bring up UserForm1. Double-click on the Close
button. Just as when we double-clicked on the “Show My Form” button
in the spreadsheet, double-clicking the Close button automatically creates
a subroutine called cmdClose_Click, which will be called when the user clicks
the Close button. The VBA command to close a form is Unload. Therefore we
could close our form with the line of code “Unload UserForm1”.
However, since this code is UserForm1’s own code, we can instead type “Unload
Me”. “Me” is a VBA keyword that a form can use to refer
to itself.
Example 3.
Private Sub cmdClose_Click()
Unload Me
End Sub
That was the easy one. Next, let’s switch back to the form. I mentioned
above that the form appears under a “Forms” folder in the Project
Explorer window on the left. To view the form, you can double-click on its
name in the Project Explorer, or highlight it and click menu item View, Object
(keyboard shortcut Shift+F7). To view the code for a form, highlight it and
click menu item View, Code (keyboard shortcut F7.
Double-click the Add button to create the cmdAdd_Click subroutine. The first
thing we will do with this button is, when the user clicks it, read the contents
of the TextBox and display it in a message box. The contents of a VBA TextBox
control is accessible by using the Text property. We will declare a string
variable called sData, set it equal to the Text property of the TextBox, then
display it in a message box:
Example 4.
Private Sub cmdAdd_Click()
Dim sData As String
sData = txtData.Text
MsgBox sData
End Sub
Give it a try. Switch back to Excel, click “Show My Form”, enter
some text, then click “Add”. Try your Close button too.
Next let’s add the data to the spreadsheet instead of showing it in
a message box. We already know how to set the value of a worksheet cell. The
only question is where to put it. For this example, we will add each piece
of “data” to the end of a list in column A. We’ll declare
a variable called lRowNum, determine how many rows are already used (with ActiveSheet.UsedRange.Rows.Count),
then set lRowNum to the next row. One quirk of Excel will throw a small monkey
wrench into this plan. Even if the spreadsheet is completely empty, ActiveSheet.UsedRange.Rows.Count
will be set to 1. To get around this, we will first check to see if cell A1
is empty. If so, we will put the data there. If not, count the used rows:
Example 4.
Private Sub cmdAdd_Click()
Dim sData As String
Dim lRowNum As Long
sData = txtData.Text
' Put the data in the current worksheet:
If Cells(1, 1).Value = "" Then
lRowNum = 1
Else
lRowNum = ActiveSheet.UsedRange.Rows.Count + 1
End If
Cells(lRowNum, 1).Value = sData
End Sub
Try it again. You’ll notice two small problems. After we add the text
to the spreadsheet, it remains in the TextBox. Also, the focus is now on the
Add button. This means that to make another entry, we have to first click on
(or tab back to) the TextBox, then we have to erase the previous entry before
we can make a new one. These problems are solved easily. Just as we can use
the Text property of the TextBox to read its contents, we can use it to set
the contents. Also, each control you place on your form has a method called
SetFocus. Add two lines of code to your subroutine and try it again:
Private Sub cmdAdd_Click()
Dim sData As String
Dim lRowNum As Long
sData = txtData.Text
' Put the data in the current worksheet:
If Cells(1, 1).Value = "" Then
lRowNum = 1
Else
lRowNum = ActiveSheet.UsedRange.Rows.Count + 1
End If
Cells(lRowNum, 1).Value = sData
' Clear text box and set focus for next entry:
txtData.Text = ""
txtData.SetFocus
End Sub
Much better!
Well, I hate to cut this short, but I’m going on vacation this week!
You’ve had but a taste of user forms in Excel. If you’d like an
exercise to practice with, try making a phone list form for yourself. Store
names in column A, and phone numbers in column B. This will be similar to the
exercise we’ve just done, but in addition to adding new phone numbers,
your form should allow you to go up and down your list to view existing numbers,
change existing entries, and delete entries. Your form should have one TextBox
for name and another for telephone number. It should have Previous, Next, Add,
Delete, and Close buttons. Couple of hints:
You can write code which will be called as soon as your form comes up, by
creating a subroutine called: UserForm_Activate. This is used to set the initial
contents of the form. For example, if we wanted the form we created above to
initially display the first string in our list, we would add:
Private Sub UserForm_Activate()
txtData.Text = ActiveSheet.Cells(1, 1).Value
End Sub
You will need to keep track of which row number the form is currently displaying.
The easiest way to do this will be to declare a variable within the code of
your form, but not within any of its subroutines. At the top of the code window
for your form, add the line:
Dim lCurrentRow As Long
You can then use this variable in any of your subroutines. You will want to
set it to 1 in your UserForm_Activate subroutine, then change its value accordingly
each time the user clicks Previous or Next.
Your Add button will not work quite the same as the one we created in the
example above. Instead, your Add button should set lCurrentRow to the first
unused row in your spreadsheet, and clear both TextBox controls. You will then
save whatever the user has entered to the spreadsheet when the user clicks
either Previous, Next, or Close.
If you want a bit more of a challenge, try adding a dropdown list box (“ComboBox” control)
to your form, and filling it with all existing names. When the user selects
an item from the list, set the TextBox controls to the selected person’s
name and telephone number. You can also try adding more TextBox controls to
your form to turn the phone list into an address book.
I’ve attached the example we did above, for your enjoyment: Excel
VBA Lesson 4 Example.xls. Until we meet again…
Need further help getting your Excel VBA code right? Join our free Excel VBA
support group! See this link for details: http://groups.yahoo.com/group/ExcelVBA/ .
|
