Getting the Most Out of Visual Basic for ApplicationsThis content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
As a developer, your goal is to write code that's fast, efficient, easy to read and maintain, and if possible, reusable. To do so, you must have a solid working knowledge of Microsoft® Visual Basic® for Applications (VBA) — what features the language includes and what you can do with it.
As you develop applications, you'll find that there are a number of operations that you must perform repeatedly — parsing a file path, for example, or returning all the files in a directory. Rather than rewriting these routines every time you require them, you can begin building an arsenal of procedures that solve common problems. This section gives you a head start by providing functions that perform some often-required operations on strings, numbers, dates and times, files, and arrays. It also explains the key aspects of each procedure and covers fundamental VBA programming issues so that you can continue to expand your code arsenal yourself. You can use these procedures not only in VBA code but also in Visual Basic Scripting Edition (VBScript) code in HTML documents.
In This Section
- Working with Strings
- Understand how to get information from strings.
- Working with Numbers
- Learn how to use numeric values and data types in Microsoft® Visual Basic® for Applications (VBA).
- Working with Dates and Times
- Manipulate date values in Microsoft® Visual Basic® for Applications (VBA), and understand how VBA stores date values internally.
- Working with Files
- Understand the Microsoft Scripting Runtime object library, and work with drives, folders, and files as objects.
- Understanding Arrays
- Use arrays when you must store a number of values of the same type, but you don't want to create individual variables to store them all.
- Tips for Defining Procedures in VBA
- Define a Function or Sub procedure, and use the options available to you to make your code more extensible or more flexible.
- Optimizing VBA Code
- Understand how to streamline your Microsoft® Visual Basic® for Applications (VBA) code to streamline your memory requirements.
- Developing Office Applications Using VBA
- Create Microsoft® Office applications that can range from writing a simple Microsoft® Visual Basic® for Applications (VBA) procedure to creating a sophisticated financial analysis and reporting application
- The Benefits of Office Programmability
- Quickly and easily build and deploy custom desktop applications and take advantage of the objects exposed by Microsoft® Office applications, so your custom applications can leverage existing, proven, and tested Office functionality.
- Office Objects and Object Models
- Integrate the features from two or more Microsoft® Office applications into a single application to amplify and focus users' productivity.
- Working with Office Applications
- Take advantage of different objects, collections of objects, properties, methods, and events to build your application.
- Working with Shared Office Components
- Search for files, use the Office Assistant, manipulate command bars, read and write document properties, read and write script, and hook add-ins to your Microsoft® Office application using a set of shared objects available in all Office applications.
- Add-ins, Templates, Wizards, and Libraries
- Create and use COMAddIn objects (a shared Microsoft® Office component in the Microsoft Office XP object library).