How our training can help you.
Our 3-day workshop shows you how to use Excel and VBA to produce
hi-tech UI based tools for engineering and science. Excel and VBA have
amazing "off the shelf" user interface elements that can be rapidly
employed to help solve technical problems.
Coupling Excel's UI features with VBA's ability to
control calculations, data, charts, databases, workbooks, userforms... allows
a host of high-powered tools to be developed.
Possibilities include model tools that upload
data, run Solver, perform trade studies, and then carpet plot it all or
data processing tools that scan folders, analyze 100's of workbooks,
chart their data, and then email the report. Amazing visual tools can
also developed that display various statuses, chart data, draw diagrams, hide and show
pictures...etc.
Join us and we will show you how to push
Excel VBA to the redline and create advanced tools that will solve your
toughest tasks.
Key advanced Excel VBA topics covered.
- Review of the Excel VBA language, VBA Editor,
debugging, and objects
- How to use Excel VBA to control Windows
workbooks, files,
and folders
- Advanced model and analysis problem
construction integrating trade studies, Solver, and UDFs
- Advanced report automation using VBA,
PivotTables, and Excel's data tools
- Using loops and logic to
process advanced engineering / science data
- Advanced chart automation covering formatting, limit lines, curve
fits, combined charts, and multi-chart layout on the worksheet
- How to process engineering / science data in folders, multiple
workbooks, and on multiple worksheets
- Constructing advanced UIs using userforms,
events, the Ribbon, file / folder pickers, and ActiveX controls
- Constructing advanced worksheet UIs using
VBA, events,
shapes, worksheets, cells, Data Validation, and ActiveX controls
- Constructing advanced engineering /
science dashboards using Excel VBA, shapes, pictures, cells, and charts
- Using Excel VBA to build advanced
engineering / science diagrams on
the worksheet
- Using SQL, ADO, and VBA to control and query
databases, text files, and workbooks
- How to use Excel VBA to control Word,
PowerPoint, and Outlook for report generation purposes
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 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 Object Review for Engineers and Scientists (Discussed Where Needed)
- A complete review of the VBA
Editor and language including variables, arrays, data types,
constants, operators, expressions, loops, logic, and calling conventions
- A complete review of arrays
including VBA arrays, the ArrayList object, and the Array VBA
function
- How to create your own objects using Class modules, Subs, Functions, Properties, and public variables
- Tracking objects and other
programs in your code using the Set statement and the
CreateObject / GetObject functions
- Strategies for handling run-time errors using logic and error traps
Using Excel VBA to Control
Folders and Parameter / Data Workbooks
- Why store engineering / science
parameters and data in
separate workbooks and use Excel VBA to access them?
- How to create, move, delete...
folders / files using FileSystemObject, File, Folder... in your
code
- Controlling your storage workbooks
using Open, Close, Save, Add, and SaveAs in VBA
- How to test for folder and file
existence in your code using FolderExists, FileExists...
- How to use string concatenation to
build path and file names for your parameter / data workbooks
Advanced Model and Analysis Problem
Construction, Trade Studies, and Solver Automation
- Problem layout on the worksheet
and using Data Validation to control user entry
- Designing Sub procedures and UDFs
to calculate engineering / science equations
- Using macros to control cell names,
tables names, formula construction, and to toggle formulas on the worksheet
- Design strategies for
models / problems that use multiple workbooks, worksheets, and
integrate multiple teams
- How to use Excel VBA to build parameter upload, download, and batch processing procedures
- Automating Solver and performing
trade studies using macros and the worksheet
Rapid Report Generation Using Excel's Data Tools
and Excel VBA
- Looping Autofilter, Sort, Remove
Duplicates, and Advanced Filter in your code to create rapid data
processing tools
- How to use loops, logic, and
functions to create multiple PivotTable and Pivot Chart reports
- Using VBA macros to copy / paste /
stack
filtered engineering and science data in report worksheets
- Building filter logic on the fly inside
Excel VBA loops
- Using Excel VBA to build
report workbooks / worksheets to store your filtered data
- Automating the Analysis Toolpak to
process your engineering / science data
- Using VBA to delete or move
worksheet rows, cells, and columns in your data
Using Loops and Logic to
Process Advanced Engineering / Science Data
- Tracking worksheet table size,
headers, position... using Range, Cells, CurrentRegion, MATCH,
Find, Address... in your code
- Using statistical, math, text... functions
in your macro code to rapidly process engineering / science data
- Using VBA to analyze complex worksheet table patterns like stacked tables, fragmented tables, indented tables, double row headers...
- How to use loops and logic to find
max, min, steady state... values in your engineering / science data
- Using VBA to fill in various
data patterns in report tables
- Automatically inserting worksheet
rows and columns in your data and filling them with
formulas or values
- How to use VBA to perform
multi-table lookups and output the results to specific rows or
columns in your data
Advanced Engineering / Science
Chart Automation
- How to use macros to create and
format charts like xy scatter, column, pie, combo...
- How to create a chart series from
VBA arrays, great for emailing chart reports
- Using macros to create limit lines,
color data points, hide /display series, build curve fits...
- How to use macros to add multiple
tables to a chart
- How to use VBA to create and
arrange multiple charts on a worksheet (chart report)
- How to create trade study plots using
Excel VBA
Day-2
Storing Your Program Settings in the Windows Registry
- Why store your program settings in the Windows registry
and not in your code workbook?
- Using GetSetting, SaveSetting, and
DeleteSetting to write and retrieve program settings in the Windows Registry
- How to use delimiters in your code to stack
program information in the registry and use Split to retrieve it
Creating Advanced UIs from
Worksheets, Controls, Events, File / Folder Pickers, and Ribbon
- How to construct advanced worksheet
based UIs to control your models, analysis, and data processing
tools
- How to create and position ActiveX
controls on worksheets
- Creating event procedures that run
when a worksheet based control is clicked, changed, typed in,
moused over...
- Using Excel VBA to control and
read worksheet based controls and integrate their selections
- Constructing advanced UIs that respond to worksheet
and workbook events like
Open, BeforeClose, Calculate, SelectionChange,
BeforeRightClick...
- How to build a file / folder picker
for your worksheet based UI
- Using shapes and VBA to create
simple program setting displays for your worksheet UIs
- How to create a Ribbon tab, its
controls, and assign them procedures to run
- How to use the OnTime and OnKey methods to
run your code at a specific time or by pressing shortcut keys
Advanced UI
Construction Using VBA Userforms, Controls, Events, and Add-Ins
- How to construct advanced userform based
UIs to control your model, analysis, and data processing tools
- How to create and position ActiveX
controls on userforms including pictures and text
- Creating event procedures that run
when a userform based ActiveX control is clicked, changed, typed
in, moused over...
- Using Excel VBA to read and
control userform based ActiveX controls and integrate their
selections
- Launching
userforms and presetting their ActiveX control values with Excel
VBA
- Reading multi-column and
multi-select userform list boxes in your code
- How to design a list box or combo
box on a
userform that fills in another box
- Designing userform based floating toolbars
to run your Excel tools
- How to design and package userform based apps to run
as Excel Add-Ins
Creating and Reading
Engineering / Science Text Files
with Excel VBA
- Rapidly importing and
parsing engineering / science text data using VBA, Text Wizard, and Text to
Columns
- How to open, read, write, modify,
and close text files in your Excel VBA code
- Assembling text data and writing
text files using Excel VBA loops and string concatenation
- Loading specific data from a text
file onto a worksheet using loops, logic, and arrays in your
macro code
- Using macros to parse and clean worksheet text data using
functions like Trim, Clean, Left, Right, Replace, Instr,
Split...
Searching for Engineering /
Science Data in Workbooks, Worksheets, and Folders
- Design strategies for naming workbooks and folders for large scale data storage
and access
- Scanning for a specific dataset in open workbooks using For...Each Next loops, logic, and functions
- Scanning for a dataset on multiple worksheets using For...Each Next loops, logic, and functions
- Scanning for specific data workbooks
and text files in folders using For...Each Next loops, logic, and functions
Day-3
Advanced Diagram
Automation for Engineers and Scientists
- How to use macros to add, edit, delete, hide, resize, and position shapes and pictures on the worksheet
- Overview of how to use names to track shapes and pictures on the worksheet
- Using the worksheet as a grid to position
various engineering / science diagrams
- Using the Freeform shape, Line shape, Trigonometry, and VBA to draw advanced engineering / science diagrams on the worksheet
- Controlling shape text as well as formats with Excel VBA
- Using macros to assign Sub procedures to shapes and pictures, key to advanced UI development
- Determining what shape was clicked
inside a procedure, designing advanced picture driven interfaces
Advanced Excel VBA Dashboard
Design for Engineers and Scientists
- Review of various dashboard types,
scorecards, metrics, benchmarks, and KPIs
- How to use worksheets, charts, shapes, cells,
pictures, and VBA to build engineering / science dashboard displays
- Assembling dashboard controls from
Data Validation, ActiveX controls, cells, and Excel VBA
- Creating hi-tech dashboard
controls using VBA, shapes, and pictures
- How to integrate dashboard UI
selections in your Excel VBA code
- How to use macros to consolidate data
and build tables for dashboard charts
Advanced Status Tool
Design for Engineers and Scientists
- Using worksheet tables, cells, and
shapes to quickly build various engineering / science status tools
- Tracking status info in team
workbooks using headers and specific scripts
- How to easily track and organize
team workbooks storing status data
- Processing status data in multiple workbooks and worksheets
using loops and logic
- Using worksheet tables and Excel
VBA to display and color status data
- Using Excel VBA, shapes, pictures,
and cells to create unique status displays
- Using Excel VBA, shapes, and the worksheet to
rapidly create milestone and timeline tools
Using SQL and ADO to
Control Databases, Text files, and Workbooks
- Review of SQL and how to
write a query
- How to insert, update, and delete
database data using SQL
- How to use string concatenation to assemble a SQL statement in your VBA code
- How to use ADO to query databases
like Access, SQL Server, and Oracle
- How to query text files and
workbooks using ADO and SQL
Using Excel VBA to Automate PowerPoint, Word, and Outlook
- Automating Microsoft Word for
report generation purposes
- Automating Microsoft PowerPoint
for report generation purposes
- Using macros to email and read
emails in Microsoft Outlook
Additional Classes:
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.