加速VBA代码以更快地运行 [英] Speeding up VBA Code to Run Faster

问题描述

我有一个Excel工作簿，用户在其中单击按钮即可导入文本文件.我的代码完全可以按我的要求工作，但是在填写H列读取日期"时，它的运行速度非常慢.将文本文件导入到Excel工作表后，这是我的Excel工作簿的外观:

I have an Excel Workbook where the user imports a text file by the click of a button. My code works exactly as I need it to but it is extremely slow when filling in column H, Reading Date. Here is what my Excel Workbook looks like when the text file has been imported to the excel sheet:

这是我的代码:

Sub Import_Textfiles()
Dim fName As String, LastRow As Integer

Worksheets("Data Importation Sheet").Activate

LastRow = Range("A" & Rows.Count).End(xlUp).Row + 1
    ' Finds the first blank row to import text file data to
fName = Application.GetOpenFilename("Text Files (*.txt), *.txt")

If fName = "False" Then Exit Sub

  With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fName, _
        Destination:=Range("A" & LastRow))
        .Name = "2001-02-27 14-48-00"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 2
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
        .TextFileFixedColumnWidths = Array(14, 14, 8, 16, 12, 14)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    ActiveWindow.SmallScroll Down:=0


    Dim strShortName As String


    'Adding Reading Date to Excel Sheet:
    Dim rowCount As Integer, currentRow As Integer
    Dim sourceCol As Integer, nextCol As Integer
    Dim currentRowValue As String
    Dim fileDate1 As String
    Dim fileDate2 As String

    sourceCol = 1 'columnA
    nextCol = 8 'column H
    rowCount = Cells(Rows.Count, sourceCol).End(xlUp).Row

    strShortName = fName
    fileDate1 = Mid(fName, InStrRev(fName, "\") + 1)
    fileDate2 = Left(fileDate1, 10)

    Cells(LastRow, 9) = ("Updating Location: " & strShortName)

    For currentRow = 1 To rowCount
        currentRowValue = Cells(currentRow, nextCol).Value
        If currentRowValue = "" Then
        Cells((currentRow), (nextCol)).Select
        Cells((currentRow), (nextCol)) = fileDate2
        End If
    Next

End Sub

如果有人对我如何加快阅读日期的输入有任何建议，我将不胜感激！预先感谢！

If anyone has any suggestions as to how I can speed up the importation of the reading date I would appreciate it greatly! Thanks in advance!

推荐答案

我注意到的几件事

1. 如Chris在评论中提到的那样，您可以关闭屏幕更新并将计算设置为手动，然后将其重新打开，然后在代码末尾将计算设置为自动.

例如

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

'
'~~> Rest of your code
'
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With

1. 避免使用.选择.它降低了代码的速度.您不需要选择要写入的单元格.

1. Avoid the use of .Select. it reduces the speed of the code. You do not need to select the cell to write to it.

您的 For 循环可以写为.

For currentRow = 1 To RowCount
    If Cells(currentRow, nextCol).Value = "" Then
        Cells(currentRow, nextCol).Value = fileDate2
    End If
Next

它本身可以提高代码的速度，因为您在写入之前不再选择单元格.

This it self will increase the speed of your code as you are not selecting the cell anymore before writing to it.

1. 理想情况下，我会将范围复制到数组中，然后对数组进行操作，然后将其写回到单元格中，但这就是我.

1. Ideally I would copy the range to an array and then do what you are doing with the array and then write it back to the cell but then that is me.

删除不必要的代码行. ActiveWindow.SmallScroll Down:= 0 不需要.

Remove unnecessary lines of code. ActiveWindow.SmallScroll Down:=0 is not needed.

使用对象并完全限定您的对象.

Work with object(s) and fully qualify your object(s).

在处理Excel行时，使用 Long 代替 Integer

When working with Excel rows, use Long instead of Integer

这篇关于加速VBA代码以更快地运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！