How our class can help you.
Our 2-day class shows you how to couple Excel's "off-the-shelf" data abilities with VBA to build adaptive, scalable data tools in mere days.
Using VBA’s abilities with Excel’s data tools allows you to design
time saving tools that can swiftly calculate, reorganize, and format
worksheet data. You can also design high powered report tools that process and integrate data stored in folders, databases, text files, and workbooks
and then chart it.
Join us and our class will show you all you need to know to turn Excel and VBA into a
rapid data processing environment that fits your specific tasks.
Key Excel VBA data topics covered in class.
- Review of the VBA Editor, its
debug tools, and the Excel VBA language
- How to use worksheet and
VBA functions in your macro code to analyze and calculate engineering /
science data
- Automating and looping Excel's data
tools (PivotTables, AutoFilter, Analysis Toolpak..) for large scale
data processing
- Controlling the Windows folder system with
VBA to store reports and data
- Building adaptive loops and logic to
analyze engineering / science worksheet datasets
- Using VBA to build, fill-in, shape, format,
and calculate various engineering / science report tables
- How to use Excel VBA to perform various
table lookups and interpolation
- How to use VBA to create, write, and
modify text files
- Using SQL, ADO, and VBA to control and
integrate
text files, databases, and workbooks
- Using VBA and Excel's Query Tools to
rapidly filter and integrate data
- Large scale multi workbook, worksheet,
and text file processing using VBA
- Automating engineering / science chart reports
with Excel VBA
- How to design worksheet based user interfaces (UIs) to control your
data tools and email your reports
Click to enlarge, scroll < > if hidden.
Detailed class syllabus.
Available:
Public >,
Virtual >, Onsite >
How we run the class:
We focus our Excel VBA class on what our customers need. When class
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 classes are ever the same with EMAGENIT.
Day-1
Excel VBA Language and Editor
Review for Engineers and Scientists (Discussed Where Needed)
- Excel VBA Editor and debugger
review
- Review of the VBA language 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 data analysis code
- A review of Excel objects, properties, and methods and how to use them in
your macro code
- How to use object expressions and
the Set statement in your
macro code to track objects
- How to use CreateObject and GetObject to control other programs libraries
Using Functions in VBA to Analyze
and Calculate Engineering / Science Data
- Full review of how to run Excel
worksheet and VBA functions in your
macro code
- How to track ranges, headers, and data
subsets using
MATCH, COUNTA, Range, Cells, Find, CurrentRegion, Columns, Rows,
Address...
- Calculating math, trig, and statistical
values in your
macros using functions like Sum, Max, Min, ATAN, SIN, Sqr, STDEV.S, RANK...
- Using logical functions in your
code like SUMIFS, AVERAGEIFS, MAXIFS, MINIFS,
COUNTIFS... to analyze data
- Parsing and merging date time values in your code using
functions like Year, Format, DateDiff, DatePart,
NETWORKDAYS, EOMONTH...
- Performing calculations on
engineering / science tables and placing values or formulas below or
beside them
Controlling Workbooks,
Worksheets, Folders, and
Files with Excel VBA
- Design strategies for storing data in multiple workbooks
and
folders and using VBA to access them
- Using the FileSystemObject to create, move, delete, rename, and access Windows folders
and files
- How to use Open, Add, Close, Save,
and SaveAs in your code to control report and data workbooks
- Design strategies for naming workbooks and folders for large scale data storage
and access
- Using Add, Delete, Move, Name,
Protect, and Unprotect to control report worksheets in Excel VBA
Automating and Looping Excel's
Data Tools for Large Scale Data Processing
- How to automate AutoFilter, Sort,
Remove Duplicates, and Advanced Filter using Excel VBA
- How to use Record Macro
and VBA to automate PivotTables and the Analysis Toolpak
- Determining what statements to
keep in the Record Macro code, replacing hard coded arguments,
replacing active paths
- Using Excel's data tools inside
loops to create large scale data processing tools
- Using
macros to build filter logic on the fly for Excel's filters
inside loops
- Using macros to copy, paste, and
stack filtered data on single and multiple report worksheets
Dynamically Shaping and
Formatting Engineering / Science Worksheet Tables
- How to dynamically track resizing worksheet tables in
your code using Range, Cells, Find, Columns, Rows, Address, CurrentRegion...
- How to dynamically track moving
table headers in your
code using MATCH, Find,
Column, Row...
- Creating rules on the worksheet to
tell VBA what to do with report table columns, rows, formats...
- How to use loops and logic to insert,
delete, and move worksheet cells, rows, and columns
- Using Excel VBA to lock onto and
format simple and complex engineering / science worksheet tables
Building Loops and
Logic to Analyze Complex Engineering / Science Datasets
- Using Range, Cells, and
Offset in your loops to access engineering and science worksheet
data
- Determining worksheet header
positions, table size, and table position on the fly using
MATCH, COUNTA, Find, Column, Row, Address...
- How to use cell formats, cell
values, data breaks in
procedures as logic triggers when analyzing worksheet data
- Using adaptive loops and logic to analyze
standard and complex worksheet table patterns like stacked tables, fragmented tables, indented tables...
- Using adaptive loops and logic to process worksheet data and find values like steady state, max, min,
limit violations, increments,
time...
- Using macros to color cells and
create formulas in
engineering / science worksheet tables
Using Excel VBA to Build, Fill,
and Lookup Data in Engineering / Science Report Tables
- Using Range, Cells, Offset,
CurrentRegion, Find, Match... to dynamically track worksheet tables, headers,
position, and size
- Building report tables by copying
and pasting data rows and columns with VBA
- Building report tables by filling
in various data value patterns with VBA
- Using COUNTIF, VLOOKUP,
XLOOKUP,MATCH, HLOOKUP, and Cells in VBA to lookup table data and fill in rows / columns
- How to perform interpolation in
Excel VBA
- Using macros to automatically
build and fill existing report tables including status tables
- How to use VBA to perform
calculations and generate formulas in report table columns and
rows
Day-2
Controlling and Processing Text Data with
Excel VBA
- Cleaning and parsing text in your
Excel VBA code using Split, Mid, Replace, Instr, Format, CLEAN,
TRIM...
- Automating Text Wizard and Text to
Columns to load and parse text data to 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 VBA loops and string concatenation
- Using Do loops, logic, functions,
and arrays to scan and analyze text file data and output the results to an Excel worksheet
Using VBA, SQL, and ADO to
Control / Integrate
Database, Workbook, and Text file Data
- Basic SQL language review
- How to write an SQL query
statement and connection string in Excel VBA
- How to use VBA, ADO, and SQL to
update, insert, and delete database data
- Using VBA, ADO, and SQL to quickly
query data in workbooks, text files, and databases and return
data to the worksheet
- Using VBA and SQL to quickly
integrate data from different data sources
Filtering and
Integrating Data Using VBA and Excel's Query Tools
- Design methods to use Power Query,
Microsoft Query, Excel tables, and VBA to quickly integrate and
filter data from different sources
- Using Record Macro to record code
that automates Excel tables, Power Query, and Microsoft Query
- Determining what statements to
keep in Record Macro code, replacing hard coded arguments, replacing active paths
- Controlling query settings with
Excel VBA
Large Scale Multi-Workbook,
Worksheet, and Text File Processing Using VBA
- Scanning for an open workbook with
a specific dataset using For...Each Next loops, logic, and functions
- Scanning multiple worksheets for a
specific dataset
using For...Each Next loops,
logic, and functions
- Scanning
folders for specific data workbooks
using For...Each Next loops,
logic, and functions
- Scanning folders
for specific text files using For...Each Next loops,
logic, and functions
- Designing scalable procedures that
adapt to data in n...workbooks, worksheets, and text
files
Building Automated Chart
Reports with Excel VBA
- Review of how to use VBA to create chart sheets and embedded worksheet charts
- How to use Excel VBA to create and arrange
multiple charts on a worksheet (chart report)
- How to use macros to create
engineering / science combo charts
- Loading VBA arrays directly into a
chart's series, a must know for emailed chart reports
- How to use macros to create chart
limit lines, curve fits, labels...
- How to use VBA to format charts including
coloring data points and hiding / displaying series
Designing Worksheet UIs
to Control Your Data Tools
and Email Your Reports
- Laying out user interfaces (UIs) on the worksheet
to control your Excel VBA data tools
- Using the FileDialog object in VBA to construct file and folder pickers
for your data
- Using Data Validation and ActiveX
controls to control user entry on the worksheet
- Controlling and reading ActiveX controls
and Data Validation using Excel VBA
- How to create a simple Outlook email
macro to automatically mail your reports
Additional Classes:
_EMAGENIT_2019.png)
