Advanced Excel VBA for Engineers and Scientists- Class

Class syllabus.

Download Class Brochure

Day-1

 Advanced Excel VBA Language and Object Topics

* Topics below discussed when needed in the class

• Advanced debugging methods for Excel App development
• Using Excel and VBA element object modules for events
• VBA array control and using the ArrayList object in your code
• Tracking objects in your code using the Set statement and For...Each Next loops
• Using the Run method, concatenation, and logic to execute macros on the fly
• Creating your own objects using Class modules, procedures, and public variables
• Using the CreateObject / GetObject functions to create and track objects in VBA

Advanced Folder, File, and Workbook Control Using Excel VBA

• Overview of using the FileSystemObject to control the Windows folder and file system
• How to test for folder and file existence in your VBA code using FolderExists, FileExists...
• VBA methods for creating, moving, copying, and deleting folders for your Excel apps
• Controlling your data storage workbooks using Open, Close, Save, Add, and SaveAs in VBA
• Using concatenation and key functions in VBA to build workbook paths and file names
• VBA methods for storing and retrieving workbook data in a multi-user, high demand environment
• How to build a file / folder picker for your Excel apps

Advanced Report Generation Using Excel's Data Tools and Toolpak

• VBA methods for determining where datasets start and end on a worksheet
• Looping Autofilter, Sort... in your macro code to rapidly filter large datasets
• Using VBA macros to copy, paste, and stack filtered data on multiple report worksheets
• Using loops, logic, and functions to create multiple PivotTables and Pivot Chart reports
• Automating the Analysis Toolpak to process your engineering and science data

Using Loops, Logic, Functions, and Ranges to Process Complex Data Patterns

• Tracking complex worksheet data ranges, headers, data position... in your code
• Using the Math, Statistical, and Lookup functions in VBA to analyze complex datasets
• Reading ranges into VBA arrays for max processing speed
• Using loops, logic, and functions to process stacked tables, table fragments, indented tables...
• Using loops, logic, and functions to find max, min, steady state... values in data
• Using Excel VBA to fill in various advanced worksheet table patterns; outputting arrays to the worksheet
• Inserting worksheet rows / columns in your data tables and auto filling them with formulas or data

Advanced Chart Design and Automation

• Combining charts, shapes, and Conditional Formatting to make visual displays
• Changing chart ranges with VBA and loading VBA arrays as source data
• Using macros to create and format charts like xy scatter, column, pie, combo...
• Using VBA to create chart limit lines, color data points, hide /display series, build curve fits...
• Adding multiple tables to a chart using VBA
• Using VBA to create and arrange multiple charts on a worksheet
• How to use Excel VBA to create trade study plots

Creating, Reading, and Parsing Text Files with Excel VBA

• Automating Text Wizard and Text to Columns to import text data on the worksheet
• Using the Text Stream object to open, read, write... text files in your Excel VBA code
• Rapidly loading text file data onto the worksheet using loops, logic, and arrays
• Using loops, functions, and string concatenation to assemble data and write it to text files
• Cleaning and parsing text data in VBA using functions like Trim, Left, Replace, Instr, Split...
• Using VBA to create arrays from text file data; used to load ActiveX controls

Storing Your Program Settings in the Windows Registry

• Why store your program settings in the Windows Registry?
• Using GetSetting, SaveSetting, and DeleteSetting to write and retrieve program settings in the Windows Registry
• How to stack array information in the registry and retrieve it

Day-2

Advanced Model Construction, Trade Study, and Solver Automation

• Using Conditional Formatting, logic, and VBA to auto format model layout
• Automating Data Validation to control model input
• Using worksheet events to control your model and its execution
• Controlling cell names, Excel tables, and formulas on the worksheet with VBA
• Designing Sub procedures and UDFs to calculate equations and numerical methods
• Changing model configuration / inputs / outputs on the worksheet using templates and VBA
•  Uploading and downloading model parameters with Excel VBA
• Using VBA to automate trade studies and Solver on the worksheet

Advanced GUI Construction Using VBA, Sheets, Charts, Controls, Shapes, and Events

• How to create and position ActiveX controls on worksheets
• Creating event procedures for worksheet-based ActiveX controls (Click, Change...)
• Controlling worksheet-based ActiveX controls in VBA (loading combo boxes, setting scroll bars...)
• Constructing advanced UIs that respond to worksheet / workbook events like Open, Calculate...
• Integrating VBA, ActiveX controls, Data Validation, Charts, and shapes to produce visual displays
• Creating a Ribbon tab, its controls, and assign them procedures to run
• Using the OnKey and OnTime methods to run your code by shortcut key or at a specific time

Advanced GUI Construction Using Userforms, ActiveX Controls, and Events

• How to create userforms and place ActiveX controls, pictures, and text on them
• Launching userforms; creating a modal vs. non-modal userform
• How to create event procedures procedure for userform ActiveX controls (Click, KeyDown...)
• Using Excel VBA to load list boxes, combo boxes, and set other control values
• Reading multi-column and multi-select userform list boxes in your code
• Designing a list box or combo box to fill another list or combo box
• Designing userform based floating toolbars to run your Excel apps
• How to turn your app into an Excel Add-In

Searching for Data in Multiple 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

Technical Diagram Automation Using the Worksheet, Shapes, and VBA

• How to use macros to add, edit, delete, position... 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 diagrams
• Controlling shape text and formats with Excel VBA
• Using VBA to assign Sub procedures to shapes and pictures on the fly
• Using VBA and logic to determine what shape button was clicked

Status Tool Design Using the Worksheet, Shapes, and VBA

• Using worksheet tables, cells, and shapes to quickly build 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 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, milestones, and timelines

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

Intro to Controlling PowerPoint, Word, and Outlook with VBA

• 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

Prerequisites for our advanced Excel VBA 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, 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:   2013-Present, Microsoft Excel 365, Windows and Mac
• Repeats:   Repeat your Excel training. Details >

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