Advanced Excel VBA Design for Engineers and Scientists

What we can teach you.

No time to attend, get a manual instead.

Key Excel VBA topics covered 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

Class examples.

Pricing and class options.

Detailed class syllabus.

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

• 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

Excel VBA skills needed for the class.

The class details.

• Audience:   Engineers, scientists and technicians
• Duration:   9:00 - 5:00 CT
• # of Days:   3-days
• Instruction Type:   Hands-on, live, instructor taught training
• Full Excel Manual Included:   Our 2200 + page EAE manual full of Excel tips, how-to-do topics and Excel examples included with the training. Details >
• Excel Versions:   2010-2019, Microsoft 365, Windows and Mac
• Repeats:   Repeat your Excel training. Details >
• Personal Examples:   We review our attendees personal examples in our training instruction
• Available Training Formats:
  • In Person Training >
  • Live Virtual Learning >
  • Company Onsite >

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

Contact us by form.