Excel VBA - Learn from real case studies and projects

Name: Excel VBA - Learn from real case studies and projects
Rating: 3.9 (23 reviews)

Improve your knowledge in Excel VBA from real case studies of projects of my students and from my works

Created byDan Prodi, Pipi Rahayu

Last updated 10/2018

English

What you'll learn

Their knowledge in Excel VBA will improve
They will understand how to apply thier VBA knowledge to their projects
They will be able to create Applications with VBA in Excel
They will be able to import, export and format data into Excel tables
They will improve their knowledge in using VBA to generate Excel formulas and references
They will improve their knowledge in using VBA to apply conditional formatting
They will improve their knowledge in using VBA to exchange data using VBA dictionaries
They will improve their knowledge in using VBA to import/export data with OLE DB
They will improve their knowledge in using VBA to generate, access, format Excel tables
They will improve their knowledge in using VBA to generate, format and customize Excel charts
They will improve their knowledge in using VBA to add ActiveX objects to Excel sheets and assign macros

Course content

12 sections • 174 lectures • 18h 55m total length

Introduction1:08
Material0:05
In this lecture you will find the files that I used to develop the case studies

Introduction to VBA0
The Developer Ribbon5:05
VBE: the VBA editor7:54
What is a macro?2:56
Record a macro6:29
Macro security5:37
The workbook as object4:19
The Worksheet as object3:35
Manage cells and ranges with VBA as objects5:12
The VBA Project structure and composition3:29
The Worksheet Module Code3:27
The Worksheet Properties5:03
The Worksheet Methods2:44
The General variables and subroutines5:23
The Workbook Methods3:00
Variables and Constants2:30
Subroutines and Functions3:28
User Defined Functions1:38
VBA Modules3:06
The Worksheet Module3:03
Public and Private Subroutines and Functions8:13
Public and Private Variables5:46
Variable Types and Declarations3:29

Case study presentation5:41
Get columns and rows information8:09
Get date difference3:10
Get last date of month2:13
Generate sheets for each month in a defined period of time14:08
Prepare the dictionary as data structure (I)8:25
prepare the dictionary as data structure (II)8:43
prepare the dictionary as data structure (III)6:40
Save the report data into a dictionary3:08
Get the one dimensional dictionary7:16
Parametrize and make a function general2:49
Print the dictionary values and create the tables9:15
Format the tables11:56
Format the headlines of a table2:22
Get the column label by header content7:18
Get the rows by range values4:24
Verify if the amounts are printed per time period5:26
Print first and last dates of a month on specific ranges4:55
Insert complex Excel formulas into Excel ranges as summary14:57
Use the formulaArray property to insert complex Excel formulas into ranges13:58
Evaluate the macro execution time2:12

Case overview2:06
Prepare the date fields on the Application worksheet4:35
Add command buttons to a worksheet and assign a macro5:04
Add a combobox to a worksheet and add elements5:20
Create a macro to open a new workbook and export data to it4:29
Change the properties and add worksheets to the new workbook via macro3:10
Prepare the metadata file containing the data to export to the new workbook5:20
Add a command button and assing a macro to open an Excel file5:00
Use OLE DB to retreieve data from an external Excel file without opening it10:19
Refine the OLE DB macro to retrieve data from a workbook with several sheets6:15

Case overview and generation of first table header11:34
Creation of a general routine to create and format an Excel table10:33
Creation of a routine to create + assign Excel formulas to columns of table (1)6:54
Creation of a general routine to create and assign Excel formulas to columns of an Excel table
Creation of a routine to create + assign Excel formulas to columns of table (2)11:49
Creation of a routine to create + assign Excel formulas to columns of table (3)8:29
Generate a multiline header of an Excel table based on information on other tabl6:45
Add formatting customization to the multiline headers according to other information from other tables or ranges
Test the subroutines created so far on other tables5:36
Add formatting customization to the multiline headers according to other information from other tables or ranges
Make the subroutines more complex with dynamic references (I)2:18
Add formatting customization to the multiline headers according to other information from other tables or ranges
Make the subroutines more complex with dynamic references (II)8:46
Add formatting customization to the multiline headers (I)4:09
Generation of Routine to automatically customize the borders of a range and of another routine to set the colors of a range (font and background)
Add formatting customization to the multiline headers (II)2:22
Add formatting customization to the multiline headers (III)3:34
Add formatting customization to the multiline headers (IV)5:03
Add formatting customization to the multiline headers (V)4:03
Routine to automatically customize the borders and colors of a range8:16
Application and test of our formatting routines to Excel table ranges7:59
Change the background pattern of a range5:02
Application and test of all our formatting routines to Excel table ranges2:34
Set conditional formatting in the ranges of an Excel table7:57
Apply our routines to another Excel table11:27
Complete the import, creation and formatting of all the Excel tables4:53

