Excel VBA Data Processing Class for Business and Industry

Next Class: 9/28/2022

More Dates >

Key Excel VBA topics covered in class.

• Review of the VBA Editor, Excel VBA language, and the debugger
• Using functions and VBA to rapidly calculate dataset data and build / fill formulas
• Using Excel VBA to control data / report workbooks and organize them in folders
• Building loops and logic to find, format, and shape (i.e., insert, delete...columns) datasets on multiple worksheets
• Using Excel's data tools, PivotTables, and VBA loops to rapidly process large datasets
• Building adaptive loops and logic to process financial, inventory, stacked / broken, grouped... datasets
• Performing multi-table lookups and calculations in your datasets using Excel VBA
• How to design effective UIs on the worksheet to control your data tools and email your reports
• How to use VBA to build, fill, calculate, and format various report tables and forms
• Using VBA to build / format report grade charts and layout multiple charts on a worksheet
• How to open, process, write, and close text files
• Using VBA, Power Query, and ADO to integrate database, workbook, and text file data
• How to scan and process data stored in folders, open workbooks, and on multiple worksheets

Click to enlarge, scroll < > if hidden.

Pricing and class options.

Detailed class syllabus.

Day-1

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

• Review of using the VBA Editor windows and debugging tools
• VBA language review including variables, data types, constants, arrays, operators, expressions, loops, logic decisions, functions, and calling conventions
• Designing logic for your data processing tasks
• Design strategies for creating and using object expressions to track objects in your code
• Using the Set statement, CreateObject, and GetObject to track Excel objects as well as other program objects in your code
• How to create error handlers and the logic to handle run-time errors in your macro code

Using VBA and Functions to Rapidly Calculate Data and Build / Fill Formulas

• Dynamically tracking worksheet data ranges in your macro code using MATCH, COUNTA, Range, Cells, Columns, Rows, CurrentRegion...
• Performing math and statistical operations in your code using functions like SUM, MAX, MIN, SUMIFS, AVERAGE, COUNTA...
• Performing date time operations in your code using functions like Month, Year, DateDiff, DatePart, Format, NETWORKDAYS, EOMONTH...
• Using macros to build and auto fill formulas down or across worksheet datasets
• Using Excel VBA to build formulas or place calculated values below or beside datasets
• Automating the Analysis Toolpak to analyze worksheet datasets

Controlling Workbooks and Organizing Them in Folders with Excel VBA

• The benefits of making and managing your own folder system to store program workbooks, text files, documents...
• Controlling workbooks in your code using Open, Close, Save, SaveAs, and Add
• Using the Set statement in Excel VBA to track multiple workbooks at once in your code
• Controlling the Windows folder / file system in your code using FileSystemObject, File, Folder...
• How to test for folder / file existence in your macro code using FolderExists, FileExists...
• How to use concatenation in your code to build folder paths and filenames for your report / data workbooks

Using Excel VBA to Dynamically Shape, Format, and Find Resizing Worksheet Datasets

• How to dynamically track range data size, position, headers, and data subsets using MATCH, Range, Cells, Find, CurrentRegion, Address...
• Using Excel VBA to dynamically format resizing dataset fonts, cell colors, number formats, alignments, borders...
• Using macros to automatically insert, delete, and move dataset cells, rows, and columns
• How to build and loop worksheet tables that guide VBA on what rows / columns to delete, move, and insert
• Using loops and logic to find and format multiple datasets on multiple worksheets

Using Excel's Data Tools with VBA Loops to Rapidly Process Large Datasets

• How to dynamically track ranges, headers, and position using MATCH, Range, Cells, CurrentRegion, Find, Address...
• Using Excel VBA to automate AutoFilter, Advanced Filter, Sort, PivotTables, and Remove Duplicates
• Using macros to copy and paste filtered data to a report worksheet
• Generating a report name list from a worksheet dataset using Remove Duplicates, Sort, and VBA
• Looping through a report name list and adding report worksheets to a report workbook in a specific order
• Looping Excel's filters in your code and stacking the data on specific report worksheets
• Using loops and logic to build and stack PivotTables on the worksheet

Building Adaptive Loops and Logic to Process Complex Worksheet Datasets

