New Perspectives on Microsoft Office Excel 2003 Tutorial 12 - PowerPoint PPT Presentation


PPT – New Perspectives on Microsoft Office Excel 2003 Tutorial 12 PowerPoint presentation | free to view - id: 24e998-NjViM


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation

New Perspectives on Microsoft Office Excel 2003 Tutorial 12


For example, you could create a macro that will take you to cell A1 of a ... Each object has 'properties', which are the object's attributes or characteristics. ... – PowerPoint PPT presentation

Number of Views:62
Avg rating:3.0/5.0
Slides: 41
Provided by: course264


Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: New Perspectives on Microsoft Office Excel 2003 Tutorial 12

Microsoft Office Excel 2003
  • Tutorial 12 Enhancing Excel With Visual Basic
    for Applications

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.

The Record Macro dialog box
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
  • 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
  • You now have a macro that will go to cell A1 of
    the target worksheet.

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.

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
  • 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

Sample macro code in the VBA window
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.

The Project Explorer window
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
  • This will bring up the Project Properties window

The VBA Project Properties window
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.

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.

Example of the Properties window
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
  • 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.

The Code window
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

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
  • 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
  • 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

Example of macro syntax
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.

The Add Procedure dialog box
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.

The Assign Macro dialog box
Basic concepts and principles of the VBA language
  • VBA is an object-oriented programming language
  • 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.

Excel objects and their VBA names
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
  • 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

The Excel Object Model
Special Excel object names
Some Excel objects and their properties
Modify object properties
Apply methods to objects
Define and modify variables
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
  • You can then add statements to set some objects
    property to the value contained in the variable.

A macro with a variable
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

Modify a macro so it responds to different user
  • 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.

An example of an If-Else statement
Test multiple conditions
Use the MsgBox function
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.