Excel中/ VBA如何分配数组的可变长度整型变量 [英] excel/VBA how to assign the variable length of an array to integer variable

问题描述

在Excel / VBA我有这个code

In excel/VBA I have this code

Dim colName as Variant
Dim lengthToDispl as Integer
colName = Array("A", "B", "C")
lengthToDispl = colName.length

我得到以下错误需要的对象。它涉及到最后一行。

I get the following error 'Object Required'. It relates to the last line.

我如何变长（包含字符串）数组的长度赋值给一个变量（整数）？

How do I assign the length of an array of variable length (containing strings) to a variable (integer)?

推荐答案

要返回数组的'大小'的功能是的 UBound函数它返回的上限即可。这往往与其配对使用时， LBound函数它返回的下边界

The function you want to return the 'size' of the array is the UBound function which returns the Upper Boundary. This is often used with its counterpart, the LBound function which returns the Lower Boundary.

这可能会或可能不会被返回你正在寻找的数目。有些阵列布置成带有一个从零开始的索引，部分基于一个索引。这取决于他们的声明和分配的方式。

This may or may not be returning the number you are looking for. Some arrays are arranged with a zero-based index and some have a one-based index. It depends on the way that they are declared and assigned.

colName = Array("A", "B", "C")

上面创建了一个从零开始的索引的数组，并在适当的位置三要素 COLNAME（0）， COLNAME（1）和 COLNAME（2）。该UBound函数（例如 UBound函数（COLNAME））将返回2，不要3.循环通过它，同时使用LBOUND和UBound函数。

The above creates an array with a zero-based index and three elements in position colName(0), colName(1) and colName(2). The UBound (e.g. UBound(colName)) will return 2, not 3. To cycle through it, use both LBound and UBound.

for i = LBound(colName) to UBound(colName)
    debug.print colName(i)
next i

当分配从工作表的单元格的值，你会得到一个基于一个，两个标注尺寸阵列，即使你只从单一的列或行收集的值。其他尺寸或浏览量简直就是 1对1

When assigning values from a worksheet's cells, you get a one-based, two dimensioned array, even if you are only collecting the values from a single column or row. The other dimension or Rank is simply 1 to 1.

colName = Range("A1:C2").Value2

这将创建一个基于一个索引两尺寸阵列在使用ReDim COLNAME（1〜2个，1〜3）。

This creates a two-dimensioned array with a one-based index as in ReDim colName (1 to 2, 1 to 3).

debug.print LBound(colName, 1) & " to " & UBound(colName, 1)
debug.print LBound(colName, 2) & " to " & UBound(colName, 2)

for i = LBound(colName, 1) to UBound(colName, 1)
    for j = LBound(colName, 2) to UBound(colName, 2)
        debug.print colName(i, j)
    next j
next i

我建议同时使用 LBOUND 和 UBound函数与数组元素的时候。

这篇关于Excel中/ VBA如何分配数组的可变长度整型变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！