Advanced Excel VBA for Business and Industry

How our class will boost your Excel VBA skills.

Our 3-day class shows how to combine VBA with Excel elements, userforms, and events to create advanced analysis, dashboard, and report generation tools.

It covers advanced app essentials like tracking data ranges, controlling arrays, using events, and workbook / text file control. Advanced data processing, automated reporting, and creating chart reports are also discussed.

In addition, our class discusses in depth how to create advanced interfaces for your apps using VBA, userforms, ActiveX, charts, and other key Excel elements. Advanced modeling and analysis tools, dashboards and KPIs, and database control using ADO are also covered.

Read our detailed outline > below and see everything you'll discover in our class.

See what you can learn.

Learn to find where complex ranges start/stop, auto-build formulas, control workbook files with file pickers, and command the registry.

Learn to automate Excel's data tools (PivotTables, AutoFilter...), batch process reports with them, and then auto-format those reports.

Learn to process complex data patterns; fill report templates with the data; and auto-insert formulas in the report.

Learn to track multi-file downloads in folders, auto-build report tables, and lookup table data using VBA or auto-embedded lookup formulas.

Learn to scan folders for downloaded files, load arrays as charts datasets, and organize and format charts on worksheets to create reports.

Learn to use VBA to create text files; clean and parse downloaded text files; and calculate text file data.

Learn to use VBA, userforms, ActiveX, and events to design advanced program GUIs and run them from custom Ribbon controls.

Learn to design project status tools that auto-reformat their display and fuse data from multiple data sources and files.

Learn to combine VBA with worksheets, charts, ActiveX, shapes, and events to build advanced dashboard displays.

Learn to design advanced interfaces that control and query databases, text files, and workbooks using SQL, ADO, and VBA.

Tracking range configurations and controlling arrays in advanced VBA apps
Controlling folders, files, workbooks, and the Windows Registry
Advanced report generation using VBA, Excel's data tools, and PivotTables
Using loops, logic, and functions to analyze complex datasets and fill tables and forms
Using VBA to build / format advanced charts and layout multiple charts on a worksheet (chart report)
Advanced VBA methods for creating, writing, reading, parsing, and cleaning text files
Creating UIs for your Excel apps using the worksheet, ActiveX controls, shapes, and VBA
Creating UIs for your Excel apps using userforms, ActiveX controls, events, and VBA
Processing data in multiple folders, multiple workbooks, and worksheets
Managing advanced model and analysis problems with Excel VBA
Creating advanced dashboards on the worksheet using charts, shapes, events, ActiveX controls, and VBA
Constructing advanced status and calendar tools with Excel VBA
Using SQL, ADO, and UIs to query and integrate databases, workbooks, and text files

Some key topics we cover in class.

4 ways to train.

Virtual

View the dates, signup, and pricing for our hands-on live virtual training.
Virtual Signup > Learn More >

In-Person

View the dates, signup, and pricing for our hands-on in-person training.
In-Person Signup > Learn More >

Manual

Can't take the class, buy the self-study manual, apply the price later.
View Manual >

Onsite

Learn how we can help your business save time and effort in Excel.
View Onsite Details >

Class syllabus.

How we run the class: We focus our training on what our customers need. When training begins, we analyze those needs and shift our training outline appropriately. We will stress topics or add topics that our customers want. No two training sessions are ever the same with EMAGENIT.

Class Brochure

Day-1

Finding Ranges, Using Arrays, and Tracking Objects

* Topics below discussed when needed in the class

Using VBA to track various worksheet range configurations
Loading and clearing arrays, sequencing arrays, and using ReDim
Using Set, For...Each Next loops, CreateObject, and Getobject to track objects
Using the Run method, concatenation, and logic to create macro calls on the fly
Creating your own objects using Class modules, procedures, and public variables

Using VBA to Control Workbooks, Folders, Files, and the Registry

Using FileDialog to build a File/Folder picker for your app
How to use VBA to create, move, copy, and delete folders and files
How to control your workbooks in VBA using Open, Close, Add, SaveAs...
Storing and retrieving workbook data in a multi-user, high demand environment
Why store your program settings in the Windows Registry and not in your code workbook?
Using GetSetting, SaveSetting, and DeleteSetting to write, read, and delete settings in the registry

Advanced Report Automation and Formatting Using Excel VBA

Determining where worksheet tables start and stop using Range, CurrentRegion, Find...
Looping Excel's data tools (AutoFiter, Sort,PivotTables...) to process large worksheet datasets
Using VBA to copy, paste, and stack filtered data in different report worksheets
Using Remove Duplicates and VBA to determine report sheet names from data
Using loops, logic, and formulas to create summary rows / columns for report worksheets
Using macros to format your worksheet tables and remove columns / rows

Processing Complex Worksheet Data Patterns with Excel VBA

Processing bank, quick books, inventory... worksheet downloads with VBA
Using the Math, Statistical, and Lookup functions in VBA to analyze worksheet data
Inserting worksheet rows and columns in your data and filling them with formulas or values
Using VBA and functions to perform table lookups and fill worksheet rows / columns
Using loops and logic to delete and move worksheet data rows, cells, and columns
Using VBA to read data and output it to various worksheet table configurations and forms

Advanced Chart Design and Automation

