Worksheet functions and VBA arrays

VBA arrays are very fast to manipulate even lots of data, because there is little or no overhead in reading or writing to the spreadsheet.

For instance I often use arrays to extract data (records) that meet certain conditions to another array, and then it is very convenient that I can use worksheet functions on the extracted records to get e.g. average and standard deviation.

The examples on this page are pretty simple, but you can download two zip-compressed workbooks with more advanced features including a userform with a listbox and a fileopen dialogue.

The file for download contains a workbook with the macros and a workbook with data.

The syntax for using worksheet functions is simple. Say for instance that you have an array called "arMatrix" and two variables "dAverage" and "dStdDev," you get the array's average and standard deviation in this way:

With Application.WorksheetFunction
   dAverage = .Average(arMatrix)
   dStdDev = .StDev(arMatrix)
End With

Of course you could also write:

dAverage = Application.WorksheetFunction.Average(arMatrix)
dStdDev = Application.WorksheetFunction.StDev(arMatrix)

but the "With - End With" construction is faster when addressing something in a hierarchy more than once.

If you copy values from a range to an array (as described on Arrays and ranges) in one swift operation, the array must be a Variant (datatype), but worksheet functions don't care if it is declared as e.g. Long or Double.

Like in this example, which is pretty useless except for showing how to fill an array with a 100 rows and columns and use worksheet functions.

Sub Demo()
Dim lCol As Long                          'Counter
Dim lRow As Long                          'Counter
Dim arMatrix(1 To 100, 1 To 100) As Long  'Array
Dim rTable As Range                       'Range

For lRow = 1 To 100
   For lCol = 1 To 100
      arMatrix(lRow, lCol) = lCol

'Use some worksheet functions and show the result
With Application.WorksheetFunction
   MsgBox "Average " & .Average(arMatrix)
   'The number is formatted to two decimals
   MsgBox "Standard deviation " & Format(.StDevP(arMatrix), "#0.00")
End With

'Set the rTable range to the same dimensions as the array
Set rTable = Range(Range("A1"), Range("A1").Offset(99, 99))

'Copy the array to rTable
rTable.Value = arMatrix

'Free memory
Set rTable = Nothing
Erase arMatrix

End Sub

At work (a cement plant) I can get log files with 17280 records (rows every 5 seconds) and X process values (columns) for each day. To analyze so much data manually would take a long time, but with macros it can be done in a few seconds.

There is a much simplified example in the workbooks for download, and to put it short I copy the process values to arrays, loop through them and copy records that meet certain criteria (e.g. "normal production level") to a new array.

I then use worksheet functions to help analyze the extracted data. One often used parameter for evaluating process stability is the number of "outliers" defined as values that are greater or lower than the average +/- 3 times the standard deviation.

To do stuff like this it is practical to have the macros in a separate workbook so you don't need to copy data or macros. The workbooks for download show how. At work I copy the results to a different workbook. The example doesn't show how to do that, but you can design your own way.

To limit the size of the download file there isn't much data in the datafile, but you can easily add more using copy/paste.

Try it out - I bet the speed will surprise you!

There is just one thing you should beware of: Newer versions of Excel have some worksheet functions that are not supported by older versions (like e.g. 2003), but most of them - I think - have a similar backwards compatible function.

To top