Microsoft Excel 2007 401 Advanced Workshop

Streamlining Your Work with Macros Workshop Manual Presented by David Newbold & Jennifer Tran

03/01/11

UC San Diego Libraries 1 

 

Introduc on to Excel Macros  Visual Basic for Applica ons (VBA)     

    Macro Defini on:   A macro is a sequence of instruc ons that automates some aspect of Excel   so that you can work more efficiently and with fewer errors.       The example at the right inserts and formats today’s date.    Program Language:  In Microso  Office, macros are composed using a programming tool     called Visual Basic for Applica ons, or VBA for short.            There are Two Types of Macros:    VBA Sub Procedures  A Sub procedure is a group of  VBA statements that performs an ac on with Excel. It is a series of commands that either the user   or another macro can execute.    VBA Func on Procedures  ♦ A VBA Func on procedure is a group of VBA statements that performs a calcula on and returns a single value.  ♦ Can be executed by another VBA procedure or used in worksheet formulas  ♦ Func on procedures can only be created manually 

2 

 

Why Use Macros? •

They automate repetitive tasks, thus saving time and improving efficiency.

•

Make difficult tasks easier

•

Provide a means of extending the usefulness of applications

•

Can assist people in completing forms

Some Applications for Macros •

Insert boiler plate text

•

Automate a procedure you perform frequently

•

Create custom commands

•

Create a simplified “front end” for users who do not know much about Excel

•

Expand formulas and worksheet functions



3 

 

Developer Tab Because Office 2010 does not display the Developer tab by default, you must enable it using the following procedure: To enable the Developer tab •

On the File tab, choose Options to open the Excel Options dialog box.

•

Click Customize Ribbon on the left side of the dialog box.

•

Under Choose commands from on the left side of the dialog box, select Popular Commands.

•

Under Customize the ribbon on the right side of the dialog box, select Main tabs, and then select the Developer check box.

•

Click OK.

4 

 

The Developer Tab and Its Func ons  Visual Basic:    

 

 

Opens the Visual Basic Editor 

Macros:  

 

 

Opens macro dialogue box containing list of macros 

 

 

Ini ates the macro recording process 

 

Records Macro:  

Absolute/Rela ve References:  

Causes macros being recorded to func on in either absolute or rela ve mode 

Macro Security:  

 

 

Ac vates “Trust Center” in which various levels of security can be selected 

Add‐Ins:  

 

 

 

Adds a range of tools and func ons 

COM Add‐Ins:   

 

 

Allows for interac on with other applica ons 

Insert:   

 

 

 

Inserts bu ons, lists, boxes, labels, etc, for crea ng forms and Ac ve X controls 

Design Mode:   

 

 

Used for designing  forms and media 

Proper es:  

 

 

 

Lists of pre‐set proper es for use in VBA code 

View Code:    

 

 

Displays code in VBA Editor 

XML:    

 

 

Not per nent to this class 

 

 

Stores informa on about the ac ve document 

 

Document Panel:  

5 

 

How to create VBA Sub Procedure Macros by Doing    • • • • • • • • •

Select “Developer” Tab  Click on “Record Macro”  Record Macro Dialogue Box Opens  Enter a name (no Spaces or non‐alpha‐numeric characters)  Enter a Shortcut Key (le er from A‐Z)  Store Macro in:   Descrip on of macro  Perform the func ons that you want the   macro to perform (see right sequence)  Click on “Stop Recording”   

Steps in Crea ng Macro InsertFmtDate  Here are the Steps we used to insert and format the cur‐ rent date and the corresponding code:  1.  Select right adjacent cell  2.  Insert current date using Today() formula from preset  formulas in Date & Time category, Formula ribbon  3.  Center text in cell  4.  Change background color to blue  5.  Change font to Ariel Rounded MT Bold 

6 

 

Steps for Assigning/Edi ng Your Macro Shortcut Key  When you record a macro, Excel will automa cally give you the op on to assign a  shortcut key for your macro.  Once you’ve assigned your shortcut key, you will see in your  VBA code a comment that tells you what the assigned shortcut key is (pictured in the im‐ age on the bo om right).  Changing the keyboard shortcut in the comment code will not  make any actual change to the ini ally assigned keyboard shortcut.  Also, when crea ng your own macro with VBA code, this shortcut key assignment dialog  box will not pop‐up and give you the op on to assign a shortcut key.      If there is ever a  me you need to assign or edit your shortcut key, you will need to do  the following steps:  1.  Go to the Developer Tab  2.  Click the Macro Bu on  3.  Select the name of the macro you either want to 1. assign a shortcut key to, or 2. edit  its exis ng shortcut key  4.  Click the Op ons bu on in the Macro dialog box  5.  Assign or edit your shortcut key in the Macro Op ons dialog box 

