Excel Application Development with VBA

This course will help Excel developers of all levels add professional touches and techniques to their Excel-based applications.
Course info
Rating
(73)
Level
Intermediate
Updated
May 1, 2015
Duration
3h 51m
Table of contents
Description
Course info
Rating
(73)
Level
Intermediate
Updated
May 1, 2015
Duration
3h 51m
Description

This course will help Excel developers of all levels add professional touches and techniques to their Excel-based applications. From custom ribbons using XML to advanced forms and Windows API techniques, this course has something for every Excel developer looking to take their skills to the next level.

About the author
About the author

Terry has over 25 years in the IT industry, working both in the corporate world as well as many years as a consultant. Terry has a passion for honing his craft as a developer, and enjoys sharing his experiences and insights with others.

More from the author
VBA Fundamentals
Intermediate
3h 38m
Nov 5, 2013
Section Introduction Transcripts
Section Introduction Transcripts

Introduction
Hi, I'm Terry Bradbury with Pluralsight. Welcome to Excel Application Development with VBA. In this course, we will be covering a number of topics that are common to just about any professional level Excel application. We'll begin with building an application framework. A solid foundation that you can use and reuse for every application you build. Next, we'll look at making our applications more object-oriented using interfaces. Using declared interfaces instead of the default interfaces for your classes and userforms is not only good programming discipline, it will make your future coding effort easier as well. Then we'll take your userforms to the next level by explaining how to make them resizeable, how to create dynamic forms, and we'll use some advanced techniques to create a better user experience with your applications. Since Microsoft Office 2007, all Office applications use the ribbon for navigation. We'll use ribbon XML to create professional-looking ribbons for your applications as well as creating a collection of modules that you can reuse for every application you create. While the VBA language has a tremendous amount of power all on its own, there are times when you need just a little more. That's where the Windows Application Programming Interface comes in. By using these APIs, you can tap into the power of the operating system directly. We'll look at how to use this power to handle many common tasks. Finally, we'll look at how to manage your project at the source code level. The VBA environment doesn't provide a way to work with your source code outside of the editor, so we will equip you with the tools you need to properly archive all your hard work in a version control system and how to incorporate an actual build process into your coding methodology.

Building an Application Framework
Hi, I'm Terry Bradbury with Pluralsight. And in this module, we will look at building a framework for developing VBA applications in Microsoft Excel. We'll start with a little background by taking a look at a typical development cycle for most types of programming and contrast that with the development environment for Office solutions developers. Then we'll move on to a traditional multi-layered approach to software development and apply it to creating applications in Microsoft Excel. In executing this multi-layered approach, you will need a plan for managing your code so you are continually working towards building a library of modules that are reusable across multiple projects as much as possible. You'll also find that good code management will allow you to find what you're looking for with very little effort, helping you to be more productive. Next, we'll build some general-purpose libraries and services that will be used repeatedly in virtually every application. We'll use these building blocks to create a foundation that will support development activities, such as debugging, logging and error handling.

Building an Application Framework Part II
Hi, I'm Terry Bradbury with Pluralsight, and in this module we will continue building our application framework for developing VBA applications in Microsoft Excel. Building upon our base libraries, we'll add some libraries for Microsoft Office in Excel. Next we'll add a global app object to centrally manage our application. While controlling user input on forms can be achieved fairly easily, trying to control what happens on our worksheet is a much more daunting task, so, we'll take a look at a technique for managing data validation for worksheets. Navigating an application can mean a lot of things, depending on the context. In an Excel-based application, you may be talking about the ribbon-to-start procedures that might open dialogues, or using the tabs to navigate between sheets. But you may also want to create your own keyboard shortcuts to improve the usability of your app. It isn't immediately evident how to accomplish this, so we'll look at it here. We'll take an in-depth look at the ribbon in a later module. Finally, at some point, just about any true application will need to store some sort of system data, whether it is remembering where the user was in a process, storing configurable options, historical information, or any other data that needs to be persisted across sessions. We will cover a reusable approach to system data.

