Excel VBA 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 leverage Excel VBA to build automated engineering and science tools that solve multi-step problems fast.

It covers essential topics like how to debug and use modules; how to use and type the VBA language; how to design programs; and how to use objects, properties, and methods in VBA to command key Excel elements.

Also discussed is how to perform a variety of key tasks with Excel VBA including automating calculations; controlling workbooks; building, formatting, and reorganizing tables; basic model control; automating chart reports; and processing worksheet / text file data.

Join us and our class will show you straight forward, industry proven techniques, which will allow you to get your Excel VBA projects up and running with minimal build time.

Some key topics we cover in class.

Complete review of the Excel VBA language, VBA Editor, debugging, procedures, and modules
Review of common Excel elements that engineers and scientists will want to automate with VBA
How to track, read, and write to various range and cell configurations in your Excel VBA code
Using Excel and VBA functions in your code to find, calculate, and lookup data
Controlling and calculating model and analysis problems with Excel VBA
Automating worksheet formula construction and repetitive calculations with Excel VBA
How to control workbooks, worksheets, and formats with Excel VBA
Using loops and logic to process and calculate worksheet data and text files
How to automatically build, fill in, and format report tables with Excel VBA
Automating Excel's data tools like Text Wizard, PivotTables, Sort, AutoFilter, Group...
Using Excel VBA to automatically build and format engineering and science charts
Designing basic user interfaces (UIs) to control your Excel tools
Constructing UDFs for calculation, numerical analysis, and data processing

Prerequisites for our Excel training.

Never programmed or not programmed in over a year
Just used the Record Macro tool to obtain VBA code
Programmed but have not used objects before or minimally
Used Microsoft Excel and know its operational capabilities
Built basic worksheet formulas (=A1+A2) and used worksheet functions like SUM
Used Excel data processing tools like Sort, Autofilter, Advanced Filter, Group, and Remove Duplicates
Built charts manually

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, VBA Editor, and Debugging Review for Engineers and Scientists (Discussed Where Needed)

A complete review of the VBA Editor windows, toolbars, and debugger and how they are used in engineering and science problems
A complete review of VBA modules, Sub procedures, Function procedures, statements, and syntax typing rules
A complete review of how to use data types, variables, constants, arrays, operators, expressions, loops, logic decisions, calling conventions, and argument lists in your code
How to write logic in VBA using <, >, <=, >=, <>, =, And, Or, Not, and various functions
Overview of objects, properties, and methods and how are they used to control Excel and other programs?
How to create an object expression (i.e., path) in Excel VBA and use a Set statement to track objects
Why use Record Macro to research Excel commands and not to build entire procedures with it?

Using Functions and Excel VBA to Find, Lookup, and Calculate Worksheet Data

How engineers and scientists can run Excel and VBA functions in VBA
How to track ranges, headers, and data subsets using MATCH, COUNTA, Range, Cells, Find, CurrentRegion, Columns, Rows, Address...
Analyzing dates and times in your code using functions like DateAdd, DateDiff, EOMONTH, Month, Day, Year, Format...
Performing math, statistical, and trig calculations in your code using functions like Tan, Sqr, SUM, COMPLEX, ROUND, COUNTIFS, LOG, STDDEV.S...
Performing table lookups in your code using functions like MATCH, VLOOKUP, XLOOKUP, HLOOKUP...
Using VBA to calculate table data and automatically place results / formulas below or beside them

Controlling and Calculating Model and Analysis Problems with Excel VBA

How to layout interfaces for model / analysis problems that can be read by Excel VBA
How to rapidly construct model / analysis interfaces on the worksheet using cell names, cell references, shapes, and Data Validation
Accessing worksheet parameter information using Cells, Range, Columns, Rows, CurrentRegion, Find, Address...
How to automate calculations in VBA using mathematical operators (+,-,/,*, ^...), loops, functions, and variables
Using logic in your code to decide what calculations to use in your model / analysis problems
How to leverage cell names, range names, and Excel tables in your macro code to track worksheet parameters, data, and output cells
Using macros to automatically create, modify, and delete worksheet formulas, cell, and range names