Combining charts, shapes, and Conditional Formatting to make visual displays
How to modify chart data ranges and assign data subsets to charts with VBA
Using macros to create and format charts like xy scatter, column, doughnut, combo...
Using VBA to create chart limit lines, color data points, hide /display series, build curve fits...
Adding multiple tables to a chart using VBA
Using VBA to create and arrange multiple charts on a worksheet

Advanced VBA Methods for Creating and Reading Text Files

Automating Text Wizard in Excel VBA to load text data on the worksheet
Using the TextStream object in Excel VBA to open, read, write, modify, and close text files
Parsing, stacking, and processing worksheet text data with VBA
How to use loops and string concatenation to assemble and write text data to text files
How to use loops, logic, and arrays to access and load specific text file data onto a worksheet
Using macros to clean and parse text file data using functions and Text to Columns

Day-2

Creating Advanced User Interfaces on the Worksheet

Creating and positioning ActiveX controls on the worksheet
Combining Data Validation and VBA to create advanced worksheet UIs
Using worksheet Object modules to create ActiveX control events (Click, Change...)
Controlling and reading worksheet ActiveX controls with VBA
Using worksheet, workbook, and application events to run your UIs
Running worksheet UI procedures at specific times using the OnTime method
Using VBA, shapes, cells, and formats to build UI displays
Combining your worksheet UIs with your project

Developing Advanced User Interfaces with VBA Userforms

How to create userforms and place ActiveX controls, pictures, and text on them
Launching and setting userforms with VBA; using modal vs. non-modal userforms
Designing event procedures for userform ActiveX controls
Controlling and reading userform ActiveX controls with Excel VBA
Using Excel VBA to load and format list and combo boxes
Designing a list or combo box to fill another list or combo box
How to control multi-column and multi-select list boxes in your code
Creating a Ribbon tab and its controls to run a userform
How to turn your userform app into an Excel Add-In

Advanced Modeling and Problem Analysis with Excel VBA

Filling downloaded data in model tables with VBA
Controlling Excel tables and range names with Excel VBA
How to use VBA to manage problem formulas in complex table configurations
Calculating worksheet data in VBA using worksheet and VBA functions
Using loops, logic, and names to collect multi-worksheet information, calculate it, and output it
Using Excel VBA to copy worksheets, modify their formulas, and tie them into summary sheets
Using names, logic, loops, and formulas to track and build models on the worksheet

Searching for Data in Multiple Workbooks, Worksheets, and Folders Using VBA

Design strategies for naming workbooks and folders for large scale data storage and access
Scanning open workbooks for a specific data set using For...Each Next loops, logic, and functions
Searching multiple worksheets for a specific data set using For...Each Next loops, logic, and functions
Accessing multiple folders for specific data workbooks using For...Each Next loops, logic, and functions

Day-3

Constructing Advanced Status and Calendar Tools with Excel VBA

Why use the worksheet to build status and calendar tools?
Looking up table headers in your code using MATCH, COUNITIF, Find...
Simple worksheet strategies to track project status info in team workbooks
Writing loops and logic to process project data from multiple workbooks
Writing loops and logic to fill and color the cell data in your status dashboard
Using Excel VBA to create and fill monthly and 2-week calendars with data
Outputting timelines to the worksheet, making calendar controls

Assembling Advanced Worksheet Dashboards Using Excel VBA

Review of the various dashboard types, metrics, benchmarks, and KPIs
How to combine charts, shapes, and VBA to create various dashboard gages
Using Data Validation, ActiveX controls, cells, and VBA to build dashboard controls
Using VBA to create, write to, format, and position shapes and pictures on the worksheet
Assigning macros to shapes and pictures on the fly with VBA
How to turn shapes into hi-tech dashboard displays (i.e. timelines, factory layouts...)
Creating various dashboard displays using VBA, cells, and formats

Using SQL, ADO, and UIs to Control Databases, Workbooks, and Text Files

SQL language review for querying, inserting, updating, and deleting data
How to automatically build SQL statements and connection strings in VBA
How to use ADO and SQL to update, insert, delete, and query database data
How to use ADO and SQL to query a worksheet table or text file and return a record set
Combining ADO, SQL, loops, and functions to integrate data from different data sources
Automating Power Query and Microsoft Query to query / integrate data from multiple sources
Developing UIs to control database tasks

Additional Class Information

Prerequisites for our class.

Used the Excel VBA language before
Used variables, loops, and logic before and know how they basically operate
Used objects, properties, and methods before in code to control a program
Built basic worksheet formulas (=A1+A2) and used worksheet functions like SUM, MATCH, COUNTIF, COUNTA...
Used Excel drawing shapes and built charts manually
Used Excel's data processing tools like Autofilter, Sort, Remove Duplicates, and PivotTables

Class time, manual, and repeats.

Audience: Business, government, industry, military, engineering, science, techs...
Duration: 9:00 - 5:00 CT
# of Days: 3-days
Instruction Type: Live, hands-on
Class Manual: Our complete Excel manual for business and industry (TCEM). Details >
Excel Versions: 2013-Present, Microsoft Excel 365, Windows and Mac
Repeats: Repeat your Excel training. Details >

If you need to contact us about our class.

Phone Number: 1.629.207.9662

Business Hours: Mon-Fri 8:00 AM - 7:00 PM CT

You can email us at info@emagenit.com >

Form >

Advanced Excel VBA for Business and Industry - Live Class