Interfaces
Hi, I'm Terry Bradbury with Pluralsight, and in this module we'll look at making your code more object-oriented by using interfaces. We'll start by looking at default interfaces in VBA, and explain what they are. Then, we'll take more control of our code by creating declared interfaces. Once we've defined an interface, we'll look at how to implement it. One of the great benefits of using interfaces is that you can have multiple implementations. We'll look at a couple of practical examples of this. Not only can you have multiple implementations of a single interface, you can also use multiple interfaces together to build more complex or robust solutions. So we'll finish with that.

UserForms
Hi, I'm Terry Bradbury with Pluralsight and in this module, we will look at techniques for creating advanced UserForms. We'll begin with some basics of UserForm design. It's not enough to just throw some controls on a form. We'll look at some of the issues involved in creating professional-looking UserForms. Next, we'll look at issues surrounding resizable forms. You may want to resize your forms through code or allow the user to resize a form directly. We'll create code that supports either of these methods. You may not always know all of the controls that are needed on a form at design time, so we'll cover dynamic forms so you can add more controls at runtime through code. Next, we'll take a look at validating and correcting data at the keystroke level. Many developers only use basic controls such as labels, text boxes, and buttons for their UserForms. Let's liven it up by using some advanced controls and techniques to take our UserForm designs to the next level. The use of event sinks is common for C# and C++ developers but not as often by VBA developers mostly because they are unfamiliar with the techniques involved. UserForms are a great place to use event sinks because they can keep you from creating a lot of duplicate code when you have a lot of controls to support.

Ribbon XML
Hi I'm Terry Bradbury with Pluralsight, and in this module we'll create custom ribbons for your Excel applications using Ribbon XML. There are some prerequisites to completing this module, so we'll start by covering what tools and skills you'll need for creating Ribbon XML code. Then we'll look at some of the basic building blocks of the ribbon, and how they work together to render the finished product. The presentation and behavior of controls on a ribbon are controlled by setting their attributes, which can either be statically or dynamically set for each control. Dynamic attributes are implemented using callbacks, which are really nothing more than a specialized type of sub-procedure that can return data back to the ribbon. At this point, we'll have a basic understanding of how all the mechanics of a custom ribbon work, so we'll look at the specifics of each of the ribbon controls, which I've broken into basic, advanced, and dynamic groups. As we move through them, you will see that advanced controls are similar to the basic controls, but they generally have additional attributes and events to support their more complex structure. Some controls are completely dynamic, and are built at runtime through code. This is a little tricky, but you'll be able to understand it by the time we get there. At some point, you may need intercept the default behavior of a built-in control, and repurpose it for your own solutions. We'll also discuss how to share controls between ribbons using qualified namespaces.

The Windows API
Hi, I'm Terry Bradbury with Pluralsight, and in this module we'll expand our toolbox by accessing the Windows API through external functions. We'll start with the basics of declaring external functions, then we'll show some practical examples that you can use in your own applications. Programming UserForms can be improved by using API calls because the Microsoft Forms library provides no way to accurately set dimensions at runtime. The size of borders and title bars vary depending on what version of Office you are using, and what operating system it is running on. Excel only provides two very limited ways to display HTMLHelp, so we'll look at some API functions to work with these files. The ShellExecute function allows you to open any file using its registered application. This versatile function should have been part of the VBA language to begin with, but since it isn't, we'll add it ourselves. Finally, we'll wrap it up by adding sound to your Excel application.

Source Code
Hi, I'm Terry Bradbury with Pluralsight, and in this module we'll take a brief look at working with VBA Source Code. We'll start by looking at how to prepare for building from source code. Even the simplest applications will need additional code to support building structured worksheets, adding theme support if you've developed a custom theme file, or adding resources, such as icons for your ribbon. Once you have finished preparing your application, you will need to export your source code into a folder structure that can be added to your version control system. We'll look at ways you might want to organize your project structure. Then we'll wrap it up by demonstrating a simple build process, and creating a build sequence for your application.