Beginning Microsoft Excel VBA Training for Business

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

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

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