Excel Formula Array with VBA (Visual Basic for Applications) code example

Excel Formula Array with VBA (Visual Basic for Applications) code example

FormulaArray

Refers to a property exposed by Range object in Excel which sets the array formula of a range. Formula array refers with curly “{” as starting and curly “}” as closing boundary of an array.

Syntax

Range.FormulaArray

Example

In this example we will insert COUNTA formula through selected range in given data:

Code example

Public Sub FormulaArray()
    'Declare range object
    Dim oRange As Range
    'Bind selection to Range
    Set oRange = Selection
    
    'Insert COUNTA using formula array using name range
    oRange.FormulaArray = "=COUNTA(NameList)"
    
    'Memory cleanup
    Set oRange = Nothing
End Sub

Note: NameList refers to a named range in Excel user can pass physical address like “B2:B12” and formula will look like “=COUNTA(B2:B12)

Output

Next >> Bulk formula updates in a Sheet with Bulk Data Excel PoC

Leave a Reply

Your email address will not be published. Required fields are marked *