Principles of Financial Modelling

CHAPTER 32Manipulation and Analysis of Data Sets with VBA

INTRODUCTION

This chapter focuses on the use of VBA in the manipulation and analysis of data sets. In fact, almost all of the necessary techniques used here have already been mentioned earlier; to some extent this chapter represents the integration and consolidation of these within the specific context of data analysis.

PRACTICAL APPLICATIONS

The examples in this section include:

• Working out the size of a given data range.
• Defining the data set at run time, based on user input.
• Defining the data set at run time by automatically detecting its position.
• Reversing the rows and/or columns of a data set (either into a new range, or in place).
• Automation of general Excel functionalities.
• Automation of processes to clean data sets, such as deleting rows containing blanks.
• Automation of the use of filters to clean, delete or extract specific items.
• Automation of Database function queries.
• Consolidation of several data sets that reside in different worksheets or workbooks.

Example: Working Out the Size of a Range

One of the most compelling reasons to use VBA to manipulate or analyse data sets (rather than build the corresponding operations using Excel functions and functionality) is that VBA can detect the size of a data set, and conduct its operations accordingly. By contrast, if the size of a data set changes, then Excel functions that refer to this data would generally need to be adapted as well as copied to additional ...