简化重复代码的方法

Question

我需要运行很多代码，类似于下面的代码。我可以检查一下是否有简化代码的方法吗？用途:主要是从excel中提取数据，然后在powerpoint的表格中进行更新

ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(2, 1).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("A5").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(3, 1).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("A6").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(4, 1).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("A7").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(5, 1).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("A8").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(6, 1).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("A9").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(7, 1).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("A10").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(8, 1).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("A11").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(9, 1).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("A12").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(10, 1).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("A13").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(11, 1).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("A14").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(12, 1).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("A15").Text
'Update 2nd column
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(2, 2).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("B5").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(3, 2).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("B6").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(4, 2).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("B7").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(5, 2).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("B8").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(6, 2).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("B9").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(7, 2).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("B10").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(8, 2).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("B11").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(9, 2).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("B12").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(10, 2).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("B13").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(11, 2).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("B14").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(12, 2).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("B15").Text
'Update 3rd column
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(2, 3).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("C5").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(3, 3).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("C6").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(4, 3).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("C7").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(5, 3).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("C8").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(6, 3).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("C9").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(7, 3).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("C10").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(8, 3).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("C11").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(9, 3).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("C12").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(10, 3).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("C13").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(11, 3).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("C14").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(12, 3).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("C15").Text
'Update 4th column
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(2, 4).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("D5").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(3, 4).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("D6").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(4, 4).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("D7").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(5, 4).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("D8").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(6, 4).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("D9").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(7, 4).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("D10").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(8, 4).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("D11").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(9, 4).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("D12").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(10, 4).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("D13").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(11, 4).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("D14").Text
ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(12, 4).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range("D15").Text

Answer 1

您可以创建一个(A到D)字母数组，而不是一个单元格数组(2到12)

Dim Letters(4) As String
Letters(0) = "A"
Letters(1) = "B";
Letters(2) = "C";
Letters(3) = "D";

Dim Cols(11) As Integer
Cols(0) = 2
Cols(1) = 3
Cols(2) = 4
Cols(3) = 5
Cols(4) = 6
Cols(5) = 7
Cols(6) = 8
Cols(7) = 9
Cols(8) = 10
Cols(9) = 11
Cols(10) = 12

Dim rowIndex = 1
Dim startIndex = 5

然后遍历字母

For i = 0 to 3
    startIndex = 5
    For j = 0 to 10
    ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(Cols(j), rowIndex).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Range(Letters(i) + "" + startIndex).Text
    startIndex = startIndex + 1
Next
rowIndex = rowIndex +1   
Next

Answer 2

是的，这就是循环的用途。对于您的示例代码，以下代码可以替换整个代码块：

Sub looping()
Dim i As Integer, j As Integer

For i = 1 To 4
    For j = 2 To 12
        ppPres.Slides("China_A_T10").Shapes("China_A_T10").Table.Cell(i, j).Shape.TextFrame.TextRange.Text = xlBook.Worksheets("China_A_T10").Cells(j + 3, i).Text
    Next j
Next i
End Sub

其工作方式是将表格单元格行和列值分配给一个整数，这会改变循环的每一次运行。工作表区域应该具有相同的形状，因此由起始位置调整的相同整数应该从正确的单元格中抓取。然后，它为每一列和每一行循环，并设置所有位置。

请注意，它可能需要根据您的数据进行调整，请在运行之前备份它，这样您就不会丢失任何东西。

同样，这也可以通过像Application.screenupdating = false这样的东西来加速，或者通过将所有这些内容读取到一个数组中并一次性输出。