Next Class:
6/20/2022
More Dates >
Explore powerful
ways to use Excel VBA to rapidly build analysis, data processing, and
reporting tools for engineering and science.
One click. Problem solved.
How our training can help you.
Our 2-day workshop shows you how to leverage Excel
VBA in engineering and science to build automated tools that solve your
problems at the click of a button.
Coupling Excel's "pre-built " features with VBA
provides you with a powerful rapid design environment. Automated tools
can be made that perform a variety of tasks including automated
calculations, formatting data tables, managing models, automating chart
reports, and processing large multi-worksheet and workbook data sets.
Join us and we will show you straight forward,
industry proven techniques that will allow you to get your Excel VBA
projects up and running with minimal build time.
Key Excel VBA topics covered.
- 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.
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
Additional Classes:
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 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
- Have 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
You can email us at info@emagenit.com >
Contact us by form.