Advanced Excel VBA Design for Engineers / Scientists Class

Class overview.

What makes our training different?

Live, hands-on, industry topics
Bring your problems, ask tough questions
Taught by an engineer
Limited class size

Our 3-day class shows you how to use Excel and VBA to produce powerful GUI driven tools for engineering and science. It focuses on how to combine the worksheet, ActiveX controls, shapes, pictures, userforms, events, and VBA to develop high-powered UIs that control things like calculations, models, charts, trade studies, and data processing.

Other key topics discussed include controlling folders and files; scanning multiple folders / workbooks for data; text file reading and writing, controlling databases; uploading / downloading / storing problem parameters; running batch file problems; and auto running Solver and the Analysis ToolPak.

Also reviewed is how to use shapes, pictures, charts, and VBA to develop powerful dashboard tools that display various statuses, KPIs, chart data, and technical diagrams. Join us and our class will show you how to push Excel VBA to the redline and create advanced tools that will solve your toughest tasks.

Some key topics we cover in class.

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
Basics of using Excel VBA to control Word, PowerPoint, and Outlook for report generation purposes

Prerequisites for our Excel training.

Used the Excel VBA language in a basic capacity
Used variables, loops, and logic before and know how they basically operate
Used objects, properties, and methods before in code to control a program
Used Excel drawing shapes and built charts manually
Built basic worksheet formulas (=A1+A2) and used worksheet functions like SUM, Match, COUNTIF, COUNTA...
Used Excel's data processing tools like Autofilter, Sort, Remove Duplicates, and PivotTables

Class time, duration, manual, and repeats.

Audience: Engineers, scientists and technicians
Duration: 9:00 - 5:00 CT
# of Days: 3-days
Instruction Type: Live, hands-on, taught by engineer
Class Manual: Our complete Excel manual for engineering and science (EAE). Details >
Excel Versions: 2010-2019, Microsoft 365, Windows and Mac
Repeats: Repeat your Excel training. Details >

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 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 Form 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

Basics of automating Microsoft Word for report generation purposes
Basics of automating Microsoft PowerPoint for report generation purposes
Using macros to email and read emails in Microsoft Outlook

If you need to contact us about our class.

Phone Number: 1.805.498.7162

Business Hours: Mon-Fri 8:00 AM - 7:00 PM CT

You can email us at info@emagenit.com

Advanced Excel VBA Design for Engineers and Scientists

Push Excel to the redline and create hi-tech Excel VBA apps that solve complex engineering and science projects.