Advanced Excel VBA Training

Detailed training syllabus.

How we run the class: We focus our advanced Excel VBA training on what our customers need. When training begins, we analyze those needs and shift our advanced Excel VBA 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

Excel VBA Language, Object, and Error Trap Review (Discussed Throughout Class)

A review of all major VBA programming elements including variables, data types, constants, arrays, operators, expressions, loops, logic decisions, functions, and calling conventions where needed
Review of an object, property, and method and how are they used in Excel VBA programming
Advanced ways to use the debugger when debugging your VBA code
Syntax rules used in VBA code to identify an object and run its properties and methods
Advanced strategies for handling run-time errors in your code using error handlers and logic
Advanced strategies for using the Set statement to track objects in your VBA code
How to create objects in Excel using Class modules, Subs, Functions, Properties, and public variables
How to use the CreateObject and GetObject VBA functions in your code to access other class libraries and programs

Processing and Formatting Advanced Data Sets on the Worksheet

Review of basic Excel VBA data processing methods, loops, and logic
Review of using worksheet and VBA functions in your VBA code to process worksheet data
Review of table design on the worksheet for large scale data storage
Review of using the Cells, Range, Rows, Columns, CurrentRegion, SpecialCells, and Find properties to lock onto complex worksheet data configurations
How to use VBA to parse, interpret, and fix worksheet text data using the TRIM, CLEAN, Left, Right, Mid, Replace, Instr, Split... functions
Parsing and merging date / time values in your VBA code using the Day, Month, Year, DateDiff, DateAdd, DatePart, Weekday, Format, WEEKNUM, NETWORKDAYS, EOMONTH... functions
How to use VBA loops, logic, and functions to analyze complex worksheet table patterns like stacked tables, table start / stop points, fragmented tables, indented tables, double row header tables...
Scanning for and deleting multiple rows and columns from worksheet tables using advanced Excel VBA strategies
VBA techniques for inserting / deleting data and formulas in worksheet table columns and rows
How to use VBA to lock onto various worksheet data configurations and use them in your code with the statistical, math, and lookup worksheet functions
VBA strategies for finding max, min... and starting / end points in complex data sets
Using Excel VBA to build summary tables from complex data patterns and perform advanced table lookups
Importing a worksheet range into VBA as an array to speed up your processing time

Using VBA to Create Advanced Excel Charts and Chart Report Sheets

Review of how to create chart sheets and embedded charts using Excel VBA
Review of advanced chart formatting methods using Excel VBA
How to use VBA For...Each Next loops to loop through series, legends and data points formatting them (make invisible, color a specific point, color points that are negative, grey out...)
Using Excel VBA to add, edit and delete an existing chart's data series
How to use Excel VBA to create a chart series using arrays instead of worksheet ranges, great for emailed reports
Creating combo charts using Excel VBA, ranges, and arrays
Creating chart limit lines and curve fits using Excel VBA
Combining worksheet cells, charts, and VBA to automatically arrange, align, and format multiple charts on a report worksheet

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

Why store data in multiple workbooks and use Excel VBA to integrate their data?
How to use Excel VBA to access and control the Windows folder system using the FileSystemObject
Using Excel VBA to add, open, save, close, and track workbooks
How to use VBA, functions, and string concatenation to create workbook names (dates, version...) that can be easily tracked in code
Scanning and tracking data in open workbooks using VBA For...Each Next loops, functions, and logic
Using Excel VBA loops and the Remove Duplicates tool to add, move, and rename worksheets in advanced data analysis apps
Advanced Excel VBA looping strategies for processing data in multiple workbooks / worksheets and text files

Advanced Excel VBA Methods for Controlling Reports and Analysis Problems

Review of analysis problem layout on the worksheet, interface design, cell naming strategies, and using VBA to control it all
How to use Excel VBA to control a cell's Data Validation settings
Review of how to use the Range, Cells, Columns, Rows, CurrentRegion, SpecialCells, Offset, and Find properties and methods to identify cells and ranges in VBA
How to use cell names, loops, and logic in Excel VBA to track various output areas on a report worksheet and add data to them
Using Excel VBA to automatically build worksheet reports like revenue / expense reports and create the formulas for calculations like variance...
How to use VBA loops to build parameter upload, download, and batch processing procedures for analysis problems
How to use Excel VBA to copy/paste a worksheet formulas to a new worksheet, adjust its formulas, and adjust the formulas on other sheets to account for the new sheet addition

Day-2

