How to create a workbook array dynamically in VBA

Question

I am trying to add workbook objects to an array of workbooks The array is of type public created in a separate module

Option Explicit
Public w() As Workbook
Public i As Integer

then i have the below procedure in a sub stored in a worksheet

Sub test_addwbobjects()
ReDim Preserve w(2)
Application.Workbooks("Quick analysis - Moneycontrol.xlsx").Activate
Set w(i) = ActiveWorkbook
'Set w(i) = Application.Workbooks("Quick analysis - Moneycontrol.xlsx")
i = i + 1
'ActiveSheet.Range("b2") = w(0).Sheets("CF analysis").Range("b2")
'ActiveSheet.Cells(1, 1) = w(i).Sheets("CF analysis").Range("b2")
'ActiveSheet.Range("b2") = w(1).Sheets("CF analysis").Range("b5")
End Sub

Context: The workbook "Quick analysis - Moneycontrol.xlsx" contains a financial model of a company. I want to be able to compare financial information of multiple companies, so for that purpose I want to create a workbook object every time I paste a company's financials into the "Quick analysis - Moneycontrol.xlsx" workbook

I am currently getting the subscript out of range error.

Can anyone help me with this?

Thanks

This seems counter-productive. Your commented line seems to rely upon a very specific worksheet name. Might be better to use a variant array of workbook names and encapsulate the referenced workbook in a With ... End With statement. It is hard to offer more without a wider overview of what you are trying to accomplish. (hint - you would have to ReDim the w array before assigning values or objects to it) — user4039065
thanks, your hint has worked. So we have know the array size before assigning anything to it? — Navkanth
Here is a link on loop through workbooks, Loop through a Folder of Workbooks and a short clip on that as well..Loop through a folder of workbooks,copy and paste data — Davesexcel

Darren Bartrup-Cook Darren Bartrup-Cook · Accepted Answer · 2016-02-01T15:23:05

Maybe this will help:

Public w As Collection

Sub Test_addWBObject()

    Set w = New Collection

    w.Add Item:=Workbooks("Quick analysis - Moneycontrol.xlsx"), _
          Key:="MyUniqueKeyForThisWorkbook"

    'Can use collection as this:
    ThisWorkbook.Worksheets("Sheet1").Range("A1") = w(1).Worksheets("Sheet1").Range("C2")

    'or this
    ThisWorkbook.Worksheets("Sheet1").Range("A1") = w("MyUniqueKeyForThisWorkbook").Worksheets("Sheet1").Range("C2")

    'or this
    Dim wrkBk As Variant
    For Each wrkBk In w
         ThisWorkbook.Worksheets("Sheet1").Range("A1") = wrkBk.Worksheets("Sheet1").Range("C2")
    Next wrkBk

    'or this
    Dim x As Long
    For x = 1 To w.Count
        ThisWorkbook.Worksheets("Sheet1").Range("A1") = w(x).Worksheets("Sheet1").Range("C2")
    Next x

End Sub

How to create a workbook array dynamically in VBA

1 Answers