Advanced Excel VBA Design for Engineers and Scientists

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

Select a Class Excel Data Analysis Engr/Sci Excel VBA Fundamentals Engr/Sci Excel VBA Data Analysis Engr/Sci Advanced Excel VBA Methods Engr/Sci Calling Fortran from Excel VBA Excel Classes All Disciplines