New Perspectives on Microsoft Office Excel 2003 Tutorial 12

1
Microsoft Office Excel 2003

• Tutorial 12 Enhancing Excel With Visual Basic
  for Applications

2
Create macros using the macro recorder

• You know that macros are a means of repeating an
  operation automatically by recording the
  keystrokes and actions for later use.
• For example, you could create a macro that will
  take you to cell A1 of a specific worksheet in a
  multi-sheet workbook.
• To do so, select any one of the other worksheets
  besides the target worksheet.
• From the Tools menu, select Macro, and then
  select Record new macro. This will bring up the
  Record Macro dialog box.
• Assign a name to the macro, and put an
  appropriate description in the Description box.

3
The Record Macro dialog box
4
Record your keystrokes

• When you click OK in the Record Macro dialog box,
  the Stop Recording toolbar will appear on the
  screen, and Excel will begin recording every
  keystroke.
• Go to the target worksheet by clicking its tab.
• Press Ctrl Home to move to cell A1 on the
  target sheet.
• Stop recording by clicking the Stop Recording
  button.
• You now have a macro that will go to cell A1 of
  the target worksheet.

5
Test your macro

• It is a good idea to test every macro you write.
• To test the macro
• Move the cursor away from cell A1 on the target
  worksheet, and then click the tab for one of the
  other worksheets
• From the Tools menu, choose Macro
• From the submenu, choose Macros
• From the list of macros, choose the name of your
  macro and click Run.
• Excel should take you to the target worksheet,
  with cell A1 selected.
• If the macro doesn't work, you can delete it and
  record it again, until it works as it should.

6
View macro code in the Visual Basic Editor

• When you create a macro in Excel by recording it,
  the macro is stored by Excel as a piece of code
  in the language called Visual Basic for
  Applications (VBA).
• You can view the code for the macro in the VBA
  editor.
• You can revise the macro, copy the macro's code
  to another macro, or add to the macro.
• To open the VBA editor and view the code that you
  have just recorded for your macro
• From the Tools menu click Macro
• From the submenu, click Macros
• Select the macro you recorded, and click Edit

7
Sample macro code in the VBA window
8
Features of the Visual Basic Editor

• When you open the VBA editor, there are usually
  three open windows on the editing pane.
• The first window is the Project Explorer.
• To invoke this window
• From the View menu, click Project Explorer.
• When the Project Window opens, it may be docked,
  that is, fastened to the edge of the screen,
  and always on top, no matter what other windows
  are open in the pane.
• You can undock it by right-clicking the title bar
  and choosing Dockable.

9
The Project Explorer window
10
Examine the Project Explorer window

• In the Project Explorer window shown in the
  previous figure, you can see the current project
  (VBAProject (Datalia2.xls)), with a list of its
  Microsoft Excel objects.
• An object in VBA is an element of an application.
  Worksheets, macros, workbooks, and projects are
  objects in VBA
• The objects listed under our project are the six
  worksheets in the project and the workbook itself
  
• In this window you can change the name of the
  project from the generic VBAProject to
  something more descriptive. To do so
• Click the Tools menu, then choose VBAProject
  Properties
• This will bring up the Project Properties window

11
The VBA Project Properties window
12
Rename a project

• In the Project Name box, key in a descriptive
  name (a project's name cannot contain spaces, so
  use underlines to separate words).
• In the Project Description box put an
  appropriate comment describing the project.
• Click OK to close the Project Properties window.

13
Open the Properties window

• The Properties window displays the current values
  for all object properties. To open it
• Select the project object
• Click the View menu and choose Properties Window
• When the Properties window opens, undock it if it
  is docked, so it will float.
• You will see a list of the properties of the
  project. The next slide shows the Properties
  window, and right now, the project has no
  properties listed.
• From the list of objects in the Project Explorer,
  select one of the worksheets and notice that the
  Properties window immediately changes to show a
  new list of properties, the properties of the
  worksheet you selected.

14
Example of the Properties window
15
Modify object properties

• You can change the values of properties by
  clicking the name in the left hand pane to select
  it and then change its value (in the right-hand
  column).
• When you press Enter, the properties value is
  changed, and the change is reflected not only in
  the Properties window and the Project Explorer
  window, but also in the workbook itself, on the
  tab of the worksheet.
• You can get an explanation of any of the
  properties that you do not understand by
  selecting the property and clicking F1, to bring
  up the Visual Basic on-line help.

16
The Code window
17
Write a macro with the Visual Basic Editor

• Take a closer look at the code in the Code window
  of the VBA Editor shown in the previous slide.
• The code you see is the code produced when you
  recorded the macro.
• Notice that on the first line, the piece of code
  is identified as a Sub.
• This means that it is a sub procedure, one of
  three types of procedures in VBA. (The other two
  are function procedures and property procedures.)
• A sub procedure is a block of code that performs
  an action on a project or worksheet.
• The action this sub procedure performs is to go
  to cell A1 of the Statistics worksheet

