We wish to create a user defined VBA array function called DevAve that determines the average of an array and then calculates how far from the average each element in the array is (see below). In this example, the average of the range is 2.5.

Download the file \"DevAve_starter.xlsm\" and in the code for the DevAve function there are 5 spots that say 'Fill in the code here!' Replace all 5 of these with code to make the array function work properly. There is also an error with ONE of the Dim statements - one of the data types is wrong so you'll need to fix it. Don't forget to highlight the output region and use Ctrl-Shift-Enter after you type in the array function!

Option Explicit

Option Base 1

Function DevAve ('Fill in the code here!' As Range)

Dim i As Integer, j As Integer, nr As Integer, nc As Integer Dim avg As Integer, A() As Double

nr = DataRange.Rows.Count

nc = 'Fill in the code here!

ReDim A(nr, nc) As Double

avg = 'Fill in the code here!

For i = 1 To nr

For j = 1 To nc

'Fill in the code here!

Next j

Next i

DevAve = 'Fill in the code here!

End Function