【原创】VBA学习笔记（300）VBA 很多工作表函数都只对1维数组有用，用2维数组上经常报错！

1 VBA 很多工作表函数，都只对一维数组有用，用2维数组上经常报错

• 很多工作表函数都不能对二维数组生效
• 有时候连错误值都不返回，直接代码中断）

 

2 举例1：join() 和 split() 函数 只对一维数组生效

2.1比如 join()  和 split() 函数，只适合一维数组

• 值得注意的是：split和join只能对一维数组进行操作

Sub try001()
str1 = "1,2,3,4,5,6,7"
arr1 = Split(str1, ",")

For Each I In arr1
    Debug.Print I;
Next
Debug.Print

str2 = Join(arr1, "-")
Debug.Print str2

End Sub

 

2.2 join() 直接用于2维数组会报错，如果一定要使用，则需要先把二维数组转为一维数组

Sub try002()


'二维数组，静态redim
Dim arr3()
ReDim arr3(1 To 3, 1 To 3)
For I = 1 To 3
   For J = 1 To 3
      arr3(I, J) = (I + J) * J
   Next
Next
Debug.Print


For I = LBound(arr3) To UBound(arr3)
    For J = LBound(arr3, 2) To UBound(arr3, 2)
    Debug.Print arr3(I, J);
    Next
    Debug.Print
Next
Debug.Print

'这样会报错
'Str3 = Join(arr3, "-")
'Debug.Print Str3
'Debug.Print

'二维数组连接得用循环了
For I = LBound(arr3) To UBound(arr3)
    For J = LBound(arr3, 2) To UBound(arr3, 2)
       str4 = arr3(I, J) & "-"
       str5 = str5 + str4
    Next
Next
Debug.Print

Debug.Print str5
Debug.Print



'整个维度用join 维度外循环？ 按行做
For I = LBound(arr3) To UBound(arr3)
str6 = Join(Application.Index(arr3, I), "-")
str7 = str6 + "-" + ";"
Debug.Print str7
Next
Debug.Print



'整个维度用join 维度外循环？ 按列做
For J = LBound(arr3, 2) To UBound(arr3, 2)
str6 = Join(Application.Transpose(Application.Index(arr3, , J)), "-")
str7 = str6 + "-"
Debug.Print str7
Next
Debug.Print


End Sub

 

 

3 举例2：match 也只能对1维数组操作

3.1 match用于一维数组

Sub try003()
str1 = "11,22,33,44,55,66,77"
arr1 = Split(str1, ",")     '被split() 返回的数字也会是字符串

For Each I In arr1
    Debug.Print I & ",";
Next
Debug.Print

target1 = "55"
Debug.Print Application.Match(target1, arr1, 0)

End Sub

 

3.2 match 用于二维数组

• match 如果要正确查到内容，也需要先把二维数组拆解为一维数组才行

Sub try004()
'二维数组，静态redim
Dim arr3()
ReDim arr3(1 To 3, 1 To 3)
For I = 1 To 3
   For J = 1 To 3
      arr3(I, J) = (I + J) * J
   Next
Next
Debug.Print


For I = LBound(arr3) To UBound(arr3)
    For J = LBound(arr3, 2) To UBound(arr3, 2)
    Debug.Print arr3(I, J);
    Next
    Debug.Print
Next
Debug.Print

target1 = 10
Debug.Print Application.Match(target1, arr3, 0)
target2 = "10"
Debug.Print Application.Match(target2, arr3, 0)
Debug.Print

For I = LBound(arr3) To UBound(arr3)
    Debug.Print Application.Match(target1, Application.Index(arr3, I, 0), 0)
Next
Debug.Print


End Sub