Excel VBA编程指南：快速掌握数组技巧

Excel VBA编程指南：快速掌握数组技巧

引用

CSDN

等

9

来源

1.

https://blog.csdn.net/gogoel/article/details/138440907

2.

https://blog.csdn.net/Gordennizaicunzai/article/details/73702406

3.

https://blog.csdn.net/zhaotianyu950323/article/details/90725946

4.

https://blog.csdn.net/2401_83795651/article/details/136924560

5.

https://blog.csdn.net/miocaro507/article/details/137631344

6.

https://blog.csdn.net/m0_68120716/article/details/142672571

7.

https://learn.microsoft.com/zh-cn/office/vba/language/reference/user-interface-help/public-statement

8.

https://docs.pingcode.com/baike/4250265

9.

https://www.wdooc.com/archives/tag/javajichulianxi/

在Excel VBA编程中，数组是一种非常强大的工具，可以显著提高数据处理效率。通过学习和掌握VBA数组，你能够更高效地完成各种数据处理任务。本文将从基础到进阶，逐步介绍VBA数组的使用方法，并通过实际案例帮助你更好地理解其应用场景。

在Excel VBA中，数组是一种用于存储一系列数据的变量集合。与普通变量只能存储单个值不同，数组可以存储多个值，这使得它在处理大量数据时特别有用。通过数组，你可以一次性读取和处理大量数据，避免了频繁访问工作表的性能开销，从而显著提高代码执行效率。

1. 数组的基本概念

数组可以看作是一系列变量的集合，每个变量都有一个索引（下标）来标识其位置。数组的元素可以是任何数据类型，如整数、字符串、浮点数等。

• 一维数组：最简单的数组形式，可以看作是一行或一列数据。
• 二维数组：类似于表格结构，具有行和列的概念，通过两个索引访问元素。
• 多维数组：除了二维外，还可以有三维、四维等更高维度的数组。

2. 数组的声明与初始化

静态数组

静态数组在声明时就确定了大小，不能在运行时改变。

Dim arr(2) As Variant ' 定义一个包含3个元素的数组
arr(0) = "vba"
arr(1) = 100
arr(2) = 3.14
MsgBox arr(0) ' 输出: vba

动态数组

动态数组的大小可以在运行时动态调整，使用ReDim语句。

Dim arr() As Variant ' 声明动态数组
ReDim arr(1 To 5) ' 运行时分配大小
For i = 1 To 5
    arr(i) = i * 10
Next i
MsgBox arr(3) ' 输出: 30

3. 使用Array函数创建数组

Array函数可以快速创建并初始化数组。

Dim arr As Variant
arr = Array("张三", 100, "李四", 76)
MsgBox arr(0) ' 输出: 张三

4. 数组的基本操作

• 合并数组：使用Join函数

  Dim a As Variant
  a = Array("Red", "Blue", "Yellow")
  MsgBox Join(a, ",") ' 输出: Red,Blue,Yellow
  

• 拆分字符串为数组：使用Split函数

  Dim b As Variant
  b = Split("Red$Blue$Yellow", "$")
  For Each item In b
      MsgBox item
  Next item
  

• 筛选数组：使用Filter函数

  Dim arr As Variant
  arr = Array("ABC", "F", "D", "CA", "ER")
  Dim filteredArr As Variant
  filteredArr = Filter(arr, "A", True) ' 筛选含"A"的元素
  MsgBox Join(filteredArr, ",") ' 输出: ABC,CA
  

• 转置数组：使用Application.Transpose

  Dim arr As Variant
  arr = Array(10, "vba", 2, "b", 3)
  Dim transposedArr As Variant
  transposedArr = Application.Transpose(arr)
  MsgBox transposedArr(2, 1) ' 输出: vba
  

1. 处理单元格数据

将区域数据加载到数组中进行高效计算：

Sub calculateData()
    Dim arr As Variant
    arr = Range("A2:D5").Value ' 将区域数据读入数组
    
    Dim i As Integer
    For i = 1 To UBound(arr, 1)
        arr(i, 4) = arr(i, 2) * arr(i, 3) ' 计算并更新数组中的值
    Next i
    
    Range("A2:D5").Value = arr ' 将结果写回工作表
End Sub

2. 多维数组的应用

多维数组在处理复杂数据结构时非常有用。例如，你可以使用二维数组来存储和处理表格数据。

Dim matrix(1 To 5, 1 To 3) As Integer  ' 声明一个 5 行 3 列的整数数组
Debug.Print LBound(matrix, 1)  ' 输出第一维的下限（1）
Debug.Print UBound(matrix, 1)  ' 输出第一维的上限（5）
Debug.Print LBound(matrix, 2)  ' 输出第二维的下限（1）
Debug.Print UBound(matrix, 2)  ' 输出第二维的上限（3）

