数组,由于其在内存中运行,速度快,特别适用于处理大量数据的计算。它的应用范围也非常之广,我们分享过的案例中,绝大多数都会用到数组。可以毫不夸张地说,掌握了数组,就好比练武之人打通了仁督二脉,功力瞬间提高几个层次。在上期我们定义的数组是没有指定变量类型的,如果指定数组的值的类型,那么它的元素就只限输入、输出该种类型数据,比如:Dim arr1(5) as integer
Dim arr2() as string
Dim arr() As Range
第一种,定义了数组的元素是整型数值,只能授受整型数值的赋值,如果不是整型,会自动转换成整型;它也能接受数字形式的字符串,转换成整型;日期会转换成日期序列值;其他非数字类型会报错,“类型不匹配”。
第二种,定义了数组的元素是字符型,它可以授受字符串,数字,日期等,如果不是字符串,会自动转换成字符串。第三种,定义了数组的元素是Range,这种情况目前我们碰到的比较少。为了演示,我们写了下面的代码:
Sub RangeInArray()
Dim arr() As Range
Dim ws As Worksheet
Set ws = Sheets("Sheet1")
ReDim arr(1 To 15, 1 To 15)
With ws
.Activate
For i = 1 To 15
For j = 1 To 15
Set arr(i, j) = Cells(i, j)
If i = j Then
arr(i, j).Interior.Color = vbBlue
If i Mod 5 = 0 Then
Application.Wait Now + TimeSerial(0, 0, 1)
End If
End If
Next
Next
For i = 1 To 15
For j = 1 To 15
If i = j Then
arr(i, j).Interior.Color = vbRed
If i Mod 5 = 0 Then
Application.Wait Now + TimeSerial(0, 0, 1)
End If
End If
Next
Next
End With
End Sub
给对象赋值,我们用SET语句,这里给Range对象赋值,把每一个单元格赋值给arr:
Set arr(i, j) = Cells(i, j)
arr(i, j).Interior.Color = vbBlue
这种方式好像还没有什么实际应用的案例,暂时先了解一下吧。Dim arr()
arr = Array(Array(1, 2, 3), Array("A", "B", "C"))
Sub loopArr()
Dim ws As Worksheet
Set ws = Sheet2
Dim arr()
arr = Array(Array(1, 2, 3), Array("A", "B", "C"))
For i = 0 To 1
For j = 0 To 2
ws.Cells(i + 1, j + 1) = arr(i)(j)
Next
Next
End Sub
Sub loopArr1()
Dim ws As Worksheet
Set ws = Sheet2
Dim arr()
arr = Array(Array(1, 2, 3), Array("A", "B", "C"))
For i = 0 To 1
ws.Cells(i + 1, 4).Resize(1, UBound(arr(i)) + 1) = arr(i)
Next
End Sub
Dim arr()
Dim str As String
arr = Array("A", "B", "C")
str = Join(arr, ",")
'str="A,B,C"
2、SPLIT,把一个字符串按照指定的字符分列为一个数组
Dim arr() As String
Dim str As String
str = "A,B,C"
arr = Split(str, ",")
3、TRANSPOSE,这是一个工作表函数,转置数组,即把数组元素进行行、列交换,这个昨天讲过。
4、MATCH,这也是一个工作表函数,可用于查找数组元素位置,与我们的自定义函数Pxy功能相同。
i = Application.WorksheetFunction.Match("A", arr, 0)
其实,很久以前我自定义Pxy函数就采用的这种方法:Function Pxy(arr(), Field As String)
Pxy = Application.WorksheetFunction.Match(Field, arr, 0)
End Function
三、数组在用户窗体控件中的应用,昨天没有讲完,今天就再补充一些吧。1、把数组赋值给复合框的List
Dim arr()
arr = Array(1, 2, 3, 4, 5, 6)
Me.ComboBox1.List = arr
或者循环数组逐个添加
Dim arr()
arr = Array(1, 2, 3, 4, 5, 6)
For i = LBound(arr) To UBound(arr)
Me.ComboBox1.AddItem arr(i)
Next
2、把数组赋值给ListView
Dim arr(), arrTitle(), Item As ListItem
arr = Array(1, 2, 3, 4, 5, 6)
arrTitle = Array("一", "二", "三", "四", "五", "六")
With Me.ListView1
.View = lvwReport
For i = LBound(arr) To UBound(arr)
.ColumnHeaders.Add , , arrTitle(i),30
Next
Set Item = .ListItems.Add
Item.Text = arr(0)
For i = 1 To UBound(arr)
Item.SubItems(i) = arr(i)
Next
End With
这里我们就简单处理,详细的参数设置可以看前期的案例分享。
3、把数组赋值给ListBox,假设我们给ListBox设置了两栏
For i = LBound(arr) To UBound(arr)
With Me.ListBox1
.AddItem
.List(i, 0) = arrTitle(i)
.List(i, 1) = arr(i)
End With
Next
4、把数组赋值给TreeView
Dim Nodx As Node
Set Nodx = Me.TreeView1.Nodes.Add(, , "A", "列表")
For i = LBound(arr) To UBound(arr)
If i = 0 Then
Set Nodx = Me.TreeView1.Nodes.Add("A", tvwChild, "A" & i, arrTitle(i))
Else
Set Nodx = Me.TreeView1.Nodes.Add("A" & i - 1, tvwChild, "A" & i, arrTitle(i))
End If
Next
哎呀,是不是有点跑题啦?
四、跟数组有关的几个自定义过程、函数(在前期案例中均分享过)
1、数组排序
数组排序的方法有好多种,我们日常使用都无所谓,掌握一种可以使用的方法就行,需要排序的时候,拿来就用。我们分享过的案例中,有不少涉及到数组的排序。我们再重温一下:
Sub SortArray(ByRef arr() As Variant)
Dim temp As Variant
For i = LBound(arr) To UBound(arr)
For j = i + 1 To UBound(arr)
If arr(j) < arr(i) Then
temp = arr(i)
arr(i) = arr(j)
arr(j) = temp
End If
Next
Next
End Sub
2、数组字段定位
(1)一维数组元素定位:
Function Pxy(arr() As Variant, searchValue As Variant) As Long
t = LBound(arr)
t = 1 - t
For i = LBound(arr) To UBound(arr)
If arr(i) = searchValue Then
Pxy = i + t
Exit Function
End If
Next
Pxy = 0 ' 如果未找到值,则返回 0
End Function
第10行,在以前的案例中是“Pxy = -1”,前两天在做案例【Excel VBA【完整代码】销货小票批量打印/电商小工具【代码更正与优化】】时用到它,仔细考虑以后,认为如果没有定位到应该是为0才对,在VBA中,0表示假,-1表示真。
(2)二维数组元素定位:
这个有点复杂,参数1是目标数组,参数2是定位字段,参数3是定位的方向,默认为0,表示一维数组,无所谓方向;取值为1,表示二维数组按行查找;取值为2,表示二维数组按列查找。
Function Pxy(arr(), FieldName As String, Optional arrType As Integer = 0)
Dim k$, t$
k = 0
t = 0
Select Case arrType
Case Is = 0
For i = LBound(arr) To UBound(arr)
k = k + 1
If arr(i) = FieldName Then
t = 1
Exit For
End If
Next
Case Is = 1
For i = LBound(arr, 1) To UBound(arr, 1)
k = k + 1
If arr(i, 1) = FieldName Then
t = 1
Exit For
End If
Next
Case Is = 2
For i = LBound(arr, 2) To UBound(arr, 2)
k = k + 1
If arr(1, i) = FieldName Then
t = 1
Exit For
End If
Next
End Select
If t = 1 Then
Pxy = k
Else
Pxy = 0
End If
End Function
(3)二维数组转一维数组:
Function FlattenArray(arr As Variant) As Variant
' 将二维数组转换成一维数组
Dim iCol As Integer, iRow As Integer
Dim FlattenedArr(), Lbnd As Integer
iRow = UBound(arr, 1)
iCol = UBound(arr, 2)
Lbnd = LBound(arr, 1)
For i = Lbnd To iRow
For j = Lbnd To iCol
ReDim Preserve FlattenedArr(k)
FlattenedArr(k) = arr(i, j)
k = k + 1
Next
Next
FlattenArray = FlattenedArr
End Function
昨天觉得写得差不多了,今天不知不觉又写了这么多,数组的内容真是多啊。要提高VBA编程水平,一定要掌握数组。
好,今天就到这吧。