Integrate an array into a subroutine in excel VBA

Question

I'm new to vba and I'm trying to create a subroutine that will perform the same copy and paste operation to 5 workbooks.

I'm trying to do this by building an array containing all of the windows that the function needs to be carried out on as per the below:

Sub Copyinforevised()
Dim i As Integer
Dim z As Integer
i = 1
z = 5
Dim wb(i To z) As Window
wb (1): Set wb1 = Windows("Chariot OPS project workbook.xlsx")
wb (2): Set wb2 = Windows("Chariot RAN project workbook.xlsx")
wb (3): Set wb3 = Windows("Chariot AT project workbook.xlsx")
wb (4): Set wb4 = Windows("Chariot OSS project workbook.xlsx")
wb (5): Set wb5 = Windows("Chariot MOB project workbook.xlsx")
For i = 1 To z
    Windows(wb(i)).Activate
[function to be done to the workbook]
Next i
End Sub

But when I try to run the macro I get the error "compile error constant expression queried"

I think you need Workbooks instead of Windows. Also wb1 etc is not part of the array. So something like Set wb(1) = Application.Workbooks("Chariot OPS project workbook.xlsx") makes more sense. In your loop you can also then use wb(i).Activate without using Windows — Alex de Jong
– Alex de Jong, Commented Mar 4, 2019 at 14:39
@AlexdeJong: Not good idea to use .Activate. You don't need to do that as you already have the object. There are many examples on the net which will go into details why we should never use .Activate or .Select. Please have a read — Zac
– Zac, Commented Mar 4, 2019 at 14:52

Gary's Student · Accepted Answer · 2019-03-04 14:52:11Z

2

Here is a working syntax:

Sub Copyinforevised()
Dim i As Integer
Dim z As Integer
Dim wb

i = 1
z = 5

ReDim wb(i To z) As Window

Set wb(1) = Windows("Chariot OPS project workbook.xlsx")
Set wb(2) = Windows("Chariot RAN project workbook.xlsx")
Set wb(3) = Windows("Chariot AT project workbook.xlsx")
Set wb(4) = Windows("Chariot OSS project workbook.xlsx")
Set wb(5) = Windows("Chariot MOB project workbook.xlsx")
End Sub

Note the use of ReDim.

answered Mar 4, 2019 at 14:52

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Zac Over a year ago

Would the Set throw an error if the workbook is not open?

Gary's Student Over a year ago

@Zac .................yes................you need a captioned Window Object............

BruceWayne Over a year ago

@Gary'sStudent - I haven't really seen Windows() before. Is that a suggested way to use this, or is it perhaps better practice to set these as actual Workbook variables?

BruceWayne · Accepted Answer · 2019-03-04 14:47:40Z

0

Instead of setting a bunch of Workbook variables (which is certainly a way to do this), you could put your workbook names in to an array, and use that to loop through:

Sub Copyinforevised()
Dim wbList() As Variant
wbList = Array("Chariot OPS project workbook.xlsx", "Chariot RAN project workbook.xlsx", _
    "Chariot AT project workbook.xlsx", "Chariot OSS project workbook.xlsx", _
    "Chariot MOB project workbook.xlsx")

Dim i As Long
For i = LBound(wbList) To UBound(wbList)
    With Workbooks(wbList(i))
        ' Do whatever with the workbook
        Debug.Print .Sheets(1).Range("A1").Value
    End With
Next i

End Sub

(And just a note these workbooks must be open.)

answered Mar 4, 2019 at 14:47

BruceWayne

23.5k16 gold badges75 silver badges126 bronze badges

Collectives™ on Stack Overflow

Integrate an array into a subroutine in excel VBA

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related