3. 嵌套数组

嵌套数组是将一个数组作为另一个数组的元素，形成多层次的数据结构。

Dim nestedArr(2) As Variant
nestedArr(0) = Array(1, 2, 3)
nestedArr(1) = Array("a", "b", "c")
nestedArr(2) = Array(True, False, True)

For Each subArr In nestedArr
    For Each item In subArr
        Debug.Print item
    Next item
Next subArr

假设需要统计销售数据中各产品的总销售额，可以通过以下步骤实现：

1. 读取数据到数组
   将销售数据从工作表读入数组，便于快速处理。

2. 遍历数组计算总额
   使用循环和条件判断累加每个产品的销售额。

3. 输出结果
   将计算结果写回工作表或显示在消息框中。

示例代码：

Sub totalSalesByProduct()
    Dim salesData As Variant
    salesData = Range("A2:C100").Value ' 假设数据范围为A2:C100，列分别为产品、数量、单价
    
    Dim productTotals As Scripting.Dictionary
    Set productTotals = New Scripting.Dictionary
    
    Dim i As Long
    For i = 1 To UBound(salesData, 1)
        Dim productName As String
        productName = salesData(i, 1)
        
        If Not productTotals.Exists(productName) Then
            productTotals.Add productName, 0
        End If
        
        productTotals(productName) = productTotals(productName) + (salesData(i, 2) * salesData(i, 3))
    Next i
    
    ' 输出结果
    Dim key As Variant
    For Each key In productTotals.Keys
        Debug.Print key & ": " & productTotals(key)
    Next key
End Sub

学习要点总结

1. 数组是存储多个值的变量集合，可以显著提高数据处理效率。
2. 静态数组大小固定，动态数组可以运行时调整。
3. 数组可以处理一维、二维甚至更高维度的数据。
4. 数组在数据处理中可以替代循环读取单元格，提高代码执行效率。

练习题

1. 基础练习：创建一个动态数组，存储1到10的平方值，并输出。

Dim arr() As Integer
ReDim arr(1 To 10)
For i = 1 To 10
    arr(i) = i * i
Next i
For i = 1 To 10
    Debug.Print arr(i)
Next i

2. 进阶练习：读取工作表中A1:B10区域的数据到数组，然后将数组中的所有数值乘以2，再写回工作表。

Dim arr As Variant
arr = Range("A1:B10").Value
For i = 1 To UBound(arr, 1)
    For j = 1 To UBound(arr, 2)
        arr(i, j) = arr(i, j) * 2
    Next j
Next i
Range("A1:B10").Value = arr

3. 高级挑战：给定两个数组A和B，计算所有元素相乘后的最小累加值。

Sub minProductSum()
    Dim A() As Integer
    Dim B() As Integer
    Dim C(100, 100) As Integer
    Dim D(100) As Integer
    Dim n As Integer
    Dim min As Integer
    Dim ans As Integer
    Dim x As Integer
    Dim y As Integer
    Dim a As Integer
    
    n = 5
    ReDim A(1 To n)
    ReDim B(1 To n)
    
    ' 初始化数组A和B
    A = Array(1, 8, -1, 4, -2)
    B = Array(0, 6, 1, -4, -1)
    
    ' 将两个数组相乘的结果存储到二维数组C中
    For x = 0 To n - 1
        For y = 0 To n - 1
            C(x, y) = A(x + 1) * B(y + 1)
        Next y
    Next x
    
    ' 递归计算最小累加值
    min = 0
    ans = 0
    For i = 0 To 100
        D(i) = -1
    Next i
    suanfa C, 0, min, ans, D
    
    MsgBox "最小累加值为：" & ans
End Sub

Function first(D() As Integer, a As Integer, step As Integer) As Boolean
    Dim i As Integer
    For i = 0 To step - 1
        If D(i) = a Then
            first = False
            Exit Function
        End If
    Next i
    first = True
End Function

Sub suanfa(C() As Integer, x As Integer, min As Integer, ByRef ans As Integer, D() As Integer)
    Dim y As Integer
    Dim a As Integer
    
    If x = n Then
        If ans > min Then
            ans = min
        End If
        Exit Sub
    End If
    
    For y = 0 To n - 1
        If first(D, y, x) Then
            min = min + C(x, y)
            a = 1
            D(x) = y
            suanfa C, x + 1, min, ans, D
            If a = 1 Then
                min = min - C(x, y)
            End If
        End If
    Next y
End Sub

通过以上练习，你可以进一步巩固VBA数组的相关知识，并尝试将其应用到实际工作中。数组是VBA编程中非常重要的工具，掌握它将帮助你更高效地处理数据，提升工作效率。