Excel Range object and VBA arrays

Combining resize and assigning an array

option explicit

sub main() ' {

    dim ary(1 to 5) as string

    ary(1) = "one"
    ary(2) = "two"
    ary(3) = "three"
    ary(4) = "four"
    ary(5) = "five"

    range("b3").resize(1, uBound(ary)).value = ary

end sub ' }

option explicit

sub main() ' {

    dim ary_2d as variant

    ary_2d = [{ 1,2,3,4,5 ; "one","two","three","four","five" ; "a","b","c","d","e" }]

    dim ary_width  as integer
    dim ary_height as integer

    ary_width  = uBound(ary_2d, 2)
    ary_height = uBound(ary_2d, 1)

    debug.print ary_width  ' 5
    debug.print ary_height ' 3

    range("b2").resize(ary_height, ary_width).value = ary_2d

end sub ' }

Writing an array horizontally or vertically to a worksheet

option explicit

sub main() ' {

    dim rngRowNames as range
    dim rngColNames as range

    set rngRowNames = writeArrayToSheet(3, 2, array("1st row", "2nd row", "3rd row", "4th row"), false)
    set rngColNames = writeArrayToSheet(2, 3, array("val one", "val two", "val three"         ), true )

    rngRowNames.interior.color = rgb(240, 200, 170)
    rngColNames.font.bold      = true

end sub ' }

function writeArrayToSheet(r as long, c as long, ary as variant, optional horizontal as boolean = false, optional ws as worksheet = nothing) as range ' {

    if ws is nothing then
       set ws = activeSheet
    end if

    dim szArray_minusOne as long
    szArray_minusOne = uBound(ary) - lBound(ary)

    with ws ' {

        if horizontal then
           set writeArrayToSheet = .range(.cells(r, c), .cells(r, c+szArray_minusOne))
           writeArrayToSheet.value = ary
        else
           set writeArrayToSheet = .range(.cells(r, c), .cells(r+szArray_minusOne, c))
           writeArrayToSheet.value = application.worksheetFunction.transpose(ary)
        end if

    end with ' }

end function ' }

After executing main, the arrays were written like so:

See also