Best Way to Transfer 'Dynamic' 2D Arrays?

lyndess · #1 06-06-2009, 02:00 PM

I believe I've reached a conculsion on this issue, but wanted to see if others might offer some expert advice.

Background: When working with two-dimensional (2D) tables of data in VBA, a best practice is to store the data in a 2D array and then transfer that array directly to an Excel range -- and vice versa. (For more on this, see here and here -- among many other other sources.)

Problem: Sometimes, it is not possible to know the full dimenions of the data table until runtime -- and not until the array is being filled with data -- thereby creating a need to potentionally redimension the 2D array (using ReDim Preserve) multiple times as it is filled up with data. (Specifically, we often know the number of columns in the data table, but not the number of rows.) Another problem here is that only the last dimension in a multi-dimension array can be resized (while preserving the existing data), but -- in the case of a 2D array that we will transfer to an Excel range -- we usually need to resize the first dimension (the rows), not the second (last) dimension (the columns).

Proposed Solution 1: Fill up the array by "flipping" rows and columns -- i.e., making the 2nd dimension in the array the rows (rather than the columns) -- and then transpose the array before writing it to the range. (Note that this solution still only allows you to resize one dimension of the array.)

Proposed Solution 2: Instead of creating and filling up a 2D array, create an array-of-arrays (described very well here by Excel guru Tushar Mehta) and fill that up with data -- effectively allowing you to re-dimension both dimensions, as necessary. Once the array-of-arrays is filled, create a new 2D array (based on the dimensions of the array-of-arrays), transfer the data from the array-of-arrays to the new 2D array (using a loop; unfortunately, simple setting the 2D array equal to the array-of-arrays won't work), and then transfer the new 2D array to Excel in one feel swoop.

Can anyone offer comments or expert advice on proposed solution 2?

(My ultimate goal is to create a reusable function that will read a set of data from a Microsoft Project file -- with a variable number of rows and columns, depending on which parameters are passed to the function -- and then transfer that data to an Excel worksheet.)

Thanks!

**Leith Ross** · #2 06-06-2009, 03:35 PM

Hello lyndess,

It isn't clear what question you want answered. You spend a lot of time discussing arrays but very little about the specific problems you are having with them. The problems you described are vague. You should provide code samples and/or a workbook that better illustrate your problems.

Welcome to the Excel Forum

Forum Statistics