时间戳

首页 > 解决方案 > 将数组集成到excel VBA中的子例程中

excel - 将数组集成到excel VBA中的子例程中

问题描述

我是 vba 新手，我正在尝试创建一个子例程，它将对 5 个工作簿执行相同的复制和粘贴操作。

我试图通过构建一个包含所有需要执行该功能的窗口的数组来做到这一点，如下所示：

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

但是当我尝试运行宏时，我收到错误“查询编译错误常量表达式”

标签： excelvba

解决方案

这是一个有效的语法：

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

注意使用ReDim.

推荐阅读