Beginning Excel VBA for Business and Industry - Live Class

Class syllabus.

Class Brochure

Day-1

Excel VBA Language, VBA Editor, and Module Review

*This section discussed throughout the class when needed

• Review of VBA Editor, Modules, and how to debug procedures
• How to use Sub, Function, and property procedures in VBA
• Complete Review of the Excel VBA language and syntax
• How to translate your Excel tasks into a VBA procedure
• What are objects, properties, and methods and how to use them in your code?
• How to create logic in your code using the < , > , <=  And, Or, Not... operators

Tracking Ranges, Using Functions, and Controlling Formulas in VBA

• How to track ranges and cells in your macro code using Range, Cells, Rows, Columns...
• How to run worksheet and VBA functions in Excel VBA
• How to find table start positions, headers, and output rows using Find, MATCH, COUNTA...
• Summing, counting... data in your code using functions like MAX, SUMIFS, COUNTIFS...
• How to output calculated values to cells around a table
• How to use VBA to create, manage, and autofill formulas on the worksheet

Calculating Problems and Forms with Excel VBA

• Using Range, Cells, Offset, Find... to locate worksheet input and output cells
• How to layout problems and forms on the worksheet so VBA can read them
• Using cell names to easily track worksheet cells in your VBA code
• How to read and write to worksheet cells using Range, Cells, Offset, Value...
• How to perform calculations (i.e., expressions) in VBA using +, -, / , *, ( ), ...
• Using logic in your macro code to determine what calculations to perform
• Using a loop to read your form, lookup table data, and return it

 Controlling and Scanning Workbooks and Worksheets for Data

• Why store data in separate workbooks from your macro code workbook?
• Using the Set statement in Excel VBA to track workbooks and worksheets
• How to use Open, Close, Add, Save, SaveAs... in your code to control a workbook
• How to use Add, Delete, Move, Name... in your code to control a worksheet
• Scanning worksheets in open workbooks for data using loops, logic, and functions

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, write to, read, and close text files with VBA
• The basics of outputting text file data to the worksheet for analysis

Formatting Worksheet Tables and Copying / Pasting Data with VBA

• Using macros to copy and paste ranges, cells, rows, columns, entire worksheets...
• How to insert, delete, move...worksheet cells, rows, and columns with VBA
• Using VBA to apply number formats, fonts, cell colors, alignments... to cells and ranges
• Isolating different worksheet table parts and formatting them with Excel VBA

Day-2

Creating User Interfaces to Control Your VBA Macros

• Arranging Data Validation, Conditional Formatting, shapes, and cells to create worksheet UIs
• How to create a button from a drawing shape or picture to run a VBA procedure
• How to create a file picker in your code to select which data file to process
• Using macros to control Data Validation logic, formulas, limits, and message boxes
• Alerting users and answering questions in your code using VBA message boxes
• Using Excel VBA to control Conditional Formatting and Data Validation

Automating Excel's Data Tools to Create Reports

• How to automate Sort, Autofilter, Text Wizard, Remove Duplicates... to rapidly process data
• Using VBA to copy and paste filtered table data to a new report workbook / worksheet
• Using VBA and worksheet functions to calculate filtered data on report worksheets
• How to use Record Macro to quickly generate PivotTable and Excel table code

Using VBA to Calculate, Lookup, and Fill Worksheet Data

• Using loops and functions to calculate worksheet data rows and columns
• How to use loops and logic to automatically delete table rows and columns
• Using VBA to color data that is above or below a limit, value matching...
• Parsing and cleaning text data in VBA using functions like Mid, Instr, CLEAN, TRIM...
• Using loops to lookup table data and output it to worksheet rows and columns
• Using Excel VBA to read raw data and fill common worksheet tables or forms

Using Excel VBA to Create and Format Excel Charts

• How to use Excel VBA to create, format, and place Column, Line, Doughnut... charts
• Updating a pre-built chart's source data with Excel VBA
• Using macros to automatically add and delete chart elements like labels, gridlines...
• Using macros to color data points in a chart based on logic
• How to apply a custom chart template to a chart with VBA
• Combining VBA, charts, shapes, worksheets, and ranges to make reports

Creating Custom Worksheet Functions in VBA

• How custom worksheet functions (UDFs) can perform tasks that worksheet formulas cannot
• Using a single UDF instead of creating the same formula over and over again on the worksheet
• General VBA design rules for UDFs
• Designing UDF's that can accept cells, ranges, values, and arrays as arguments
• Using loops, logic, and functions to look up and calculate values inside a UDF
• Returning data back from UDFs to worksheet formulas

Prerequisites for our Excel VBA training.

• Never programmed
• Just used the Macro Recorder before
• Not programmed in over a year
• Programmed but have not used objects before
• Used Microsoft Excel and know its operational capabilities and its data tools
• Formatted worksheets and built charts
• Built basic worksheet formulas (=A1+A2)
• Used worksheet functions like SUM, MATCH, VLOOKUP, SUMIFS...

Class time, manual, and repeats.

• Audience:   Business, government, industry, military, engineering, science, techs...
• Duration:   9:00 - 5:00 CT
• # of Days:   2-days
• Instruction Type:   Live, hands-on
• Class Manual:  Our complete Excel manual for business and industry (TCEM).  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 >