Available Onsite Only
About Our Onsites
Learn how to create innovative data analysis, data fusion and data visualization tools for engineering and science.
Our Excel VBA data analysis
training shows you how to combine VBA with key Excel elements to create powerful data
tools that can analyze, organize, visualize, store and fuse engineering
and science data.
Questions? 1.805.498.7162 Customers >
What is covered in 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. Our Excel VBA training
starts off with showing you unique ways to control Excel cells,
ranges and data tools in Excel VBA and how these elements can be
used together to create high end data fusion and data analysis
tools. Our Excel VBA training then focuses on how to use various
loops, Excel worksheet/VBA functions, arrays and logical
techniques to analyze technical data with a focus on reading
unconventional worksheet data layouts, re-sizing moving data
areas and locating hard to find information on a worksheet. Our
training then discusses sophisticated data fusion techniques
using VBA loops and logic to sift through data in multiple
workbooks and worksheets from multiple folders. The data fusion
discussion also covers methods for reading and writing to text
files and databases and fusing that information together. Also covered is various data
visualization methods showing how to use Excel VBA to visualize your engineering/science data in various worksheet
table configurations, filters, colored cells, drawing shapes, pictures and charts.
The key skills learned in our Excel VBA data analysis for
engineers and scientists.
Key Excel VBA skills learned:
- A review of key VBA language
elements during the training including variables, data types, constants,
arrays, operators, expressions, loops, logic decisions,
functions and calling
- Full object, property and method
review, how to use them and their syntax rules in VBA
code
- A full review of key of how to
use Excel worksheet and VBA functions in your code that sum and
count values; check for errors; lookup information;, clean and
process text; and process dates and times
- Advanced methods to identify
cells, ranges, columns and rows in VBA and control them
- Using Excel VBA to locate and
track hard to find data on the worksheet
- How to automate key Microsoft Excel data
tools (AutoFilter, Advanced Filter, sort,
Excel Tables, Microsoft Query and Pivot Tables) in VBA that are key to
data fusion and data analysis techniques
- Automating key Excel VBA data
operations that are used in data fusion like sorting, filtering,
finding, copying, pasting, inserting and deleting
- How to use Excel VBA to control key
Microsoft Excel objects that relate to data fusion and data
visualization in Microsoft Excel
- Using VBA to control the Windows
folder and file system
- How to use VBA to control other
programs
- How to use Excel and VBA to open, scan and
process data in text
files including hard to read text files
- Using Excel VBA to access
data stored on multiple worksheets in
multiple workbooks
- Using VBA ADO, DAO and SQL to
access and control databases
- Key strategies and methodologies
for creating data fusion tools in VBA
- How to use Excel VBA to control
worksheets, charts and drawing shapes and turn them into data visualization tools
The skills you need for our Excel VBA
data analysis training.
Select this Excel VBA training if you or your group have:
- Written a procedure in VBA
- Used variables, loops, functions, declarations,... in some capacity in VBA
- Used and written basic logic in VBA
- Used Microsoft Excel's data tools manually and know them operationally
- Formatted worksheets and know Excel's basic formatting capabilities
- Have used Excel's worksheet functions like MATCH, COUNTIF, SUMIF, VLOOKUP...
Our Excel VBA data analysis for engineers and scientists training syllabus.
Day-1
Using Objects in Excel
VBA
- A review of critical VBA
programming elements during the class including variables,
data types, constants, arrays, operators, expressions, loops,
logic decisions, functions and calling conventions
- Definition of an object, how to
use one in programming and the syntax rules for using them in
your VBA code
- Definition of a property, how to
use them in programming and the syntax rules for running them in
your VBA code
- Definition of a method, how to
use them in programming and the syntax rules for running them in
your VBA code
- How to construct object expressions
using accessors
in your VBA code
- How to use the Set statement in
VBA to
track objects
- How to use the VBA For...Each
Next loop to loop through object collections and track
individual objects
Review of Key Excel Worksheet and
VBA Functions Commonly Used in Data Fusion, Data Analysis and Data
Visualization
- Full syntax review of how to run Excel and
VBA functions in your VBA code
- Using
the SUMIF, COUNTIF, SUMIFS and COUNTIFS worksheet functions in
Excel VBA to sum and count technical data
- Review of using the imaginary
and engineering functions in Excel VBA to crunch data
- How to perform table lookups in
Excel VBA using MATCH, COUNTIF, VLOOKUP, SUMIFS
- How to use the COUNTIF worksheet
function in Excel VBA to test for data existence
- Using the IS VBA and Excel
worksheet functions to detect errors in your data
- Using the Left, Right, Mid and
Len VBA functions to break text apart in VBA
- Using the TRIM, CLEAN, Replace
and Lcase functions in Excel VBA to condition text for use in
logic
- How to test for a subtext within
a piece of text using the Instr VBA function
- Using the Split VBA
function to break data apart based on a specific delimiter
- How to use the C VBA functions
(Cdbl, Cdate,...) to flip data types for use in logic and
expressions
- How to determine date elements in Excel VBA
using the Month, Day, Year and Format VBA functions
- Using DateAdd and DateDiff in
VBA to add or subtract days, months, years...from a specific
date
- How to use string concatenation
to create and modify dates in your VBA code
- How to use the VBA For...Next
Loop and DateAdd, DateDiff and Format VBA functions to create
automated calendars
Key Methods for Tracking Excel
Ranges in Your VBA Code
for Data Fusion, Data Analysis and Data Visualization Purposes
- Why do you need to know how to
assemble multiple Range accessors together in VBA code to
isolate data on a worksheet
- Review of using the Cells, Rows,
Columns and Range accessors in your Excel VBA code
- How to isolate parts of a range
within another range in your VBA code by assembling multiple Range
accessors together in an object expression
- Using the Find accessor to
locate hard to find data on a worksheet
- Unique ways of using the Offset
accessor to locate data in cells around cells, great for
modeling and multi-row data headers
- Using the CurrentRegion accessor in your
VBA code to
track
a range of unknown size
- Alternate ways of tracking
ranges that change size in your VBA code
- Clever ways to use range names in
your VBA code to track data and flag worksheet positions
Using Excel VBA to Control Key Excel Operations
and Tools Vital to
Data Fusion, Data Analysis and Data Visualization
- Various high-end Excel VBA
methods for reading and writing to ranges (single cell values, ranges...)
- Various high-end Excel VBA
methods for
coping and pasting worksheet data (entire sheets, specific table
sections, rows, columns...)
- Worksheet data management
(inserting, deleting, and moving rows, columns, cells)
- Using Excel VBA to
control formatting for cells and various range configurations (fonts, colors, borders, number formats
and
alignments)
- Using Excel VBA to manage Excel
tables and queries
- Strategies and methods for using Excel VBA to
control Excel's filter tools like AutoFilter,
Advanced Filter and Remove Duplicates
- Using Excel VBA to
control the Sort, Text-to-Columns and grouping tools
- Using Excel VBA to create, edit
and delete formulas and to determine their cell and range
references
- Strategies and methods for using
Excel VBA to control cell and range names
- Various high-end Excel VBA
methods for adding and controlling Pivot Tables
- How to design For...Next and
For...Each Next loops that use these operations and tools
Methods for Using VBA Arrays and For...Next Loops
to Perform Advanced Data Analysis
- Benefits of using the VBA
For...Next loop to analyze worksheet data
- Strategies and methods for
designing VBA For...Next loops and logic to analyze worksheet data
arranged in various patterns (stop and go data, stacked tables,
oddly grouped data....)
- Strategies and methods for
designing multiple counters and logic to track start and stop
positions of data inside VBA For...Next loops
- Strategies and methods for
designing VBA For...Next loops and logic that deal with
shifting header names, column positions and multi-row headers in
your data
- How to declare, write to, and
read arrays in VBA
- How to create dynamic arrays in
VBA
- How to use Excel worksheet and VBA functions
that generate arrays in your VBA code
- How to read worksheet ranges as
arrays in VBA and process them using For...Next loops
- How to use Excel VBA to build
report worksheets
- How to use VBA For...next loops
to build worksheets from table values
Day-2
Controlling the Windows Folder and
File Systems, Registry; and Other
Programs Using VBA
- How do these programming
elements time into data visualization, analysis and fusion tools
- Strategies and methods for
controlling other programs
and libraries in VBA with early (tools/reference) and late
binding (CreateObject, GetObject)
- Using the VBA Object Browser and
on-line help to determine the
object hierarchy of another program
- Why store program information
and settings in the Windows Registry
- How to use VBA to store and
retrieve information in the Windows Registry
- Delimiting multiple pieces of program
information when storing in the Windows Registry
- Using the FileSystemObject in VBA to
move, create, delete and rename Windows folders and files
- Using VBA, the
FileSystemObject and logic to detect if program files and folders exist
- Strategies and methods for labeling files for use in a
data fusion environment
- Using the File and Folder
objects in VBA to determine folder and file names
- Overview of using VBA For...Each
Next loops to loop through folders and files
- How to deconstruct folder and
file
names using the Split and Instr VBA text functions
Data Fusion Methods in
Excel VBA for
Looping Through
Workbooks and Worksheets
- What is data fusion, how can it
be applied to worksheets and workbooks, and how to implement it
using Excel VBA
- Data fusion strategies for
naming and reading workbook files in a data fusion environment
- Benefits of using the Set
statement to track workbooks in VBA when you open them
- How to use Excel VBA to automate
the Excel Open dialog box turning it into a file selector
- Using Excel VBA to
name and save workbooks to different folders and closing them
- Using VBA For...Each Next loops,
various Excel methods and properties, and logic to loop through
open workbooks and their worksheets looking for data
- Using VBA For...Each Next and
For...Next loops, various Excel methods and properties, and
logic to loop through workbooks in folders, open them and scan
their worksheets looking for data
Data Fusion Purposes in Excel VBA to
Control Text Files
- VBA data fusion strategies for
storing data in multiple text files
- VBA strategies for naming and
reading text files in a data fusion environment
- Using VBA and the TextStream
object to open and close text files
- How to read and write to a text
file using VBA
- Using VBA Do loops, logic and
functions to scan
large and small text files
for key information and transfer that data to the worksheet
- How to clean misaligned
degraded text files and parse their information using Excel VBA
Data Fusion Methods in Excel VBA to
Control Databases
- Data fusion strategies for using
multiple databases along with Excel based data
- SQL language review
- How to use string
concatenation and loops to assemble a SQL statement in
your VBA code
- How to use DAO to
control Microsoft Access databases
- How to use ADO to
control SQL driven databases
- How to use ADO to
query and process data in workbooks and text files
How to Assemble and Use Excel VBA to Control Charts, Drawing Shapes
and the Worksheet for
Data Visualization Purposes
- How to automatically build
embedded charts and chart sheets using Excel VBA
- How to use VBA to size and place
embedded charts on the worksheet
- How to add and change a chart's series
using Excel VBA (ranges and arrays)
- How to use VBA including
For...Each Next loops to format various
chart elements
- How to use VBA to add and
control Excel drawing shapes, their format and their text
- Strategies for laying out visual
display areas on the worksheet so VBA can fill them in
- How to assemble and use these
elements to make data visualization tools for data fusion
programs
_EMAGENIT_2019.gif)
_EMAGENIT_2019.gif)