Developing Advanced User Interfaces (UI's) With VBA Userforms, ActiveX Controls, Images, and Events

What is a VBA userform and how to use them in your different Excel user interfaces?
What is the difference between a modal and non-modal userform in VBA?
How to create and layout ActiveX controls and pictures on userforms
How to create event procedures for ActiveX controls on userforms
Controlling text boxes, scroll bars, spin buttons, check boxes, option buttons, and image controls on userforms with VBA
How to use VBA to control list boxes and combo boxes on userforms
Using ranges, VBA arrays, and other Excel data tools like Sort and Remove Duplicates in VBA to load data into list boxes and combo boxes
Using the ArrayList object in Excel VBA to collect and sort data from worksheets, text files, and databases for use in list boxes and combo boxes
VBA methods for formatting data inside of list boxes and combo boxes
How to read multi-column list boxes and combo boxes using Excel VBA
How to create multi-select list boxes on userforms and command them using VBA
How to create a list box on a userform in VBA that fills in another list box using events
Designing VBA procedures that launch and preset ActiveX controls values on userforms
How to create ActiveX control events in your Excel VBA code that trap keystrokes on a userform

Using the Ribbon, Custom Toolbars, File / Folder Pickers, and the Windows Registry In Your UI's

How to use Excel VBA to build a file / folder picker that steers VBA to the right file or folder to use
How to create a Ribbon tab, its controls, and assign them VBA procedures in an Excel workbook file
Using userforms to quickly create floating toolbars that run your Excel apps
How to use the OnTime and OnKey methods in VBA to create procedures that run at a specific time or by pressing shortcut keys
How to use the Run method in VBA to run VBA procedures in other workbooks, add-ins, or call by string
How to store and retrieve program information in the Windows Registry

Developing Advanced Forms and Project Status Tables Using VBA, ActiveX Controls, Data Validation, and Worksheet Events

Creating advanced event driven forms on the worksheet using worksheet VBA events that react to user entry, double clicking, right-mouse clicking...
Writing Excel VBA logic to control worksheet event driven calculations and when they are implemented
Using the Range, Cells, and Offset properties along with event target cells to determine where to output calculations on a worksheet form
Using Excel VBA to automatically create and manage a project status table (i.e. Project vs. Region) on the worksheet
Looking up headers and rows in worksheet status tables using the MATCH and COUNITIF worksheet function in VBA
Writing Excel VBA loops and logic to process data from multiple workbooks and load them into worksheet status tables

Creating Gantt Charts and Calendar Style Reports Using Shapes, the Worksheet, ActiveX Controls, and Excel VBA

Creating UI's with ActiveX combo boxes, spinners, scroll bars, and VBA to control what data is viewed in a Gantt chart and calendar
Creating UI's with Data Validation and VBA to control what data is viewed in a Gantt chart and calendar
VBA coding strategies for creating times, dates, months, years... in specific worksheet patterns
Using VBA to automatically format calendar layouts and timelines on the worksheet
Using string concatenation with the Excel and VBA date functions to assemble dates, months, years, days... from data
Looking up dates and times on the worksheet using the MATCH and COUNITIF worksheet function in VBA
Looking up dates and times on the worksheet using the Find method, and the Cells, Row, and Column properties in VBA (alt method)
Using shapes and Excel VBA to build interactive project timelines on the worksheet
Assigning VBA procedures to shapes on the fly, figuring out what shape was clicked to run a procedure
Using VBA loops, logic, and functions to quickly fill in data in worksheet based calendars and timelines

Combining the Worksheet, ActiveX Controls, and Data Validation With VBA to Construct Automated Dashboards

When to make a new chart or use an existing chart in Excel VBA controlled dashboards?
Using Excel VBA to create categories from data to display in dashboard ActiveX combo boxes and Data Validation cell drop downs
Creating UI's with ActiveX combo boxes, spinners, scroll bars, and VBA to control what data is viewed in a dashboard chart
Creating UI's with Data Validation and VBA to control what data is viewed in a dashboard chart
Using Excel VBA and ActiveX control selections to extract data subsets from data to display in a chart dashboard
Using Excel VBA and ActiveX control selections to scan multiple workbooks for data and assemble it into ranges or arrays that can be used by dashboard charts
Using the workbook Open event to preload dashboard interfaces when a workbook opens

Day-3

Rapid Data Report Construction by Automating Excel's Data Tools With Excel VBA

Advanced VBA looping and logic strategies for quickly turning Autofilter, Sort, Remove Duplicates, and Advanced Filter into large scale data processing tools
Using Excel VBA to build report workbooks, build report worksheets, and transfer filtered data
How to use Excel VBA to control Excel tables and refresh / change their queries
Creating and controlling PivotTables and PivotCharts with Excel VBA
How to automate the Analysis Toolpak with VBA

How to Use Excel VBA to Control Text Files and Integrate Their Data With Your Excel Tools

To automate Text Wizard or run VBA loops, which programming approach to use when integrating text file data with your Excel VBA programs?
The different text file extension types and how to access them using Excel VBA
Using Excel VBA to automate the Text Wizard tool to open and parse text file data onto a worksheet
Using Excel VBA to automate the Text to Columns tool to parse worksheet based text data
How to use VBA to open, read, write, and close text files using the TextStream object
How to use string concatenation and loops in VBA to assemble text that will be written to text files
How to use the VBA Do...Loop to access and load text file data onto a worksheet
Advanced Excel VBA methods for loading text file data on the worksheet using arrays
Cleaning, parsing, and reorganizing corrupted text file data using various functions, the worksheet, and the Excel Text to Columns tool in Excel VBA

How to Use SQL and ADO to Control and Query Data in Databases, Workbooks, and Text Files

SQL language review for querying, inserting, updating, and deleting database data
Building SQL statements and connection strings in VBA using string concatenation, key VBA functions, and loops
How to use ADO, SQL, and VBA to query and control a database
How to use ADO, SQL, and VBA to query a workbook and text file
Advance VBA methods for querying and combining workbook and text file data using SQL and ADO

Controlling Word, PowerPoint, and Outlook With VBA for Report Generation Purposes

Why does Excel VBA with PowerPoint, Word or Outlook make such a good report generation environment?
How to track and output report data to Word using Excel VBA
How to track and output report data to PowerPoint using Excel VBA
Using Excel VBA to automate Outlook to read incoming emails and send emails

Creating, Packaging, and Distributing Excel Add-Ins for Userform Based Excel Apps

Designing userform based apps to run from Excel Add-Ins
Designing Excel Add-Ins to run from the Ribbon or custom toolbars
Strategies for packaging and distributing Excel Add-Ins to users

Additonal Classes:

Free Repeats for Our Training

Learn More >

US Military and DOD Discounts