Excel VBA Data Processing for Business and Industry

How our class will boost your Excel VBA skills.

Our 2-day class focuses on the key Excel VBA topics needed to format, calculate, chart, and report various types of data.

It covers the essentials like using VBA to track worksheet ranges; calculate data; manage formulas; and perform table lookups. Also covered is how to scan for data in external workbooks and folders and automate Excel's data tools to process large worksheet datasets.

In addition, it shows how to analyze complex worksheet data patterns; fill forms; and create chart, status, summary tables... from raw data.

It also shows how to query databases; control text files; create multi-chart reports; and visualize data using VBA, charts, shapes, and worksheet ranges.

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

See what you can learn.

Learn to use Excel VBA and worksheet functions to perform single and multi-table lookups and scan for data on worksheet.

Learn to use Excel VBA to create file pickers, track ranges, control formulas, sum data, and scan datasets for max, mins...

Learn to use Excel VBA to loop through folders and files, consolidate data, build and format tables, and parse and calculate text files.

Learn to quickly create user interfaces from worksheets, userforms, ActiveX controls, and shapes to control your various data tools.

Learn to use VBA to control Excel's data tools, use their data, control table columns and rows, and control worksheets and workbooks.

Learn to use Excel VBA to scan irregular data tables, fill in premade forms and worksheet templates with data, and flag data limits.

Learn to use Excel VBA to read stacked data, reassign existing chart data sets, and update chart report templates.

Learn to use VBA, SQL, and ADO to control databases; query workbooks and text files; and import data rapidy to the worksheet.

Learn to use Excel VBA to scan workbooks for data, create summary tables, create and format charts, and arrange them on a report worksheet.

Review of the critical Excel objects VBA language elements used in data tool development
How to track ranges, control formulas, and run data functions in VBA
Using Excel VBA to control workbooks, folders, and files
Building loops and logic to scan for data in multiple workbooks and folders
How to design user interfaces on the worksheet to control your data tools
Automating Excel's data tools and PivotTables to rapidly process large worksheet datasets
Building loops and logic to process financial, inventory, stacked / broken, grouped... worksheet data
Performing multi-table lookups and calculations on your worksheet data with Excel VBA
How to use VBA to create, fill, calculate, and format various report tables and forms
How to open, process, write, and close text files
Using VBA to build, format, and layout charts on worksheets (chart reports)
How to combine VBA, Charts, and shapes to make dashboards
Using VBA, SQL, ADO, and Power Query to integrate database, workbook, and text file data

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

Excel VBA Editor, Language, Object, and Debugging Topics

*This section discussed throughout the class when needed

How to use the VBA Editor and its debugging tools in Excel data app design
Review of the VBA elements and Excel objects used in data app development
How to design logic for your data processing tasks
Using object expressions and Set statements in your code to track objects
Using CreateObject and GetObject to track other program objects in your code
How to use OnTime to run procedures at a specific time

Tracking Ranges, Calculating Data, and Managing Formulas with VBA

Tracking worksheet data ranges in VBA using Range, Cells, CurrentRegion, Find...
Performing math, statistical... operations in your code using SUMIFS, STDEV.S, COUNTA...
Determining range start / stop points in datasets and using them in function calculations
Performing date time operations using functions like Month, Year, DateDiff, Format... in VBA
Using macros to build and auto fill formulas down or across worksheet tables
Using Excel VBA to build formulas and place calculated values below or beside worksheet tables

Scanning for Data and Controlling Workbooks / Folders with VBA

Controlling workbooks in your code using Open, Add, Close, SaveAs...
Using the Set statement in your VBA code to track multiple workbooks at once
How to control the Windows folder and file system with VBA (create folders, delete files...)
Scanning for data in folders, workbooks, and text files using loops, logic, and functions
How to name your data workbooks and text files so they can be easily tracked in VBA
Using MATCH, COUNTA, Cells, Find... in your code to locate data on a sheet

Designing UIs to Control Your Excel VBA Data Tools

Laying out and formatting your data tool interface on the worksheet
Using the FileDialog object to construct file and folder pickers for your data tools
How to control inputs in your UIs with Data Validation and ActiveX controls
How to use Excel VBA to set and read ActiveX controls and Data Validation
Creating program displays with shapes and Excel VBA
Using userforms, buttons, and VBA to design a toolbar to run your data tools

Rapid Report Generation Using Excel's Data Tools and VBA

Using Excel VBA to automate AutoFilter, Advanced Filter, Sort, PivotTables...
Using macros to copy and paste filtered data to a report worksheet
Using VBA to parse a report name list and create report worksheets in a specific order
Looping Excel's filters and stacking the data on multiple report worksheets
Using loops and logic to build and stack PivotTables on report worksheets
Controlling Excel tables, their formulas, data features, and slicers with Excel VBA

Using VBA to Analyze Complex Worksheet Data Patterns

Using VBA. to analyze and calculate complex downloads like Bank, inventory, stacked... tables
Using cell formats, blanks, indents, fonts... as logic triggers when analyzing sheet data
Parsing and cleaning corrupted data in VBA using functions like Mid, Instr, CLEAN, TRIM...
Inserting formulas and calculated values at specific rows or columns in downloaded data sets
Using Excel VBA to lookup up data in worksheet tables and perform calculations
Building summary tables and reorganizing data on different sheets with VBA

Day-2

Using VBA to Create, Format, and Fill Worksheet Tables and Forms

Inserting, deleting, and moving worksheet table rows and columns with VBA
Using Excel VBA to format various worksheet table patterns
Rules for designing pre-built worksheet report templates (naming cells, marking start cells...)
Using VBA to read data and fill out and calculate premade worksheet forms and templates
Creating summary report tables like revenue / expenses, human resources... with Excel VBA
How to use Excel VBA to add formulas or output data to table rows and columns
Using VBA to consolidate data from multiple workbooks and create data tables for charts

Using VBA, Charts, and Shapes to Create Basic Dashboards

Using Excel VBA and templates to create various chart types like doughnut, line, combo charts....
Resetting a chart's data source to a range or array with VBA
Arranging multiple charts on a worksheet with Excel VBA (chart report)
Creating chart limit lines, curve fits, data labels... with VBA
Using Excel VBA to color data points, hide / display series...
Combining VBA, Charts, and shapes to form basic dashboards and data displays

Analyzing and Controlling Text Files with Excel VBA

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

Using ADO and SQL to Query Databases, Workbooks, and Text Files

Review of ADO and how it can be used to control different data sources in Excel VBA
Basic SQL language review and how to use it to query, update, insert, and delete database data
Using string concatenation in your VBA code to assemble SQL statements and connection strings
Using ADO objects in VBA to query databases, workbooks, and text files
How to use Power Query, Excel tables, and VBA to quickly integrate and filter data
Generating VBA code to control Power Query with Record Macro

Using VBA and Outlook to Auto Email Your Reports

Key Microsoft Outlook objects to know
Attaching an Excel file or text file to an email using VBA
Loading email recipient, subject, body text... using Excel VBA
Using Excel VBA to automatically send your email from Outlook

Additional Class Information

Prerequisites for our Excel VBA 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, 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: 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.805.498.7162

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

You can email us at info@emagenit.com >

Form >

Excel VBA Data Processing for Business and Industry - Live Class