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

FULL COURSE OUTLINE

Part I: Introduction to Excel VBA 1

Chapter 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

Chapter 2: Introducing Visual Basic for Applications

  • Getting a Head Start with Macro Recorder
  • Working with the Visual Basic Editor
  • VBA Fundamentals
  • Deep Dive: Working with Range Objects
  • Essential Concepts to Remember
  • Don’t Panic–You Are Not Alone

Chapter 3: VBA Programming Fundamentals

  • VBA Language Elements: An Overview
  • Variables, Data Types, and Constants
  • Assignment Statements
  • Arrays
  • Declaring Arrays
  • Object Variables
  • User-Defined Data Types
  • Built-in Functions
  • Manipulating Objects and collections
  • Controlling Code Execution

Chapter 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
  • Utility Availability
  • Evaluating the Project

Chapter 5: Creating Function Procedures

  • Sub procedures versus functions procedures
  • Why Create Custom Functions?
  • An Introductory Function Example
  • Function Procedures
  • Function Arguments
  • Function Examples
  • Emulating Excel’s 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

Chapter 6: Understanding Excel’s Events

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

Chapter 7: VBA Programming Examples and Techniques

  • 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

Part II: Advanced VBA Techniques

Chapter 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

Chapter 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 Activate a Chart
  • Moving 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

Chapter 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

Chapter 11: Working with External Data and Files

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

Part III: Working with UserForms

Chapter 12: Leveraging Custom Dialog Boxes

  • Alternatives to UserForms
  • Using an input box
  • Using The VBA Msgbox function
  • Using The excel GetOpenFileName Method
  • Using the Excel GetSaveAsFilename Method
  • Prompting for a Folder
  • Displaying Excel’s Built-in Dialog Boxes
  • Displaying a data form

Chapter 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 control’s properties
  • Displaying a UserForm
  • Closing a UserForm
  • Creating a Userform: An Example
  • Referencing UserForms controls
  • Customizing the toolbox
  • Creating UserForms templates
  • A UserForm checklist

Chapter 14: Looking at UserForm Examples

  • Creating a Userform “Menu”
  • Selecting ranges from a userform
  • Creating a splash screen
  • Disabling a UserForm’s Close Button
  • Changing a UserForm’s Size
  • Zooming and Scrolling a Sheet from a UserForm
  • Exploring ListBox Techniques
  • Using the multipage control in a userform
  • Using an external control
  • Animation a label

Chapter 15: Implementing 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

Part IV: Developing Excel Applications

Chapter 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

Chapter 17: Working with the Ribbon

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

Chapter 18: Working with Shortcut Menus

  • CommandBar overview
  • Referring to Controls in a CommandBar
  • Properties of CommandBar Controls
  • Displaying All Shortcut Menu Items
  • Using VBA to customize shortcut menus
  • Resetting a shortcut menu
  • Shortcut menus and events

Chapter 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

Chapter 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

Chapter 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
  • Multilanguage Applications
  • VBA Language Considerations
  • Using Local Properties
  • Identifying System Settings
  • Date and Time Settings

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

Become Part of MasterGrade IT to Further Your Career.