Learn how to reduce your repetitive, complicated, Excel jobs from hours to seconds by learning the basics of Excel VBA.
Our hands-on Excel VBA training shows how to:
- Use the VBA Editor, the Excel VBA language, and debug code.
- Automate key Excel features like formatting, sorting, filtering, PivotTables....
- Automate repetitive calculations, formula construction, and control models.
- Use Excel VBA to track changing data, ranges, forms, and tables.
- Process data on
different worksheets and in multiple workbooks.
- Automate charts and generate reports.
Detailed training syllabus.
How we run the class: We focus our beginning Excel VBA training on what our customers need. When training begins, we analyze those needs and shift our
beginning Excel VBA training outline appropriately. We will stress topics or add topics that our customers want. No two training sessions are ever the same with EMAGENIT.
Visual Basic Editor, Debugging, Procedure, and Excel VBA Language Review (Discussed Throughout Class)
- A complete review of the Excel VBA Editor and its windows
- What is a VBA project and module used for in programming?
- Overview of how to use Sub and Function procedures in your Excel VBA programs
- How to use the VBA Editor's debugging tools to correct your Excel VBA code
- What is an Excel VBA program and how to translate your ideas into one?
- Complete review of the VBA core language elements that make up a program including variables, data types, constants, arrays, operators, expressions, logic decisions, and calling conventions
- Complete review of For...Next, For Each...Next, and Do...Loops
- What are objects, properties, and methods and how to use them in Excel VBA
- How to use VBA to control specific Excel program elements (command.command.command structure)
- What is logic and how to use it to construct Excel VBA programs?
- How to use the relational operators < , > , <=, >=, <>, = in VBA to build logic
- How to use IF, Select Case, and relational expressions in Excel VBA to create logic decisions
Controlling and Calculating Worksheet Forms / Analysis Problems With Excel VBA
- Design strategies for constructing worksheet forms and analysis problems on the worksheet using cell names, cell references, shapes, pictures, and Data Validation
- Accessing form and problem information in VBA using the Cells, Range, Columns, CurrentRegion, and Rows properties
- How to read worksheet information into your VBA calculations
- Using the different mathematical operators (+,-,/,*, ^...) and variables in your Excel VBA code to create calculations and store information
- Using VBA logic to decide what calculations to use
- How to leverage cell names, range names, and Excel tables in VBA to track user entry and output cells
- Automatically creating, modifying, and deleting worksheet form formulas using Excel VBA
- Using Excel VBA to create, delete, rename, and reassign worksheet form cell and range names
- Controlling cell number formats, fonts, colors, alignments, and borders in your forms using Excel VBA
- Using VBA based logic to flag limit violations in output cells
Finding and Analyzing Worksheet Data in Excel VBA Using Functions, Logic, and Expressions
- Designing worksheet tables that can be analyzed with Excel VBA
- Using the Range, Cells, Columns, Rows, and Current Region properties in VBA to identify worksheet table size, rows, columns....
- Finding the position of worksheet table columns and rows using the MATCH, COUNTIF, and COUNTA worksheet functions and the Find method
- How to use the Range, Cells, CurrentRegion, Columns, and Rows properties in VBA to isolate worksheet columns and data subsets and use them as arguments to worksheet functions
- Using key VBA and Excel worksheet functions in your code that analyze dates and times like DateDiff, DateAdd, DatePart, EOMONTH, Day, Month, Year, NETWORKDAYS...
- Using key Excel worksheet functions in your VBA code that sum and count like SUM, MAX, MIN, AVERAGE, COUNTA, SUMIFS, COUNTIFS, AVERAGEIFS....
- Using key Excel worksheet functions in your VBA code that lookup information like MATCH, VLOOKUP, HLOOKUP, INDEX...
- Using key VBA and Excel functions in your code that control text like Left, Right, Mid, Instr, InstrRev, Split, CLEAN, TRIM...
- Figuring out where worksheet tables end and automatically placing formulas and calculated values in that row
Tracking and Controlling Workbooks and Worksheets Containing Data With Excel VBA
- Why store data in separate Excel workbooks from your Excel VBA code workbook?
- Design tips for using the Set statement in VBA to track the workbooks and worksheets you want to command
- How to use Excel VBA to open, add, save, and close workbooks
- How to create a file picker in Excel VBA using the Open and SaveAs Excel dialog boxes
- Using Excel VBA to add, move, and rename worksheets
Automating Excel's Data Tools in VBA for Rapid Report Creation
- How to use the Range, Cells, Columns, Rows, and CurrentRegion properties in VBA to identify various worksheet table elements
- Automating the Sort tool with Excel VBA
- Automating the Autofilter and Advanced Filter tools with Excel VBA
- Using Excel VBA to copy and paste sorted and filtered table data to new worksheets and workbooks
- Using Excel VBA and worksheet functions to calculate filtered data
- Automating the Remove Duplicates tool with Excel VBA
- Creating PivotTables using Excel VBA and the Macro Recorder
- Automating Excel Table construction using Excel VBA
How to Track, Shape, Fill, and Format Worksheet Table Data Using Excel VBA
- How to track worksheet table parts like header rows, last rows, entire tables, columns... using the Range, Cells, CurrentRegion, Columns, Count, and Rows properties in VBA
- Using the COUNTA and MATCH worksheet functions in your Excel VBA code to determine where worksheet tables start, stop, and their header positions
- Using the Find, Cells, Row, and Column methods and properties in VBA to figure out where worksheet tables start, stop, and their header positions (alt methods)
- Copying and pasting various parts of worksheet tables with Excel VBA (cells, rows, columns, entire worksheets, row sections...)
- Inserting, deleting, and moving worksheet cells, rows, and columns with Excel VBA
- Worksheet table format strategies for controlling number formats, fonts, colors, alignments, and borders using Excel VBA
- Using cell characteristics like bold, color, address, column number, row number... to build relational expressions (logic conditions) for your VBA logic
- Using Excel VBA logic, functions, and For...Next loops to scan data and fill out report tables
How to Quickly Create Basic User Interfaces (UI's) to Run Your Excel VBA Tools
- The basics of using Data Validation, shapes, pictures, ActiveX controls, and worksheet cells to create user interfaces for your Excel tools
- Creating a drawing shape as a button to run a VBA procedure
- Creating and modifying Data Validation logic, formulas, limits, and message boxes using Excel VBA
- Using Excel VBA to control drawing shapes and their text for display purposes
- Using Excel VBA to read ActiveX controls
- Arranging drawing shapes, pictures, and cells on the worksheet itself to create a high tech dashboard look
- Using workbook events like Open to preload UI information
Using VBA For...Next Loops and Logic to Analyze Worksheet Data
- When to use For...Next loops and logic to analyze worksheet data vs. using Excel's data tools
- Design strategies for organizing data on the worksheet so it can be easily read by Excel VBA loops
- Using the Cells, Range, and Offset properties inside a For...Next loop to access data on the worksheet
- The basics of using variables, functions, logic, and arrays inside loops to collect and process data
- When to use logic to shut down a For...Next loop vs. figuring out how many rows are in a table
- Processing common worksheet data patterns using VBA For...Next loops, counters, and logic
- How to use VBA to scan worksheet data with loops, copy/paste the target data to another table, and summarize using functions
- Using Excel VBA logic, functions, and For...Next loops to scan data and fill out worksheet forms and displays
- Deleting rows and columns in data using VBA loops and logic
- Shaping worksheet data text (remove characters, clean, trim...) with VBA loops, functions, and string concatenation
- How to use Excel VBA to add columns, rows, formulas, and calculated data to various worksheet table positions
Searching for Data in Workbooks and Worksheets Using Excel VBA
- Design strategies for finding data when you do not know what workbook or worksheet contains it
- Overview of how to use the VBA For...Each Next loop to loop through object collections
- How to use a VBA For...Each Next loop and logic to search for a specific workbook
- How to use a VBA For...Each Next loop and logic to search for data on different worksheets within a workbook
- How to track a worksheet or workbook in Excel VBA once you find it with a For...Each Next loop
Automating Excel Chart Creation and Formatting Using VBA
- How to use Excel VBA to create charts from worksheet data
- Creating a chart sheet or embedded worksheet chart using Excel VBA
- Updating a pre-built chart's source data with Excel VBA
- Using Excel VBA to color and format data points in charts based on logic
- Adding and deleting chart elements with Excel VBA
- How to format charts using Excel VBA
- Applying a custom chart template to a chart with Excel VBA
Creating Custom VBA Worksheet Functions (UDF's)
- How custom worksheet functions (UDF's) can perform tasks that Excel formulas and array formulas cannot
- General design rules for custom worksheet functions (UDF's)
- Designing UDF's that can accept cells, ranges, values, and arrays as arguments
- Implementing loops, logic, and various functions in UDF's to process data and perform calculations
- Implementing loops, logic, and functions to filter and collect data inside of a UDF
- Returning single values and arrays back from UDF's to worksheet formulas
Learn More >
Learn More >
Excel VBA skills needed for the training.
Select our beginning 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 training 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
- Free Repeats: Repeat your Excel training. Details >
- Personal Examples: We review our attendees personal examples in our training instruction
Available Training Formats:
If you need to contact us about our training.
Phone Number: 1.805.498.7162
Business Hours: 8:30 - 5:00 PM PT
Contact us by form.
Need Help? Please call us at 1.805.498.7162
Copyright © 2002-2021
EMAGENIT All Rights Reserved