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
Combining VBA with Excel's data tools to rapidly process large worksheet datasets
Building loops and logic to analyze advanced worksheet data patterns
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 VBA, SQL, ADO, and Power Query to integrate database, workbook, and text file data
Using VBA to build, format, and layout charts on worksheets (chart reports)

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

Tracking Objects

*This section discussed throughout the class when needed

How to use the VBA Editor, modules, and debugger to develop data apps
Review of the VBA elements and Excel objects used in data app development
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 track other program objects
Creating VBA procedures that run at a specific time

Tracking Data Ranges, Managing Formulas, and Using Functions in VBA

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

Scanning for Data and Controlling Workbooks / Folders with VBA

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 Excel 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 Excel's Data Tools and VBA to Rapidly Process Worksheet Data

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

Analyzing Simple and Advanced Worksheet Data Patterns with VBA

Analyzing worksheet data using loops, logic, and various range commands (Range, Cells...)
How to use worksheet formats, breaks, fonts... as logic triggers when analyzing advanced datasets
How to scan stacked, indented, broken... worksheet tables in Excel VBA
Using VBA to find steady state, max, min, limits, increments, time... values in sheet data
Finding and reading data values from specific worksheet columns / rows and generating output tables
Processing worksheet data and outputting it to multiple report sheets using VBA

Day-2

Formatting and Filling Worksheet Tables and Forms with VBA

How to use VBA to insert, delete, and move worksheet table rows and columns
Using Excel VBA to format simple and advanced 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