【VBA】数组介绍

vba数组详解

数组创建

Dim arr ()     创建一个动态变量数组,不受长度/数据类型受制
Dim arr(5)  as string    创建一个一维数组,下标从0开始,最大下标值为5
Dim arr(3,3) as string   创建一个二维数组,开始arr(0,0),最后一个arr(3,3)

数组的赋值

1.通过Array函数赋值

Dim arr()
arr=Array("red","yellow","blue","black")

2.通过for循环赋值

Dim arr(3) 
dim i as integer
for i=0 to ubound(arr)
	arr(i)=i
next i

3.通过Range赋值
注意此赋值方法数组定义的时候一定要定义变体类型,不要指定数据类型

Dim arr as Variant
arr=Range("A1:C13").value

数组的常用方法

Array函数

Array函数允许你在代码执行中间创建一个数组，而不必事先确定其大小。该函数总是返回一个Varant数组。

Dim arr()
arr=Array("red","yellow","blue","black")

IsArray函数

IsArray()函数返回一个布尔值，指示指定的输入变量是否是数组变量。

Dim arr()
arr=Array("red","yellow","blue","black")
msgbox("the arr is array:" & IsArray(arr))

Erase函数

Erase()函数⽤于重置固定⼤⼩数组的值并释放动态数组的内存。它的⾏为取决于数组的类型。
1.固定数值数组，数组中的每个元素重置了零。
2.固定字符串数组，数组中的每个元素都被重置了零长度 " ".
3.对象数组，数组中的每个元素重置了特殊值无。

Private Sub Constant_demo()
  Dim NumArray(3)
  NumArray(0) = "VBScript"
  NumArray(1) = 1.05
  NumArray(2) = 25
  NumArray(3) = #23/04/2013#
  
  Dim DynamicArray()
  ReDim DynamicArray(9)   ' Allocate storage space.
  
  Erase NumArray          ' Each element is reinitialized.
  Erase DynamicArray      ' Free memory used by array.
  
  ' All values would be erased.
  msgbox("The value at Zeroth index of NumArray is " & NumArray(0))
  msgbox("The value at First index of NumArray is " & NumArray(1))
  msgbox("The value at Second index of NumArray is " & NumArray(2))
  msgbox("The value at Third index of NumArray is " & NumArray(3))
End Sub

redim 函数

重新定义数组的大小
Redim后的数组,其中数据都会被清空,而数组的大小和维度都可以被改变

dim arr(3) as string
arr=Array("red","yellow","blue","black")
Redim arr(9)

LBound函数和UBound函数

LBound函数和UBound函数分别返回表明数组的下界和上界的数字。
LBound(ArrayName[,dimension]) &Ubound同上
1.ArrayName - 必需的参数。该参数对应于数组的名称。
2.Dimension - 一个可选参数(默认为1)。 这需要一个与数组的维度相对应的整数值。如果是“1”，则返回第一维的下界; 如果是“2”，则返回第二维的下界，依此类推。

dim arr(3) as string
arr=Array("red","yellow","blue","black")
msgbox(arr(2,1))

如何遍历数组

1.for循环

dim arr(3) as string,i as Integer
arr=Array("red","yellow","blue","black")
for i=lbound(arr) to ubound(arr)
  msgbox(arr(i))
 next i

2.foreach循环

dim arr(3) as string,i as String
arr=Array("red","yellow","blue","black")
for each i in arr
	msgbox(i)
next i