☰

如何重构 VBA 代码来迭代应用一组单元格操作的工作表？

tag-icon • tag-icon microsoft-excel macros
如何重构 VBA 代码来迭代应用一组单元格操作的工作表？

这是我的代码：

'copy cells
Sub start()
'variable for all worksheets in the workbook
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim sh3 As Worksheet
Dim sh4 As Worksheet
Dim sh5 As Worksheet
Dim sh6 As Worksheet
Dim sh7 As Worksheet
Dim sh8 As Worksheet
Dim sh9 As Worksheet
Dim sh10 As Worksheet
Dim sh11 As Worksheet
Dim sh12 As Worksheet
Dim sh13 As Worksheet
Dim sh14 As Worksheet
Dim sh15 As Worksheet
Dim sh16 As Worksheet
Dim sh17 As Worksheet
Dim sh18 As Worksheet
Dim sh19 As Worksheet
Dim sh20 As Worksheet
Dim sh21 As Worksheet
Dim sh22 As Worksheet
Dim sh23 As Worksheet
Dim sh24 As Worksheet
Dim sh25 As Worksheet
Dim sh26 As Worksheet
Dim sh27 As Worksheet
Dim sh28 As Worksheet
Dim sh29 As Worksheet
Dim sh30 As Worksheet
Dim sh31 As Worksheet
Dim sh32 As Worksheet
Dim sh33 As Worksheet
Dim sh34 As Worksheet
Dim sh35 As Worksheet
Dim sh36 As Worksheet
Dim sh37 As Worksheet
Dim sh38 As Worksheet
Dim sh39 As Worksheet
Dim sh40 As Worksheet
'variable for the workbook itself
Dim wkb As Workbook
'set workbook
Set wkb = Workbooks("macro2.xlsm")
With wkb 'create with statement to call on worksheets

Set sh1 = .Sheets("1")
Set sh2 = .Sheets("2")
Set sh3 = .Sheets("3")
Set sh4 = .Sheets("4")
Set sh5 = .Sheets("5")
Set sh6 = .Sheets("6")
Set sh7 = .Sheets("7")
Set sh8 = .Sheets("8")
Set sh9 = .Sheets("9")
Set sh10 = .Sheets("10")
Set sh11 = .Sheets("11")
Set sh12 = .Sheets("12")
Set sh13 = .Sheets("13")
Set sh14 = .Sheets("14")
Set sh15 = .Sheets("15")
Set sh16 = .Sheets("16")
Set sh17 = .Sheets("17")
Set sh18 = .Sheets("18")
Set sh19 = .Sheets("19")
Set sh20 = .Sheets("20")
Set sh21 = .Sheets("21")
Set sh22 = .Sheets("22")
Set sh23 = .Sheets("23")
Set sh24 = .Sheets("24")
Set sh25 = .Sheets("25")
Set sh26 = .Sheets("26")
Set sh27 = .Sheets("27")
Set sh28 = .Sheets("28")
Set sh29 = .Sheets("29")
Set sh30 = .Sheets("30")
Set sh31 = .Sheets("31")
Set sh32 = .Sheets("32")
Set sh33 = .Sheets("33")
Set sh34 = .Sheets("34")
Set sh35 = .Sheets("35")
Set sh36 = .Sheets("36")
Set sh37 = .Sheets("37")
Set sh38 = .Sheets("38")
Set sh39 = .Sheets("39")
Set sh40 = .Sheets("40")
Set shsum = .Sheets("sum") 'name sheet to copy to
'add more if there are more sheets example
'set sh41 = .sheets("41")

'name first range to copy
sh1.Range("b8").copy shsum.Range("a2")
sh1.Range("b5").copy shsum.Range("b2")
sh1.Range("b4").copy shsum.Range("c2")
sh1.Range("G13:G30").copy shsum.Range("d2:d18")
sh1.Range("h13:h30").copy shsum.Range("e2:e18")
sh1.Range("i13:i30").copy shsum.Range("f2:f18")
sh1.Range("j13:j30").copy shsum.Range("g2:g18")
sh1.Range("k13:k30").copy shsum.Range("h2:h18")

End With
End Sub

