Excel VBA Data Analysis for Engineers / Scientists Class

Class overview.

What makes our training different?

Live, hands-on, industry topics
Bring your problems, ask tough questions
Taught by an engineer
Limited class size

Our 2-day class shows you how to couple Excel's "off-the-shelf" data capabilities with VBA to build adaptive scalable data tools for engineering and science. It focuses on using key Excel / VBA elements together to rapidly calculate, reorganize, format, chart, and report various types of engineering and science data.

It also demonstrates how to create high-powered report tools that process, integrate, and chart data stored in multiple folders, databases, text files, and workbooks.

Also discussed is how to use VBA to read stacked non-standard worksheet / text file datasets and how to automatically email reports to your associates using VBA and Outlook. Join us and we will show you all you need to know to turn Excel and VBA into a rapid data processing machine that meets your specific needs.

Some key topics we cover in class.

Review of the VBA Editor, its debug tools, and the Excel VBA language
How to use worksheet and VBA functions in your macro code to analyze and calculate engineering / science data
Automating and looping Excel's data tools (PivotTables, AutoFilter, Analysis Toolpak...) for large scale data processing
Controlling the Windows folder system with VBA to store reports and read data
Building adaptive loops and logic to analyze engineering / science worksheet datasets
Using VBA to build, fill-in, shape, format, and calculate various engineering / science report tables
How to use Excel VBA to perform various table lookups and interpolation
How to use VBA to create, write, and modify text files
Using SQL, ADO, and VBA to control and integrate text files, databases, and workbooks
Using VBA and Excel's Query Tools to rapidly filter and integrate data
Large scale multi-workbook, worksheet, and text file processing using VBA
Automating engineering / science chart reports with Excel VBA
How to design worksheet based user interfaces (UIs) to control your data tools and email your reports

Select this Excel VBA training if you or your group have:

Used Excel VBA in a basic manner before
Used Microsoft Excel's data tools manually and know them operationally
Formatted worksheets manually and know Excel's basic formatting capabilities
Built charts and drawing shapes manually before in Excel
Used Excel's worksheet functions like MATCH, COUNTIF, SUMIF, VLOOKUP...

Class time, duration, manual, and repeats.

Audience: Engineers, scientists and technicians
Duration: 9:00 - 5:00 CT
# of Days: 2-days
Instruction Type: Live, hands-on, taught by engineer
Class Manual: Our complete Excel manual for engineering and science (EAE). 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 and Editor Review for Engineers and Scientists (Discussed Where Needed)

Excel VBA Editor and debugger review
Review of the VBA language including variables, data types, constants, arrays, operators, expressions, loops, logic decisions, functions, and calling conventions
Constructing error handlers and logic to handle run-time errors in your data analysis code
A review of Excel objects, properties, and methods and how to use them in your macro code
How to use object expressions and the Set statement in your macro code to track objects
How to use CreateObject and GetObject to control other programs libraries

Using Functions in VBA to Analyze and Calculate Engineering / Science Data

Full review of how to run Excel worksheet and VBA functions in your macro code
How to track ranges, headers, and data subsets using MATCH, COUNTA, Range, Cells, Find, CurrentRegion, Columns, Rows, Address...
Calculating math, trig, and statistical values in your macros using functions like Sum, Max, Min, ATAN, SIN, Sqr, STDEV.S, RANK...
Using logical functions in your code like SUMIFS, AVERAGEIFS, MAXIFS, MINIFS, COUNTIFS... to analyze data
Parsing and merging date time values in your code using functions like Year, Format, DateDiff, DatePart, NETWORKDAYS, EOMONTH...
Performing calculations on engineering / science tables and placing values or formulas below or beside them

Controlling Workbooks, Worksheets, Folders, and Files with Excel VBA

Design strategies for storing data in multiple workbooks and folders and using VBA to access them
Using the FileSystemObject to create, move, delete, rename, and access Windows folders and files
How to use Open, Add, Close, Save, and SaveAs in your code to control report and data workbooks
Design strategies for naming workbooks and folders for large scale data storage and access
Using Add, Delete, Move, Name, Protect, and Unprotect to control report worksheets in Excel VBA

Automating and Looping Excel's Data Tools for Large Scale Data Processing

How to automate AutoFilter, Sort, Remove Duplicates, and Advanced Filter using Excel VBA
How to use Record Macro and VBA to automate PivotTables and the Analysis Toolpak
Determining what statements to keep in the Record Macro code, replacing hard coded arguments, replacing active paths
Using Excel's data tools inside loops to create large scale data processing tools
Using macros to build filter logic on the fly for Excel's filters inside loops
Using macros to copy, paste, and stack filtered data on single and multiple report worksheets

