Available Onsite Only
About Our Onsites
Learn how to create adaptive
upgradable data tools that
process, fuse, and visualize engineering and science data.
Our 2-day hands-on training shows you how to combine VBA with
the worksheet, text files, databases, Excel's data tools, charts,
shapes, pictures and Windows folder system to create
tools that process, organize, visualize, store, and fuse engineering
and science data.
Questions? 1.805.498.7162 Customers >
Learn More >
Learn More >
About our Excel VBA data analysis for
engineers and scientists.
Our 2-day hands-on live Excel VBA
data analysis focuses on how to turn Excel into an adaptive data
processing, data fusion and data visualization tool for engineering
and science data. Key topics include:
- Complete Excel VBA language and
coding strategies review More >
- Using key
functions and the Analysis Toolpak in VBA to process engineering /
science worksheet data More >
- Using Excel VBA to process
complex engineering and science data patterns on the worksheet More >
- Using Excel VBA to build, fill,
and format engineering / science worksheet tables More >
- Using Excel VBA to perform worksheet
table lookups and create PivotTable reports More >
- Searching folders, open
workbooks, and worksheets for engineering / science data More >
- Shaping engineering /
science worksheet tables with Excel VBA More >
- Controlling engineering / science
text files with Excel VBA More >
- Querying databases, workbooks,
and text files with Excel VBA, SQL, and ADO More >
- Visualizing engineering /
science data with charts using Excel VBA More >
- Designing User Interfaces (UI's)
on the worksheet to control and view your engineering / science data More >
The skills you need for our Excel VBA
data analysis training.
Select this Excel VBA training if you or your group have:
- Used variables, loops, functions, declarations... in
VBA
- Used and written basic logic in VBA
- Used Excel objects, properties,
and methods in VBA
- Used Microsoft Excel's data tools manually and know them operationally
- Formatted worksheets manually and know Excel's basic formatting capabilities
- Built charts and drawing shapes manually
before in Excel
- Used Excel's worksheet functions like MATCH, COUNTIF, SUMIF, VLOOKUP...
Our Excel VBA data analysis for engineers and scientists training syllabus.
Day-1
Excel VBA Language Review
and Coding Strategies for Engineers and Scientists (Discussed
Throughout Class) Back >
- A review of critical VBA
programming elements used during the class including variables,
data types, constants, arrays, operators, expressions, loops,
logic decisions, functions, and calling conventions
- Constructing error handlers and
logic to handle run-time errors in your VBA data analysis code
- A review of Excel objects, properties, and methods and how to
use them in VBA code
- Creating and using object
expressions in your VBA code to track objects
- How to use the Set statement in
VBA to track workbooks, worksheets, ranges, charts...
- How to use CreateObject and
GetObject to control other programs and libraries
- Using the VBA For...Each
Next loop to loop through object collections and find
individual objects
Using Key
Functions and the Analysis Toolpak in VBA to Process Engineering / Science Worksheet Data Back >
- Full syntax review of how to run Excel and
VBA functions in your VBA code
- How to identify stationary
and changing data ranges inside VBA
using combinations of the Range, Cells, Columns, Rows,
and CurrentRegion properties
- Using the MAX, MIN, AVERAGE, and
COUNTA worksheet functions in VBA to perform a variety of
technical tasks
- Using
the SUM, SUMIFS, AVERAGEIFS, MAXIFS, MINIFS, and COUNTIFS
worksheet functions in Excel VBA to analyze data
- Using the trig worksheet functions in your code
including ATAN, COS, SIN, RADIANS, DEGREES...
- Using the statistical worksheet functions in
your VBA code including STDEV.S, SKEW, CONFIDENCE, RANK...
- Parsing and merging date / time
values in your VBA code using the Day, Month, Year, DateDiff,
DateAdd, DatePart, Weekday, Format, WEEKNUM, NETWORKDAYS,
EOMONTH... functions
- Cleaning, parsing, and merging
text in your VBA code using the Split, Left, Right, Mid,
Replace, Instr, InstrRev, Format, CLEAN, TRIM... functions
- Automating the Analysis
Toolpak tools in VBA to analyze worksheet table data
Using Excel VBA to Process
Complex
Engineering and Science Data Patterns
on the Worksheet Back >
- Full review of using VBA
For...Next loops to make adaptable Excel VBA programs that process worksheet data
- Using the Cells, Offset, Rows,
Columns, Row, Column, CurrentRegion, and Range properties in
your For...Next loops to locate cells and ranges
- How to determine table header positions
and table sizes in your VBA data procedures, key to adaptable
procedure design
- Using VBA loops and logic to
determine engineering / science values at specific start or stop points
- Figuring out where your worksheet data
enters steady state with VBA loops and logic
- VBA looping and logic strategies for determining
data points that are above or below set limits
- Methods for analyzing stacked
worksheet table data patterns using Excel VBA loops and logic
- Using VBA to color cells in an
engineering / science worksheet table based on logic
- Using cell formats in Excel VBA as logic
triggers when analyzing data
- Importing worksheet tables and
converting them to VBA arrays for speed runs
Using Excel VBA to Build,
Fill, and Format Engineering / Science Worksheet Tables Back >
- Review of building single and nested loops to
access various patterns of engineering / science worksheet table data
- VBA strategies for formatting
engineering and science tables on the worksheet
- Using the COUNTIF and MATCH worksheet
functions along with Find, Range, and Cells in VBA to locate
single and double row table headers
- Using the Find, Row, and Column
methods and properties to determine the start cell of a
worksheet table
- Using Excel VBA to automatically
build worksheet summary tables from engineering / science data
- Building Excel VBA code that reads
worksheet data, generates the output
workbook/worksheets, and fills them in
- Using VBA to determine a unique
value set from a worksheet table column
- Creating Excel VBA loops that
iterate through tables on multiple worksheets, find their boundaries,
format them, and perform the necessary calculations
- Using VBA loops and logic to read
worksheet data sets, build and fill 2-d tables (i.e. Project
columns vs. Site rows), and color code the data cells
Using Excel VBA to Perform Worksheet Table Lookups and
Create PivotTable Reports Back >
- Using the VLOOKUP and HLOOKUP worksheet functions in your
VBA code to perform simple worksheet table lookups
- How to use the MATCH and COUNTIF
worksheet functions in your VBA code to perform complex table lookups
- Performing offset
worksheet table lookups in VBA using the Find, Offset, Row, Column, and Cells
properties and methods
- How to use Excel VBA to isolate a
data subset within a larger worksheet data set and lookup values within that range
- How to perform worksheet table interpolation
with VBA
- Using Excel VBA loops and logic to fill in table
columns with lookup values or lookup formulas
- How to use Excel VBA to create
an Excel table on a worksheet
- Creating, modifying, and deleting PivotTables
on the worksheet
using Excel VBA
Searching Folders, Open
Workbooks, and Worksheets for Engineering / Science Data Back >
- Design strategies for storing data
in multiple workbooks and then using VBA to combine and fuse the
data
- Design strategies for naming
workbooks and folders for large scale data storage
- Using the FileDialog object in VBA
to construct file and folder pickers
- Using the FileSystemObject object in
Excel VBA to create, move, delete, rename, and access Windows
folders
- How to open, add, save, and close
workbooks using Excel VBA
- Using VBA to lock onto and scan
open workbooks and worksheets for engineering / science data
- Excel VBA looping and logic
strategies for processing engineering / science data stored on single and multiple
worksheets
- How to design VBA loops and logic
to loop through workbooks stored in single and multiple Windows
folders
Day-2
Shaping Engineering /
Science Worksheet Tables With Excel VBA Back >
- Removing unwanted
rows and columns from engineering / science tables using Excel
VBA
- Inserting rows and
columns in tables and then filling them with data and formulas
using Excel VBA
- Using VBA to automate the AutoFilter and Advanced
Filter tools, copying the filtered data, and pasting it to
report worksheets
- Merging or splitting text from
worksheet table columns and placing them in new columns using Excel VBA loops and functions
- Using
Excel VBA loops and functions to extract specific date / time
elements like month, day, and year from table columns and
placing them in new columns
- Merging table columns containing
date / time elements into valid dates in new columns using Excel VBA loops and
functions
- Various VBA strategies for cleaning,
trimming, and replacing text in worksheet table columns
Controlling Engineering / Science Text Files
With Excel VBA Back >
- Automating the Excel Text Import
Wizard tool with VBA to quickly parse and load data onto an
Excel worksheet
- Using the FileSystemObject and
TextStream objects in VBA to open, close, read, and write to text files
- Writing Excel data to a text file
using loops, string concatenation, and the Cells, Columns, Rows,
Range, and CurrentRegion properties in VBA
- Using Excel VBA Do loops, logic,
and functions to scan and analyze text file data and output the
results to an Excel worksheet
- Cleaning, parsing, replacing, and
reorganizing corrupted text files using various functions, the
worksheet, and the Excel Text to Columns tool in Excel VBA
- Loading text files into VBA arrays
and processing them (speed run)
- Designing Excel VBA loops and
logic to loop through multiple text files in folders and fuse
their data
Querying Databases, Workbooks,
and Text Files with Excel VBA, SQL, and ADO Back >
- SQL language review
- How to use string
concatenation and loops to assemble a SQL statement in
your VBA code
- Using ADO and SQL to
query and update databases
- Using VBA, ADO, and SQL to
query data in workbook tables and text files
- Using ADO, SQL, and VBA to combine
and fuse data stored from multiple data sources
Visualize Engineering /
Science Data Using Excel VBA and Charts Back >
- Review of how to use Excel VBA to create
chart sheets and embedded worksheet charts
- Loading data into VBA arrays and creating a chart series
from them, a must know for emailed chart reports
- How to use VBA to create combo
charts for engineering / science data
- Using Excel VBA to create chart limit
lines and curve fits
- Excel VBA chart formatting methods
including data point conditional formatting and hiding / displaying series
- Excel VBA methods for flagging
particular data points with enhanced labels
- How to write Excel VBA code that
creates and arranges multiple charts on a report
worksheet
Designing User Interfaces
(UI's) on the Worksheet to Control and View Your Data Back >
- Laying out user interfaces (UI's)
on the worksheet
for your data tools
- Using Data Validation to control
user entry on the worksheet and construct drop-down lists in your UI's
- Using ActiveX and Form controls to
control user inputs in your data tool UI's
- Using shapes, pictures, and VBA to dynamically
display data values in your UI's
- Automating Data Validation using
Excel VBA
- Controlling and reading ActiveX
controls on the worksheet using Excel VBA
- For advanced UI design for data
tools also see our advanced
Excel VBA...Engr/Sci