Advanced Microsoft Excel VBA Training Outline for Business Professionals

Cost Saving Excel VBA Skills You Learn

• An advanced review of the Excel VBA language including objects, properties, methods, loops, logic, variables and expressions

• Advanced control of workbooks and worksheets using VBA

• Advanced control of ranges including calculations, finding data boundaries and finding information using VBA

• How to use VBA and worksheet functions in your code to scan for times, dates, parse text, information...

• Advanced VBA techniques for scanning data in multiple worksheets and workbooks

• Advanced techniques for scanning Windows folders for workbooks and then opening them using VBA

• Advanced VBA programming techniques for creating charts and pivot tables

• How to create event driven programs using VBA including running code at a specific time or key stroke

• Advanced excel dashboard construction using userforms, ActiveX controls, drawing shapes, pictures, worksheets and VBA

• How to create custom toolbars to run your VBA code and provide navigation

• How to create advanced custom worksheet functions for modeling and analysis purposes

• How to create, open, read and close text files using VBA

• How to communicate with databases using DAO, ADO and SQL in VBA and how to download data from the web using VBA

• Advanced VBA techniques for communicating with other languages and programs including report generation using Word and PowerPoint

Included in Our Advanced Excel VBA Workshop

• Free Repeats

• 1800+ page manual filled with key Excel strategies, examples and tips

• Hands-on training

• Personal questions answered

Audience Prerequisites

• Excel users with a good understanding of Microsoft Excel and basic knowledge of the VBA language.

• It is intended for all types of business professionals.

Workshop Training Choices

Available:  Public, Webcast, Onsite, Self-Study Manual

• Public, Webcast and Self-Study signup buttons below

• For onsite information or if you have any questions, please contact us 1.805.498.7162 or 1.805.558.9277.

Advanced Excel VBA Training Focus

The capability exists within Microsoft Excel and VBA to construct complex dashboard driven analysis and data processing business tools. These business tools can go far beyond simple macros becoming actual programs. Excel has a vast array of automated features like drawing shapes, ActiveX controls, worksheets, userforms and cell ranges that when combined together with VBA allow these dashboard driven programs to be created. When you tie other programs and computer languages to Excel through VBA like Word, PowerPoint, Access, SQL Server, C and VB .Net, Excel VBA can tap into these environments obtaining the functionality it does not have. This workshop focuses on using all of these elements to construct sophisticated data processing and analysis tools in Microsoft Excel.

Advanced Excel VBA Workshop Outline

In this 3-day "hands-on" workshop you will learn:

Day-1

• A complete Microsoft Excel VBA language review including an advanced discussion on using loops, logic, variables and arrays

• How objects, properties and methods really work and how to build an object using Excel class modules

• Advanced strategies on how to command workbooks and worksheets using VBA including adding, opening, deleting, closing and naming strategies

• Advanced VBA strategies on how to command ranges including performing advanced calculations, finding data boundaries and finding information

• Advanced strategies for using VBA and worksheet functions in your code to speed it up and also process information like header position, times, dates and text

• How to fill in data, track data and extract data in advanced forms and tables using Excel VBA

• How to automatically create worksheet formulas for models using VBA

• How to automatically scan for information and data in different worksheets and workbooks and lock onto it using VBA

• Advanced strategies for processing data on single worksheets and processing data in multiple worksheets and workbooks

• How to access, copy, move and delete Windows folders and files using VBA

• How to control the Windows registry for storing and retrieving program information

• Advanced techniques for scanning Windows folders for workbooks and then opening them using VBA. Process information in thousands of workbooks.

• How to use VBA functions like Split, Instr, DateAdd and DateDiff to find dates and times on the worksheet and in file and folder names

Day-2

• How to create event driven applications in Microsoft Excel that run when workbooks open, worksheets are typed on, worksheet tabs are clicked....

• How to create procedures that run at a certain time or run based on pressing a hot key combination

• How to develop user interfaces on the worksheet using ActiveX controls and Excel VBA

• Overview of using userforms to create business dashboards

• How to create userforms in VBA

• How to create controls and graphics on userforms

• How to use VBA to command userforms and their ActiveX controls

• How to use VBA to command combo boxes and list boxes on userforms

• How to create toolbars that control your Excel VBA programs and provide navigation for your projects

• How to control Excel’s ribbon system with VBA

 Day-3

• How to create, open, read and close text files including a discussion on importing large text files into excel (>100,000 rows)

• How to use drawing shapes, userforms, ActiveX controls, worksheets, cells and Excel VBA to create advanced business dashboards in Excel

• How to take existing formula based models and integrate them with ActiveX controls and VBA

• How to create advanced custom worksheet functions for modeling and analysis purposes

• Advanced command of Microsoft Word and PowerPoint using Excel VBA (report generation tools)

• How to command databases, workbooks and text files using DAO, ADO and SQL in VBA

Signup, Pricing and Self-Study

The Public and Webcast buttons below will lead you to our training schedule page listing all possible dates, prices and signups for those training categories. If you are unsure what a webcast is click here. The Self-Study button will lead you to our manual page where you can get the manual pricing and the manual outline. The manual comes with the class.

 

 

 

Award Winning VBA Software

Typical Workshop Examples

Advanced VBA Review; Complete Object, Property and Method Review

Advanced Range, Worksheet, Workbook Control as Well as Advanced VBA and Worksheet Function Uses

How to fill in data, track data and extract data in advanced forms and tables using Excel VBA

Single and Multi Workbook/Worksheet Data Processing; Windows Folder/File Management Using VBA

Advanced Multi Chart Construction, Microsoft PowerPoint Control and Microsoft Word Control Using VBA

Advanced Worksheet Form Construction Using ActiveX Controls, Worksheets, Shapes and VBA

Advanced Dashboard Construction Using Userforms, Shapes, Worksheets and ActiveX Controls

Advanced VBA Techniques for Downloading Data from the Web and Databases