Advanced Excel VBA Class for Business and Industry

Class overview.

What makes our training different?

Live, hands-on, industry focused topics
Bring your own problems, ask tough questions
Taught by an Excel software developer
Limited class size

Our 3-day class shows you how to harness the full power of Excel and VBA to make advanced UI driven apps.

It stresses how to develop high-tech UIs from userforms, ActiveX controls, worksheets, shapes, pictures, charts, and events and use those interfaces to control forms, data processing, models, databases, reports, and charts.

Our class also covers key topics including how to control folders and files; batch reporting using PivotTables and Excel’s data tools; automated chart reports; scanning multiple folders / workbooks for data; and text file processing.

Also discussed is how to use shapes, pictures, charts, and VBA to develop powerful dashboard tools that display various statuses and KPIs.

Join us and our class will show you how to unleash Excel VBA's full potential and design the right Excel tool that gets the job done.

Some key topics we cover in class.

Review of the VBA Editor, Excel VBA language, debugging, and error traps
Controlling the Windows folder system to store report and data workbooks
Advanced data processing methods using VBA, Excel's data tools, and PivotTables
Advanced worksheet data processing and report generation using Excel VBA loops and logic
Processing data in multiple folders, multiple workbooks, and on multiple worksheets
How to use VBA to build / format charts and layout multiple charts on a worksheet (chart report)
Using Excel VBA to create, write, read, parse, and clean text files
Creating UIs for your Excel tools using the worksheet, ActiveX controls, Ribbon, and file / folder Pickers
Creating UIs for your Excel tools using userforms, ActiveX controls, events, and VBA
Creating advanced dashboards on the worksheet using charts, shapes, events, ActiveX controls, and VBA
Creating and managing advanced model and analysis problems with Excel VBA
Using SQL, ADO, and UIs to query and integrate databases, workbooks, and text files
Using VBA to control Outlook emails and Word / PowerPoint for report generation purposes

Prerequisites for our Excel training.

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, duration, 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: 2010-2019, Microsoft 365, Windows and Mac
Repeats: Repeat your Excel training. Details >

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.

Day-1

VBA Editor, Excel VBA Language, and Object Review (Discussed Where Needed)

Review of VBA programming elements including variables, data types, constants, arrays, operators, expressions, loops, logic decisions, functions, and calling conventions
Advanced use of the VBA Editor windows and debugger
Handling run-time errors in your VBA code using error handlers and logic
Using the CreateObject and GetObject functions along with the Set statement to create / track multiple objects in your code
How to create objects using Class modules, Subs, Functions, Properties, and public variables

Advanced Workbook and Folder Control Using Excel VBA

Why store data in multiple workbooks and use Excel VBA to integrate their data?
Using VBA to access and control the Windows folder / file system using FileSystemObject, File, Folder...
Using Open, Close, Save, Add, and SaveAs to control your workbooks
Testing for folder and file existence in your macro code using FolderExists, FileExists...
Using string concatenation in your code to build folder path and file names for your report / data workbooks

Advanced Report Methods Using VBA, Excel's Data Tools, and PivotTables

Using VBA, loops, and logic to control PivotTables and Pivot Charts
Using VBA, loops, and logic to control AutoFilter, Sort, Remove Duplicates, and Advanced Filter
Using string concatenation to build filter logic on the fly inside macro loops
Using Remove Duplicates in your macro code to filter report worksheet names from your data
Using loops and logic to build report workbooks / worksheets to store your filtered data
Using macros to copy, paste, and stack filtered data on report worksheets

Advanced Worksheet Data Processing Using Excel VBA Loops and Logic

Using Range, Cells, Offset, CurrentRegion, Find, Match... to dynamically track worksheet tables, headers, position, and size
Looping and logic strategies for processing bank, quick books, inventory, human resources... downloads
Automatically inserting worksheet rows and columns in your data and filling them with formulas or values
Performing single and multi-table lookups in your worksheet data and outputting the results to specific data rows and columns
Using loops and logic to delete and move worksheet data rows, cells, and columns
Creating and tracking multiple report workbooks / worksheets in your code and outputting data to them in various patterns

VBA Methods for Creating Text Files and Reading Text File Downloads

Using VBA, loops, and logic to control Text Wizard and parse / stack / process text file data on a worksheet
Using the FileSystemObject and TextStream object to open, read, write, modify, and close text files
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 various functions, the worksheet, and Text to Columns

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

Searching 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
Searching multiple folders for specific data workbooks using For...Each Next loops, logic, and functions
Designing scalable procedures that adapt to n... number of workbooks, worksheets, text files...

Advanced Chart Report Generation Using Excel VBA