为了获得 40 张表上的数据，我需要在每张表上输入相同的信息，这需要大量的打字工作。

  • 有什么方法可以为表格做某种阵列吗？
  • 然后，我需要为每个附加工作表向下跳过 18 行，以便工作表中没有数据重叠sum。这是因为 g13 到 k30 有 18 行数据需要填写。b8、b5 和 b4 是与每个电子表格中占用 g13 到 k30 的项目相关的项目名称 ( X)、编号 ( Y) 和数字 ( Z)。我需要确保复制到sum工作表中的每个电子表格的数据在第一行包含X、Y和Z项目，然后是 18 行长的相关项目。

答案1

Option Explicit

'copy cells
Sub start()
Dim ws As Worksheet, wsum As Worksheet
Dim wb As Workbook
Dim vws As Variant 'Need to use a Variant for iterator
Dim i As Integer, j As String, k As String

i = 0
Set wb = Workbooks("macro2.xlsm")
Set wsum = wb.Sheets("sum")

'Iterate through the sheets
For Each vws In wb.Sheets
If vws.Name <> "sum" Then 
j = CStr(i + 2)
k = CStr(i + 18)
vws.Range("b8").Copy wsum.Range("a" & j)
vws.Range("b5").Copy wsum.Range("b" & j)
vws.Range("b4").Copy wsum.Range("c" & j)
vws.Range("G13:G30").Copy wsum.Range("d" & j & ":d" & k)
vws.Range("h13:h30").Copy wsum.Range("e" & j & ":e" & k)
vws.Range("i13:i30").Copy wsum.Range("f" & j & ":f" & k)
vws.Range("j13:j30").Copy wsum.Range("g" & j & ":g" & k)
vws.Range("k13:k30").Copy wsum.Range("h" & j & ":h" & k)
i = i + 18
End If
Next
End Sub

经过大量重构以使用循环和字符串连接。仅经过编译测试，因此可能无法正常工作；任何缺陷均由您自行承担，如果您不满意，我将退还全部购买价格。感谢您在 McSoftware 购物。

这是非常基础的（没有双关语的意思），你应该可以学会。以下几点可以解释我是如何想到这个的：

  • For Each vws In wb.Sheets 迭代遍历工作簿中的每个工作表。“迭代”的意思是“逐个遍历并在循环中分别处理每个元素”。
  • 如果你需要在一个函数中使用十几个变量，那么你就做错了，应该立即停止并尝试其他方法。在 SuperUser 上提问并不是你能做的最好的事情，但它比再复制粘贴 48 次代码要好，所以我赞扬你至少意识到了存在问题。
  • 细绳级联表示将两个较小的字符串放在一起。例如，“he”&“llo”将产生连接字符串“hello”。该&运算符用于在 Visual Basic 中连接字符串。
  • 您可能可以通过添加循环并创建“d”、“e”、“f”、“g”、“h”字母的常量数组来进一步减少此代码的重复性——但由于只有五个字母，因此编写循环的工作量几乎与逐个执行相同。如果您最终得到的列更多，请务必编写一个包含字母数组或字典的循环。
  • 我使用函数将数字转换为字符串，CStr()将整数i加上从原始代码派生的常数进行转换。因此，在循环的第一次迭代中，它将放入vws.Range("b8")求和表的范围中，A2因为0 + 2 = 2。在循环结束时，请注意i = i + 18增加计数器，以便在第二次迭代中，第二个工作表中的相同单元格被复制到求和表的范围中A20。您可能必须调整数字，因为我无法从您的源数据中判断这是否会意外覆盖/践踏行。

参考

在尝试使用 Visual Basic（用于应用程序）进行任何更奇特的操作之前，请先阅读一本有关编程的书。

  • 一本非常基础的书，一步一步指导你（可能是你所需要的）这里-Excel VBA in Easy Steps作者：埃德·罗宾逊（Ed Robinson）。
  • 一个虽然过时但好用，VBA Developer's Handbook 2nd Edition作者：Ken Getz。

如果你尝试一种更现代、更通用的编程语言，比如 Python、Java 或 C++，你也许会获得更多的视角和洞察力，从而成为一名熟练的程序员，只是为了学习一些概念，这些概念可以帮助你用 Python 或 Java 或 C++ 编写出好的代码。任何语言。

相关内容