VBA Macros

Module

Module Topic

Module coverage

1

Introduction to VBA (Visual Basic for Application)

Introduction to Object-oriented programming

Examining the Excel object model

Working in the VB Editor

Setting VBA project properties

Code Modules - Create, export and delete

Creating Subroutine or Function

Controlling procedure scope

Adding comments to the code

Running a VBA routine

2

Variables, Constants and calculations

Excel VBA Data types

Declaring variables & why it is needed?

Managing variable scope

What are static variables and constants?

Calculations using mathematical operations

Defining arrays

Defining and using object variables

3

Adding logic to VBA Code

Using With…End with statement

Repeating a task using a For…Next loop

Stepping through all items of a collection using For…Each loop

Repeating a task using a Do loop

Using If…Then statement to perform a task

Using Cast statements to decide an action

4

Debugging VBA code

Managing errors with "On Error" statements

Stepping through a subroutine or function

Setting breakpoints in the code

Verifying the output in the immediate window

Watching a value in a routine

5

Managing workbook elements and data in VBA

Writing a value to a cell

Cutting, copying and pasting cell data

Finding values in cells

Referring to cells using the "Offset" function

Concatenating text strings

Returning part of a string

Managing worksheets with VBA

Managing workbooks with VBA

6

Advance VBA

Screen updating on/off

Using worksheet functions in a macro

Using input box or message box to receive user input values

Calling a subroutine from another subroutine

7

Excel events in VBA

Running a procedure when you open, save or close a workbook

Running a procedure when a cell range changes

Triggering a procedure using a specific key sequence

8

Recording and enhancing Macro

Recording a Macro to drill down data

Improving the macro by writing our own code

Enhancing the macro to work with different files

9

Managing files using VBA

Determine if a workbook exits

Determine if a folder (directory) exits

Detecting whether a file is open

Opening a workkbok

Closing a workbook

Saving a workbook under a new name

Saving a workbook in different formats

10

Managing worksheets using VBA

Checking if a worksheet exists

Creating and renaming a worksheet

Copying a worksheet within the active workbook

Copying a worksheet to a new workbook

Copying a worksheet to an existing workbook

Moving worksheets

11

Using Built-in Functions in VBA

Using the built-in Open dialog box

Managing alerts (Suppress and restore)

Calculating data using Excel Worksheet functions

Using the current date and time

Removing spaces in a string

12

Working with charts via VBA

Creating a chart

Moving a chart to a chart sheet

Adding a new data series to a chart

Export a chart as an image

13

Creating and managing User Forms with VBA

Creating a UserForm

Adding a TextBox to a UserForm

Adding a ListBox to a UserForm

Adding a ComboBox to a UserForm

Adding an option button/spin button to a UserForm

Adding graphics to a UserForm

Creating a multitab UserForm

Writing UserForm data to a worksheet

Running a UserForm

14

Interactive Pivot table dashboard with VBA

Creating a dashboard with Pivot Tables

Using form controls and active controls in dashboards

Simple macro codes to create an interactive pivot dashboard

Using Radio Buttons in a dashboard

Creating a Top/Bottom data view in dashboard

15

User Defined functions and Add-ins

Creataing User Defined Functions

Saving UDF as Excel Add-ins

Distributing Excel Add-in files

Using an existing add-in file