Macros in Excel are referred to as procedures and by ywr18717

VIEWS: 10 PAGES: 4

									                              Advanced Spreadsheet Applications in Accounting
                                     An Introduction to Excel Macros

Macros in Excel are referred to as procedures and are written in the programming language called Visual Basic
for Applications (VBA). Students are not expected to be able to write VBA programs. Macros will be, for the
most part, written by using the Macro recorder, which takes keystrokes and converts them into VBA.

These notes will create a macro that makes a date heading line for an Income Statement so that it reads “For the
Year Ended Month dd, yyyy” where the date is cell referenced to a range named ReportDate on the input sheet.
The heading is to be centred across the range that was selected prior to running the macro.

Preliminary setup for this sample macro
1. For the purpose of this example, use a blank worksheet and create an input cell for the Period End Date
    starting in cell A1 as follows:              Year End Date                                    31-Dec-02

2. Name this cell ReportDate
3. In cell A5 type “Your Name Company”
4. In cell A6 type “Statement of Income”
5. Select A5 through F6  Format  Cells  Alignment  Horizontally  Centre Across Selection 
First Step
The first step in writing a macro is to plan ahead and list the exact steps that the macro is to perform. For the
„HeadingDate‟ macro these steps are:
1. Select the range over which you want the heading to be centred (A7:F7)
2. Enter the formula in the active cell that will produce the desired result:
           = “For the Year Ended ”&TEXT(ReportDate, “mmmm dd, yyyy”)
3. Center across selection. Format  Cells  Alignment  Horizontally  Centre Across Selection 
4. Select Cell A1
Recording the Macro
1. Since we want this macro to be performed on a selected range, that range must be selected prior to
    recording. For the sample macro, select the range A7:G7
2.  Tools Macro Record New Macro
    A window appears with the following:
     Macro name - Use an appropriate name that describes what the macro does. Spaces and punctuation
        cannot be used. An appropriate name for the sample macro would be HeadingDate.
     Shortcut key - If you want the macro to be executable by pressing the Ctrl key in conjunction with a
        letter, then type that letter in this box. Upper and lower case matters here, so unless you want to have to
        press the shift key to execute the macro, use lower case. An appropriate letter for the sample would be
        lower case 'h' (for heading date).
     Store macro in - There are three options here. For our purposes, we will always use 'This Workbook'.
     Description - This allows the writer of the macro to give a detailed description of the macro. A default
        description appears with the author name and date. This can be added to or modified in any way you
        like. For the sample macro, change the description to read: "Macro recorded [current date] by [your
        name] to enter a date header and centre it in the range selected."
3. Click OK
     At this time Excel begins recording the macro and a button appears that says 'Stop Recording'.
        Everything you do from here on will be recorded and translated into VBA (including mistakes made, so
        be careful) until the 'Stop Recording" button is pressed.
     If the 'Stop Recording' button does not appear, it means that it was closed in a previous macro (by
        accidentally clicking the 'x' in the upper right corner of the button). To get it back you must click
        View  Toolbars Stop Recording.
LS02/01/10                                         Page 1 of 4          a4991a26-61bd-426e-88b6-a69d3d740e09.doc
4. Decide whether relative or absolute referencing is needed. See "Relative vs. Absolute Referencing" below
   for a detailed explanation. For the sample macro, the title being entered must be relative to the range
   selected so relative referencing is needed. Make sure the 'Relative Reference' button is depressed.
5. Go through the steps that the macro is to perform:
    Type the formula desired… =“For the Year Ended ”&TEXT(ReportDate,“mmmm dd, yyyy”)
    Hit Enter
    Click Format  Cells  Alignment  Horizontally  Centre Across Selection 

6. It is always a good idea to deselect any ranges used in the above procedures and select the active cell that is
    desired upon completion of the macro. For the sample macro, the desired location of the active cell upon
    completion of the macro is cell A1. It will always be cell A1 regardless of where the active cell currently is.
    Make sure the 'Relative Reference' button is not depressed. Click to cell A1 and the macro is now
    complete.
