加快excel格式化vba代码？ [英] Speed up excel formatting vba code?

问题描述

我使用以下vba代码将文本字符串日期更改为Excel中的实际日期，以便我可以将其用于逻辑比较等。

I am using the following vba code to change a text string date into an actual date in excel so I can use it for logical comparisons and the like.

问题是我需要这个工作大约4000行，每周更新，这段代码很慢。

The problem is I need this to work for around 4000 rows and update it weekly, and this code is very slow.

Sub Datechange()

Dim c As Range
    For Each c In Range("D2:D" & Range("D" & Rows.Count).End(xlUp).Row)
        c.Value = CDate(c.Value)
    Next c

End Sub

有什么替代方法我可以做同样的事情更快吗？我承担部分原因是因为选择单个单元格和处理代码过多涉及的开销，但我不知道如何以其他方式进行操作？

Are there any alternative ways I could do the same thing quicker? I am assuming part of the reason it is so slow is because there are overheads involved with selecting single cells and processing the code over and over but I am not sure how to do it any other way?

底部的一些行包含单词无指定，当代码到达这些单元格时，它将与

Also some of the rows at the bottom contain the words "None Specified" and when the code reaches these cells it breaks with

运行时错误'13'：类型不匹配

Run-time error '13': Type mismatch

有没有办法阻止这种情况发生，所以以下代码可以完成

Is there a way to stop this happening so the following code can complete?

推荐答案

第一步将是：

• 关闭屏幕更新


• 关闭计算


• 一次读写范围

它可能看起来像下面的代码 - 这是一个好主意，包括一个错误处理程序，以避免离开你的电子表格关闭屏幕更新或更改计算模式：

It could look like the code below - it is a good idea to include an error handler to avoid leaving your spreadsheet with screen updates off or with the calculation mode changed:

Sub Datechange()

    On Error GoTo error_handler

    Dim initialMode As Long

    initialMode = Application.Calculation 'save calculation mode
    Application.Calculation = xlCalculationManual 'turn calculation to manual
    Application.ScreenUpdating = False 'turn off screen updating

    Dim data As Variant
    Dim i As Long

    'copy range to an array
    data = Range("D2:D" & Range("D" & Rows.Count).End(xlUp).Row)

    For i = LBound(data, 1) To UBound(data, 1)
        'modify the array if the value looks like a date, else skip it
        If IsDate(data(i, 1)) Then data(i, 1) = CDate(data(i, 1))
    Next i

    'copy array back to range
    Range("D2:D" & Range("D" & Rows.Count).End(xlUp).Row) = data

exit_door:
    Application.ScreenUpdating = True 'turn screen updating on
    Application.Calculation = initialMode 'restore original calculation mode

    Exit Sub

error_handler:
    'if there is an error, let the user know
    MsgBox "Error encountered on line " & i + 1 & ": " & Err.Description
    Resume exit_door 'don't forget the exit door to restore the calculation mode
End Sub

这篇关于加快excel格式化vba代码？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！