7 

 

Examining & Tes ng Your Macro  From the Developer tab select View Macro, and the box pictured below will appear 

  Here are the Steps we used to insert and format  the current date and the corresponding code:    Select right adjacent cell  Insert current date using Today() formula from  preset formulas in Date & Time category, For‐ mula ribbon  Center text in cell  Change background color to Yellow     Change font to Ariel Rounded MT Bold        Change color of font to Light Blue  End Macro     

Tes ng  To test your macro, use the keyboard shortcut  you specified, in this case: Ctrl+Shi +G. If the macro fails to run, re‐examine your macro in order to find the error in  the code. 

8 

 

Absolute vs Rela ve Recording  You can either record a macro in absolute mode, meaning only the cells you s pulate  will be included in the macro, or you can record you macro in rela ve mode, which  means that wherever the star ng cell happens to be is where the macro will begin.  Absolute is the default. To switch from Absolute to Rela ve, click on the “Use Abso‐ lute” icon in the Code sec on of the Developer ribbon. Click on it again to return to  Absolute mode. 

Assigning a Macro to a Bu on  You can ini ate a macro using a bu on and other types of graphics  that are mainly designed as form controls. To assign a macro to a  bu on:  •

From the Insert bu on, under Form Controls, select the bu on  

•

Draw the bu on where you want it situated 

•

The Assign Macro dialogue box appears 

•

Select the macro you want the bu on to ini ate 

•

Click OK 

•

Revise the name on the top of the bu on and modify font  proper es as necessary 

•

De‐select and test 

You can also record a new macro from the Assign Macro bu on   dialogue box. 

9 

 

Saving Workbooks that Contain Macros  •

Workbooks that contain one or more macros need to be  saved as XLSM files rather than the default XLSX. If you save  the file in the usual format, the macro will not be saved. 

•

When first saving a file that contains a macro, Excel will pro‐ vide a message advising you to save the file as an XLSM. 

•

Click on the Save as type list and select Excel Macro‐Enabled  Template, then press OK. 

Storing Macros in your Personal Macro Workbook  •

When you want to use a macro in more than one workbook, you  can store it in your Personal Macro Workbook.  

•

When you press the Record Macro bu on and the Record Macro  dialogue box appears, under Store Macro In, select Personal Mac‐ ro Workbook. 

10 

 

Example 1  Insert and Format UCSD Name Text   

Select Range=A1‐E1  Select = Merge & Center  Select Typeface =Ariel  Select Font Size = 16 pt  Select Font Color = White  Select Background Color= Blue  Insert Text = University of California ~ San Diego 

11 

 

Find Out What’s Going On in Your Code Using the                 DEBUGGING MODE to Step Through Each Line  You can ac vate the debug mode by:     

1. Going to the DEBUG menu in the VBA Editor Window and clicking Step Into or  2. Hi ng the F8 func on key on your keyboard 

Once you step‐into your code, you will see the line that will be executed, highlighted in yellow (as shown  below); when you hit your F8 hotkey, or go to Debug and Step Into again, you will see this line of code   execute, and the yellow highlight will move to your next line of code.        When you step through your code, you will quickly realize that a lot of the code you have recorded using  your Macro Recorder is scrap code that does nothing, and contributes nothing to your macro’s end result.     When you find these types of codes, you can delete them by highligh ng the line and hi ng the ‘delete’  or ‘backspace’ bu on on your keyboard.    Once you delete your code, you will see a pop‐up such as the image  to the right.  Click OK.  Once you click OK, you will need to restart  your debugging process again by hi ng F8 or by going to Debug >  Step Into.    Tip:  If you have your Excel window open on the le ‐half of your monitor, and your VBA Editor window  open on the right‐half of your monitor, you can view what each line of code is doing, and find out also  which lines of code do nothing.  12 

 

Example 2  Insert and Format Current Date  Select Current Date Formula = TODAY()  Select Cell = Center text  Select Font = “Tekton Pro Ext”  Select Font Size = 11 pt  Select Background color = Burgandy  Select Font Color = White  Select Font = Bold  Select border = Medium  Move cursor = 2 cells 

13 

 

Example 3   Change Number to Date 

Select cell in which number is located  Right click and select Format  Select Date Format  Format text and background 

14 

 

Example 4  Eliminate Decimal Places  •

Select Number with decimal 

•

Right click on number 

•

Select Format 

•

Select Number 

•

Set number of decimal places to 0 

•

Press OK 

•

Center and bold number 

15 

 

Example 5  Change Decimal to a Percentage  •

Select decimal 

•

Right click on decimal 

•

Select Format 

•

Select percentage 

•

Press OK 

•

Center, color and bold percentage 

