Excel VBA: range.value

Writing a 2D array to a worksheet

option explicit

sub main() ' {

    dim array2D as variant
  '
  ' Use application.evaluate to create a 2D array:
  '
    array2D = evaluate(                                                    _
                  "{ """"         , ""Val 1"", ""Val 2"", ""Val 3""  ; " & _
                  "  ""Row one""  ,      17  ,       29 ,        18  ; " & _
                  "  ""Row two""  ,       4  ,       13 ,        12  ; " & _
                  "  ""Row three"",      16  ,       25 ,         7  ; " & _
                  "  ""Row four"" ,      22  ,        9 ,        14  } " )


    cells(3,2).resize(uBound(array2D, 1), ubound(array2D, 2)).value = array2D
    cells(3,2).resize(1                 , ubound(array2D, 2)).entireColumn.autoFit

end sub ' }

Create a 2D array from a range

option explicit

sub main() ' {


    dim ary_2d as variant

    dim referenceStyleOrig as long : referenceStyleOrig = application.referenceStyle

  '
  ' R1C1 really makes things a lot easier:
  '
    application.referenceStyle = xlR1C1

  '
  ' Create a 4x3 two-dimensional array and initialize its values to 42
  '
  ' Beware: if must be immediately followed by paranthesis.
  '
    ary_2d = [ if( isError(r1c1:r4c3), 42, 42 ) ]

    application.referenceStyle = referenceStyleOrig

    debug.print "Dimensions of created array are: "          & _
       lBound(ary_2d, 1) & " to " & uBound(ary_2d, 1) & ", " & _
       lBound(ary_2d, 2) & " to " & uBound(ary_2d, 2)

    debug.print "ary(2,3) = " & ary_2d(2,3)

end sub ' }

See also