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 to 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; creating PivotTables; and analyzing data.

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

See what you can learn.

Learn to use the Excel VBA language, modules, the debugger, and Excel objects, properties, and methods in your macro code.

Learn to use Excel VBA to open, create, and track workbooks; locate ranges; copy/paste data; automate Filters; and create formulas.

Learn to use Excel VBA to control forms and Data Validation; perform calculations; manage formulas; and lookup table values.

Learn to use Excel VBA loops and logic to find workbooks; calculate and color worksheet data; remove columns; and format tables.

Learn to use Excel VBA to read text files; parse text; create and calculate tables for charts; and adjust prebuilt chart data sources.

Learn to use Excel VBA to pick files; scan datasets, track worksheet headers, and fill various worksheet tables with data.

Learn to use Excel VBA to automate chart construction; format chart elements; color data points; and load premade reports with data.

Learn to use Excel VBA to create Excel PivotTables, Excel tables, slicers, timelines, and format them.

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.

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

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

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