Advanced Excel VBA Methods Training for Engineers and Scientists

Our 3-day hands-on advanced Excel VBA training shows you how to build adaptive, smart, UI driven Excel tools for engineering and science. Key topics include:

• A complete review of the VBA language including variables, data types, constants, operators, expressions, loops, logic structures, string concatenation, and calling conventions
• Constructing objects in class modules and how they can help engineers and scientists
• Advanced analysis and formatting methods for complex engineering and science worksheet data sets
• Scanning Windows folders for engineering and science data stored in workbook and text files
• Advanced chart creation with Excel VBA including curve fits, data point conditional formatting, and limit line construction
• Using Excel VBA for advanced model / analysis problem control including trade studies, parameter upload / download, and automating Solver
• How to use Excel VBA to create rapid design and analysis UDF libraries
• Creating user interfaces (UI's) with VBA Userforms and ActiveX controls
• Constructing worksheet based user interfaces (UI's) using worksheet events, ActiveX controls, shapes, and Data Validation
• Creating engineering / science drawing programs on the worksheet with Excel VBA, shapes, ActiveX controls, and pictures
• How to use the Ribbon, custom toolbars, Windows Registry, and file / folder pickers in your Excel UI's
• Rapid data report construction by automating Excel's data tools with Excel VBA
• Controlling and reading text files in Excel VBA
• Excel VBA strategies for using ADO and SQL to control databases, workbooks, and text files
• Advanced custom worksheet function design (UDF) for engineers and scientists
• Using Excel VBA to control Word, PowerPoint, and Outlook
• Creating Excel Add-Ins for userform apps and UDF rapid design / analysis libraries

Select this Excel VBA training if you or your group have:

• 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

Day-1

Full Excel VBA Language, Object, and Error Trap Review for Engineers and Scientists (Discussed Throughout Class)

• A complete review of the VBA language including variables, data types, constants, operators, expressions, loops, logic structures, string concatenation, and calling conventions
• A complete review of VBA arrays including adjustable arrays, the ArrayList object, and the Array VBA function
• Advanced VBA Editor methods for debugging and viewing your code
• A review of objects, properties and methods and how are they used to control a program
• Advanced Excel VBA strategies for using the Set statement to track objects in your VBA code
• Advanced Excel VBA strategies for handling run-time errors using logic and error traps
• How to use CreateObject and GetObject to command other programs and class libraries
• How to create objects in Excel using Class modules, Subs, Functions, Properties, and public variables

Processing and Formatting Advanced Engineering / Science Worksheet Data Sets

• Review of basic Excel VBA data processing methods, loops, and logic
• Using worksheet and VBA functions in your VBA code to process data
• How to create proper engineering and science tables on the worksheet for large scale data storage
• Review of using the Cells, Range, Rows, Columns, CurrentRegion, SpecialCells, and Find properties to lock onto complex worksheet data configurations
• How to use VBA to parse, interpret, and fix worksheet text data using the Trim, Clean, Left, Right, Mid, Replace, Instr, Split... functions
• How to use VBA loops, logic, and functions to analyze complex worksheet table patterns like stacked tables, fragmented tables, indented tables, double row headers...
• Advanced VBA techniques for inserting / deleting data and formulas in worksheet tables
• How to use VBA to lock onto various worksheet data configurations and use them in your code with the statistical, math, and lookup worksheet functions
• Advanced VBA strategies for finding max, min... and starting / end points in complex data sets
• Using Excel VBA to build summary tables from complex data patterns and perform advanced table lookups
• How to import a range as an array in your Excel VBA code to speed up data processing

How to Use Excel VBA to Search for Engineering / Science Data in Folders

• How to use Excel VBA to access and control the Windows folder system using the FileSystemObject
• How to use Excel VBA to make folders on the fly
• Review of using Excel VBA to add, open, close, and save workbook files
• How to use VBA For...Each Next loops to loop through data workbooks or text files in folders
• Various VBA logic strategies for detecting data in a workbook
• Using Excel VBA loops and the Remove Duplicates tool to add, move, and rename worksheets in data analysis apps
• Advanced Excel VBA looping strategies for processing data in multiple workbooks / worksheets and text files

How to Create Advanced Engineering / Science Charts Using Excel VBA

• Review of how to use Excel VBA to create chart sheets and embedded worksheet charts
• Using VBA arrays to create a chart series
• How to use VBA to create combo charts for engineering / science
• Using Excel VBA to create chart limit lines and curve fits
• Advanced Excel VBA formatting methods including data point conditional formatting and hiding / displaying series
• Using Excel VBA to move a design point or analysis line within a chart
• How to write Excel VBA code that creates and arranges multiple charts on a report worksheet

Advanced Model / Analysis Problem Control Using VBA

• Review of model / analysis problem layout on the worksheet (ActiveX control integration discussed below)
• Using Excel VBA to control cell names and formula construction on the worksheet
• Design strategies for engineering / science problems that use multiple workbooks / worksheets that integrate teams
• How to use the VBA loops to build parameter upload, download, and batch processing procedures
• How to perform engineering / science problem trade studies using Excel VBA
• Using Excel VBA to automate Solver

Day-2

Creating User Interfaces (UI's) With VBA Userforms and ActiveX Controls

• How to use userforms and ActiveX controls to build model, analysis, and data processing UI's
• Creating modal and non-modal userforms in VBA, when is each type used?
• How to create VBA procedures and logic that launch VBA userforms for viewing
• How to create and layout ActiveX controls and pictures on userforms
• How to create events for ActiveX controls on userforms
• Using VBA to control text box, scroll bar, spin button, check box, option button, list box, and combo box controls on userforms
• VBA methods for formatting data inside of list boxes and combo boxes
• Loading table data into list boxes and combo boxes using worksheet ranges, VBA arrays, ArrayList object, and other Excel data tools like Sort and RemoveDuplicates
• How to use Excel VBA to read multi-column list boxes and combo boxes
• Creating multi-select list boxes on userforms and commanding them with VBA
• How to create a list box on a userform in VBA that fills in another list box using events
• Designing VBA procedures that launch and preset ActiveX controls values on userforms
• How to create ActiveX control events in your Excel VBA code that trap keystrokes on a userform

Constructing Worksheet Based User Interfaces (UI's) Using Worksheet Events, ActiveX, and Data Validation

• How to use the worksheet, events, ActiveX controls and Data Validation to build model, analysis, and data processing UI's
• How to use Excel VBA to control ActiveX controls on the worksheet
• Using Excel VBA to control Data Validation on the worksheet
• Creating events that respond to ActiveX controls on the worksheet
• Constructing interfaces that respond to VBA worksheet events like typing, clicking, double clicking, and right-mouse clicking
• Using Workbook level events like Open, Close, Save... to control your worksheet based interfaces
• Designing advanced VBA logic to control worksheet events and how they execute
• Using Excel VBA, events, and ActiveX controls to control charts
• Using the Image ActiveX control and events on the worksheet to create chart digitizers

Creating Engineering / Science Drawing Programs With Excel VBA

• How to use Excel VBA 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 engineering / science diagrams
• Using the Freeform shape, Line shape, Trigonometry, and VBA to draw advanced engineering / science diagrams on the worksheet
• Controlling shape text and formats with Excel VBA
• Assigning Sub procedures to shapes and pictures with VBA, key to advanced UI development
• Determining what shape was clicked inside a procedure using VBA, key to advanced UI development
• Using Excel VBA, functions, shapes, and the worksheet to create project management / timeline tools

Using the Ribbon, Custom Toolbars, File / Folder Pickers, and the Windows Registry In Your UI's

• How to use Excel VBA to build a file / folder picker that steers VBA to the right file or folder
• How to create a Ribbon tab, its controls, and assign them VBA procedures in an Excel workbook file
• Using VBA userforms to quickly create floating toolbars that run your Excel apps
• How to use the OnTime and OnKey methods in VBA to create procedures that run at a specific time or by pressing shortcut keys
• How to use the Run method in VBA to run VBA procedures in other workbooks, add-ins, or call by string
• How to store and retrieve program information in the Windows Registry

Day-3

Rapid Data Report Construction by Automating Excel's Data Tools With Excel VBA

• Advanced VBA looping and logic strategies for quickly turning Autofilter, Sort, Remove Duplicates, and Advanced Filter into large scale data processing tools
• Using Excel VBA to build report workbooks, build report worksheets, and transfer filtered data
• How to use Excel VBA to control Excel tables and refresh / change their queries
• Creating and controlling PivotTables with Excel VBA
• How to automate the Analysis Toolpak with VBA

Controlling and Reading Text Files and External Data Sources in Excel VBA

• Various Excel VBA methods for processing data in text files
• How to use Excel VBA to open, read, write, and close engineering and science text files
• Cleaning, parsing, and reorganizing corrupted text file data using various functions, the worksheet, and the Excel Text to Columns tool in Excel VBA
• How to use different string concatenation techniques in Excel VBA to assemble text file data
• Using advanced VBA string concatenation to stack data in text files for user interface purposes
• How to use VBA to extract data from large text files (100,000 + rows of data)
• Basic SQL language review
• How to use string concatenation and loops to assemble a SQL statement in your VBA code
• How to use ADO to control and query SQL driven databases, text files and worksheet tables

Creating Report Tools Using Excel VBA, PowerPoint, Word, and Outlook

• The basics of using Excel VBA to automate Microsoft Word
• The basics of using Excel VBA to automate PowerPoint
• The basics of using Excel VBA to automate Microsoft Outlook

Advanced Custom Worksheet Function Design (UDF) for Engineers and Scientists

• Review of creating and running a UDF from the worksheet
• How to create cell, range, value, and array arguments for UDF's
• Design strategies for UDF's that access other programs and data sources
• Construction strategies for UDF's that calculate advanced equations, calculate numerical methods, implement advanced logic, and gather data
• Design strategies for developing system model UDF's that accept large amounts of parameter information

Creating Excel Add-Ins for Userform Apps and UDF Rapid Design / Analysis Libraries

• Designing userform based apps to run from Excel Add-Ins
• Designing Excel Add-Ins to run from the Ribbon or custom toolbars
• Creating UDF library add-ins for worksheet based rapid design and analysis environments