VBA中下一个循环的替代方案 [英] Alternatives to For Next Loop in VBA

问题描述

我已经在VBA中创建了一个能够读取excel工作簿并允许我修改和保存的程序。但是我的程序运行需要相当多的时间。我正在寻找比我目前的For-Next循环计算成本更低的东西。这是我的代码如下。我非常感谢您提供的任何帮助和反馈。谢谢！

Hi, I''ve created a program in VBA that is able to read in excel workbooks and lets me modify and save. However my program takes quite a bit of time to run. I''m looking for something less computationally expensive than my current For-Next loops. Here is my code below. I greatly appreciate any help and feedback you may give. Thanks!

For rowindex = 1 To DataGridView3.RowCount

    For columnindex = 1 To DataGridView3.ColumnCount

        objworksheet3.Cells(rowindex + 3, columnindex + 1) = DataGridView3(columnindex - 1, rowindex - 1).Value

    Next
Next

For rowindex = 1 To DataGridView1.RowCount
    For columnindex = 1 To DataGridView1.ColumnCount
        objWorkSheet1.Cells(rowindex + 4, columnindex + 0) = DataGridView1(columnindex - 1, rowindex - 1).Value
        objWorkSheet1.Cells(rowindex + 4, columnindex + 1) = DataGridView1(columnindex - 1, rowindex - 1).Value
        objWorkSheet1.Cells(rowindex + 4, columnindex + 2) = DataGridView1(columnindex - 1, rowindex - 1).Value
        objWorkSheet1.Cells(rowindex + 4, columnindex + 3) = DataGridView1(columnindex - 1, rowindex - 1).Value
        objWorkSheet1.Cells(rowindex + 4, columnindex + 4) = DataGridView1(columnindex - 1, rowindex - 1).Value
        objWorkSheet1.Cells(rowindex + 4, columnindex + 7) = DataGridView1(columnindex - 1, rowindex - 1).Value
        objWorkSheet1.Cells(rowindex + 4, columnindex + 8) = DataGridView1(columnindex - 1, rowindex - 1).Value

    Next
Next

推荐答案

没有什么可以改变以加快速度。已知Excel对象模型非常慢。



如果您使用的是Excel 2010及更高版本（OpenOfficeXML格式工作簿），您可以直接加载和编写工作表，比通过Excel oObject模型要快得多。虽然这样做比你现在的FAR复杂得多。

There''s nothing to change to speed things up. The Excel object model is known to be pretty slow.

If you were using Excel 2010 and above (OpenOfficeXML format workbooks) you could load and write the sheets directly, which would be much faster than going through the Excel oObject model. Though, doing so is FAR more complex than what you have now.

我意识到你已经接受了这个答案，但你可以做些什么来加快速度。



您设置的每个单元格（范围）都需要跨越互操作边界进行调用;这个过境点是缓慢的部分。当您填充连续的单元格范围（矩形区域）时，您可以计算此整体范围并将其设置为相同尺寸的数组。



这是一个简单的示例（控制台应用程序）来演示这个概念。



在我的机器上，这产生了以下时间：

每个单元经过的时间：2783毫秒

阵列更新经过的时间：15毫秒

I realize that you have already accepted an answer for this, but there is something that you can do to speed things up.

Each cell (range) that you set requires a call across the interop boundary; this boundary crossing is the slow part. As you are filling a contiguous range of cells (a rectangular region), you can compute this overall range and set it to an equally dimensioned array.

Here is a simple example (console app) to demonstrate the concept.

On my machine, this yielded the following times:
Each Cell Elapsed Time: 2783 ms
Array Update Elapsed Time: 15 ms

Imports Excel = Microsoft.Office.Interop.Excel

Module Module1

   Sub Main()
      '' make some data to copy to Excel
      Dim data(0 To 99, 0 To 9) As Object
      Dim count As Int32 = 0
      For row As Int32 = 0 To UBound(data, 1)
         For col As Int32 = 0 To UBound(data, 2)
            count += 1
            data(row, col) = count
         Next
      Next

      Dim app As New Excel.Application '' start Excel
      app.Visible = False

      Dim wb As Excel.Workbook = CType(app.Workbooks.Add(), Excel.Workbook)
      Dim sht As Excel.Worksheet = CType(wb.ActiveSheet, Excel.Worksheet)
   ''  *******************************************
   ''  copy individual values to each cell

      Dim sw1 As New Diagnostics.Stopwatch
      sw1.Start()

      For row As Int32 = 0 To UBound(data, 1)
         For col As Int32 = 0 To UBound(data, 2)
            count += 1
            sht.Cells(row + 1, col + 1) = data(row, col)
         Next
      Next
      sw1.Stop()
      Console.WriteLine("Each Cell Elapsed Time: " & sw1.ElapsedMilliseconds.ToString() & " ms")

   ''  *******************************************
   ''  Compute range to hold array and copy array

      Dim sw2 As New Diagnostics.Stopwatch
      sw2.Start()

      ''set the 1st cell in range
      Dim startcell As Excel.Range = CType(sht.Cells(110, 1), Excel.Range)

      ''compute last cell in range
      Dim endcell As Excel.Range = startcell.Offset(UBound(data, 1), UBound(data, 2))

      '' set range data
      sht.Range(startcell, endcell).Value = data

      sw2.Stop()
      Console.WriteLine("Array Update Elapsed Time: " & sw2.ElapsedMilliseconds.ToString() & " ms")

   ''  *******************************************

      app.Visible = True

      sht = Nothing
      ''wb.Close(False) '' leave Excel open to see results
      wb = Nothing
      ''app.Quit()
      app = Nothing
      Console.ReadLine() '' pause to show results

   End Sub

End Module

取而代之

Instead of

For rowindex = 1 To DataGridView1.RowCount
            For columnindex = 1 To DataGridView1.ColumnCount
                objWorkSheet1.Cells(rowindex + 4, columnindex + 0) = DataGridView1(columnindex - 1, rowindex - 1).Value
                objWorkSheet1.Cells(rowindex + 4, columnindex + 1) = DataGridView1(columnindex - 1, rowindex - 1).Value
                objWorkSheet1.Cells(rowindex + 4, columnindex + 2) = DataGridView1(columnindex - 1, rowindex - 1).Value
                objWorkSheet1.Cells(rowindex + 4, columnindex + 3) = DataGridView1(columnindex - 1, rowindex - 1).Value
                objWorkSheet1.Cells(rowindex + 4, columnindex + 4) = DataGridView1(columnindex - 1, rowindex - 1).Value
                objWorkSheet1.Cells(rowindex + 4, columnindex + 7) = DataGridView1(columnindex - 1, rowindex - 1).Value
                objWorkSheet1.Cells(rowindex + 4, columnindex + 8) = DataGridView1(columnindex - 1, rowindex - 1).Value
 
            Next
        Next

Just Do

Just Do

For rowindex = 1 To DataGridView1.RowCount
            For columnindex = 1 To DataGridView1.ColumnCount
                objWorkSheet1.Cells(rowindex + 4, columnindex + 0) = DataGridView1(columnindex - 1, rowindex - 1).Value
            Next
Next

这篇关于VBA中下一个循环的替代方案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！