vba - Excel VBA - 寻找简化循环的方法

Question

我最近做了一个循环，它在每个单元格中获取字符串，在字符串中搜索一个“_”，如果有一个则切断该位及其后面的任何字符。看着代码，我意识到它可能太复杂了，可以缩短或简化，但我不太确定该怎么做。有没有办法让这段代码更有效率？

Sub Name_Change()

Sheets("Sheet1").Activate

Dim tg_row As Integer
tg_row = 1

For Each nm_cl In Range("Table1[Name]")
    If InStr(1, nm_cl, "_", vbTextCompare) = 0 Then
        Range("Table1[Name]").Cells(tg_row, 1).Value = nm_cl.Value
    Else
        Range("Table1[Name]").Cells(tg_row, 1) = _
                Left(nm_cl, InStr(1, nm_cl, "_", vbTextCompare) - 1)
    End If
    tg_row = tg_row + 1
Next nm_cl

End Sub

谢谢您的帮助!

Answer 1

对此进行优化的第一次尝试是注意您正在调用 InStr。多次。您可以通过计算一次并存储结果来加快速度。

除此之外，我会注意到大概 Range("Table1[Name]")只有一列(否则您将用其他列的数据覆盖第一列)。因此，您可以替换 Range("Table1[Name]").Cells(tg_row, 1)与 nm_cl .在此过程中，我们注意到 nm_cl.Value = nm_cl.Value 的冗余语句可以删除。这导致以下代码:

Sub Name_Change()

Sheets("Sheet1").Activate

Dim index As Long

For Each nm_cl In Range("Table1[Name]")
    index = InStr(1, nm_cl, "_", vbTextCompare)
    If index <> 0 Then
        nm_cl = Left(nm_cl, index - 1)
    End If
Next nm_cl

End Sub

如果您需要更高的效率，除此之外，您可以使用以下方法将数据加载到变体中

dim data as Variant
data = Range("Table1[Name]").Value

在 VBA 中处理所有数据，然后使用

Range("Table1[Name]").Value = data

这将提高您的速度，因为在 Excel 和 VBA 之间传输数据很慢，这意味着您将有 1 次读取和 1 次写入，而不是每行 1 次读取和 1 次写入，但它需要(轻微)重写您的算法为使用变体中的数组的语法与使用范围不同。请注意，如果超出 65536 行，这将不起作用。我相信这是 Excel 2003 及更早版本的遗留约束。