Excel VBA – Learn from real case studies and projects
Improve your knowledge in Excel VBA from real case studies of projects of my students and from my works
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
In this course I have reproduced the works to develop and test solutions in VBA for some of my works and of my students’ projects.
This is a collection of great works we did together in the last 3 years.
In details new students can learn how to use Excel VBA to:
- import data from other workbooks using OLE DB
- use VBA dictionaries to import/export data between worksheets, workbooks and tables
- generate, format and customize Excel tables in VBA
- generate and customize complex charts
- generate dynamically created named ranges
- assign Excel formulas to ranges
- format borders and colors of ranges
- create reusable, parametrized, common and generic routines
- handle errors
- define data validations
- create complex conditional formatting formulas
At the end students will be able to create their own Application using Excel and VBA and generate automatic reports
Introduction
1
Introduction
2
Material
In this lecture you will find the files that I used to develop the case studies
Summary of Excel VBA
1
Introduction to VBA
2
The Developer Ribbon
3
VBE: the VBA editor
4
What is a macro?
5
Record a macro
6
Macro security
7
The workbook as object
8
The Worksheet as object
9
Manage cells and ranges with VBA as objects
10
The VBA Project structure and composition
11
The Worksheet Module Code
12
The Worksheet Properties
13
The Worksheet Methods
14
The General variables and subroutines
15
The Workbook Methods
16
Variables and Constants
17
Subroutines and Functions
18
User Defined Functions
19
VBA Modules
20
The Worksheet Module
21
Public and Private Subroutines and Functions
22
Public and Private Variables
23
Variable Types and Declarations
A reporting system for a sales person using VBA dictionaries
1
Case study presentation
2
Get columns and rows information
3
Get date difference
4
Get last date of month
5
Generate sheets for each month in a defined period of time
6
Prepare the dictionary as data structure (I)
7
prepare the dictionary as data structure (II)
8
prepare the dictionary as data structure (III)
9
Save the report data into a dictionary
10
Get the one dimensional dictionary
11
Parametrize and make a function general
12
Print the dictionary values and create the tables
13
Format the tables
14
Format the headlines of a table
15
Get the column label by header content
16
Get the rows by range values
17
Verify if the amounts are printed per time period
18
Print first and last dates of a month on specific ranges
19
Insert complex Excel formulas into Excel ranges as summary
20
Use the formulaArray property to insert complex Excel formulas into ranges
21
Evaluate the macro execution time
A complex reporting tool including dashboards, charts and calculation sheets
1
Case overview
2
Prepare the date fields on the Application worksheet
3
Add command buttons to a worksheet and assign a macro
4
Add a combobox to a worksheet and add elements
5
Create a macro to open a new workbook and export data to it
6
Change the properties and add worksheets to the new workbook via macro
7
Prepare the metadata file containing the data to export to the new workbook
8
Add a command button and assing a macro to open an Excel file
9
Use OLE DB to retreieve data from an external Excel file without opening it
10
Refine the OLE DB macro to retrieve data from a workbook with several sheets
Import data and formats of a set of Excel tables into a new workbook
1
Case overview and generation of first table header
2
Creation of a general routine to create and format an Excel table
3
Creation of a routine to create + assign Excel formulas to columns of table (1)
Creation of a general routine to create and assign Excel formulas to columns of an Excel table
4
Creation of a routine to create + assign Excel formulas to columns of table (2)
5
Creation of a routine to create + assign Excel formulas to columns of table (3)
6
Generate a multiline header of an Excel table based on information on other tabl
Add formatting customization to the multiline headers according to other information from other tables or ranges
7
Test the subroutines created so far on other tables
Add formatting customization to the multiline headers according to other information from other tables or ranges
8
Make the subroutines more complex with dynamic references (I)
Add formatting customization to the multiline headers according to other information from other tables or ranges
9
Make the subroutines more complex with dynamic references (II)
10
Add formatting customization to the multiline headers (I)
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)
11
Add formatting customization to the multiline headers (II)
12
Add formatting customization to the multiline headers (III)
13
Add formatting customization to the multiline headers (IV)
14
Add formatting customization to the multiline headers (V)
15
Routine to automatically customize the borders and colors of a range
16
Application and test of our formatting routines to Excel table ranges
17
Change the background pattern of a range
18
Application and test of all our formatting routines to Excel table ranges
19
Set conditional formatting in the ranges of an Excel table
20
Apply our routines to another Excel table
21
Complete the import, creation and formatting of all the Excel tables
Generate and customize the Dashboard (Part I)
1
Prepare the dashboard with the main data input fields (I)
2
Prepare the dashboard with the main data input fields (II)
Generate the Calculation parts for an Excel Dashboard and report in VBA
1
Case overview
2
Prepare the summary tables including the generation of Excel formulas (I)
3
Prepare the summary tables including the generation of Excel formulas (II)
4
Create a detail data table with a calendar as a header and calculate dates
5
Include formulas in the ranges of the calendar table (I)
6
Complex Excel formulas to calculate durations, working days and holidays
7
Include formulas in the ranges of the calendar table (II)
8
Set format information (border, colors, number formats) into the detail table
9
Generate and format another table (the ticket table) in a separate sheet
10
Insert slicers into the ticket table
11
Prepare the ticket summary tables in the calculation sheet from the ticket table
12
Prepare data selection fields in the calculation sheet to customize data filteri
13
Prepare the ticket detail table in the calculation sheet
14
Generate the calendar in the ticket detail table with references to specific dat
You can view and review the lecture materials indefinitely, like an on-demand channel.
Definitely! If you have an internet connection, courses on Udemy are available on any device at any time. If you don't have an internet connection, some instructors also let their students download course lectures. That's up to the instructor though, so make sure you get on their good side!
Community
- Connect
- Jobs
- Events
- Forums
- Groups
For Professionals
- List Item #1
- List Item #2
- List Item #3
For Businesses
- List Item #1
- List Item #2
- List Item #3
Partnership Opportunities
- Sponsorships
- Advertise with us
- List your Business
- List your Event
- List your Course