Dynamically Shaping and Formatting Engineering / Science Worksheet Tables

How to dynamically track resizing worksheet tables in your code using Range, Cells, Find, Columns, Rows, Address, CurrentRegion...
How to dynamically track moving table headers in your code using MATCH, Find, Column, Row...
Creating rules on the worksheet to tell VBA what to do with report table columns, rows, formats...
How to use loops and logic to insert, delete, and move worksheet cells, rows, and columns
Using Excel VBA to lock onto and format simple and complex engineering / science worksheet tables

Building Loops and Logic to Analyze Complex Engineering / Science Datasets

Using Range, Cells, and Offset in your loops to access engineering and science worksheet data
Determining worksheet header positions, table size, and table position on the fly using MATCH, COUNTA, Find, Column, Row, Address...
How to use cell formats, cell values, data breaks in procedures as logic triggers when analyzing worksheet data
Using adaptive loops and logic to analyze standard and complex worksheet table patterns like stacked tables, fragmented tables, indented tables...
Using adaptive loops and logic to process worksheet data and find values like steady state, max, min, limit violations, increments, time...
Using macros to color cells and create formulas in engineering / science worksheet tables

Using Excel VBA to Build, Fill, and Lookup Data in Engineering / Science Report Tables

Using Range, Cells, Offset, CurrentRegion, Find, Match... to dynamically track worksheet tables, headers, position, and size
Building report tables by copying and pasting data rows and columns with VBA
Building report tables by filling in various data value patterns with VBA
Using COUNTIF, VLOOKUP, XLOOKUP,MATCH, HLOOKUP, and Cells in VBA to lookup table data and fill in rows / columns
How to perform interpolation in Excel VBA
Using macros to automatically build and fill existing report tables including status tables
How to use VBA to perform calculations and generate formulas in report table columns and rows

Day-2

Controlling and Processing Text Data with Excel VBA

Cleaning and parsing text in your Excel VBA code using Split, Mid, Replace, Instr, Format, CLEAN, TRIM...
Automating Text Wizard and Text to Columns to load and parse text data to an Excel worksheet
Using the FileSystemObject and TextStream objects in VBA to open, close, read, and write to text files
Writing Excel data to a text file using VBA loops and string concatenation
Using Do loops, logic, functions, and arrays to scan and analyze text file data and output the results to an Excel worksheet

Using VBA, SQL, and ADO to Control / Integrate Database, Workbook, and Text file Data

Basic SQL language review
How to write an SQL query statement and connection string in Excel VBA
How to use VBA, ADO, and SQL to update, insert, and delete database data
Using VBA, ADO, and SQL to quickly query data in workbooks, text files, and databases and return data to the worksheet
Using VBA and SQL to quickly integrate data from different data sources

Filtering and Integrating Data Using VBA and Excel's Query Tools

Design methods to use Power Query, Microsoft Query, Excel tables, and VBA to quickly integrate and filter data from different sources
Using Record Macro to record code that automates Excel tables, Power Query, and Microsoft Query
Determining what statements to keep in Record Macro code, replacing hard coded arguments, replacing active paths
Controlling query settings with Excel VBA

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

Scanning for an open workbook with a specific dataset using For...Each Next loops, logic, and functions
Scanning multiple worksheets for a specific dataset using For...Each Next loops, logic, and functions
Scanning folders for specific data workbooks using For...Each Next loops, logic, and functions
Scanning folders for specific text files using For...Each Next loops, logic, and functions
Designing scalable procedures that adapt to data in n...workbooks, worksheets, and text files

Building Automated Chart Reports with Excel VBA

Review of how to use VBA to create chart sheets and embedded worksheet charts
How to use Excel VBA to create and arrange multiple charts on a worksheet (chart report)
How to use macros to create engineering / science combo charts
Loading VBA arrays directly into a chart's series, a must know for emailed chart reports
How to use macros to create chart limit lines, curve fits, labels...
How to use VBA to format charts including coloring data points and hiding / displaying series

Designing Worksheet UIs to Control Your Data Tools and Email Your Reports

Laying out user interfaces (UIs) on the worksheet to control your Excel VBA data tools
Using the FileDialog object in VBA to construct file and folder pickers for your data
How to make a simple toolbar using a userform and buttons
Using Data Validation and ActiveX controls to control user entry on the worksheet
Controlling and reading ActiveX controls and Data Validation using Excel VBA
How to create a simple Outlook email macro to automatically mail your reports

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 Analysis for Engineers and Scientists

Turn Excel into a data processing machine and stop wasting days assembling engineering and science reports!