Controlling Workbooks, Worksheets, and Formats with Excel VBA

Using Open, Add, Save, SaveAs, and Close in your code to control workbooks
Using the Set statement and names to track workbooks and worksheets in your Excel VBA code
Using Add, Delete, Move, Name, Protect, and Unprotect to control worksheets in Excel VBA
How to use VBA to control worksheet number formats, fonts, colors, alignments, and borders
Using macros to insert, delete, and move worksheet cells, rows, and columns
Using macros to lock onto and format various engineering and science worksheet table configurations

Processing and Calculating Engineering / Science Data Using Loops and Logic

When to use loops to process engineering / science data vs. using Excel's built-in data tools
Using Cells, Range, Offset, and variables inside a VBA loop to access engineering / science data on the worksheet
When to use logic to shut down a loop vs. figuring out how many rows are in a data table
Using loops, logic, functions, and counters to process worksheet data and find values like steady state, max, min, increments, time...
How engineers and scientists can use loops and logic to detect data rows and columns to delete or copy
How to use VBA to insert columns and rows in a data table and place formulas or calculated values in them
Using macros to color cell data based on limits

Using Excel VBA to Control Text Files and Perform Worksheet Text Operations

Performing text operations in your code using functions like CLEAN, TRIM, Left, Mid, Len, Replace, Instr...
Using loops, logic, concatenation, and functions to clean and parse text data in your tables
How to automate Text Wizard and Text to Columns to process text files and text data in Excel
How to open, read, write to, and close text files with VBA
Using string concatenation in VBA to create text that is written to a text file
The basics of outputting text file data to the worksheet to process

Day-2

Using Excel VBA to Generate Engineering / Science Reports on Worksheets

How to track your output report workbook and worksheet in VBA
Designing logic in your code to decide which data to transfer to your report
Creating report tables by copying and pasting data rows and columns with VBA
Creating report tables by filling in specific data value patterns with VBA
How to use macros to automatically place formulas or calculated values in your report tables
Using macros to control report table format like number formats, fonts, colors, alignments, and borders
Using loops and logic to fill data in various report table formats

Automating Excel's Data Tools to Rapidly Process Engineering / Science Data

Using macros to control Sort, Remove Duplicates, and Grouping
Using macros to control AutoFilter and Advanced Filter
Using macros to copy paste filtered data to report workbooks and worksheets
How to use VBA to stack filtered data on a report worksheet
Using Excel VBA and Record Macro to build and control PivotTables and Excel tables
Using Record Macro to figure out how to automate the Analysis Toolpak

Scanning for Engineering / Science Data in Workbooks and Worksheets

Tweaking your code to scan for data in open workbooks and on multiple worksheets
Scanning for an open workbook with a specific data set using For...Each Next loops, logic, and functions
Scanning multiple worksheets for a specific data set using For...Each Next loops, logic, and functions

Excel VBA Chart Automation for Engineers and Scientists

How to use macros to create a chart sheet or embedded worksheet chart
How to use macros to update a chart's source data without rebuilding it
Using VBA to color data points in charts based on logic
Using macros to add / delete chart elements
Using VBA to format engineering and science charts
How to load a chart template using a macro

Building Basic User Interfaces (UIs) to Run Your Excel VBA Tools

Design strategies for laying out engineering / science UIs on the worksheet to run VBA code
Building a file picker and saver in your macro code to select workbook files to analyze
How to create buttons from drawing shapes and pictures to run your macro code
How to use Data Validation, shapes, and cells to create selection inputs for your UIs
How to integrate your UI selections with logic in your code to run your Excel tools

Designing a Rapid Modeling and Analysis Environment Using UDFs

What are user defined worksheet functions (UDFs) and why are they different from VBA Function procedures?
How to use UDFs to design a rapid modeling and analysis environment on the worksheet
General VBA design rules for UDFs
Designing UDFs to accept cells, ranges, values, and arrays as arguments
Implementing loops, logic, and functions in UDFs to process data, perform calculations, and perform numerical analysis
Returning scalar and array values back from to the worksheet from UDFs

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

Solve multi-step engineering and science problems with the click of a button.