Excel VBA Macro Software Design

Lesson 1: Software Design With Macros

TOPIC A: Getting to Know Excel Macros

• Visual Basic for Applications
• Macros
• Object-Oriented Programming
• Macro Options
• Activity: Getting to Know Macros

TOPIC B: Creating a Macro in Microsoft Excel

• The Macro Recorder
• Personal Macro Workbooks
• How to Create a Macro Using the Macro Recorder
• Activity: Creating a Macro Using the Macro Recorder

TOPIC C: Editing an Excel Macro

• The Visual Basic Editor
• Projects
• Modules
• The Modules Folder
• Objects
• Properties
• Methods
• VBA Comments
• How to Edit a Macro
• Activity: Editing a Macro

TOPIC D: Debugging a Macro in Excel

• The Debugging Process
• Debugging Tools
• How to Debug a Macro
• Activity: Debugging a Macro

TOPIC E: Customizing the Quick Access Toolbar and Hotkeys

• Add a Button to the Quick Access Toolbar to Run a Macro
• Assign a Keyboard Shortcut to a Macro
• Activity: Customizing the Quick Access Toolbar and Hotkeys

TOPIC F: Setting Macro Security in Excel

• Digital Certificates
• Digital Signatures
• Macro Security Settings
• Activity: Setting Macro Security

Lesson 2: Formatting Worksheets Using Macros

TOPIC A: Inserting Text With Excel Macros

• The Selection Property
• The ActiveSheet Property
• The Name Property
• The Value Property
• Concatenation
• How to Insert Text
• Activity: Inserting Text

TOPIC B: Formatting Text with Macros in Excel

• How to Format Text
• Activity: Formatting Text

TOPIC C: Sorting Data within Excel

• The Range Object
• The Select Method
• The CurrentRegion Property
• How to Sort Data
• Activity: Sorting Data

TOPIC D: How To Duplicate Data With Excel Macros

• Data Types
• Variables
• Variable Naming Rules
• Operators
• The Assignment Operator
• Arithmetic Operators
• Comparison Operators
• Logical Operators
• The For Next Loop
• Do Loops
• The Worksheets Object
• The Count Property
• The Offset Property
• The Copy Method
• The Paste Method
• How to Duplicate Data
• Activity: Duplicating Data

TOPIC E: Generate a Report in Excel Using Macros

• The Columns Property
• The AutoFit Method
• The Address Property
• The Call Statement
• The Font Property
• The End Property
• How to Generate a Report
• Activity: Generating a Report

Lesson 3: Creating an Interactive Worksheet with Excel Macros

TOPIC A: Determining the Dialog Box Type

• Message Boxes
• Input Boxes
• Activity: Determining the Dialog Box Type

TOPIC B: Capturing User Input With Macros

• The InputBox Function
• Constants
• The MsgBox Function
• The Code Continuation Character
• The vbCrLf Constant
• Decision Structures
• The Select Case Statement
• The If Then Structure
• The Else Clause
• How to Capture User Input
• Activity: Capturing User Input

Lesson 4: Working with Multiple Worksheets

TOPIC A: Insert, Copy, and Delete Worksheets With Excel Macros Methods

• The Add Method
• The Copy Method
• The Delete Method
• How to Insert, Copy, and Delete Worksheets
• Activity: Inserting and Deleting Worksheets

TOPIC B: Renaming Worksheets Within Excel

• Expressions
• The DateSerial Function
• The Format Function
• How to Rename Worksheets
• Activity: Renaming Worksheets

TOPIC C: Modify the Order of Worksheets Easily With Excel Macros

• The Move Method
• How to Modify the Order of Worksheets
• Activity: Moving Worksheets within a Workbook
• TOPIC D: Print Worksheets
• The PrintPreview Method
• The PrintOut Method
• How to Print Worksheets
• Activity: Printing a Workbook

Lesson 5: Performing Calculations With Excel Macro Code

TOPIC A: Create User-Defined Functions inside Excel

• User-Defined Functions
• Types of Functions
• Arguments
• How to Create User-Defined Functions
• Activity: Creating and Applying a User-Defined Function

TOPIC B: Automate SUM Functions

• Declared Range Objects
• The Set Statement
• Range Object Cell Addressing
• The Rows Property
• The Formula Property
• The Columns Property
• Address Property Cell Reference Settings
• How to Automate SUM Functions
• Activity: Automating SUM Functions