Case overview0:47
Prepare the summary tables including the generation of Excel formulas (I)5:55
Prepare the summary tables including the generation of Excel formulas (II)7:47
Create a detail data table with a calendar as a header and calculate dates8:04
Include formulas in the ranges of the calendar table (I)9:19
Complex Excel formulas to calculate durations, working days and holidays9:00
Include formulas in the ranges of the calendar table (II)9:58
Set format information (border, colors, number formats) into the detail table7:06
Generate and format another table (the ticket table) in a separate sheet0:36
Insert slicers into the ticket table14:32
Prepare the ticket summary tables in the calculation sheet from the ticket table2:16
Prepare data selection fields in the calculation sheet to customize data filteri6:49
Prepare the ticket detail table in the calculation sheet7:05
Generate the calendar in the ticket detail table with references to specific dat6:06
Generate the formulas to populate the ranges of the ticket detail table11:52
Generate Excel LOOKUP formulas for table ranges13:07
Set conditional formatting to the calendar of a table based on dates (I)1:18
Complete the formulas for the ticket detail table6:38
Set conditional formatting to the calendar of a table based on dates (II)9:42
Set conditional formatting to the calendar of a table based on conditions on dates e.g. format in different color cells containg dates < today, dates >today, dates=today
Set conditional formatting to the ranges of the ticket detail table9:53
Verify the formulas in the ticket tables1:12
Prepare the ticket summary tables including the generation of Excel formulas (1)8:03
Prepare the ticket summary tables including the generation of Excel formulas (2)10:40
Prepare the ticket summary tables including the generation of Excel formulas (3)
Prepare the ticket summary tables including the generation of Excel formulas (4)5:19
Prepare the ticket summary tables including the generation of Excel formulas (5)5:59

Complete the data tables with conditional formatting based on dates (1)8:20
Complete the data tables with conditional formatting based on conditions on dates from data in the calculation sheet and dashboard sheet
Complete the data tables with conditional formatting based on dates (2)16:11
Complete the data tables with conditional formatting based on conditions on dates from data in the calculation sheet and dashboard sheet
Complete the data tables with conditional formatting based on dates (3)3:10
Complete the data tables with conditional formatting based on conditions on dates from data in the calculation sheet and dashboard sheet
Complete the data tables with conditional formatting based on string values (1)9:30
Complete the data tables with conditional formatting based on string values (2)10:48
Set data validation in the data table with reference to values in other tables7:42

Case overview3:50
Prepare the meta data for the export information and store into a VBA dictionary11:26
Get Excel tables positions and addresses using the Areas property of ranges5:23
Store the meta information and the range addresses into a VBA array10:04
Retrieve and export the Excel tables into a new workbook (Part I)12:05
Retrieve and export the Excel tables into a new workbook (Part II)6:38
Complete the export and verify the reference of Table data into other sheets7:18

Case overview1:19
Generate the data field sections12:01
Create named ranges with variable address using Excel formulas7:17
Create named ranges with variable address using Excel formulas (Part II)5:13
Generate multi series charts with reference to variable Excel named ranges (I)13:22
Generate multi series charts with reference to variable Excel named ranges (II)11:50
Generate multi series charts with reference to variable Excel named ranges (III)4:41
Generate and format charts (titles, axis, formats)7:33
Apply lables and legends to Excel chart dynamically with VBA10:10
Assign/Modify the chart type of an Excel chart with VBA11:04
Format a chart (axes, titles, etc.) with VBA7:18
Apply the VBA macros developed so far to generate other charts (I)9:13
Arrange formulas, range number formats to prevent errors when generating charts6:48
Apply the developed solutions to other charts (I)6:18
Apply the developed solutions to other charts (II)2:05
Apply the developed solutions to other charts (III)2:55
Apply the developed solutions to other charts (IV)6:24

Requirements

Basic knowledge of Microsoft Excel
Basic knowledge of Excel VBA

Description

In this course, I have reproduced real development workflows used to design, build, and test practical solutions in Excel VBA based on both my professional experience and the projects developed together with my students.

This course represents a curated collection of high-value solutions and real use cases created and refined over the past three years. Instead of focusing only on theory, you will learn through practical examples inspired by real business challenges, allowing you to understand not only how VBA works, but also how to apply it effectively in real scenarios.

Throughout the course, new students will learn how to use Excel VBA to automate complex processes and significantly improve productivity, including how to:

Import data from external workbooks using OLE DB connections
Use VBA dictionaries to efficiently import and export data between worksheets, workbooks, and structured tables
Generate, format, and fully customize Excel tables programmatically
Create and customize complex and dynamic charts automatically
Generate dynamically created named ranges for scalable solutions
Assign and manage Excel formulas directly through VBA
Format borders, layouts, and colors of ranges automatically
Create reusable, parameterized, and generic VBA routines
Implement structured error handling for reliable applications
Define and automate data validation rules
Build advanced conditional formatting using complex formulas

By the end of this course, students will be able to design and build their own Excel-based applications using VBA, automate repetitive workflows, and generate professional automatic reports ready for real business environments.

This course is ideal for professionals, analysts, engineers, and students who want to move beyond basic Excel usage and start creating powerful automation tools that save time, reduce errors, and increase efficiency.

Who this course is for:

Who already knows how to use Excel and create VBA macros

Excel VBA - Learn from real case studies and projects

What you'll learn

Explore related topics

Course content

Introduction2 lectures • 1min

Summary of Excel VBA23 lectures • 1hr 35min

A reporting system for a sales person using VBA dictionaries21 lectures • 2hr 27min

A complex reporting tool including dashboards, charts and calculation sheets10 lectures • 52min

Import data and formats of a set of Excel tables into a new workbook21 lectures • 2hr 20min

Generate and customize the Dashboard (Part I)2 lectures • 14min

Generate the Calculation parts for an Excel Dashboard and report in VBA26 lectures • 2hr 59min

Complete the data table with references and conditions from other sheets6 lectures • 56min

Import Multiple Excel Tables using dictionaries7 lectures • 57min

Generate an Excel dashboard with navigation fields and charts with VBA17 lectures • 2hr 6min

Requirements

Description

Who this course is for: