In Person / Live Online
Next Date: Click Dates|...|Signup
Dates | Prices | Signup
*Our live, hands-on, instructor led Excel online training is running as scheduled above.
Excel in person training will hopefully resume in the near future.
Learn the fundamentals behind
creating time saving Excel VBA tools for your repetitive Excel business tasks.
Our 2-day hands-on beginning Microsoft Excel VBA
training for business gives you the essential Excel VBA programming skills that you need to start
developing time saving, cost effective, data processing and analytical business tools right away.
Questions? 1.805.498.7162 Customers >
About our beginning Excel VBA training.
Our 2-day hands-on beginning
Microsoft Excel VBA training for business gives you the essential
Excel VBA programming skills that you need to start developing time
saving, cost effective, data processing, and analytical business
tools right away. Key topics include.
- A full review of the VBA Editor,
debugging, procedure types, Excel objects, and the VBA language
- Analyzing business worksheet data with Excel VBA
using functions, logic, and expressions
- Building business forms on the worksheet and
using VBA to control and calculate
them
- How to use VBA to track worksheet
tables, their size changes, and their headers
rows for adaptable business data tool creation
- Tracking and controlling workbooks and
worksheets containing business data with Excel VBA
- Automating worksheet PivotTables, sorting,
and filtering in VBA
for rapid business report creation
- Using Excel VBA to insert, move,
delete, copy, and paste worksheet table columns, rows,
and data
- Using Excel VBA to lock onto and format
dynamic worksheet data
- How to quickly create basic
user interfaces (UI's) to run your Excel VBA business tools
- Using VBA For...Next loops
and logic to analyze worksheet business data
- How to search for business data in
different workbooks and
worksheets using Excel VBA
- Automating business chart creation and
formatting
using Excel VBA
- Creating custom VBA worksheet functions (UDF's)
for business that replace complex formulas on the worksheet
The skills you need for our beginning VBA training.
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...
Our beginning Microsoft Excel VBA training syllabus.
Day-1
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, loops,
logic decisions, and calling conventions
- 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 Business Forms
/ Analysis Problems
With Excel VBA
- Design strategies for constructing business 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 Business Worksheet Data
in Excel VBA Using Functions, Logic, and Expressions
- Designing business 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 business tables
end and automatically placing formulas and calculated values in
that row
Tracking and Controlling Workbooks and
Worksheets Containing Business 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 Business 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
Business 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 business 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 business data and fill out report tables
Day-2
How to Quickly Create Basic
User Interfaces (UI's) to Run Your Excel VBA Business Tools
- The basics of using Data
Validation, shapes, pictures, ActiveX controls, and worksheet cells to create user
interfaces for your business 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 Business Data
- When to use For...Next loops and
logic to analyze business worksheet data vs. using Excel's data tools
- Design strategies for organizing
business 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 business data on
the worksheet
- The basics of using variables,
functions, logic, and arrays inside loops to collect and process
business data
- When to use logic to shut down a
For...Next loop vs. figuring out how many rows are in a table
- Processing common business 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 business data and fill out
worksheet forms and displays
- Deleting rows and columns in
business 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 business
various worksheet table positions
Searching for Business Data in Workbooks and Worksheets
Using Excel VBA
- Design strategies for finding business 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 business workbook
- How to use a VBA For...Each Next
loop and logic to search for business 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 Business Chart
Creation and Formatting
Using Excel VBA
- How to use Excel VBA to create
business 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 business charts based on logic
- Adding and deleting chart elements
with Excel VBA
- How to format business charts
using Excel VBA
- Applying a custom chart template to a
business chart with
Excel VBA
Creating Custom VBA Worksheet Functions (UDF's)
for Business
- 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