VBA enables the user of an Excel file to easily perform tasks that might otherwise be repetitive or quite complex, based on very simple and user-friendly instructions as defined by the creator of the file. VBA is a programming language used by Excel (and other Windows applications) to enable file creators to easily create functions and Excel short cuts that are readily implemented by file users. Many of these programmed instruction sets are called macros.

        A macro is a set of instructions created for Excel or some other Windows application. Macros are extremely useful in making Excel files more user-friendly. One begins to create a macro in an open Excel workbook by selecting the View tab in the ribbon at the top of the screen, then the Macro menu. When the dialogue box appears, four boxes should be filled in:

    1.    Select a Macro name (preferably a name related to the function to be performed by the macro)
    2.    Select a Shortcut key (This is for older versions of Excel. When this key, say a letter of the alphabet, is simultaneously depressed with the Ctrl key, the macro will be invoked)
    3.    Store the macro in (to begin with, keep then in This Workbook)
    4.    Description of the macro's task can be entered (When this seems appropriate; not necessary right away.)

        When this is complete, click the Ok tab and the indication Recording will appear to the right of the Ready indication in the lower left corner of the screen. This, along with the Stop Recording toolbar appearing elsewhere on the screen will indicate that a Visual Basic module sheet will be recorded by Excel. While the spreadsheet is in this mode, all keystrokes (including mouse cursor actions) will be recorded and included in the Visual Basic module sheet. This continues until the Stop Recording button is clicked.

    There are a number of simple macros described below. The first describes the creation of the simplest macro.

Create a Sample Macro

        Now, we will create a very simple macro to move entries on aan Excel 2007 worksheet from one range to another. Suppose we wish to invoke a macro that will cut entries from Cells A8 and A9 and paste them to Cells B8 and B9. First, enter values into Cells A8 and A9. To create the macro to move entries, we select the View tab in the ribbon, then the Macro menu and then the Record New Macro bar. When the dialogue box appears (it might be useful for you to create this memo while reading these instructions), we enter into the boxes:

    1.    Move
    2.    a
    3.    This Workbook
    4.    Moves entries from Cells A8 and A9 to B8 and B9

        We enter OK to exit the dialogue box. Now each of our keystrokes will be recorded into our Visual Basic module sheet. We must be very careful what we do from this point on. Begin by highlighting Cells A8 and A9, select the Edit menu from the menu bar at the top of the screen, then Cut, then click Cell B8, then select the Edit menu from the menu bar at the top of the screen, then Paste. To exit the recording mode, either click the Stop Recording button or select the View menu in the menu bar at the top of the screen, then the Macro menu and then the Stop recording bar. This should have moved the cell entries.

        To move cell entries with the newly created macro (run the macro), enter new values into Cells A8 and A9. Then simultaneously depress the Ctrl key and the “a” key to invoke the macro. The cell entries should replace those in Cells B8 and B9. To see the actual code for your macro, select the Tools menu in the menu bar at the top of the screen, then the Macros menu and then select the Move macro and click Edit. Lines preceded by single quotation marks are comments (they are
for explanation purposes only); indented lines are the macro instructions. The newly created macro appears as follows in the Edit screen:

Sub Move()
'
' Move Macro
' Moves entries from Cells A8 and A9 to B8 and B9
'
' Keyboard Shortcut: Ctrl+a
'
    Range("A8:A9").Select
    Selection.Cut
    Range("B8").Select
    ActiveSheet.Paste
End Sub

While this code is not exactly what we typed, it is the VBA code for accomplishing our purpose. The code can be edited directly from this edit screen. The macro-enabled file Move.xlsm was created by using these instructions and can be downloaded to view with these instructions. There isn't much to this file; the macro can be viewed from the View - Macros - View Macros - Move series of clicks. The Move.xlsm file might not load or run if your computer has firewalls that prevent it from doing so.

Attach a Macro Button

We invoked our macro above by simultaneously pressing the Ctrl and a keys. Here, we will create a special button in Move.xlsm to invoke our “move” macro. First, check to see if you have the Developer Tab available in your menu bar. Many Excel spreadsheets will not be set up with this, and th Developer tab will need to be added. Do so as follows:

    1.Click the Microsoft Office Button in the top left corner of your screen, and then click Excel Options.
    2.Then click Popular "Show the Developer Tab in the Ribbon."

Now, you can use the Developer Tab to add buttons:

    1. Click on the Developer Tab.
    2. On the Developer Tab, in the Controls group, click Insert, and then under Form Controls, click the Button.
    3.Click the worksheet location where you want the upper-left corner of the button to appear. You can move or otherwise adjust the button later as you see fit.
    4. In the Dialog Box, you can assign to the button a macro (e.g., "Move").
    5. To specify the control properties of the button, right-click the button, and then click Format Control. You can add a new name for the button here, etc.
    6. After you left click inside the spreadsheet, you will activate the button. This means that when you left click the mouse on the button, you will invoke the macro.

