Book Online Tickets for VBA Macros, Mumbai.  
Project Based Practical Course on
VBA Macros
21st to 23rd August 2019 – JW Marriott, Mumbai
If you already use Excel, you know how repetitive and time consuming some tasks can be. This course is designed to teach you how to take those re

VBA Macros

 

Invite friends

Contact Us

Page Views : 4

About The Event

 

Project Based Practical Course on

VBA Macros

21st to 23rd August 2019 – JW Marriott, Mumbai

If you already use Excel, you know how repetitive and time consuming some tasks can be. This course is designed to teach you how to take those repetitive and time consuming Excel tasks, and make them happen in a click of a button! If you use Excel a lot in your work, this course is essential; it will reduce your workload significantly.

During this course you will build a foundation of working with Excel Macros and VBA. This foundation will be built as you engage and participate in project based Excel Macro/VBA exercises, detailed throughout the lectures within this course. Each of the projects will build upon one another, introducing a new concept each time, starting with the basic building blocks of automating any task within Excel and ending with a fully customized Excel VBA project that will automate a series of Excel tasks.

What you'll learn

  • How to automate daily routine tasks within Excel
  • Best practices when creating macros and streamline your VBA code
  • 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

 

Target Audience:

  • Professionals, Students, Data Scientist, Analyst working on excel and wish to upgrade their skills.
  • Prior knowledge and hands on practice of excel is desirable.
  • Course will be useful to enhance your skills significantly.

 

 


 

Course Outline:

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

 

Projects :

 

  • Project #1: Using Excel's Macro Recorder Tool
  • Project #2: Using VBA to automate Excel Formulas
  • Project #3: Bringing it all togther and create a weekly report
  • Project #4: Working with VBA Forms

More Events From Same Organizer

Venue Map