How to write a VBA collection to an Excel sheet
If I want to write a 2D array that I've populated inside the code to a worksheet, I use this code. It is very efficient, as it only 'talks' to the worksheet once
Dim r as Range
Dim var_out as Variant
Set r = Range("OutputValues")
r.clear
var_out = r.value
'Then use code to appropriately fill the new 2D array var_out, such as your subroutine 1 above
r.value = var_out
You start by identifying the range in the workbook you want the array to print to. In this example, I assumed I named the output range "OutputValues".
The first assignment of r.value to var_out (my array variable I intend to populate) sets the dimensions of the array variable based on the size of the range. (It also reads in any existing values in the range, so if you don't want that, clear the range as I've shown here.)
The second assignment of the array variable to the range writes the values back to the sheet.
psychonomics
Marketing consultant, using statistics to understand customers. Use programming for tidy data and analysis. Simulations, optimisation Python, R, SQL. Eviews, SAS, SPSS, VBA. Did a course with Octave
Updated on July 09, 2022Comments
-
psychonomics almost 2 years
I have some existing code that I am modifying. This code creates a collection of rows from preexisting worksheet tables. It creates a large 2-D collection, with distinct information in each column. There is a separate class module that declares the data type for each column.
The code writes the 2-D collection to a new sheet by looping through each item in turn. I have never used a collection before, and would like to write the collection to the sheet in a single pass. The current code takes quite a long time when the table has lots of records.
Is there a way to convert the entire collection to a 2-D array, or so that I can then write the 2-D array in a single go? Or is there a way to write the entire collection to the sheet, just like with a 2-D array? I have tried to search for this and have so far been unsuccessful. Any general points would be appreciated!
Here is some example code, with comments in bold, to illustrate how the collection is being used.
Define the Class Module, Named as TableEntry
Public Item1 As String Public Item2 As String Public Item3 As String Public Item4 As Integer Public Item5 As Integer
Main Routine - Create the Collection, Fill the Collection, Write Collection to Sheet
Sub MainRoutine() Dim table As Collection Set table = New Collection Call FillCollection(File As String, ByRef table As Collection) Call WriteCollectionToSheet(ByRef table As Collection)
Sub Routine 1 - Fill the Collection
Dim wb As Workbook Set wb = Workbooks.Open(File) Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets Dim R As Range Set R = ws.Range("A2") Dim e As TableEntry For i = 1 To 20 Set e = New TableEntry e.Item1 = R.Offset(i + 1, 0).Offset(0, 0) e.Item2 = R.Offset(i + 1, 0).Offset(0, 1) e.Item3 = R.Offset(i + 1, 0).Offset(0, 2) e.Item4 = R.Offset(i + 1, 0).Offset(0, 3) e.Item5 = R.Offset(i + 1, 0).Offset(0, 4) table.Add e Next i Next ws
Sub Routine 2 - Write Collection to Sheet