Producing an Array of Numbers

My previous comment below describes Excel array constants and mentions a VBA Variant array. I don't believe there is a way to define an array constant in VBA, but you can assign an Excel array constant to a VBA Variant array. For example,
    Dim vArray as Variant
    vArray = [{1,2,3;4,5,6}]
Brackets like [...] are an alternate form of the Application.Evaluate method. This vArray will have dimensions (1 to 2, 1 to 3). Notice an Excel array constant is base-1 (first index is 1); so is an Excel dynamic array result.
For the constant row vector in my previous comment,
    vArray = [{0,1,2,3,4,5,6}]
vArray will be 1D with dimensions (1 to 7).
For the constant column vector in my previous comment,
    vArray = [{0;1;2;3;4;5}]
vArray will be 2D with dimensions (1 to 6, 1 to 1).
In general, an Excel range can be assigned to a VBA Variant array and vice-versa. Here are several equivalent ways to assign values for the range illustrated in Figure 1 of my previous comment (A1# references the dynamic array):
    vArray = [A1#]
    vArray = [A1:G6]
    vArray = Range("A1#")
    vArray = Range("A1:G6")
    vArray = Range("A1#").Value
    vArray = Range("A1:G6").Value
After a range has been assigned to an array it can be processed efficiently. The result can be assigned to the original range or to another range with the same dimensions as vArray. For example,
    vArray = Range("A1:G6")
    ...  'process vArray
    Range("A1:G6") = vArray
That example requires a Sub procedure (macro). Here is a Function procedure (UDF) that accepts a scalar, row vector, column vector, or matrix Source constant or range and returns each element with numbers doubled:

Function Doubler(Source As Variant) As Variant
    Dim vArray As Variant, b1D As Boolean
    Dim nR As Long, nC As Long, nCL As Long, nCU As Long
    vArray = Source
    If IsArray(vArray) Then
        On Error Resume Next
            nCL = LBound(vArray, 2)
            nCU = UBound(vArray, 2)
            b1D = (Err <> 0)
        On Error GoTo 0
        If b1D Then
            For nC = LBound(vArray, 1) To UBound(vArray, 1)
                If WorksheetFunction.IsNumber(vArray(nC)) Then
                    vArray(nC) = 2 * vArray(nC)
                End If
            Next nC
        Else
            For nR = LBound(vArray, 1) To UBound(vArray, 1)
                For nC = nCL To nCU
                    If WorksheetFunction.IsNumber(vArray(nR, nC)) Then
                        vArray(nR, nC) = 2 * vArray(nR, nC)
                    End If
                Next nC
            Next nR
        End If
    Else
        If WorksheetFunction.IsNumber(vArray) Then vArray = 2 * vArray
    End If
    Doubler = vArray
End Function

Notes:
1. VBA's IsNumeric function returns True for text like "123" but Excel's ISNUMBER does not.
2. If Source is a range, empty cells will be returned as zero values; blank text like "" will not.
3. VBA's For Each...Next statement can step through each element of an array but any change made to an element will be ignored by the array.

In Excel nomenclature, braces like {...} define an array constant in terms of numeric, text, and/or logical constants; for example,
    {-6, 3.14; "hello", TRUE}
Commas separate column elements; semicolons separate row elements. An array constant must not include cell references or expressions, so A1, 2*7, PI(), etc., are not allowed. An array constant can be considered an expression, so an array constant cannot include an array constant.
In Excel (as in VBA), arrays are expressed as rows containing columns. So the 2x3 array constant {1,2,3;4,5,6} has 2 rows and each row has 3 columns. This cannot be expressed as 3 columns with 2 rows each like {1;4,2;5,3;6}. A 2D array must be rectangular, so each row must have the same number of columns.
A 2D array can be called a matrix as noted in the Tip. And a 1D array can be called a vector. So {0,1,2,3,4,5,6} is a constant row vector with 7 columns
and {0;1;2;3;4;5} is a constant column vector with 6 rows. In modern versions of Excel that support dynamic arrays, the Tip's formula
    ={0,1,2,3,4,5,6} + {0;1;2;3;4;5}*7
returns a 6x7 matrix (6 rows and 7 columns). If that formula is in cell A1, the resulting array (see Figure 1 below) can be referenced by A1#. The Nth row is given by INDEX(A1#, N, 0), so this formula returns the 1st row:
    =INDEX(A1#, 1, 0) -- equivalent to {0,1,2,3,4,5,6}
The Nth column is given by INDEX(A1#, 0, N), so this formula returns the 1st column:
    =INDEX(A1#, 0, 1) -- equivalent to {0;7;14;21;28;35}
The element at row R column C is given by INDEX(A1#, R, C), so this formula returns the last element:
    =INDEX(A1#, 6, 7) -- equal to 41
OFFSET(A1#, I, J, K, L) can be used to extract a KxL subset of A1# beginning at row I+1 and column J+1, so this formula returns the 2x2 bottom-right corner:
    =OFFSET(A1#, 4, 5, 2, 2) -- equivalent to {33,34;40,41}
In VBA, the expression
    Evaluate("A1#")(6, 7)
returns the last element (41), but curiously
    [A1#](6, 7)
returns an error. On the other hand,
    Dim v as Variant
    v = [A1#]
    MsgBox v(6, 7)
displays 41 as expected.
By the way, the Tip's last paragraph refers to a 7x6 matrix. This is not incorrect, but Excel prefers to call it 6x7.

I've pretty much ignored array formulas up to now. Thanks for the clear and concise example that I can fit in my mental toolbox! It will come in hndy, for sure!

Hi, Allen.

ExcelTips features a section named FORMULAS and others that describe FUNCTIONS capabilities. However, there is no a FUNCTIONS section with ExcelTips concerning those Excel tools specially.

So, I want to suggest you consider the possibility to add a FUNCTIONS section to ExcelTips so that ExcelTips users can encounter tips and stuff about FUNCTIONS.

Reading today's ExcelTips Ribbon newsletter, Help Wanted section, 'Performing Integer Divisions' question was asked for some FUNCTIONS capabilities. So, I think this is the opportunity to create such FUNCTIONS section for ExcelTips Ribbon.

Thanks a lot for your attention and I hope you encounter this suggestion useful for your ExcelTips newsletter.

Please, have a nice day, weekend...