power4XL Microsoft Excel Resource Center
Free Macros, Formulas, Functions, Tutorials, Downloads, Add-Ins & More!

Writing VBA arrays to worksheets - the basics

ShareThis

In order to illustrate concepts of writing arrays to worksheet ranges, we'll use a worksheet with the range name MyData referring to the single cell A1:

We'll also use a declared array variable MyArray, which in this first example has a single dimension with five elements (0 to 4).  In the function PrintMyArray_1() below, even though the array elements are assigned the values 0, 1, 2, 3, and 4, only the first element (0) is transferred to the worksheet:



This is because the range MyData is only 1 x 1 cells in size.  So, the lesson here is that regardless of the size of the array, the output to the worksheet will be limited to the size of the range to which data is transferred.  The rest of the array output is truncated.

The logical assumption if we wish to write all five array elements into range A1:A5 of the worksheet is that we can resize the range MyData through VBA code as follows:



The disappointing surprise is that we get a column of zeros rather than the expected 0, 1, 2, 3, 4.  What went wrong?

The answer is that single-dimension arrays are stored as rows, not columns.  Thus if we resize the range MyData as in PrintMyArray_3() below to be five columns wide rather than five rows high, all five elements of the array are copied to the worksheet in row 1.  As it turns out, the example PrintMyArray_2() above was simply printing the first element of the array five times.



On the next page we'll demonstrate how to transpose the array so that we can print the output to the worksheet in column format.

 

Prev | Contents | Next

Copyright notice:  This site and all content, including computer code and spreadsheet examples, are copyright 2006 by Fritz Dooley.  License is granted for individual users to download examples and to copy code directly into user's spreadsheets and Visual Basic for Applications files.  Users may not redistribute code in any way.  Providing hyperlinks to this web site is encouraged, but posting code and examples on other web sites is expressly forbidden.  "Microsoft" is a registered trademark of Microsoft Corporation.   Neither this web site nor Fritz Dooley is affiliated with Microsoft Corporation.