Excel VBA Macro Software Design

This Excel VBA course teaches you VBA (Visual Basic for Applications), the programming language built into Excel, so you can automate tasks and build your own Excel tools.

The course covers VBA syntax and structure, data types and variables, programming constructs such as loops and conditional statements, functions and subroutines, working with arrays and collections, and interacting within the Excel application.

With this Excel VBA knowledge, you will be able to:

• Combine & consolidate multiple Excel files into a single file,
• Clean data faster, Create Pivot Tables with VBA,
• Create Forms,
• Append data from External files, and
• Learn to use Range, Objects, Properties & Methods in VBA to automate routine tasks.

The goal of the course is to provide individuals with the skills and knowledge they need to create powerful macros, automate repetitive tasks, and develop custom solutions that can help them work more efficiently and effectively with Microsoft Office applications.

Additionally, the training will include best practices for debugging, testing, and maintaining VBA code and tips for optimizing performance and writing efficient code.

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