Beginning Excel VBA for Business and Industry - Class

Class overview.

Our live, hands-on, 2-day class shows you the critical Excel VBA skills needed to start making automated tools right away

It covers critical Excel VBA topics like modules and the VBA Editor; code debugging; how to type and use the VBA language; how to use logic; and how to use Excel VBA to command various Excel elements.

Also discussed is how to use Excel VBA to perform key tasks like controlling workbooks / worksheets; worksheet formatting; automated calculations; table lookups; and data processing.

It also covers how to automate Excel's data tools, charts, and PivotTables to make rapid reporting tools.

Join us and our class will show you how to use Excel VBA to quickly solve your tough Excel problems.

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

Est 1998. If you haven't heard of us before, here's some of our customers from the past 25 years.

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

Get an instant class discount. Signup for our Exceleration newsletter and get a discount that you can apply instantly to any purchase.

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 365, Windows and Mac
• Repeats:   Repeat your Excel training. Details >

Class syllabus.

Class Syllabus

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 >