May 5,
2015

In a previous instalment, we saw how to use MINVERSE, one of Excel’s built-in array functions and we saw how we can use <ctrl><shift><enter> to insert the array result into a rectangular array of cells. We also mentioned that many Excel experts, like Mr. Excel, call such functions “CSE” functions, as a reminder of this key combination.

It goes without saying that creating your own functions is always more fun, so let’s see how to create an array function using VBA. This is actually very easy to do, thanks to the flexibility provided by the * variant* data type.

For our example, we will take some stock market data and examine the correlations of closing prices among different companies over a period of a few months. The sample data can be found in PriceData.xlsx. A completed version with the macro can be found here.

To use our function, the user will select a square range of cells in a worksheet. It must be square, since if we are looking at five companies, there will be one column for each of the five companies and one row for each of the same five companies. The user will then enter the necessary information in the formula bar. Let’s call our function CorrelationMatrix.

We start by creating a new function in the VBA editor. The declaration will look like this:

Function CorrelationMatrix(rng As Range) As Variant

We do not know how many columns will be included in the range object; it will be convenient for later code to store that value in a variable. As long as we’re delcaring variables, we’ll declare two integer variables for looping through the rows and columns of our output matrix.

Dim i As Integer

Dim j As Integer

Dim NumColumns As Integer

NumColumns = rng.Columns.Count – 1

Note that we have subtracted one from the number of columns. We do this because we are sticking with VBA’s default base value of zero for arrays. That is, a two-dimensional square array with ten columns would be declaredm for example, as ” Dim MyArray(9,9) “, since the array indices run from 0 to 9. We could avoid this if we wish by declaring “Option Base 1” for our VBA module.

Since we do not know the dimensions of the output array in advance, we declare a dynamic array and then use ReDim to assign the actual dimensions at runtime.

Dim matrix() As Double

ReDim matrix(NumColumns, NumColumns)

Note that we use the value NumColumns twice, since the output array must be square.

Now it’s just a matter of looping through this array using Excel’s CORREL function to calculate the Pearson’s correlation coefficient for each possible pairing of columns of the input.

For i = 0 To NumColumns

For j = 0 To NumColumns

matrix(i, j) = WorksheetFunction.Correl(rng.Columns(i + 1), rng.Columns(j + 1))

Next j

Next i

A few points here are worthy of mention. One is that while the array is based at zero, columns in the range object start at index one. We must keep this in mind while setting the index values in our loop. This, I think, is a good reason to use Option Base 1 for VBA arrays. Secondly, we note that while worksheet functions in the formula bar can be accessed directly, within VBA they must be prefixed with the class name “WorksheetFunction”.

Now we are ready to test. Select a 5 x 5 range on your worksheet. In the formula bar, type “=CorrelationMatrix(” and then select the columns of price data. I know that after closing the parentheses you will remember not to hit <enter>” but rather “<ctrl><shift><enter>”.

Your formula bar should look like this: (It should look better if you are using Excel 2010.)

After a brief moment, we see our results:

While the results are correct, I wasted a lot of time. A correlation matrix is symmetric, so we really don’t need to calculatthe whole thing. If we change the starting index on the “j” loop in our code from 0 to i , this inefficiency will be eliminated.

For i = 0 To NumColumns

For j = i To NumColumns

matrix(i, j) = WorksheetFunction.Correl(rng.Columns(i + 1), rng.Columns(j + 1))

Next j

Next i

Now we have a nice correlation matrix. Interesting that there is a negative correlation between IBM and Microsoft in this data.

If you often need to repeat similar calculations for a range of cells, you might wish to design your own Excel VBA array function. It’s quick and easy!

For more on VBA, have a look at Learning Tree’s 4-day hands-on course – Introduction to VBA Programming.