The macro-enabled spreadsheet file Button.xlsm is the demonstration for this button.

A Slightly More Complex Program: Inputting and Adding Numbers

        The following instructions apply to Excel 2003, and the spreadsheet files do not appear here. However, the code should still work on Excel 2007.
        The next program is intended to demonstrate the use of macros for creating input and output screens and to demonstrate a few other Excel tools. It would be useful to examine the spreadsheet on which this description is based, VBA.xls . The file initiates from the “Start Program Screen,” from which the user presses a button to exit to the “Input Screen.” This file essentially allows the user to input three numbers to add and then provides a result. The purpose of the file is to demonstrate the construction of an input screen, an output screen, buttons, macros for each of these and several other utilities. The macro takes the user to the input screen. This macro is as follows:

 Sub GoToInputScreen()
 '
 ' GoToInputScreen Macro
 ' This macro moves the cursor to Cell A1 of the Input Screen.
 '
 ' Keyboard Shortcut: Ctrl+a
 '
     Sheets("Input Screen").Select
     Range("A1").Select
 End Sub

Notice that each procedure will begin with “Sub . . .” and ends with “End Sub.” It essentially switches the screen using “Sheets("Input Screen").Select” and places the cursor with Range("A1").Select.

        From here, the user enters the number of values to be added. Notice Cells N1 and N2 and their formula/logical entries. Then, the button is pushed to activate the second macro which enables the user to input values to add:

 Sub Input1()
 '
 ' Input1 Macro
 ' This macro inputs the first value to be added.
 '
 ' Keyboard Shortcut: Ctrl+b
 '
     If Range("N2") = 0 Then
     Range("B177").Select
     Else
 

     Range("A44").Select
     End If
 End Sub

The conditional statement “If Range("N2") = 0 Then” is used to ensure that the user enters either 2 or three values to add. The statement “Range("B177").Select” directs the user to an error statement and forces him to re-enter his inputs. If the input is in the allowable range, the statement “Range("A44").Select” is invoked and the user inputs his first value to add. Note that the conditional procedure has three statements: “If Range("N2") = 0 Then,” “Else” and “End If.”

        If the number of values to add had been outside of the allowable range, the user would have been given the error statement in B177. Pressing the button here would invoke the following macro directing the user back to the first input cell:

 Sub InputAgain()
 '
 ' InputAgain Macro
 ' Re-enter the number of values.
 '
 ' Keyboard Shortcut: Ctrl+c
 '
     Range("A1").Select
 End Sub

There is only one line of instruction here.

        The fourth macro enables the user to enter his second value to add. This macro reads as follows:

 Sub Input2()
 '
 ' Input2 Macro
 ' Input the first value to add.
 '
 ' Keyboard Shortcut: Ctrl+d
 '
      Range("A85").Select
 End Sub

Again, there is only a single line of instruction here.

        The next macro, Input3, enables the user to enter the third value to add. The user will then either be directed to the Output Screen or to the entry cell for the third value to add, depending on the number of values originally entered in Cell A1. This macro reads as follows:

 Sub Input3()
 '
 ' Input3 Macro
 ' Input second value to add.
 '

 '
      If Range("N2") = 2 Then
      Sheets("Output Screen").Select
      Range("A1").Select

      Else
      Range("A126").Select
      End If
 End Sub

Again, there is a three part conditional statement which depends on the value of Cell N2.

        After the third value (if needed) is entered, the user is directed to the Output Screen:

 Sub Result()
 '
 ' Result Macro
 ' Goes to the result page.
 '
 ' Keyboard Shortcut: Ctrl+f
 '
     Sheets("Output Screen").Select
 End Sub

Now, the program run is complete. The user can repeat the program with the Repeat button which invokes the following macro:

 Sub Repeat()
 '
 ' Repeat Macro
 ' Repeat the program to add values.
 '
 ' Keyboard Shortcut: Ctrl+g
 '
     Sheets("Input Screen").Select
     ActiveWindow.ScrollRow = 1
     Range("A1").Select
 End Sub

Alternatively, the user can save his work and exit:

Sub ShutDown()
'
' ShutDown Macro
' Save work and shut the program down.
'
' Keyboard Shortcut: Ctrl+h
'
    ActiveWorkbook.Save
End Sub

Using a Loop to Compute n!
      The spreadsheet   LoopForN!.xls    is intended to demonstrate the construction of a simple loop for repetitive operations. Essentially, this macro uses a loop to compute n! = n * (n-1) * (n-2) * . . . 2 * 1:

Sub Loop1()

'
' Loop Macro
' This macro runs a simple loop and computes n!
'
' Keyboard Shortcut: Ctrl+Shift+T
'
    Dim n As Double
    Dim i As Integer
    Dim c As Double

    n = Range("B1")
    c = 1

    For i = 2 To n
        c = c * i
    Next i

    Range("b2").Select
    Selection.ClearContents
    Range("b2").Formula = c

End Sub
 
 
 

Teaching and Courses

Spreadsheets