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 arr1Debug.Print I;NextDebug.Printstr2 = Join(arr1, "-")Debug.Print str2End Sub
2.2 join() 直接用于2维数组会报错,如果一定要使用,则需要先把二维数组转为一维数组
Sub try002()'二维数组,静态redimDim arr3()ReDim arr3(1 To 3, 1 To 3)For I = 1 To 3For J = 1 To 3arr3(I, J) = (I + J) * JNextNextDebug.PrintFor I = LBound(arr3) To UBound(arr3)For J = LBound(arr3, 2) To UBound(arr3, 2)Debug.Print arr3(I, J);NextDebug.PrintNextDebug.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 + str4NextNextDebug.PrintDebug.Print str5Debug.Print'整个维度用join 维度外循环? 按行做For I = LBound(arr3) To UBound(arr3)str6 = Join(Application.Index(arr3, I), "-")str7 = str6 + "-" + ";"Debug.Print str7NextDebug.Print'整个维度用join 维度外循环? 按列做For J = LBound(arr3, 2) To UBound(arr3, 2)str6 = Join(Application.Transpose(Application.Index(arr3, , J)), "-")str7 = str6 + "-"Debug.Print str7NextDebug.PrintEnd 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 arr1Debug.Print I & ",";NextDebug.Printtarget1 = "55"Debug.Print Application.Match(target1, arr1, 0)End Sub
3.2 match 用于二维数组
match 如果要正确查到内容,也需要先把二维数组拆解为一维数组才行Sub try004()'二维数组,静态redimDim arr3()ReDim arr3(1 To 3, 1 To 3)For I = 1 To 3For J = 1 To 3arr3(I, J) = (I + J) * JNextNextDebug.PrintFor I = LBound(arr3) To UBound(arr3)For J = LBound(arr3, 2) To UBound(arr3, 2)Debug.Print arr3(I, J);NextDebug.PrintNextDebug.Printtarget1 = 10Debug.Print Application.Match(target1, arr3, 0)target2 = "10"Debug.Print Application.Match(target2, arr3, 0)Debug.PrintFor I = LBound(arr3) To UBound(arr3)Debug.Print Application.Match(target1, Application.Index(arr3, I, 0), 0)NextDebug.PrintEnd Sub