delhi
Date
  • weekwid: false
  • widHeader: false
  • orgName: register@princetonacademy.co.in
  • orwid: false
https://www.eventshigh.com/detail/mumbai/c8412b481ebf98a65467aa909c4f312d-macros-vba

Macros VBA

19.101771336172,72.825847521643
Wed, 21 Aug 9:30AM - Fri, 23 Aug 5:30PM
Rs 23482
11 people viewed this event.
I Am Interested

Get notified when the event happens next time.

Details

Details

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.
  • The 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

Advanced VBA

Screen updating on/off

Using worksheet functions in a macro

Using the 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 workbook

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 the dashboard

15

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

 

Projects :

  • Project #1: Using Excel's Macro Recorder Tool
  • Project #2: Using VBA to automate Excel Formulas
  • Project #3: Bringing it all together and create a weekly report
  • Project #4: Working with VBA Forms
Login to View Organizer Details
Like this event ? Share it with your friends !!
Show

Map & Directions

Map & Directions
JW Marriott Hotel Mumbai Juhu Tara Road, Juhu Beach Mumbai India 400049
Reviews
Reviews
No reviews available
Write a review
Be the first one to review! Share your experience.
Show

Frequently Asked Questions

FAQs
Have any query? Drop your questions here !!
Show
EventsHigh Specials, debugging, object oriented programming, project 1, radio, programming, macro recorder, upgrade, automation, visual basic,