Array Formulas

You can enter an array formula into a cell using the FormulaArray property.

Range("B2").FormulaArray = "=SUM(A1:A2)" 

SS
Returns or sets the array formula of a range.
Returns (or can be set to) a single formula or a Visual Basic array.

Entering an array formula

Range("A1").FormulaArray = "=SUM(IF(-----------------))" 

Range("A1:B20").FormulaArray = "=SUM(IF(-----------------))" 

Reading an Array Formula

If the specified range doesn't contain an array formula, this property returns null. Read/write Variant.

It is often useful to create arrays in a VBA function and return them to Excel.

Public Function MovingAverage(ByVal rgeValues As Range, _ 
                              ByVal iInterval As Integer) As Variant 
   Dim lrowno As Long 
   Dim inumber As Integer 
   Dim arresult() As String 
   Dim dbtotal As Double 
   ReDim arresult(rgeValues.Rows.Count - 1) 
   For lrowno = 1 To (iInterval - 1) 
      arresult(lrowno - 1) = 0 
   Next lrowno 
   For lrowno = 1 To (rgeValues.Rows.Count - iInterval + 1) 
      dbtotal = 0 
      For inumber = 1 To iInterval 
         dbtotal = dbtotal + rgeValues(lrowno, 1).Offset(inumber - 1, 0).Value 
      Next inumber 
      arresult(lrowno + iInterval - 2) = (dbtotal / iInterval) 
   Next lrowno 
   MovingAverage = Application.WorksheetFunction.Transpose(arresult) 
End Function