VBA Macros

Time: 2:30 PM to 5:30 PM 

(Friday, Saturday & Sunday) 

(6 sessions of 3 hours each) 

Practical exercises used to introduce each concept and automating series of Excel tasks.

You will learn

Best practices when creating macros and streamline your VBA code

How to automate daily routine tasks within Excel

Determine when it's best to use the Macro Recorder versus VBA

How to write custom VBA Macros to create more robust automation

VBA programming concepts to create dynamic, re-usable code

Complete real-world Macro/VBA projects from beginning to end

Course Outline:

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 and 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 or folder (directory) exists

Detecting whether a file is open

Opening, Closing a workbook

Saving a workbook under a new name, 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

How to Register:

Fees: Rs. 11500/- + 18% GST Per Person

Please write to register@princetonacademy co in

Call or WhatsApp on 9769963790 / 9769579090

Mention the name of participant, company, contact details.