Excel Power Programming With VBA Course overview

In this course, students will apply the Visual Basic for Applications (VBA) programming language to simplify many of the tasks that you can perform using various tools and functions in Excel

Apply Today

Instructor Led Learning

Duration: 5 Days
Registration Open Now!

Video Learning

Duration: 5 Days
Registration Open Now!

What you will learn

Course Content – Excel Power Programming With VBA

  • Lesson 1 :Essentials of Spreadsheet Application Development
  • Lesson 2 :Introducing Visual Basic For Applications
  • Lesson 3 :VBA Programming Fundamentals
  • Lesson 4 :Working With VBA Sub Procedures
  • Lesson 5 :Creating Function Procedures
  • Lesson 6 :Understanding Excels Events
  • Lesson 7 :VBA Programming Examples and Techniques
  • Lesson 8 :Working with Pivot Tables
  • Lesson 9 :Working with charts
  • Lesson 10 :Interacting with other Applications
  • Lesson 11 :Working with external Data and Files
  • Lesson 12 :Leverage Custom Dialog boxes
  • Lesson 13 :Introducing UserForms
  • Lesson 14 :Userform Examples
  • Lesson 15 :Advanced Userform techniques
  • Lesson 16 :Creating and using Add ins
  • Lesson 17 :Working with ribbon
  • Lesson 18 :Working with shortcut menus
  • Lesson 19 :Providing help for your applications
  • Lesson 20 :Leveraging class modules
  • Lesson 21 :Understanding Compatibility issues


Lesson 1: Essentials of Spreadsheet Application Development

  • What is a Spreadsheet Application?
  • Steps for Application Development
  • Determining User Needs
  • Planning an Application that meets user needs
  • Determining the most Appropriate User Interface
  • Concerning yourself with the end user
  • Other development issues

Lesson 2: Introducing Visual Basic For Applications

  • Getting a Head Start with Macro Recorder
  • VBA Programming Fundamentals
  • Deep Dive: Working with Range Objects
  • Essential Concepts to Remember

Lesson 3: VBA Programming Fundamentals

  • VBA Language Elements
  • Variables, Data Types, and Constants
  • Assignment Statements
  • Arrays
  • Objects Variables
  • User Defined Data Types
  • Built In functions
  • Manipulating Objects and collections
  • Controlling Code Execution

Lesson 4:Working With VBA Sub Procedures

  • About procedures
  • Executing Sub Procedures
  • Passing Arguments to procedures
  • Error- Handling Techniques
  • A Realistic Example that uses Sub Procedures

Lesson 5: Working with UserForms

  • Sub procedures versus functions procedures
  • Introducing UserForms
  • UserForm Examples
  • Advanced UserForm Techniques
  • Why create custom functions
  • An introductory function example
  • Function Procedures
  • Function arguments
  • Function examples
  • Emulating Excels SUM function
  • Extended Date Functions
  • Debugging Functions
  • Dealing with the Insert Function Dialog box
  • Using Add Ins to store Custom Functions
  • Using the windows API

Lesson 6: Understanding Excels Events

  • What you should know about events
  • Getting Acquainted with Workbook-Level Events
  • Examining Worksheet Events
  • Monitoring with Applications Events
  • Accessing Events Not Associated

Lesson 7:Developing Applications

  • Learning by Examples
  • Working with Ranges
  • Working with workbooks and sheets
  • VBA Techniques
  • Some Useful Functions for use in your code
  • Some useful worksheet functions
  • Windows API Cells

Lesson 8: Working with Pivot Tables

  • An Introductory Pivot Table Examples
  • Creating a more complex Pivot table
  • Creating multiple pivot Tables
  • Creating a reverse pivot table

Lesson 9: Working with charts

  • Getting the inside scoop on charts
  • Creating an embedded chart
  • Creating a chart on a chart sheet
  • Modifying a chart
  • Using VBA to deactivate a chart
  • Determining whether a chart is activated
  • Deleting from the ChartObjects or chart collection
  • Looping through all charts
  • Sizing and aligning ChartObjects
  • Creating lots of charts
  • Exporting a chart
  • Changing the data used in a chart
  • Using VBA to display Arbitrary data Labels on a chart
  • Displaying a chart in userform
  • Understanding chart events
  • Discovering VBA Charting Tricks
  • Working with sparkline charts

Lesson 10: Interacting with other Applications

  • Understanding Microsoft Office Automation
  • Automating access from excel
  • Automating word from excel
  • Automating PowerPoint from excel
  • Automating Outlook from excel
  • Starting other applications from excel

Lesson 11: Working with external Data and Files

  • Working with external Data connections
  • Using ADO and VBA to pull External data
  • Working with text files
  • Text file manipulation examples
  • Performing common file operations
  • Zipping and unzipping files

Lesson 12: Leverage Custom Dialog boxes

  • Before you create that UserForm
  • Using an input box
  • The VBA Msgbox function
  • The excel GetOpenFileName Method
  • Promoting for a directory
  • Displaying a data form

Lesson 13: Introducing UserForms

  • How excel Handles custom dialog boxes
  • Inserting a new UserForm
  • Adding controls to a UserForm
  • Toolbox controls
  • Adjusting UserForm controls
  • Adjusting a controls properties
  • Displaying a UserForm
  • Closing a UserForm
  • Creating a Userform
  • Understanding UserForm events
  • Referencing UserForms controls
  • Customizing the toolbox
  • Creating UserForms templates
  • A UserForm checklist

Lesson 14: Userform Examples

  • Creating a Userform “Menu”
  • Selecting ranges from a userform
  • Creating a splash screen
  • Disabling a userforms size
  • Zooming and scrolling a sheet
  • Listbox techniques
  • Other development issues
  • Using the multipage control in a userform
  • Using an external control
  • Animation a label

Lesson 15: Advanced Userform techniques

  • A modeless dialog box
  • Displaying a progress indicator
  • Creating wizards
  • Emulating the MsgBox Function
  • A userform with movable controls
  • A userform with no title bar
  • Simulating a toolbar with a userform
  • Emulating a task pane with a userform
  • A resizable userform
  • Handling multiple userform controls with one event handler
  • Selecting a colour in a userform
  • Displaying a chart in a userform
  • Making a userform semitransparent
  • A puzzle on a userform
  • Video poker on a userform

Lesson 16: Creating and using Add ins

  • What is an Add-In?
  • Understanding excels add in manager
  • Creating an add in
  • And add in example
  • Comparing XLAM and XLSM Files
  • Manipulating Add ins with VBA
  • Optimizing the performance of add ins
  • Special problems with add ins

Lesson 17:Working with ribbon

  • Ribbon basics
  • Customizing the ribbon
  • Creating a custom ribbon
  • Using VBA with ribbon
  • Creating an old style toolbar

Lesson 18: Working with shortcut menus

  • CommandBar overview
  • Using VBA to customize shortcut menus
  • Shortcut menus and events

Lesson 19: Providing help for your applications

  • Help for your excel applications
  • Help systems that use excel components
  • Displaying help in a web browser
  • Using a HTML help system

Lesson 20: Leveraging class modules

  • What is a class module?
  • Creating a Numlock class
  • Coding properties, methods, and events
  • Exposing a query table event
  • Creating a class to hold classes

Lesson 21: Understanding Compatibility issues

  • What is compatibility?
  • Types of compatibility problems
  • Avoid using new features
  • But will work on a mac?
  • Dealing with 64-Bit Excel
  • Creating an international application

Join Over 10,000 Students that have studied with MasterGrade IT Now

Become Part of MasterGrade IT to Further Your Career.