16 

 

VBA Func on Procedures  VBA Func on Procedures (VBA Func ons)  • A Func on procedure is a series of Visual Basic statements enclosed by the  words “Func on” and “End Func on” (see below).  • Difference between Sub and Func on Procedures:  • Func on can only return a single value (numerical or text string)  • Func on starts with the word Func on instead of Sub  • Func on procedures can only be created manually  • Func ons can be executed by   • The Insert Func on command  • Another VBA procedure or   • Being included in a worksheet formula 

•

Func on procedures do not appear in macro  list, but may  be found in the Insert Func on list under User Defined  Macros 

          •

The value returned by a func on is always assigned to the  func on’s name 

In the example above, a VBA Func on Procedure called “NumSign” has been coded and will insert the  word “zero” when num=0, “posi ve” when num is a posi ve number, and the word “nega ve” when  num = a nega ve number is entered.  The macro is executed when the “Insert Func on” command in  the Formula ribbon is invoked and a number is entered, as above.  17 

 

VBA Func on Procedures  Advantages of Func on Procedures  • You can greatly simplify your formulas by making them shorter; shorter formulas are more  readable and easier to work with  • You can write func ons to perform opera ons that would otherwise be impossible.  How to write a Func onal Procedure (Use NumSign as example)  1.  Ac vate the VB Editor (Developer > Visual Basic)  2.  Select the Module or Sheet # in the Project window.  3.  Choose Insert ➪ Module to insert a VBA module. Or you can  use an exis ng code module. However, it must be a standard  VBA module.  4.  Enter the keyword Func on followed by the func on’s name  and a list of the arguments (if any) in parentheses. If the func‐ on doesn’t use an argument, the VB Editor adds a set of  empty parentheses.  5.  Insert an apostrophe and then a descrip on of the macro  6.  Insert the VBA code that performs the work — and make  sure that the variable corresponding to the func on’s name  has the appropriate value when the func on ends. This is the  value that the func on returns. 

18 

 

Reading Your VBA Code  The basic structure of a VBA code is: 

Object.Method    

 

or           Object.Method Parameter 

Object: a cell, range, row, column, or an en re Excel workbook; objects can also be collec‐ ons of cells, rows, columns, or ranges  Method:  the ac on you want to perform on your object 

TIP:  If you do not know whether or not  to use a method or a property code, you  can use your macro recorder to record a  small macro and figure out that way  which one you will need, method or  property. 

Parameter:  specifies how to perform the method on the object; each method has a different  set of parameters  Another way to think about the Object.Method structure is to think about English grammar—Examine the table below: 

VBA CODE (Object.Method Parameter) 

PLAIN ENGLISH (Noun.Verb Adverb) 

Range("A1:E1").Select 

Ball(“Basketball”).Kick 

Range("A1:E1").Copy  Des na on:=Range(“F14:G14”) 

Ball(“Basketball”).Kick Direc on:=Le , Force:=Hard 

VBA Commands You Will See  Below are some basic VBA commands.  Take  me to review these commands so that you may get a be er understanding of VBA code.  As you  review these commands, remember to keep in mind the Object.Method or Object.Method Parameter structure of VBA coding.  If you want a  be er understanding of what each method or parameter does, you can click func on key F1 (explained in detail on page 20).  How it’s wri en in code: 

What it does: 

•

Ac veCell.Range("A1:E1").Select 

•

Highlights (or selects) the range from A1 at the upper le ‐hand corner through E1 at the low‐ er bo om right‐hand corner of the range 

•

Selec on.Font.Bold = True 

•

Changes the font property of the selected cell, making it bold 

•

Ac veCell.FormulaR1C1 = "=SUM(R2C:R[‐1]C)" 

•

Sum all values beginning in row two, above, and in the same column as, the ac ve cell  

FinalRow = Cells(Rows.Count,1).End(xlUp).Row 

•

Count the number of rows that exist in column A (or 1) and store that number in the           FinalRow variable 

 

 

•

19 

 

Understanding VBA Code through Prac ce  Pressing F1 on your keyboard opens the Excel Help screen (pictured in the image to the  right).  In VBA, this help screen will detail all the informa on you will need to be er un‐ derstand your code.  Excel’s Help screen will tell you what type of code it is (i.e. method,  func on, parameter, or property), and display the help informa on accordingly.    The help window will provide the code’s syntax, walk you through all of it’s available  arguments, and give you several sample codes at the bo om of the window, under the  Example heading. 

How to Access Excel’s Help Window:  •

Open the VBA Editor  ⇒ To open the VBA Editor, go to the Developer Tab > Macro 

Bu on > Select the name of your Macro > Edit  •

Place your cursor inside the code you want more informa on on  (view image to the right) 

