VBA: delay adding Worksheet Object to Worksheets Collection

[Rufius]

I am trying to:

1. Take the input data from a range, all at once (and loading it up in Arrays and Dictionaries)
2. do my business logic
3. export only what I need (worksheet objects), ALL at once (saving going back and forth between the logic and the 'view' - in this case the worksheet)

In this case I am export many (possibly hundreds) of worksheets. Instead of adding the worksheet with Worksheets.Add or Sheets.Add I would like to create a worksheet object (before it gets added to the collection) and add my information to it first. That way, when I'm done I can just add all the worksheet objects I have created to the worksheets collection. I tried to write some code for what I think that might look like (I know .Add doesn't take that syntax, and .Cells isn't working):

Public Sub exampleSub()

Dim worksheetInMemory As New Worksheet

worksheetInMemory.Cells(1, 1) = "test info"

Worksheets.Add (worksheetInMemory)

End Sub

I am happy to say more as to why I want to do this this way (performance reasons) if that helps solve the question.

 

[Rufius]

Also: I thought of another way to pose the question.

Conceptually the worksheet is an object (which has properties like .Cells, etc.). The Worksheets.Add method has some logic that gathers the Worksheet object and adds it to the Worksheets Collection.

Does anyone know of a way to home-brew my own Worksheets.Add method where I could add the necessary logic to append cells to the Worksheet object before adding it to its parent collections (thereby making it present in the workbook and accessible to the user)? Can you view the source code for something like Worksheets.Add?

 

[Kyle123]

You can't do it, you can only add a sheet through the collection. I suspect that no small amount of work is done in that add call.

Conceptually what's the difference in adding the sheet first to the collection and turning screen updating off (essentially disabling the view) and creating the object and adding it to the sheets collection, the view would still need to be made - just in the loop at the end rather than as you go. I don't see why performance would differ

 

[Rufius]

Ok, thanks. I will consider it solved to the fullest extent possible. If the performance will not differ, I guess I don't care what order things happen.

I appreciate your help.