18
Examine Macro syntax

• The syntax of a sub procedure is shown in the
  next figure. It consists of a line identifying
  the sub procedure and its parameters, a set of
  VBA comments and commands, and the words End
  Sub.
• Parameters are values passed to the sub
  procedure, and are enclosed in parentheses.
• This sub procedure has no parameters, so its name
  is followed by an empty set of parentheses
• Comments are identified by an apostrophe at the
  beginning of the line, and are essentially
  ignored by the computer they are for the human
  user.
• The VBA editor usually displays comments in green
  
• Commands are statements written in VBA code that
  the computer can interpret, telling the computer
  what to do.
• In the sub named Statistics( ), there are two
  lines of code. The first tells the computer to
  select (go to) the worksheet named Statistics,
  and the second tells the computer to select cell
  A1.

19
Example of macro syntax
20
Create new procedures

• The Code window figure shows a sub procedure that
  takes you to the Statistics worksheet, but you
  would like to have a sub procedure that takes you
  to the Time Chart worksheet.
• To begin, select the Code window and click
  Procedure from the Insert menu.
• This will bring up the Add Procedure dialog box.
  Enter the name of the new procedure (remember to
  use an underline instead of a space between the
  word Time and the word Chart), choose Sub as
  the type, and choose Public as the scope.
• A public procedure is accessible from all the
  modules in the project
• When you click OK, you will be returned to the
  Code window.

21
The Add Procedure dialog box
22
Assign a macro to a button

• Once your macros are written and you have tested
  each one, you can assign them to buttons.
• Close the VBA editor by choosing Close and Return
  to Microsoft Office Excel from the File menu.
• Right-click the worksheet button you want to
  assign to a macro.
• This will bring up a shortcut menu. Choose Assign
  Macro. This will invoke the Assign Macro dialog
  box, which contains a list of the available
  macros that can be assigned to this button.
• Choose the macro name you want to assign to the
  button, and click OK.
• When you click on the worksheet button, the macro
  will run.

23
The Assign Macro dialog box
24
Basic concepts and principles of the VBA language

• VBA is an object-oriented programming language
  (OOPL).
• In such a language, everything gets done by
  manipulating things, or objects.
• When you program in an OOPL, you can almost think
  of the objects in your program as taking on a
  life of their own.
• Each object has properties, which are the
  object's attributes or characteristics. Each
  object has methods, which are actions that can
  happen to the object.
• Each object knows what its properties are, and
  knows which methods apply to it.

25
Excel objects and their VBA names
26
Understand collection objects

• One important kind of object is a collection
  object, which is a group of similar objects.
• To refer to a particular object of a collection,
  you use the collection's name, followed by the
  number or the name of the particular object in
  parentheses.
• The properties of an object tell what the object
  is like.
• Each property has a value.
• For example, if a chart has an attribute called
  ChartTitle, the value of that attribute would be
  the text that is the title of the chart

27
The Excel Object Model
28
Special Excel object names
29
Some Excel objects and their properties
30
Modify object properties
31
Apply methods to objects
32
Define and modify variables
33
Write an interactive macro that asks the user
for input

• You can create a macro that asks the user for
  input and executes based on that input.
• To do so, set up a variable, give it a name, and
  set it to some initial value.
• To make it take on different values later, add a
  statement to initialize the value at the
  beginning of the macro's code, such as VarbA
  C.
• You can then add statements to set some objects
  property to the value contained in the variable.

34
A macro with a variable
35
Use the InputBox command to input user values

• To obtain an input value from a user, VBA
  provides the InputBox command.
• In a macro, this command will
• Create an input box
• Put a title on the input box
• Ask a question in the input box
• Wait for input from the user
• Change the value of a variable to the value input
  by the user

36
Modify a macro so it responds to different user
input

• Macros generally work well if the user enters the
  correct input.
• However, if the user enters invalid input, the
  macro procedure displays a VBA error message.
• You can alter your procedure so that, instead of
  a VBA error message, it displays a message box
  telling the user that the input is invalid, and
  prompting for the correct input.
• You can do that by using the VBA control
  structure called the If-Then-Else.

37
An example of an If-Else statement
38
Test multiple conditions
39
Use the MsgBox function
40
Customize Excel's menus and toolbars

• You can create a custom toolbar to contain macro
  buttons, commands you want to use on a regular
  basis, or even menus. To add a macro button
• Click the Tools menu and choose Customize
• When the Customize dialog box opens, choose the
  Toolbars tab, click New, enter a name in the New
  Toolbar dialog box in the Toolbar name box, and
  click OK
• A new toolbar appears on the screen. Click the
  Commands tab in the dialog box and scroll down
  the list of commands to find and choose Macros
• From the right-hand window, click and drag the
  Custom Button to the new toolbar, and then click
  the Modify Selection button.
• From the sub menu that pops up, change the name
  of the button by clicking on Name and keying in
  your new text.