•

Press F1 on your keyboard 

Prac ce Using F1 with These Codes:  Just type these codes into the VBA Editor, place your cursor inside the method, func on, parameter, or property commands, and hit F1 to  analyze their data in the Excel Help window.       Workbooks.OpenText  Range(“B4”).End  Basic  **The help screen does not display  For i = 2 To 10  For Each cell in Range(“A1”).CurrentRegion.Resize(,1)  Loops  any informa on for VBA objects.  Next i  Next cell 

IF 

If cell.Value = “Total” Then            cell.Resize(1,8).Font.Bold = True  End If 

If Ac veCell.Value = “Fruit” Then          Ac veCell.Resize(1,3).Font.ColorIndex=3  Else If Ac veCell.Value = “Vegetable” Then          Ac veCell.Resize(1,3).Font.ColorIndex=50  End If  20 

 

 

Crea ng and Reading Comments in VBA Code  When crea ng code, it is o en a good idea to include comments next to, above,  or below the code you have just created.  Making this common prac ce will:  1.  Help you review your code at a later date and understand what is happening  so that you may tweak it if needed  2.  Help the colleagues you send your macro to understand what your macro is  doing, and allow them to locate a specific code line to change, if desired    Comments are created by first typing in the apostrophe key.  For example:  ‘This is a comment in VBA Code  This is not a comment in VBA Code 

Once this is done, anything you type‐in a er the apostrophe will be a comment.   Once you navigate away from the line, it will turn green (pictured to the right).   Any green text in the VBA Editor window are comments.    Use the comments in the Macros Forma ngMacro_with_Comments() and   TotalValuesGreen_with_Comments() to read and understand what ac on each  line of code is performing.   

21 

 

Prac cing with VBA – TotalValuesGreen Sub Procedure  Before you can make changes to your code, you need to open the VBA Editor Window.  The steps to open the  VBA Editor Window are described on page 7.  Suggested Change

Original Code: 

Change in Code

 

1

Change the ColorIndex to something of your choosing

 Cells(i, 1).Resize(1, 6).Interior.ColorIndex = 32

2

Change the column number in the IF command to 4 and the test value from 0 to 100

If Cells(i, 4).Value = 100 Then

3

Change the values of the Resize command to 2 (row) and 8 (column)

 Cells(i, 1).Resize(2, 8).Interior.ColorIndex = 4

4

Add or delete rows of information to get a clearer understanding of the FinalRow command

Sub TotalValuesGreen()      FinalRow = Cells(Rows.Count, 1).End(xlUp).Row      For i = 2 To FinalRow          If Cells(i, 5).Value = 0 Then              Cells(i, 1).Resize(1, 6).Interior.ColorIndex = 4          End If      Next i  End Sub 

The 56 Color Codes of VBA  

22 

 

Prac cing with VBA ‐ Forma ngMacro Sub Procedure  Before you can make changes to your code, you need to open the VBA Editor Window.  The steps to open the  VBA Editor Window are described on page 7.  Suggested Change

Original Code: 

Change in Code

 

1

Make the value ‘Total’ appear in cell D16 instead of A14, and change ‘Total’ to ‘SUM ALL’

Range(“D16").Select Selec on.FormulaR1C1 = “SUM ALL"

2

Bold rows 2 through 11 instead of rows 1 and 14

Rows(“2:11").Font.Bold = True Rows("14:14").Font.Bold = True

3

Create a code that count’s the values in column 2 and places that value in cell B14

Range(“B14").Select Selec on.FormulaR1C1 = "=COUNT(R[‐12]C:R[‐1]C)"

4

Make the For Next Loop run through row 20 and search for the value 0 and change the color of the highlighted rows to RED (refer to page 22 for a color index)

 For i = 2 To 20         If Cells(i, 6).Value = 0 Then             Cells(i, 8).Value = "Service Revenue"             Cells(i, 1).Resize(1, 8).Interior.ColorIndex = 3         End If     Next i

 Sub Forma ngMacro()      Range("A1").Select      Selec on.End(xlDown).Select      Range("A14").Select      Selec on.FormulaR1C1 = "'Total"      Range("E14").Select      Selec on.FormulaR1C1 = "=SUM(R[‐12]C:R[‐1]C)"      Selec on.AutoFill Des na on:=Range("E14:G14"),_              Type:=xlFillDefault      Rows("1:1").Font.Bold = True      Rows("14:14").Font.Bold = True      Cells.Select      Selec on.Columns.AutoFit          For i = 2 To 10          If Cells(i, 6).Value > 0 Then              Cells(i, 8).Value = "Service Revenue"              Cells(i, 1).Resize(1, 8).Interior.ColorIndex = 4          End If      Next i   

End Sub 

23