intialize populated array excel vba code example

Example: excel vba initialize entire array

'VBA function to create a 2D array with every element set 
'to the same initial value:

Function Init2D(rows, cols, Optional val)
    Dim i&, j&
    ReDim v(1 To rows, 1 To cols)
    If Not IsMissing(val) Then
        For i = 1 To rows
            For j = 1 To cols
                v(i, j) = val
            Next
        Next
    End If
    Init2D = v
End Function

'This version uses no loops:
Function Init2D(rows, cols, Optional val = "")
    Const NUMER = "index(offset(a1,,,ROWS,COLS)*0+VAL,,)"
    Const ALPHA = "index(rept("""",len(offset(a1,,,ROWS,COLS))<0)&""VAL"",,)"
    DoEvents
    Init2D = Evaluate(Replace(Replace(Replace(IIf(IsNumeric(val), NUMER, ALPHA), "VAL", val), "ROWS", rows), "COLS", cols))
End Function

'--------------------------------------------------------------------

'10 rows, 1 column, all elements = 0:
a = Init2D(10, 1, 0)

'20 rows, 5 columns, all elements = "abc":
a = Init2D(20, 5, "abc")

'--------------------------------------------------------------------

'1D array version:
Function Init1D(elems, Optional val, Optional base = 1)
    Dim i&, max&
    max = base + elems - 1 
    ReDim v(base To max)
    If Not IsMissing(val) Then
        For i = base To max
            v(i) = val
        Next
    End If
    Init1D = v
End Function