强制创建和“循环"执行单元素数组?(VBA) [英] Force the creation and "looping" of single element arrays? (VBA)

问题描述

我希望找到一种压缩的数组处理解决方案，而不必重复处理大部分代码以仅处理1个元素的列表.(0元素不是一个大问题，因为它不需要执行)

I hope to find a compressed solution to array handling over having to duplicate large sections of code only to handle lists of 1 element. (0 element is not a big problem because that needs no execution)

如果我创建

testRange As Variant 
testRange = .ListColumns(2).DataBodyRange.Value2 'Case only 1 row in list
IsArray(testRange) 'FALSE!

我尝试过

testRange() As Variant
testRange() As String
...

如果仅馈送了1个元素，是否有一种方法也可以强制VBA创建Array?在那之后，有一种方法可以实现无错误使用:

Is there a way to force VBA to create Array also if only 1 elements are fed? After that is there a way to error-free use either:

For i = LBound(testRange) To UBound(testRange)

或

For each i in testRange

有了1个成员数组，仅出于1个成员列表的目的而没有编写特定的异常?

With that 1 member Array, without writing a specific exception only for the sake of 1 member lists?

如果没有，我可能会返回好旧"方法:

If none i might return to "good old" method:

For each cell in .ListColumns(2).DataBodyRange  

唯一的缺点是执行速度较慢，对吧?

The only drawback to this is a bit slower execution, right?

@CallumDA的好主意
我需要更多提示，以更好地理解它并使它起作用.我已经测试过:

Nice ideas from @CallumDA
I need some more hints to understand this better, and make it work. I have tested with:

Public Sub Test2()
    Dim testRange As Variant
    With Sheet1.ListObjects(1).ListColumns(2).DataBodyRange
        testRange = IIf(.Count = 1, Array(.Value2), .Value2)
    End With
    For i = (LBound(testRange)) To (UBound(testRange))
        Debug.Print i & " : " ' & testRange(i, 1)
    Next
End Sub

为什么要这样，当有1个元素时， i 以0开头，但是当有多个元素时， i 以1开头?

Why is it so that when there is 1 element, i starts with 0, BUT when there are multiple elements, i starts with 1 ?

为什么会这样， testRange(i，1)给出超出范围的脚本"错误?(如果是1元素方案)我应该如何测试在两种情况下都可以接受的值?

Also why is it so, that testRange(i, 1) gives a "Suscript out of range" error? (in case of the 1 element scenario) How should I test the values, that is accepted in both cases?

旁注:我已经在此数组方法和每个元素"方法之间进行了性能比较，以查看是否值得进行所有工作.如果有3000个元素表列表，则差异为2毫秒与10毫秒(所以就乘数而言，这确实是一个很大的差异，但幸运的是，对于我的情况，速度较慢也足够快.)

Sidenote: I have made a performance comparison between this array approach and the "each elements" approach to see if it is worth the effort at all. In case of a 3000 element table list the difference was 2ms vs. 10ms (So it really is a big difference in terms of multiplier, but luckily also the slower is fast enough for my scenario.)

For i = (LBound(testRange)) To (UBound(testRange))
         If testRange(i, 1) = "kaslkfjghh" Then 
VS.
For Each cell In Sheet1.ListObjects(2).ListColumns(1).DataBodyRange
         If cell.Value2 = "kaslkfjghh" Then 

推荐答案

您可以使用 Array 将元素强制为数组.如果您的 ListColumn 中只有一个元素，请使用以下命令:

You can use Array to force the element into an array. If there's only ever one element in your ListColumn then use this:

Public Sub Test()
    Dim testRange As Variant
    testRange = Array(Sheet1.ListObjects(1).ListColumns(2).DataBodyRange.Value2)
    Debug.Print IsArray(testRange)
End Sub

如果有时您的 ListColumn 中有多个，则使用类似以下的方法:

If you sometimes have more than one in your ListColumn then use something like this instead:

Public Sub Test()
    Dim testRange As Variant
    With Sheet1.ListObjects(1).ListColumns(2).DataBodyRange
        testRange = IIf(.Count = 1, Array(.Value2), .Value2)
    End With
    Debug.Print IsArray(testRange)
End Sub

这篇关于强制创建和“循环"执行单元素数组?(VBA)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！