【原创】VBA学习笔记（310）VBA的一维数组相关的 工作表函数，方法等等

1 数组相关的工作表函数，方法

工作表函数（都是只对一维数组生效）

• application.max()
• application.min()
• application.large()
• application.small()
• application.count()
• application.counta()

代码1

Sub test110()

arr1 = Array(1, 2, 3, 4, 5, 5, 5, "汉字", 5)
arr2 = Filter(arr1, 5, True)              '中间不能有空元素，否则filter报错？
arr3 = Filter(arr1, 5, False)

display_array (arr1)
display_array (arr2)
display_array (arr3)


Debug.Print Application.Count(arr1)
Debug.Print Application.CountA(arr1)
Debug.Print Application.Max(arr1)
Debug.Print Application.Large(arr1, 2)
Debug.Print Application.Min(arr1)
Debug.Print Application.Small(arr1, 3)
Debug.Print



arr4 = Array("", "", 1, "", 2, "")
Debug.Print Application.Count(arr4)
Debug.Print Application.CountA(arr4)
Debug.Print

End Sub


Function display_array(x)    '1维数组

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

End Function

 

2 VBA函数（都是只对一维数组生效）

• filter()
• join()
• split()

 

测试数组功能

Sub ponyma1()

arr1 = Array(1, 2, 3, 4, 5)

Debug.Print "arr1数组中最大的数="; Application.Max(arr1)
Debug.Print "arr1数组中最小的数="; Application.Min(arr1)
Debug.Print "arr1数组中第2大的数="; Application.Large(arr1, 2)
Debug.Print "arr1数组中第2小数="; Application.Small(arr1, 2)
Debug.Print "arr1数组中数的个数"; Application.Count(arr1, 2)
Debug.Print "arr1数组中非空数的个数="; Application.CountA(arr1, 2)
Debug.Print

Debug.Print Join(arr1, "-")
arr2 = Split(Join(arr1, "-"), "-")
Debug.Print "新数组arr2重排后=";
For Each i In arr2
   Debug.Print i;
Next
Debug.Print

arr3 = Filter(arr1, 3, True)
Debug.Print "只保留3的新数组arr3=";
For Each i In arr3
   Debug.Print i & ",";
Next
Debug.Print


arr4 = Filter(arr1, 3, False)
Debug.Print "去掉3的新数组arr4=";
For Each i In arr4
   Debug.Print i & ",";
Next
Debug.Print


End Sub