Excel VBA Basic to Advance Part3 ( 12 hours )

Name: Excel VBA Basic to Advance Part3 ( 12 hours )
Rating: 4.3 (5 reviews)

In-depth study of VBA Charts,VBA Pivots and Powerpoint Application Connectivity ,Database Connectivity -Advance Level

Created byajay parmar

Last updated 8/2024

English

What you'll learn

You will be able to learn how to create Charts using VBA
You will under Charts classes to work with existing and new Charts
How to work with more than 1 chart and create multiple charts and how to access charts across sheets or few ones
We are learning how to work with Pivots using VBA. How to automate your pivot reports
Everything that exists in VBA about Pivots is discussed in this series from working with existing pivots to creating fresh new pivots
Collection loops used in Pivots are explained
How to connect database with VBA using SQL command . Del, modify , change and insert , retrieving the information is so easy.

Course content

4 sections • 34 lectures • 11h 48m total length

What are you going to learn?4:37
Introduction to Charts - How to Create21:35
Master automating charts in Excel with VBA by recording creation, naming charts, setting data sources, and positioning chart objects through macros.
Errors Solutions in VBA - Charts are easy if you understand fundamentals6:34
How to Create multiple Charts17:24
Alignment in Charts - Resizing and dimensioning14:29
Collection Loop role in Charts15:34

Create Pivots in VBA - Begin the show22:43
How to add Pivot Fields13:24
Collection Loops in Pivot Fields8:26
How to retrieve fields3:47
Explore how to use a collection loop to retrieve items from a pivot field, specifically the zone field, and capture pivot item names to automate Excel VBA tasks.
Control Pivot fields from excel cells28:52
Project for you - Gain more confidence5:32
Create and manage pivots in Excel VBA by defining dynamic ranges and placing pivots at G2 and G10, using templates and user inputs for sheet and range.
Work with Pivots across Worksheets15:11
Learn to update every pivot across all worksheets in excel using vba, changing sum to average and aligning captions and fields, with practical debugging tips for pivot properties.
Loop Slicers in Pivots23:40
Explore how to work with slicers in Excel VBA by looping slicer caches and listing slicer items. Reference slicer names to manage pivot data efficiently.
How to control Slicers and then Pivots - SuperPowerful24:57

How to connect PPT with Excel VBA8:38
Understand the PowerPoint object hierarchy and connect PowerPoint with Excel VBA using the PowerPoint 16.0 object library, early binding, and dot operator to export charts or ranges.
How to add a presentation and a slide5:49
Add multiple Slides7:19
How to use a Loop to add 100 Slides in a second4:25
Learn to automate PowerPoint slide creation with Excel VBA by looping from 1 to 50 to add slides, manage slide indices, and save the presentation.
Open a existing PPT5:04
How Count total current Slides - Useful in projects4:18
learn to count the number of slides in an existing powerpoint presentation using vba by referencing the active presentation’s slides.count and displaying the result with a message box.
Delete, or Select a specific Slide with How to add Charts - New PPT30:00
Paste Multiple Charts from multiple Sheets23:31
Export charts from multiple Excel sheets to PowerPoint using the PowerPoint object hierarchy, early binding, and collection loops to create slides and paste charts.
Add Charts in any current Slide in a existing PPT24:10
Collection Loops for SLIDES & SHAPES - Most Powerful48:00
Project Part1 Begins - What are we going to do?5:01
Shows how to connect Excel and PowerPoint with a VBA macro that copies a range from Excel and pastes it into slide nine, with prompts for file, sheet, and range.
Project Part2 - Using File Dialogs to Open Excel & PPTs11:46
Pick up Ranges and Slides of your Choice - Part339:26
New Big Project - Created for my client - Super Advance level57:26

What are we going to do?6:57
Import ms access table in excel38:03
Update records using VBA UserForms1:12:15
Learn to update access tables from Excel VBA using update queries, including syntax, criteria, and text handling, and apply these with a user form interface.
Delete & Change the database tables using VBA56:54
Learn to write and execute delete queries in Access VBA, using where clauses to remove specific records, or delete star to clear entire tables with SQL syntax.
Insert Data32:22

Requirements

You should already know how to create Charts and Pivots in Excel. This is a VBA Course so here we not learning what are charts and pivots.
We are learning how to automate them using VBA

Description

In first Section, we are learning about Chart automation. How to create new charts iusing VBA. We can fully automate the making of charts using VBA.
Learn the Chart class called Chart and see how easy it is to create the new charts.
The role of macro recordings plays a very important role here as most of the code we can record and later customize as per the requirement.
How to work with existing charts in excel. How to go to each of them one by one and do modifications. Any type of modification like changing chart design or editing labels or chart titles etc
Understand the Loops importance in charts. You can loop through infinite charts using one simple collection loop.
Collection loops of charts - to work with multiple charts either to delete them or to edit their design, colors. You can do whatever. Even change the axis's.
Use of other Collection loops with chart collection loops. Loops in Loops
In section2, you will become master in Pivots. From making new pivots to working in existing pivots and modifying them
Collection loops in PIvots - how to access pivots . How to loop pivot field items and fully control it.
We will learn the classes we use in Pivots to handle new pivots or existing pivots like Pivot-table and Pivot cache classes.
We will take a deep dive into knowing other classes as well which helps us in controlling the pivot fields and field items like pivot item class and pivot-Field class.
How to mix worksheet collection loops with pivot collection loops in real time projects and what are the benefits of same
What is the role of macro recording in making pivots and how to edit those recording if requirement changes.
How to work with row or column of filter pivot fields. Line by line explanation.

In Section3 , we are learning how to connect Powerpoint with Excel VBA.
Discussing about the connectivity of Excel with PowerPoint in this advance series using Excel VBA.
How to copy table data from excel and paste into PowerPoint application slides.
How to export charts from excel sheets to PowerPoint and paste them in one slide or more slides one by one.
Taking a deep dive into power-point classes like Presentation class, Slide class and shapes class. Understanding the power-point object hierarchy.
How every class is responsible for other class using the dot hierarchy.
How to work with already created PowerPoint applications and also how to create new PowerPoint applications and work with them.
How to resize the charts or tables images in slides and move them to the top or left or right of the slides.
Control which chart should go in PowerPoint using the excel sheet cells.
How to loop through all sides and decide which one to be deleted or used for data presentation.
Collection loops for slides as well as shapes. This is amazing source of learning.
Real time projects discussion with you to give you live exposure of working with Power-point.
Understanding the concept of Early Binding and Late Binding methods or techniques
Several highly advance level Projects for you - Fully automated and very very knowledgeable practical projects.

In Section4 , we are learning how to connect with databases like ms access using SQL commands in Excel VBA.
How to retrieve information from a database using SQL in vba
How to delete/ update and insert the information in database like ms access using VBA and also using Userforms

Who this course is for:

Data Analysts or Data scientist who work on excel and want to produce pivot and charts in one click

Excel VBA Basic to Advance Part3 ( 12 hours )

What you'll learn

Explore related topics

Course content

VBA CHARTS6 lectures • 1hr 20min

VBA PIVOTS9 lectures • 2hr 27min

Connectivity with PowerPoint- Powerful automation14 lectures • 4hr 35min

Connecting Databases with Excel using VBA5 lectures • 3hr 27min

Requirements

Description

Who this course is for: