Excel VBA Data Analysis for Engineers and Scientists Class

Next Class: 01/25/2023

More Dates >

See the must know topics you will learn.

No time to attend, get a manual instead.

Key Excel VBA topics covered 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

 

Class examples.

Pricing and class options.

Detailed class syllabus.

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
• 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 

Excel VBA skills needed for the class.

The class details.

• Audience:   Engineers, scientists and technicians
• Duration:   8 hrs
• # of Days:   2-days
• Instruction Type:   Hands-on, live, instructor taught training
• Full Excel Manual Included:   Our 2200 + page Microsoft Excel Aided Engineering and Science manual full of Excel tips, how-to-do topics and Excel examples included with the training. Details >
• Excel Versions:   2010-2019, Microsoft 365, Windows and Mac
• Repeats:   Repeat your Excel training. Details >
• Personal Examples:   We review our attendees personal examples in our training instruction
• Available Training Formats:
  • In Person Training >
  • Live Virtual Learning >
  • Company Onsite >

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

Contact us by form.