Excel VBA Data Processing 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 2-day class shows you how to combine Excel's "built-in" data abilities with VBA to build powerful data processing tools.

It focuses on the key Excel VBA topics needed to format, calculate, reorganize, chart, and report various types of data.

Also covered is how to create high-tech reporting tools that can read multiple data sources like databases, text files, and workbooks; scan multiple folders and drives; and then integrate and process the data.

It will also show you how to use Excel VBA to process stacked non-standard worksheet and text file datasets and how to use VBA / Outlook to automatically email reports to your associates.

Join us and our class will show you how to swiftly crunch your data using Excel and VBA.

Some key topics we cover 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

Prerequisites for our Excel training.

Used variables, loops, functions, declarations... in a basic capacity in VBA
Used and written basic logic in VBA
Formatted worksheets and know Excel's basic formatting capabilities
Used Microsoft Excel's data tools manually and know them operationally
Built charts and drawing shapes manually before in Microsoft Excel
Have used Excel's worksheet functions like MATCH, COUNTIF, SUMIF, VLOOKUP...

Class time, duration, manual, and repeats.

Audience: Business, government, industry, military, engineering, science, techs...
Duration: 9:00 - 5:00 CT
# of Days: 2-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

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

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 >

Excel VBA Data Processing for Business and Industry

Make that monthly report in about 20 secs at the click of a button.