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; typing the Excel VBA language; and program logic.

In addition, it covers how use VBA to command key Excel elements like workbooks, worksheets, ranges, formulas, charts, and Excel's data tools. Also discussed is how to use Excel VBA to perform key tasks like formatting tables; automating calculations; performing table lookups, 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 use VBA to calculate various worksheet problems and forms
How to track ranges and use functions in VBA to calculate and lookup worksheet data
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
Controlling, tracking, and scanning workbooks and worksheets with Excel VBA
Using Excel VBA to run Excel's data tools like PivotTables, Sort, AutoFilter...
Creating user interfaces to run your Excel VBA tools
Using loops and logic to calculate and lookup worksheet data
Using Excel VBA to fill common report tables
Creating and formatting charts using Excel VBA
Designing custom worksheet functions (UDFs) to replace 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

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

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 and mange formulas on the worksheet

Controlling and Scanning 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
Scanning worksheets in open workbooks for data using loops, logic, and functions

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 Simple User Interfaces to Control Your Macros

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

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 a raw data and fill common tables or forms

Using Excel VBA to Create and 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

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 lookup and calculate values inside a UDF
Returning data 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