Beginning Excel VBA Class for Business and Industry

Next Class: 12/21/2022

More Dates >

See the must know topics you will learn.

No time to attend, get a manual instead.

Key Excel VBA topics covered 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

Class examples.

Pricing and class options.

Detailed 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

Excel VBA skills needed for our class.

Select this Excel VBA training if you or your group have:

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

The class details.

• Audience:   Business, government, industry, military, engineering, science, techs... general audience
• Duration:   9:00 - 5:00 CT
• # of Days:   2-days
• Instruction Type:   Hands-on, live, instructor taught training
• Full Excel Manual Included:   Our 2200 + page manual full of Excel tips, how-to-do topics and Excel examples included with the training. Details >
• Excel Versions:   2010-2019, Microsoft 365, Windows and Mac
• Repeats:   Repeat your Excel training. Details >
• Personal Examples:   We review our attendees personal examples in our training instruction
• Available Training Formats:
  • In Person Training >
  • Live Virtual Learning >
  • Company Onsite >

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 >

Contact us by form.