Review of creating various Excel chart types like pie, line, column, xy scatter, combo... in Excel VBA
Formatting charts, creating / deleting chart elements, and loading chart templates with VBA
Using macros to add, edit, and delete an existing chart's data series
Using VBA arrays to create a chart series, great for emailing chart reports
Using macros to create chart limit lines and curve fits
Using macros to layout multiple charts on a report worksheet (i.e., chart report)

Day-2

Using VBA to Store Program Settings in the Windows Registry

Why store your program settings in the Windows registry and not in your code workbook?
Using GetSetting, SaveSetting, and DeleteSetting to write and retrieve program settings in the Windows Registry
How to use delimiters in your code to stack array information in the registry and use Split to retrieve it

Creating Advanced UIs with Worksheets, Controls, Events, File / Folder Pickers, and Ribbon

Why create user interfaces (UIs) on the worksheet?
Creating buttons, shapes, Data Validation, and ActiveX controls on the worksheet to control your Excel VBA tools
Using macros to control and read ActiveX controls and Data Validation on the worksheet
How to build various file / folder pickers that steer your macro code to the right file or folder to use
How to create a Ribbon tab, its controls, and the control event procedures to run your tools
How to use worksheet events like Calculation, BeforeRightClick, and BeforeDoubleClick to control worksheet UIs

Developing Advanced UIs Using VBA Userforms, ActiveX Controls, and Events

How to use modal and non-modal userforms to construct advanced UIs
How to create ActiveX controls and event procedures on userforms and control them with VBA
Using ranges, VBA arrays, AutoFilter, Sort, ArrayList object, and Remove Duplicates to load data into list boxes and combo boxes
Using macros to format data inside of userform list boxes and combo boxes
How to control multi-column and multi-select list boxes and combo boxes in your macro code
How to create a userform list box that fills in another list box
Designing procedures that launch userforms and preset their ActiveX control values
Using userforms and buttons to quickly create floating toolbars that run your Excel VBA tools

Assembling Advanced Worksheet Dashboards Using Excel VBA

Review of various dashboard types, scorecards, metrics, benchmarks, and KPIs
How to use charts, shapes, cells, pictures, and VBA to build dashboard displays
Using Data Validation, ActiveX controls, cells, and VBA to build dashboard controls
How to turn shapes and pictures into various hi-tech controls for dashboards
Using VBA to create, format, write to, move, and delete shapes and pictures on the worksheet
Designing logic in your macro code that reads and implements dashboard UI selections
Designing macros that gather data and build tables for dashboard charts
When to use userform based UIs in your dashboards and how to integrate them

Constructing Advanced Project Status and Calendar Tools Using Excel VBA

Why use workbooks and the worksheet to build project status tools?
Looking up status table headers and rows in your code using MATCH, COUNITIF, Find, Row, Column...
Simple worksheet strategies to track project status info in team workbooks
Writing loops and logic to process project data from multiple workbooks and worksheets
Writing loops and logic to fill and color the data in your status dashboard
How to access Microsoft Share Point in your macro code
Using Excel VBA to create and fill monthly and 2-week calendars with data

Day-3

Creating and Managing Advanced Model and Analysis Problems with Excel VBA

Using ActiveX controls, Data Validation, and names on the worksheet to layout your problems
Using userform UIs to control problem inputs on single and multiple worksheets
Using VBA to manage cell and range names
Using Excel VBA to create and edit existing problem formulas based on UI selections
Using loops and logic to collect problem information, calculate it, and output it to worksheets
Creating upload and download macros that read and store your problem information in external workbooks
Using VBA to create worksheets from templates and tie their formulas / information back into the problem

Advanced Report Generation Using Excel VBA

How to use VBA to add report workbooks / worksheets, name them, and track them
Using macros to create and track report worksheets from pre-made templates
Using cell names in your macro code to track worksheet output areas
Designing loops and logic to build report worksheets like revenue / expense, various balances, inventory...
Using macros to format, perform calculations, and build formulas on your report worksheets
Designing loops and logic to fill in worksheet forms

Using SQL, ADO, and UIs to Query / Integrate 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 workbook table or text file and return a recordset to the worksheet
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
Using worksheet and userform based UIs to write SQL, control ADO, and display recordset results

Report Generation Using Word, PowerPoint, Outlook, and Excel VBA

Why use Excel VBA to automate PowerPoint, Word, and Outlook?
The basics of automating Word
The basics of automating PowerPoint
Using Excel VBA to send and read emails in Outlook

If you need to contact us about our class.

Phone Number: 1.805.498.7162

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

You can email us at info@emagenit.com >

Advanced Excel VBA for Business and Industry

Get ready to build the hi-tech apps they said you couldn't in Excel.