Visual Basic Projects

Microsoft Visual Basic is a programming language used to extend the functionality of Microsoft Word and Excel. Coding Visual Basic projects that perform essential Excel tasks will teach you how to use Visual Basic to increase your Excel productivity.

Create the Data

This project sums the numbers in a range above the active cell, entering the sum in the active cell. To begin this project, create the data for it--the numbers to be summed. In an Excel worksheet, enter any five numbers in a column. Select the first empty cell below the column of numbers.

Now, create the Visual Basic program that will sum those numbers.

Enter the Code in the Development Environment

Enter the Visual Basic integrated development environment (IDE) by pressing Alt-F11. In the code window, enter this function:

Sub Macro1()

Dim s As String
Dim o As Range
Dim p As Range

Set o = ActiveCell.Offset(-1)
Set p = o.End(xlUp)
Set q = Range(o, p)

s = "=average(" & q.Address & ")"

ActiveCell.Formula = s

End Sub

This program code is equivalent to manually entering the following text in the cell you selected earlier: "=sum( <address> )," where <address> refers to the column of numbers you entered.

Run the macro by returning to the Excel worksheet, selecting Developer>Macros, selecting Macro1, and pressing the Run button. Your macro sums the column of numbers, entering the result in the currently activated cell. (If you don't see the Developer tab, expose it by selecting the Office button in Excel's upper left corner, choosing Excel Options, and "Show Developer tab in the Ribbon.")

Project Two: Use a Form

The second project builds on the first by allowing the user of your program a choice of functions.

Re-enter the Visual Basic IDE and select Insert>Userform. Drag the following controls onto the form from the Toolbox: a listbox and a button. Press F7 to return to the code view, and select Userform from the left drop-down box in the upper portion of the code window, and Initialize from the right drop-down box. The IDE creates the function Sub UserForm_Initialize(). In that function, enter this code, which adds to the listbox the names of the functions the user will select from:

ListBox1.AddItem "sum"
ListBox1.AddItem "average"

Use the code window's left and right drop-down boxes once again, choosing CommandButton1 from the left drop-down, and Click from the right drop-down box. The IDE creates the empty function CommandButton1_Click(). Enter the following code in that function, which will call the original macro1 you wrote with the function that the user chooses from the listbox:

Macro1 (ListBox1.Value)

Modify Macro1 to accept an argument: Right click the "Macro1" text and select Definition to navigate to the Macro1 definition. Change the line "Sub Macro1()" to read "Sub Macro1(func As String)"

Now that you've added the argument, modify Macro1 to use it: change the statement s = "=average(" & q.Address & ")" to read as follows:

s = "=" & func & "(" & q.Address & ")"

Write the Function that Loads the Form

Enter this new function below macro1:

Sub macro2()
UserForm1.Show
End Sub

Return to Excel (alt-F11) and reposition the cursor below the column of numbers. Display the userform by selecting Developer>Macros and selecting Macro2 and Run. When the form appears, choose either the sum or average function and press the button. Your program will enter the formula into the worksheet. The formula summarizes the data from the column above the active cell.

References

• The Range Object