Beginning Excel VBA Class for Business and Industry

Class overview.

What makes our training different?

Live, hands-on, industry focused topics
Bring your own problems, ask tough questions
Taught by an Excel software developer
Limited class size

Our 2-day class shows you the critical Excel VBA skills needed to start designing automated Excel tools immediately. It covers critical topics like the VBA Editor; modules; code debugging; the Excel VBA language and typing rules.

Other VBA topics include automated calculations; controlling workbooks / worksheets; formatting; table lookups; and processing data on worksheets.

Also discussed is how to automate Excel's data tools, charts, and PivotTables for rapid report creation. Join us and our class will show you how to use Excel VBA to quickly solve your repetitious Excel tasks. See the Video >

Some key topics we cover in class.

Complete review of the Excel VBA language, VBA Editor, debugging, procedures, and modules
How to read cell values, perform calculations, and return values to worksheets
How to use functions in VBA to calculate and lookup worksheet data
Using VBA to create formulas for worksheet data sets and place or fill them around the data
Using Excel VBA to quickly format worksheet data and insert, delete, and move rows, columns, and cells
Tracking and controlling data workbooks and worksheets in your VBA code
Controlling PivotTables, Sort, AutoFilter, Text Wizard, and Remove Duplicates with VBA to rapidly process data
Creating basic user interfaces to run your Excel VBA tools
Using loops and logic to analyze / calculate large, downloaded data sets
Using Excel VBA to swiftly fill in pre-built reports
Finding and tracking data in external workbooks and worksheets with Excel VBA
Using Excel VBA to rapidly fill worksheet forms with data and formulas
Creating and formatting charts using Excel VBA

Prerequisites for our Excel 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, duration, 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: 2010-2019, Microsoft 365, Windows and Mac
Repeats: Repeat your Excel training. Details >

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.

Day-1

Excel VBA Language, VBA Editor, Debugging, and Module Review (Discussed When Needed)

Review of VBA Editor, Modules, and how to debug procedures
How to use Sub and Function procedures in VBA
Review of the Excel VBA language including variables, loops, data types, constants, arrays, operators, expressions, logic decisions, and calling conventions
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 use < , > , <=, >=, <>, = , And, Or, Not... in your code to create logic

Reading Cell Values, Performing Calculations, and Outputting to Worksheets with VBA

How to read worksheet cell values from different worksheets using Thisworkbook, Worksheets, Cells, Range, Offset, Find, MATCH...
Naming worksheet cells to easily track information
How to perform calculations (i.e., expressions) in VBA using +, -, / , *, ( ), ...
Using logic in your macro code to determine what calculations to perform
How to output values back to the worksheet using Thisworkbook, Worksheets, Cells, Range, Offset, Find, MATCH...

Using VBA to Calculate / Lookup Data with Functions and Build Formulas

Why run worksheet and VBA functions in Excel VBA?
Using MATCH, COUNTA, Range, Cells, Address, Current Region, Columns, Rows... in VBA to track worksheet range size, position, headers, rows, and columns
Summing, counting, and calculating data in your code using functions like SUM, MAX, MIN, AVERAGE, SUMIFS, COUNTIFS...
Performing data lookups in your macro code using functions like MATCH, VLOOKUP, XLOOKUP, HLOOKUP, INDEX...
Analyzing dates and times in your VBA code using functions like DateDiff, DateAdd, EOMONTH, Year, NETWORKDAYS...
Calculating values and outputting formulas to specific cells around worksheet data
Building formulas and auto filling them in data column / rows using Excel VBA

Using Excel VBA to Quickly Format Worksheet Data and Control Columns / Rows

How to track changing range size, position, and headers using COUNTA, MATCH, Range, Cells, Find, CurrentRegion, Count, Columns, Rows...
Using macros to copy and paste various parts of worksheet ranges like cells, rows, columns, entire worksheets...
Inserting, deleting, and moving (i.e., shaping) worksheet cells, rows, and columns with Excel VBA
Automating worksheet formats like number formats, fonts, cell colors, alignments, and borders in your macro code

Tracking / Controlling Data Workbooks and Worksheets with Excel VBA

Why store data in separate workbooks from your macro code workbook?
Design tips for using the Set statement in Excel VBA to track the workbooks and worksheets you want to command
How to use Open, Close, Save, SaveAs, and Add in your macro code to control a workbook
How to use Add, Delete, Move, and Name in your code to control a worksheet

Automating PivotTables and Excel's Data Tools to Rapidly Report Data

How to use Range, Cells, Columns, Rows, Find, CurrentRegion... in your code to track various worksheet table elements
Automating Sort, Autofilter, Advanced Filter, Text Wizard, and Remove Duplicates to rapidly process data
Using macros to copy and paste sorted and filtered table data to new report worksheets and workbooks
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 Basic User Interfaces (UIs) to Run 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
Using Excel VBA to control drawing shape text and format
Alerting users and answering questions in your code using VBA message boxes

Day-2

Using Loops and Logic to Analyze / Calculate Large Downloaded Data Sets

Basics of using Cells, Range, Offset, variables, functions, and logic inside loops to collect and process worksheet data
Processing common worksheet data patterns like financial downloads, inventory... using loops, counters, and logic
How to use loops and logic to delete data rows and columns
How to use macros to add columns, rows, formulas, calculated values, and lookups to worksheet data
Using macros to color data that is above or below a limit, does not match...
Parsing and cleaning text data in your macro code using Left, Mid, Instr, Split, CLEAN, TRIM...

How to Generate and Fill in Report Tables with Excel VBA

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 data value patterns on a report worksheet
Accessing report template cells using the Range, Find, Cells, Column, Row... in your code
Designing loops and logic to fill in various report templates with data and formulas

Using Excel VBA to Rapidly Fill Worksheet Forms with Data / Formulas

Design strategies for constructing worksheet forms on the worksheet using cell names, shapes, pictures, and Data Validation
Accessing form information in Excel VBA using Cells, Range, Find, Offset, Address...
Using logic, functions, and loops to scan data, perform calculations, and fill in worksheet forms
Using VBA logic to change cell color in your forms based on limits
Automatically creating, modifying, and deleting worksheet formulas in your forms

Searching for Data in External Workbooks and Worksheets Using 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 a macro to create a chart sheet or embedded worksheet chart
Updating a pre-built chart's source data with Excel VBA
How to format a chart 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 using VBA

Building Custom Worksheet Functions (UDFs) to Speed Up Worksheet Tasks

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 various functions in UDFs to analyze data, lookup values, perform calculations...
Returning single values and arrays back from UDFs to worksheet formulas

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 >

Beginning Excel VBA for Business and Industry

Eliminate your time consuming, repetitive Excel tasks with the click of a button!