最有效的添加格式Excel的方法-VBA [英] Most Efficent Way to Add Formatting Excel - VBA

问题描述

我有一个宏，可以将数百行数据添加到Excel电子表格中.我从循环中调用过程，该循环插入数据的每一行.每次插入该数据时，我一直在对该行应用格式.但是，在测试过程中，我发现，如果我不逐行应用格式化，而是一次全部应用，则可以更快地插入所有数据，大约为3/4秒(3.3秒与4.11秒).我要克服的问题是，并非每一行都具有相同的格式.但是，有一种可预测的模式.两行采用一种格式，另一行采用不同格式.有没有一种方法可以不循环地同时应用这两种不同的格式，这将使我能够保持所获得的性能提升(用户希望看到2秒以内的响应，因此这可能是一个很大的收益).

我当前正在使用以下代码(在此期间，所有应用程序设置(例如屏幕更新，计算和事件均已关闭)

Private Sub BuildTerminalSummary(ByRef terminals, ByVal timeFrame)
    Dim terminal As clsTerminal
    Dim curSheet As Worksheet
    Dim breakLoop As Boolean
    Dim terminalCode As String
    Dim rowNumber As Long

    Set terminal = New clsTerminal
    Set curSheet = Sheets("Terminal Summary")

    rowNumber = 7

    'Remove all content, borders, and tint
    ClearPage curSheet, rowNumber

    For Each terminal In terminals         
        AddDetailData curSheet, terminal.InfoArray, rowNumber
        AddDetailData curSheet, terminal.PriorInfoArray, rowNumber + 1
        AddDiffPercentFormulas curSheet, terminal.DiffPercentInfoArray, rowNumber + 2

        rowNumber = rowNumber + 2

    Next terminal

    'Make sure the columns are wide enough to display the numbers
    curSheet.Cells.EntireColumn.AutoFit

End Sub

Private Sub AddDetailData(ByRef curSheet, ByRef data, ByVal rowNumber)
    With curSheet
        With .Cells(rowNumber, 3).Resize(1, 16)
            .value = data
            .Style = "Comma"
            .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
        End With
        'This overides the formatting in the revenue columns with currency instead of comma style
        With .Cells(rowNumber, 5).Resize(1, 2)
            .Style = "Currency"
            .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
        End With
        With .Cells(rowNumber, 13).Resize(1, 6)
            .Style = "Currency"
        End With
    End With

End Sub

Private Sub AddDiffPercentFormulas(ByRef curSheet, ByRef data, ByVal rowNumber)
    With curSheet.Cells(rowNumber, 3).Resize(1, 16)
        .value = data
        .NumberFormat = "0.00%"
    End With

End Sub

解决方案

您有两种格式:一种模式格式化2行，另一种模式格式化1行.我称这种2行和1行格式.

您可以将2单元格格式应用于整个列/整个数据区域，然后仅遍历1单元格格式.

I have a macro that add hundreds of lines of data to an excel spreadsheet. I call a procedure from a loop which inserts each line of data. I have been applying the formatting for the row each time that I insert that data. However, during my testing I have found that I can insert all of the data about 3/4 of second faster (3.3 sec vs. 4.11 sec) when I don’t apply the formatting line by line but all at once. The issue that I am trying to overcome is that not every row has the same formatting; however, there is a predictable pattern. Two rows of one formatting and one row of different formatting. Is there a way without looping to apply these two different formats all at one that would allow me to keep the performance gains that I am getting (users would like to see a sub 2 second response so this could be a big gain).

I am currently using the following code (application settings such as screenupdating, calculations, and events are all turned off during this)

Private Sub BuildTerminalSummary(ByRef terminals, ByVal timeFrame)
    Dim terminal As clsTerminal
    Dim curSheet As Worksheet
    Dim breakLoop As Boolean
    Dim terminalCode As String
    Dim rowNumber As Long

    Set terminal = New clsTerminal
    Set curSheet = Sheets("Terminal Summary")

    rowNumber = 7

    'Remove all content, borders, and tint
    ClearPage curSheet, rowNumber

    For Each terminal In terminals         
        AddDetailData curSheet, terminal.InfoArray, rowNumber
        AddDetailData curSheet, terminal.PriorInfoArray, rowNumber + 1
        AddDiffPercentFormulas curSheet, terminal.DiffPercentInfoArray, rowNumber + 2

        rowNumber = rowNumber + 2

    Next terminal

    'Make sure the columns are wide enough to display the numbers
    curSheet.Cells.EntireColumn.AutoFit

End Sub

Private Sub AddDetailData(ByRef curSheet, ByRef data, ByVal rowNumber)
    With curSheet
        With .Cells(rowNumber, 3).Resize(1, 16)
            .value = data
            .Style = "Comma"
            .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
        End With
        'This overides the formatting in the revenue columns with currency instead of comma style
        With .Cells(rowNumber, 5).Resize(1, 2)
            .Style = "Currency"
            .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
        End With
        With .Cells(rowNumber, 13).Resize(1, 6)
            .Style = "Currency"
        End With
    End With

End Sub

Private Sub AddDiffPercentFormulas(ByRef curSheet, ByRef data, ByVal rowNumber)
    With curSheet.Cells(rowNumber, 3).Resize(1, 16)
        .value = data
        .NumberFormat = "0.00%"
    End With

End Sub

解决方案

You have two kinds of formatting 2 rows in one pattern and 1 row in another pattern. I'm calling this 2row and 1row formatting.

You could apply 2cell formatting to the entire column / entire data area, and then loop through only 1cell formatting.

这篇关于最有效的添加格式Excel的方法-VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！