Excel VBA for Engineers and Scientists

Detailed training syllabus.

Available: Public >, Virtual >, Onsite >

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

Select a Class Excel Data Analysis Engr/Sci Excel VBA Fundamentals Engr/Sci Excel VBA Data Analysis Engr/Sci Advanced Excel VBA Methods Engr/Sci Calling Fortran from Excel VBA Excel Classes All Disciplines