Macros in Excel & VBA

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

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

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

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

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

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

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

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

Managing files using VBA

• Determine if a workbook exits
• Determine if a folder (directory) exits
• Detecting whether a file is open
• Opening a workbook
• Closing a workbook
• Saving a workbook under a new name
• Saving a workbook in different formats

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

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

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

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

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

User Defined functions and Add-ins

• Creating User Defined Functions
• Saving UDF as Excel Add-ins
• Distributing Excel Add-in files
• Using an existing add-in file