Array Formulas

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

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

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

© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext