Detailed training syllabus.
How we run the class: We focus our Excel VBA fundamentals training on what our customers need. When training begins, we analyze those needs and shift our Excel VBA 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
Visual Basic Editor, Module, and Procedure Review for Engineers and Scientists (Discussed Throughout the Class)
- A complete review of the Excel VBA Editor windows, toolbars, and modules
and how they are used in engineering and science problems
- What is a VBA project and Module?
- What is a statement and how are they used in VBA programming?
- What is a Sub and Function procedure and how are they used in Excel VBA programming?
- How to use the VBA Editor's debugging tools to correct your VBA programs
- How to properly use the Macro Recorder to play detective and figure out task code and settings, not record entire procedures
Excel VBA Language Review for Engineers and Scientists (Discussed Throughout the Class)
- Overview of common Excel elements that engineers and scientists will want to control with VBA
- Core Excel VBA language review including how to use data types, variables, constants, arrays, operators, expressions, loops, logic decisions, calling conventions, and argument lists
- Translating your engineering and
science Excel tasks into an Excel VBA program
- What is an object, property, and method and how are they used to control Excel and other programs?
- What is an object expression and how to create one using the command.command.command structures in Excel VBA?
- Using the Set statement to track objects in your Excel VBA code
- Definition of a numeric expression, string expression, relational expression, and logic expression (formulas)
- What is logic and how is it used to control the flow of a VBA procedure?
- How to use different logic statements; relational operators like <, >, <=, >=...; and functions to build program logic
for your engineering and science programs
- Using the And, Or, and Not operators to make multi-criteria decisions in your VBA logic
- Using string concatenation in VBA programming to generate text
Using Excel VBA to Automate
Calculations and Control Worksheet Models
- How to layout interfaces for model
/ analysis problems that can be read by Excel VBA
- How to construct model / analysis interfaces on the worksheet using cell names, cell references, shapes, and Data Validation
- Accessing worksheet interface information using the Cells, Range, Columns, CurrentRegion, and Rows properties
- How to read worksheet based parameters into your VBA calculations
- How to automate calculations in
VBA using mathematical operators (+,-,/,*, ^...), loops, and
variables
- How to leverage cell names, range names, and Excel tables in VBA to track worksheet parameters, data, and output cells
- Using VBA to automatically create and modify
worksheet based formulas
- Creating, deleting, renaming, and reassigning cell and range names using Excel VBA
- Using VBA logic to decide what calculations to use in your model / analysis problems
- Controlling interface cell number format, font, color, alignment, and border using Excel VBA
- Creating and modifying Data Validation logic, formulas, limits, and message boxes using Excel VBA
Using Excel VBA and Worksheet
Functions to Find, Calculate, and Lookup Worksheet Data
- Using VBA and Excel worksheet
functions in your code, the key to building powerful engineering
and science analysis tools
- How engineers and scientists can lock onto fixed and resizing worksheet data ranges using the Range, Cells, CurrentRegion, Columns, and Rows properties
- How to find the position of table columns and rows using the MATCH, COUNTIF, and COUNTA worksheet functions and the Find method
- How to use the Range, Cells, CurrentRegion, Columns, and Rows properties in VBA to isolate worksheet data subsets and use them as arguments to worksheet functions
- How engineers and scientists can
couple VBA logic with functions to analyze worksheet data
- Using Key VBA and Excel worksheet functions in your code that analyze dates and times like DateAdd, DateDiff, EOMONTH, Month, Day, Year, Format...
- Using Key VBA and Excel worksheet functions in your code that perform math and statistical calculations like Tan, Cos, Sin, Sqr, SUMIF, SUM, COUNTIFS, SUMIFS, AVERAGE, DEGRESS, LOG, STDDEV.S...
- Using key Excel worksheet functions in your code that perform
engineering and science data lookup operations like MATCH, VLOOKUP, HLOOKUP...
- Using key VBA and Excel functions in your code that perform
text operations like Left, Right, Mid, Len, Replace, CLEAN, TRIM, Instr...
- Calculating engineering and
science tables and automatically placing formulas or results
under them
How Engineers and
Scientists Can Track, Shape, and Format Tables with Excel VBA
- Tracking different worksheet table parts (header rows, last rows, entire tables...) using the Range, Cells, CurrentRegion, Columns, Rows, and Count properties in VBA
- Locating worksheet table start and stop points and header positions using the COUNTA and MATCH worksheet functions in Excel VBA
- Locating worksheet table start and stop points and header positions using the Find, Cells, Row, and Column methods and properties in VBA (alt method)
- How engineers and scientists can
use Excel VBA to copy and paste various worksheet table elements (cells, rows, columns, entire worksheets...)
- How to use Excel VBA to insert, delete, and move worksheet cells, rows, and columns
in your engineering and science tables
- Using Excel VBA to control worksheet table format like number formats, fonts, colors, alignments, and borders
- How to use worksheet cell characteristics like bold, color, address, column, row... to build relational expressions (logic conditions) for your VBA logic
Using Excel VBA to Control Workbooks, Worksheets, and Text Files
That Store Data
- Why store technical data in
multiple workbooks instead of storing it in your code workbook?
- Using Excel VBA to open, close, and save
data storage workbooks
- Building a file picker and saver
in Excel VBA, the key to selecting what file type stores your
data
- Using the Set statement to track workbooks and worksheets in your Excel VBA code
- How to use Excel VBA to add, delete, move, and rename worksheets
- Naming your workbooks / worksheets for modeling and data tracking purposes
- Opening, closing, and reading text files with Excel VBA
Day-2
Custom VBA Worksheet Function
(UDF's) Design for Engineers and Scientists
- Why custom worksheet functions (UDF's) are important to
engineers and scientists in the areas of modeling, analysis, and
data processing problems
- Design rules for custom Excel worksheet functions (UDF's)
used in engineering and science
- Designing UDF's to accept cells, ranges, values, and arrays as arguments
- Implementing loops, logic, and functions in UDF's to process data, perform calculations, and perform numerical analysis
- Implementing loops, logic, and functions to filter and collect data inside of a UDF
- Returning arrays and scalars back from UDF's to worksheet formulas
Creating Rapid Reporting Tools by Automating Key Excel Data Tools with VBA
- Sorting engineering and science
tables using Excel VBA and the Sort tool
- How engineers and scientists can
filter worksheet tables using Excel VBA and the AutoFilter / Advanced Filter tools
- Filtering unique values in a worksheet table column using Excel VBA and the Remove Duplicates tool
- Using Excel VBA to lock onto filtered
engineering and science worksheet data then copying and pasting the data to different report worksheets
- Automatically grouping worksheet rows and columns using Excel VBA and the Grouping tool
- Using Excel VBA and the Macro Recorder to automate PivotTable construction
- Automating the Analysis ToolPak with Excel VBA
How to Scan, Shape, and
Calculate Worksheet Data Using VBA Loops and Logic
- When do engineers and scientists use For...Next loops and logic to analyze
technical data on the worksheet?
- Using the Cells, Range, and Offset
properties inside a VBA loop to access engineering and science
data on the worksheet
- How engineers and scientists can
organize technical data on the worksheet so it can be easily read, key to large scale data system development
- The basics of using variables, functions, and arrays inside loops to collect and process data
- When to use logic to shut down a For...Next loop vs. figuring out how many rows are in a table
- Using logic, functions, and
counters in a For...Next loop to process common engineering and
science worksheet data patterns like steady state, max, min,
time values...
- Scanning worksheet data with a For...Next loop, copying and pasting the target data to another workbook/worksheet table, and summarizing
- Using Excel VBA logic, functions, and For...Next loops to fill out worksheet forms, displays, and various table configurations
- How engineers and scientists can
use VBA loops and logic to detect the rows and columns to delete in worksheet data
- Shaping worksheet data text (remove unwanted characters, clean, trim...) with VBA loops, functions, and string concatenation
- Adding columns, rows, formulas,
and calculated data to engineering and science worksheet tables
using VBA
Scanning for Engineering and
Science Data in Multiple Workbooks and Worksheets
- Strategies for engineers and
scientists to locate data on worksheets and in workbooks using For...Each Next loops
- Combining VBA For...Each Next loops, logic, and functions to scan for
engineering and science data in open workbooks
- Combining VBA For...Each Next loops, logic, functions, and key Excel methods to scan for
engineering and science data on worksheets
- Processing strategies for analyzing a single data worksheet vs. multiple data worksheets
Excel VBA Chart Automation for
Engineers and Scientists
- How engineers and scientists can
use Excel VBA to automate chart reports
- Creating a chart sheet or embedded worksheet chart using Excel VBA
- Using Excel VBA to update a chart's source data without rebuilding it
- Using Excel VBA to color data points in charts based on logic
- Adding and deleting chart elements with Excel VBA
- Using Excel VBA to format
engineering and science charts
- Loading chart templates using Excel VBA
Additonal Classes:
Learn More >
_EMAGENIT_2021.jpg)
Learn More >
Excel VBA skills needed for the training.
Select this Excel VBA training if you or your group have:
- Never programmed or not programmed in over a year
- Just used the macro recorder for 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
The training 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:
If you need to contact us about our training.
Phone Number: 1.805.498.7162
Business Hours: 8:30 - 5:00 PM PT
email: question@emagenit.com
Contact us by form.
