Excel VBA for Engineers / Scientists Class

Next Class: 10/24/2022

More Dates >

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

Click to enlarge, scroll < > if hidden.

Pricing and class options.

Detailed class syllabus.

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

Excel VBA skills needed for the class.

The class details.

• Audience:   Engineers, scientists and technicians
• Duration:   9:00 - 5:00 CT
• # of Days:   2-days
• Instruction Type:   Hands-on, live, instructor taught training
• Full Excel Manual Included:   Our 2200 + page EAE 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
• Free 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

You can email us at info@emagenit.com >

Contact us by form.