Excel VBA 范围内的长度 1

发布于 2024-11-08 02:42:36 字数 820 浏览 7 评论 0原文

在很长一段时间没有需要之后，我最近开始从事 Excel 宏开发。

我有一列有 200 行，每行都有一个值。我编写了一个循环来迭代每个行值，读取当前值，然后写回减去最后一个字符的值。

这是我编写的一些实际（和伪）代码。

Dim theRow as Long
Dim totRow as Long
Dim fooStr as String


theRow = 2 'we begin on the second row of the colummn
totRow = 201 'there are 200 values


For theRow = 2 to totRow
    fooStr = WorkSheets(DestSheet).Cells(theRow,"A").Formula 'read the cell value
    fooStr = Left(fooStr,Len(fooStr)-1 'subtract the last character from the value
    Cells(theRow,1).Value = fooStr 'write the value back
Next theRow

在阅读了一些内容之后，我了解到使用范围读取和写入值是最佳实践。是否可以使用 Range 重写我正在做的事情，这样它会运行得更快。

这是我到目前为止想到的。

Range("A2:A201").Value = Len(Range.Left("A2:A201").Value)-1

然而，这不起作用。

如果这确实可能的话，有关于如何做到这一点的任何线索吗？

感谢您的任何提示。

原文

I recently got into Excel macro development after a long time of not having the need to.

I have one column with two-hundred rows where each row has a value. I wrote a loop to iterate to each row value, read the current value and then write the value back minus the last character.

Here is some actual (and pseudo) code of what I wrote.

Dim theRow as Long
Dim totRow as Long
Dim fooStr as String


theRow = 2 'we begin on the second row of the colummn
totRow = 201 'there are 200 values


For theRow = 2 to totRow
    fooStr = WorkSheets(DestSheet).Cells(theRow,"A").Formula 'read the cell value
    fooStr = Left(fooStr,Len(fooStr)-1 'subtract the last character from the value
    Cells(theRow,1).Value = fooStr 'write the value back
Next theRow

After I did some reading I learned that it is best practice to read and write values using a Range. Is it possible to rewrite what I am doing using a Range so it willl go faster.

Here is what I came up with so far.

Range("A2:A201").Value = Len(Range.Left("A2:A201").Value)-1

However, this doesn't work.

Any clues on how to do this if this is indeed possible?

Thanks for any tips.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一绘本一梦想 2024-11-15 02:42:36

如果您想要最大的性能（您不需要 200 行，但是......）您必须最大限度地减少对范围的读取和写入（主要是写入）数量。这意味着将整个范围读入数组，操作该数组，然后将其写回到该范围。与循环中的 200 次读取相比，这只是一次读取和一次写入。这是一个例子。

Sub RemoveLastChar()

    Dim vaValues As Variant
    Dim i As Long

    vaValues = Sheet1.Range("A2").Resize(200).Value

    For i = LBound(vaValues, 1) To UBound(vaValues, 1)
        vaValues(i, 1) = Left$(vaValues(i, 1), Len(vaValues(i, 1)) - 1)
    Next i

    Sheet1.Range("A2").Resize(UBound(vaValues, 1), UBound(vaValues, 2)).Value = vaValues

End Sub

If you want maximum performance (you don't need it for 200 rows, but...) you have to minimize the number of reads and writes (mostly writes) to ranges. That means reading the whole range into an array, manipulating the array, then writing it back to the range. That's one read and one write compared to 200 in a loop. Here's an example.

Sub RemoveLastChar()

    Dim vaValues As Variant
    Dim i As Long

    vaValues = Sheet1.Range("A2").Resize(200).Value

    For i = LBound(vaValues, 1) To UBound(vaValues, 1)
        vaValues(i, 1) = Left$(vaValues(i, 1), Len(vaValues(i, 1)) - 1)
    Next i

    Sheet1.Range("A2").Resize(UBound(vaValues, 1), UBound(vaValues, 2)).Value = vaValues

End Sub

回复收藏 0 原文

池木 2024-11-15 02:42:36

您可以做类似“

 Sub StringTrim()
    Dim xCell as Range 
    Range("A1:A201").Select

    For Each xCell in Selection
       xCell.Value = Left(xCell.Value, Len(xCell.Value) - 1)
    Next

 End Sub

我不知道您正在寻求什么样的速度改进”之类的事情，但这也可以完成工作。

您可能已经知道这一点，但将 Application.ScreenUpdating = False 放在代码顶部可以显着加快速度（除非您喜欢在脚本运行时观看所有内容）。您应该在代码末尾将值重置为 True。

You could do something like

 Sub StringTrim()
    Dim xCell as Range 
    Range("A1:A201").Select

    For Each xCell in Selection
       xCell.Value = Left(xCell.Value, Len(xCell.Value) - 1)
    Next

 End Sub

I don't know what kind of speed improvements you are seeking, but that would also do the job.

You might know this already but putting Application.ScreenUpdating = False at the top of your code can speed it up significantly (unless you like to watch everything flash by as the script works). You should reset the value to True at the end of your code.

回复收藏 0 原文

~没有更多了~