7. It is also a good idea to deselect any ranges that may have been copied during a macro. To do this, just hit
    the escape key.
8. Click the 'Stop Recording' button (not the x in the corner)
9. Save the workbook. Erroneous macros can do serious damage to a workbook, so it is strongly
    recommended that the workbook be saved prior to testing the macro.
10. Before testing the macro, delete row 7 so the formula and format is set back to the way it was before
    recording started. Remember, this macro assumes a range is selected before execution, so select the desired
    range (A7:G7 for this example). The macro can be executed in one of the following ways.
      Tools Macro Macros Select the desired macro Run
      Alt+F8 Select the desired macro Run
      Ctrl+d (this works since the short-cut key was entered in step 1).

Other notes
1. Each macro should end by hitting the escape key and then selecting the cell or range that is desired to be
   selected upon completion of the macro.
2. Many of the VBA commands recorded by the macro recorder get added even though they are not needed.
   For example, several of the lines on the sample macro were added even though these commands were not
   executed during recording. These lines should be deleted to streamline the program.
3. To change the shortcut key or the macro description, perform the following:

    Tools Macro Macrosselect the desired macro Options

4. Alt+F11 goes back and forth between the VBA editor and Excel
5. If a macro crashes it will prompt you to either end or debug. If you choose debug, it will take you to the line
   in the macro where the problem was encountered. The macro is now in “Break Mode”. At this point there
   are two options; correct the error and click the “Run Sub” icon to finish the macro, or click the “Reset” icon
   to end the macro immediately.
6. If a macro is not working properly it can be debugged by going through it one line at a time. To do this
   perform the following, then hit F8 each time you want it to proceed to the next line:
    Tools Macro Macros select the desired macro Step Into

7. To print a macro, go to the VBA editor and click  File  Print
8. Long macros should be edited to include descriptive lines (starting with „ ) to explain what is happening.
9. Cell referencing in macros should be avoided. Use named ranges.
10. A line can be added at the beginning of a macro to lock the screen at the current position throughout the
    execution of the macro and thus speed up processing as follows: Application.ScreenUpdating = False