• Overview of using loops, logic, functions, counters, Cells, Range, Offset... to build adaptable data processing programs
• Using cell formats and data patterns as logic triggers when processing data
• Using loops and logic to process datasets like financial downloads, inventory, stacked / broken tables, multi-column grouped data...
• Detecting and inserting different formulas in worksheet dataset breaks (i.e., text) using Excel VBA
• Performing worksheet table lookups in your code using VLOOKUP, SUMIFS,  MATCH, Cells, Offset, Find...
• Using loops and logic to color data points based on worksheet table lookups
• Using Excel VBA to lookup up data in multiple worksheet tables and perform calculations
• Using loops and logic to reorganize worksheet datasets into different configurations

Day-2

Creating, Filling, and Formatting Report Tables and Forms Using Excel VBA

• Using  MATCH,COUNTIF, COUNTA, Find, CurrentRegion... to dynamically find worksheet table position, headers, size, end rows...
• Using Excel VBA to create summary report tables "on the fly" like inventory, human resources...
• Building loops and logic to fill in various data patterns like stacked tables, 2-d tables, multi-column grouped data...
• Automatically formatting various data patterns on report worksheets including data subsets
• Rules for designing pre-built worksheet report templates to ease code complexity, mark report table start cells
• Using loops and logic to fill out and calculate premade worksheet forms

Designing Effective UIs to Control Your Excel VBA Data Tools and Email Reports

• Using Data Validation, shapes, pictures, and VBA to quickly build the interfaces that control your Excel VBA data tools
• Using the FileDialog object in VBA to construct file and folder pickers for your data tools
• Using shapes and VBA to create hi-tech status displays, path displays, and large check boxes
• Integrating your UI selections in your code using logic
• Creating a basic Outlook macro in Excel VBA to automatically mail your reports 

Creating Professional Chart Reports with Excel VBA

• Using Excel VBA and templates to create various chart types like pie, line, combo charts....
• Using macros to add chart elements like data labels and gridlines
• How to use macros to format charts including coloring data points and hiding / displaying series
• How to use VBA arrays to create a chart series, a must know for emailed chart reports
• Using macros to create vertical / horizontal chart limit lines, curve fits, custom label text, position labels...
• Using Excel VBA to create and align multiple charts on a report worksheet

Using Excel VBA to Open, Process, and Close Text Files

• Automating Text Wizard to parse and load text data directly onto a worksheet
• How to open, close, write, and modify text files using the FileSystemObject and TextStream objects
• Using loops, logic, arrays, and functions to scan and analyze text file data and output the results to an Excel worksheet
• Cleaning and parsing corrupted text files using VBA, functions, the worksheet, and Text to Columns
• Using loops, logic, and string concatenation to assemble text data and write it to a text file

Large Scale Multi-Workbook, Worksheet, and Text File Processing Using VBA

• Scanning open workbooks and worksheets for specific datasets using For...Each Next loops, logic, and functions
• Scanning folders for specific data workbooks and text files using For...Each Next loops, logic, and functions
• Designing scalable procedures that adapt to data in n...workbooks, worksheets, and text files

Using VBA, Power Query, ADO to Integrate Database, Workbook, and Text File Data

• Review of ADO and how it can be used to query different data sources in Excel VBA tying them together
• Basic SQL language review and how it is used to query, update, insert, and delete database data
• How to use ADO to query and control databases like SQL Server, Oracle, and Microsoft Access
• How to use ADO and SQL to query and process data in workbooks and text files
• Using string concatenation to assemble SQL statements and ADO connection strings in your VBA code
• Design methods for using Power Query, Excel tables, and VBA to quickly integrate and filter data from different sources
• Using VBA and Record Macro to control Power Query and Excel table settings

Excel VBA skills needed for our class.

The class details.

• Audience:   Business, government, industry, military,... general audience
• Duration:   8 hrs
• # of Days:   2-days
• Instruction Type:   Hands-on, live, instructor taught training
• Full Excel Manual Included:   Our 2200 + page manual full of Excel tips, how-to-do topics and Excel examples included with the training. Details >
• Excel Versions:   2010-2019, Microsoft 365, Windows and Mac
• Free Repeats:   Repeat your Excel training. Details >
• Personal Examples:   We review our attendees personal examples in our training instruction
• Available Training Formats:
  • In Person Training >
  • Live Virtual Learning >
  • Company Onsite >

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 >

Contact us by form.