1 / 17

Excel Macros and Visual Basic for Applications: Personal Macro Workbook

This chapter covers the Personal Macro Workbook in Excel, storing and using macros, writing macros in Visual Basic for Applications, using the Visual Basic Editor for creating and debugging macros, navigating macro modules, and using macro statements and cell references. It also covers automating data management tasks, creating custom buttons, and common VBA statements like MsgBox and InputBox.

anniematthews
Download Presentation

Excel Macros and Visual Basic for Applications: Personal Macro Workbook

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 9 Macros And Visual Basic For Applications

  2. Personal Macro Workbook • A workbook that opens automatically whenever Excel is opened • Any macro stored in this workbook can be used by any open workbook

  3. Macro • Macro storing Excel commands (written in Visual Basic for Applications) • Visual Basic Editor (VBE) creating, displaying, running, and debugging macro commands • Project Explorer locating macro modules • Code window displaying micro statements

  4. Macro Statements • Sub (subroutine) statementfollowed by the name of the macro • Defining the beginning of the macro • Comments begin with apostrophe (’) • Providing documentation • End Sub statement • Defining the end of the macro

  5. The With Block • Including With and End With • Performing multiple actions on the same object

  6. Absolute Cell References • Referring to a specific cell • Referred to by cell address or by range name • Syntax: Range(“A1”).Select • Always selecting cell A1

  7. Relative Cell Reference (I) • Referring to a cell by its position relative to the active cell • Offset: giving the position relative to the active cell • First number: indicating the offset in rows • A positive number: rows below the active cell • Second number: indicating the offset in columns • A positive number: columns to the right of the active cell • Offset (2, 3) always refers to a cell two rows below and three columns to the right of the active cell

  8. Relative Cell Reference (2) • Range: dimensions of cell(s) • Range(“A1”): a single cell • Range(“A1:A3”): a range three rows by one column • Syntax: ActiveCell.Offset(2,1).Range(“A1”). Select • Always selecting a single cell two rows below and one column to the right of the active cell

  9. Data Management Macro • Automating data list management tasks • Prompting user for input to populate a criteria range • Filtering list or displaying database functions based on those criteria • Use range name for the list or the criteria range

  10. Visual Basic for Applications • Tool menu, Macro, Visual Basic Editor • Or Alt+F11 • VBA: a subset of Visual Basic • Macro converted to VBA programs • Creating macro using macro recorder • Using Visual Basic Editor to modify

  11. Custom Button – Standard Toolbar • Creating custom button: • Tools menu, Customize, Command tab, selecting Macro for the category, dragging custom button to the standard toolbar, selecting Modify Selection button, typing the name to replace “&name button” (do not press enter”, selecting Assign Macro to assign a macro, selecting This Workbook for Micro in, selecting Close • Deleting a micro and its custom button: • Tools menu, Micro, selecting micro name, clicking Delete button • Dragging the corresponding custom button from the standard toolbar to the worksheet area

  12. Custom Button – Worksheet • Creating: • View Menu, Toolbars, Forms, clicking Button tool, drawing a button, assigning a associated macro, typing the name for the button • Selecting • Ctrl key and clicking the button (releasing the Ctrl key right away) • Coping • Holding Ctrl key and clicking the button (+ sign) • Deleting the macro and its corresponding micro • Tools menu, Macro, selecting the macro name, clicking Delete button, OK (performing this first) • Selecting the custom button, right clicking the mouse, selecting Cut

  13. Common VBA statements • Underscore and ampersand: continuation • MsgBox statement • Displaying information to the user • InputBox statement • Accepting information from the user • Storing information in a cell or memory for future usage in the program

  14. Decision Making • “If-Then-Else-End If” statement • If testing a condition (true or false) • “Then” clause including a series of commands to execute for the true condition • Then clause is necessity • “Else” clause including commands to execute for the false condition • Else clause is optional • Ends with End If statement

  15. Loops • Do Until -Loop statement • Executing statements repeatedly • Begin with Do Until statement • Including a true or false condition after the Until for evaluating • End with Loop statement • Executing between Do and Loop statements repeatedly until the condition is true

  16. Points to Remember • Personal Macro workbook • Macro • Visual Basic Editor • VBA statements • Information: MsgBox and InputBox • Decision making: If • Repeating: Do

  17. Assignment • Practice exercises 7 and 8 • Due date:

More Related