Excel VBA Fundamentals Training for Engineers and Scientists - Public, Virtual, Onsite

Class syllabus.

View class examples >

Day-1

Excel VBA Language, VBA Editor, and Debugging Review

*This section discussed throughout the class when needed

• Review of the VBA Editor windows, toolbars, and debugger
• Review of the VBA language, modules, procedures, statements, and typing rules
• How to use shapes and pictures to create buttons that run your procedures
• Writing logic in VBA using the <, >, <=... operators along with IF, And, OR...
• Overview of objects, properties, and methods and how they control Excel?
• How to create object expressions in VBA and use Set statements to track objects
• Using Record Macro to investigate Excel VBA command syntax and arguments

How to Control Ranges, Run Functions, and Lookup Data in VBA

• How to track worksheet ranges and cells in your Excel VBA code
• How to cut, copy, paste, insert, clear... cells, rows, columns with VBA
• How to run worksheet and VBA functions in your macro code
• Performing date, math, statistical, and trig calculations in your macro code
• Performing various table lookups using Excel VBA and functions
• Using Excel VBA to place outputs and formulas around a table

Model / Problem Layout and Calculation with Excel VBA 

• How to layout model / problem interfaces that can be read by Excel VBA
• Controlling cell input using Data Validation and ActiveX controls
• How to use Excel VBA to read parameter information off the worksheet
• Automating calculations using operators (+,-,*...), functions, logic, and variables
• Leveraging Conditional Formatting to automatically format input / outputs
• Using cell names and Excel tables in your code to track inputs and data
• Automatically creating and modifying formulas with Excel VBA
• Using Excel VBA to control Excel’s calculation mode

Process / Calculate Worksheet Data and Fill Tables with VBA

• Using the Cells and Range properties inside a loop to access worksheet data
• Using loops, logic, and counters to process common worksheet data configurations
• Filling in data and formulas in a table with VBA
• Using macros to color cell data based on limits
• Tracking a report workbook in your code and transferring data to it

Controlling Text Data and Text Files in Your VBA Code

• Performing common text operations in your code using VBA and functions
• Using loops, logic, concatenation, and functions to clean / parse worksheet text
• Using Text Wizard and Text to Columns in VBA to process worksheet text
• How to open, read, write to, and close text files with VBA
• Using string concatenation in VBA to create text that is written to a text file
• The basics of outputting text file data to the worksheet for analysis

Controlling Workbooks, Worksheets, and Formats with VBA

• How to open, close, and save workbooks with Excel VBA
• Using the Set statement and names to track workbooks and worksheets
• How to add, delete, move, name, and protect worksheets with Excel VBA
• Automatically formatting worksheet tables (color, fonts, alignment...) with VBA
• Scanning open workbook / worksheets for data using loops and logic

Day-2

Building User Interfaces to Run Your Excel VBA Tools

• Design strategies for laying out UIs on the worksheet to run your code
• Building a file picker / saver in your macro code to command files
• How to use Data Validation, shapes, and cells to create powerful UI inputs
• How to use logic in your VBA code to integrate your UI inputs

Using Excel VBA to Fill, Read, and Consolidate Worksheet Forms

• Worksheet form layout, cell naming, and controlling inputs with Data Validation
• How to fill a worksheet form using Excel VBA
• Designing your code to loop through a form gathering its data
• Consolidating form data in a master workbook table using Excel VBA

Automating Excel's Data Tools to Rapidly Report Data

• Controlling the Sort, Remove Duplicates, and Grouping feature with VBA
• Controlling AutoFilter and Advanced Filter in your macro code
• Using macros to copy / paste filtered data to report workbook / worksheets
• How to use VBA to stack filtered data on a report worksheet
• Using Record Macro to generate PivotTable and Excel table VBA code

Automating Charts with Excel VBA

• How to use macros to create a chart sheet or embedded chart
• Using Excel VBA to update range sources for chart data
• Adding and deleting chart elements with VBA
• Formatting axes, series, gridlines...using Excel VBA
• Using VBA to color data points and move, format, change data labels
• Loading chart templates using Excel VBA

Creating Custom Worksheet Functions (UDFs) in Excel VBA

• Excel VBA design rules for custom worksheet functions (UDFs)
• Designing UDFs to accept cells, ranges, values, and arrays as arguments
• Using loops, logic, and functions in UDFs to perform complex calculations
• Returning scalars and arrays back to the worksheet from UDFs
• Packaging your UDFs in a function library for Excel wide use

Prerequisites for our Excel VBA training.

• Never programmed or not programmed in over a year
• Just used the Record Macro tool to obtain VBA code
• Programmed but have not used objects before or minimally
• Used Microsoft Excel and know its operational capabilities
• Built basic worksheet formulas (=A1+A2) and used worksheet functions like SUM
• Used Excel data processing tools like Sort, Autofilter, Advanced Filter, Group, and Remove Duplicates
• Built charts manually

Class time, manual, and repeats.

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

Contact us about the class.

Phone Number: 1.629.325.9937

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

You can email us at info@emagenit.com >