Excel VBA Data Analysis for Engineers / Scientists

How our class will boost your Excel VBA data skills.

Our 2-day class focuses on combining key Excel and VBA elements to develop various data tools. It covers the essentials like using VBA to track data ranges; control data formulas; and calculate / lookup data using a variety of functions. Also covered is scanning for data in workbooks and folders and automating Excel's data tools to quickly process and report large datasets.

In addition, it covers how to create reports from various dataset types; fill in forms; and create chart, status, timeline, and summary tables from raw data. Also discussed is how to access text files; process date / times; command databases; create chart reports, and make visual displays using charts, VBA, and shapes.

It even shows how to automatically email reports using VBA and Outlook.

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

What you'll learn in our class.

Learn to use Excel VBA and functions to perform lookups, find data, generate report workbooks, and build and format report tables.

Learn to use Excel VBA to track ranges, control formulas, scan datasets for max, mins..., and create movable charts that can be dismissed.

Learn to use VBA to loop through folders and files, consolidate data, build and position charts on a sheet, and save to custom built folders.

Learn to create user interfaces and use VBA to batch process text files, parse dates/times, color chart data points, create limit lines.

Learn to use VBA to control Excel's data tools, use their data, control table columns and rows, fill in data, and build chart control lines.

Learn to use Excel VBA to read irregular data tables, perform interpolation, and fill in premade forms and worksheet templates with data.

Learn to use Excel VBA to read stacked data, load multiple tables into charts as arrays, and interact ActiveX / Charts to create visual displays.

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

Learn to combine worksheets, shapes, charts, and Excel VBA to quickly create status and visual displays for your data.

Review of the critical Excel VBA language elements and objects used to develop data tools
How to track ranges, control formulas, and run data analysis functions in VBA
Controlling and scanning folders, workbooks, and worksheets for data with VBA
How to build UIs for your data tools using VBA, ActiveX, Data Validation, and the worksheet
Using VBA with Excel's data tools to rapidly process large data sets (PivotTables, AutoFilter...)
Using VBA to generate reports and tables from various worksheet dataset types
How to shape (insert, delete, move… rows/columns) and format worksheet tables
Using Excel VBA to create and fill timelines, summary tables, status tables...
How to fill forms, lookup data, and interpolate with Excel VBA
How to use VBA to process text data and create, write, and modify text files
Using SQL and ADO to control databases and query text file and workbook data
How to use VBA to create chart reports and visualize data using charts, worksheets, and shapes
Auto emailing your data reports with Microsoft Outlook and VBA

Some key topics we cover in class.

4 ways to train.

Live Virtual

View the dates / signup for our hands-on live virtual training.
Virtual Signup > Learn More >

Public In-Person

View the dates / signup for our hands-on public training in Nashville, TN.
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.

Download Class Brochure

Day-1

Excel VBA Editor, Language, and Module Topics

*This section discussed throughout the class when needed

How to use the VBA Editor, modules, and debugger to develop data apps
Review of the critical Excel VBA language elements and objects used to develop data tools
Constructing error handlers and logic to handle run-time errors in your apps
How to use object expressions and Set statements in your VBA code to track objects
How to use CreateObject and GetObject to control other programs libraries
Creating VBA procedures that run at a specific time

Tracking Data Ranges, Managing Formulas, and Using Functions in Your Data Tools

How to find and track data ranges, headers, and data subsets in your VBA code
Using the math, trig, and statistical functions (SUMIFS, STDEV.S, TAN...) in your code to calculate data
Determining start / end dates; adding dates; parsing date / time elements... with VBA and functions
Determining range start / stop points in data sets and using them in function calculations
Using VBA to create, edit, and fill formulas on the worksheet
Using Excel VBA to track, create, delete, and modify range and cell names

Controlling and Scanning Folders, Workbooks, and Worksheets for Data

Design and naming strategies for storing data in multiple workbooks / folders
Controlling workbooks and worksheets (Open, Save, Name, Delete...) with Excel VBA
Using the FileSystemObject to create, move, delete... Windows folders and files
Using VBA to store and retrieve workbook data in a high demand, multi-user environment
Scanning folders, workbooks, and worksheets for data using loops, logic, and functions
Using MATCH, COUNTA, Cells, Find... in your code to locate data on a sheet

Designing Worksheet Interfaces (UIs) for Your Data Tools

Laying out user interfaces (UIs) on the worksheet to control your data tools
Using the FileDialog object to construct file and folder pickers for your data
How to make a simple toolbar to run your data tools 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
Using shapes and VBA to build program status and data displays

Using VBA to Process Large Data Sets Using Excel's Data Tools

Automating AutoFilter, Sort, Remove Duplicates, the Analysis Toolpak... with Excel VBA
Using VBA to generate and track multiple PivotTables on a worksheet and filter them
How to use logic and concatenation to build logic for Excel's filters inside loops
Using Excel's data filters inside loops to process large data sets with multiple conditions
Creating multiple report worksheets based on data groups and pasting filtered data to them
Controlling Excel tables, their formulas, data features, and slicers with Excel VBA

Using VBA to Generate Reports and Tables from Various Dataset Types

Using loops, logic, and various range commands (Range, Cells...) to scan and analyze datasets
How to use worksheet formats, data breaks... as logic triggers when analyzing complex data sets
How to scan stacked tables, indented tables, broken tables... using Excel VBA
Using VBA to find data values like steady state, max, min, limit violations, increments, time...
Finding and reading data values from specific columns / rows and generating output tables
Outputting processed worksheet data to multiple report worksheets / workbooks with VBA

Day-2

Formatting and Filling Report Tables and Forms with Excel VBA

How to use VBA to insert, delete, and move worksheet table rows and columns
Using Excel VBA to format simple and complex worksheet table patterns
Automatically filling worksheet-based forms using Excel VBA
Using Excel VBA to create and fill chart, status, timeline, and summary tables
Using VLOOKUP, XLOOKUP... to lookup table data and perform interpolation
How to use Excel VBA to add formulas or output data to table rows and columns

Controlling Text Files and Processing Text Data with Excel VBA

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

Using VBA, SQL, and ADO to Query Data and Control Databases

Basic SQL language review
How to write an SQL statement and connection string in Excel VBA
Using VBA and concatenation to create an SQL string
How to use VBA, ADO, and SQL to update, insert, and delete database data
Using VBA, ADO, and SQL to query workbook, text file, and database data
Automating Power Query to quickly integrate and filter data from different sources

Visualizing Data Using VBA, Charts, and Shapes

How to use Excel VBA to create and format Excel charts (xy scatter, column, pie, combo...)
How to use Excel VBA to arrange multiple charts on a worksheet (chart report)
Using Excel VBA to reset a chart's data source to a range or array
How to use VBA to create chart limit lines, curve fits, alter data labels...
Using Excel VBA to color data points, hide / display series...
How to combine VBA, Charts, and shapes to form data displays

Auto Emailing Your Data Reports with Outlook and VBA

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