Beginning Excel VBA for Business and Industry

How our class will boost your Excel VBA skills.

Our 2-day class shows you the critical Excel VBA skills needed to start making automated macro tools right away. It covers the essential Excel VBA topics you need to know like using the VBA Editor and modules; code debugging; using the Excel VBA language; and program logic.

We also cover how to use Excel VBA to command key Excel elements like workbooks / worksheets, ranges, formulas, charts, PivotTables, filtering, and sorting. Also discussed is how to use Excel VBA to perform key tasks like formatting tables; automating calculations; table lookups; filling forms, and analyzing data.

Read our detailed outline > below and see everything you'll discover in our class.

Take a video tour of what you'll learn.

Review of the Excel VBA language, procedures, VBA Editor, debugging, and modules
How to track ranges and use functions in VBA to calculate and lookup worksheet data
How to use VBA to calculate various worksheet problems and forms and return values to them
How to create, modify, and delete worksheet formulas with Excel VBA
Using Excel VBA to format worksheet data and insert, delete, move.... rows, columns, and cells
Tracking and controlling data workbooks and worksheets in your VBA code
Using Excel VBA to run Excel's data tools like PivotTables, Sort, AutoFilter, Text Wizard...
Creating user interfaces to run your Excel VBA tools
Using loops and logic to analyze / calculate / lookup downloaded table data
Using Excel VBA to generate report tables and fill forms with table data
Finding and tracking data in external workbooks and worksheets with Excel VBA
Creating and formatting charts using Excel VBA
Designing custom worksheet functions (UDFs) to solve complex calculations

Some key topics we cover in class.

4 ways to train.

Virtual

View the dates, signup, and pricing for our hands-on live virtual training.
Virtual Signup > Learn More >

In-Person

View the dates, signup, and pricing for our hands-on in-person training.
In-Person Signup > Learn More >

Manual

Can't take the class, buy the self-study manual, apply the price later.
View Manual >

Onsite

Learn how we can help your business save time and effort in Excel.
View Onsite Details >

Class syllabus.

How we run the class: We focus our training on what our customers need. When training begins, we analyze those needs and shift our training outline appropriately. We will stress topics or add topics that our customers want. No two training sessions are ever the same with EMAGENIT.

Download 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

How to Track Ranges, Use Functions, and AutoFill 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
Tracking changing worksheet tables using CurrentRegion, COUNTA...
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 and auto fill formulas in table columns and rows

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

Formatting Worksheet Tables and Copying / Pasting 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 table parts and formatting them with Excel VBA

Tracking / Controlling Workbooks and Worksheets with Excel VBA

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

Automating PivotTables and Excel's Data Tools with VBA

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 VBA and Record Macro to quickly create and control PivotTables and Excel tables
How to interpret Record Macro code and modify it so it works reliably in your procedures

Creating User Interfaces for Your Excel VBA Tools

Arranging Data Validation, drawing shapes, pictures, and cells on the worksheet to create 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

Day-2

Using Loops and Logic to Analyze / Calculate / Lookup Table Data

Using loops and logic to process worksheet downloads like financial, inventory...
How to use loops and logic to automatically delete table rows and columns
Using macros to color data that is above or below a limit, does not match...
Parsing and cleaning text data in VBA using functions like Left, Mid, Instr, CLEAN, TRIM...
Using VBA to lookup table data and output to table rows and columns
Using loops and functions to calculate data and output it to table rows and columns

How to Use VBA to Generate Report Tables and Fill Forms with Table Data

Using loops and logic to copy and paste row and column data to a report worksheet
How to use loops and logic to stack copied data on a report worksheet
Using loops and logic to fill in common table patterns on a report worksheet
Accessing template cells using the Range, Find, Cells, Column, Row... in your code
Using logic, functions, and loops to scan data, perform calculations, and fill in worksheet forms
Using VBA to automatically create, modify, and delete worksheet formulas in your forms

Searching for External Workbook / Worksheet Data with Excel VBA

The advantages of storing data in separate workbooks and worksheets from your code workbook
Slightly tweaking your code to scan for data in open workbooks and on multiple worksheets
Scanning for an open workbook with a specific data set using For...Each Next loops, logic, and functions
Scanning multiple worksheets for a specific data set using For...Each Next loops, logic, and functions

Using Excel VBA to Create / Format Excel Charts

How to use Excel VBA to create and format Column, Line, Pie... 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

Building UDFs (Custom Functions) to Solve Complex Worksheet Calculations

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
Implementing loops, logic, and functions in UDFs to analyze data, lookup values, and calculate values
Returning single values and arrays back from UDFs to worksheet formulas

Additional Class Information

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 >

Form >

Beginning Excel VBA for Business and Industry - Class