LS02/01/10                                         Page 2 of 4          a4991a26-61bd-426e-88b6-a69d3d740e09.doc
Exercises (Ass #7):
1. A cell contains a number in ºC . Write a macro that will select the cell to the right of the this cell, enter the
   ºF equivalent of the ºC in this cell, then return to the ºC cell. The formula is ºC*1.8+32. The short-cut key
   is Ctrl+f. Relative referencing must be used here.
2. Write a macro that will place a random lottery number from 1 to 49 in the active cell. The formula to do
   this is =RANDBETWEEN(1,49). After the formula is entered into the cell, it must be converted to a value
   by using Copy Paste Special Values. The macro is to start and stop in the active cell. The short cut
   key is Ctrl+l
3. Write a macro that will enter the current date in the active cell as a value, not a formula then go to cell A1.
   This could be done by using Copy  Paste Special  Values, but there is a more efficient way. First write
   the macro to record the date as =TODAY(), then edit the macro and change the date line so that the
   applicable formula is = Date rather than = "=TODAY()". The short cut key is Ctrl+d.
   = Date is a special word in VBA that enters the current date as a value in the applicable cell.
                                                                                Submit:
Assigning a macro to a button – open Macrosam – Sheet1                  Macros printout (in VBA FilePrintCurrent Module
To assign a macro to a button, complete the following steps:            Make sure the macros have proper names
                                                                        Make sure the macros have a proper description (which
1. View  Toolbars  Forms  click the „Button‟ button.                 includes your name and section)
2. The pointer now turns into a cross-hair when taken to the sheet. The printout must contain only these 3 macros.
   Click and drag the desired size of button in the desired location on the spreadsheet.
3. When the mouse button is released, a list of macros appears in a window. Select the desired one
   („HeadingDate‟ for the sample macro) and click OK.
4. Click inside the button while the handlebars are still on and change the button text as desired (“Heading
   Date” for the sample macro).
5. Click elsewhere to deselect the button, close the Forms Window, then click the button to test it.
To move or change the button after the handlebars are released just right-click it.
Deleting a macro
If you wish to delete a macro click Tools  Macro  Macros  select the desired macro  Delete
A macro can also be deleted by selecting and deleting it in the VBA window.
Using a message box
A message box is a line added to a VBA program that displays a message to the user. It remains on the screen
until the user hits enter. Open the workbook MacroSam.xls Sheet2 from scratch and we will add a message to
the macro that reads as follows:




The procedure to add a message box is as follows:
1. Edit the macro: Tools Macro Macros select the “DocSheetSetup” macro Edit.
2. Add a message box line to the desired location in the macro (before the line that reads “Set up the cell for
   the company name”) as follows:
    MsgBox “Prompt”,Buttons,“Title”
    Prompt is the message you want displayed in the box that gives instructions to the user.
    Buttons (optional) allows you to specify the type of box you want. Your choices are vbInformation (the
    default), vbOKonly, vbCritical and vbOKCancel. For our purposes, we will leave it blank which means use
    the default (vbinformation) since all we want to do is display a message.
    Title (optional) is what you want displayed in the top left corner of the box


LS02/01/10                                              Page 3 of 4              a4991a26-61bd-426e-88b6-a69d3d740e09.doc
   For the sample macro, use:
   MsgBox “The current sheet will be set up as a Documentation Sheet.”, , “Documentation Sheet”
3. Test it by pressing Ctrl+d
To store the answer to a message box prompt as a variable within the VBA macro and end the macro if the user
chooses to cancel add the following to the VBA commands:
       Answer = Msgbox (“Prompt”,vbOKCancel,”Title”)
       If Answer = vbCancel then
              End
       End If
Using the macro Input Box
If not already opened, open MarcoSam.xls from Scratch.
An input box is a line added to a VBA program that prompts the user for input and records that input into a cell
in the workbook. An input box will be added to the DocSheetSetup macro to prompt the user to enter their
name so that it can be recorded in the appropriate cell. It will look as follows:




The procedure to add input boxes is as follows:
1. Edit the macro: Tools Macro Macros select the “DocSheetSetup” macro Edit
2. Find the place in the macro where the input box is desired (i.e. the line that reads
   “Range(“B3”).Formula = "' Enter the name of the person who prepared this workbook.")
3. This line will be replaced with a line in the following format:
   Range("Rangename") = InputBox("Prompt", "Title", "Default")
   Prompt is the message you want displayed in the box that gives instructions to the user.
   Title (optional) is what you want displayed in the top left corner of the box.
   Default (optional) is what you want to come up as the default in the input area of the box (text and
   percentages must be in quotes, other values are not)
   Thus, to prompt the user to enter their name, replace the aforementioned line with the following:
   Range(“B3”) = InputBox(“Enter the name of the person who prepared this workbook", "Prepared
   by”,”Type your name here”)
Relative vs. absolute referencing:
If the macro is to perform procedures on a specific cell or range, then absolute referencing is required and the
button must not be depressed while recording. If however, the macro is to go to a range relative to the last cell
that was active, then relative referencing is required and the button must be depressed while recording.
A relative reference in VBA appears as follows:
ActiveCell.Offset(-2,1).Range(“A1:A3”).Select
This means select a range starting 2 rows up and 1 column to the right of the active cell.
The shape of the range selected is to be 1 column wide by 3 rows deep.
For example, if the active cell is D11, then the range selected starts 2 rows up (i.e. row 9) and 1 column to the
right (i.e. E). The shape of the range is to be 1 column by 3 rows. Thus the selected range is E9:E11

LS02/01/10                                         Page 4 of 4          a4991a26-61bd-426e-88b6-a69d3d740e09.doc

								
To top