楼主
DJ_Soo |
写代码时有时会需要把多个数据整合起来,此代码达到一个整合的目的,且转换为一维数组.
参数中可以放入单元格区域,数组(二维或一维)或常量.或混合使用.
代码如下:- Option Explicit
- '自定义函数Multi2Param
- '此函数对于多选区域/多个数组/多个常量参数或以上的几种混合情况下都可以将所有的数据整理为一维数组
- '如{1,2,3},4,5,{6,7,8},整理后为{1,2,3,4,5,6,7,8},且最小下标为1
- 'by Gao Dawei 2012.10.24
- Function Multi2Param(ParamArray arrRng() As Variant) As Variant
- Dim arr() As Variant '定义一个存放多个区域数据的数组,此数组为纯数据,而不存在object类型
- Dim Rng As Variant '定义临时的数组,可以接受单元格,数组和常量
- Dim n As Integer 'Cnt的循环参数,从1开始循环
- Dim Cnt As Integer 'Cnt对arrRng()中的数量进行统计
- Dim arrRes() As Variant '定义最终的结果
- Dim nElement As Long '定义最终结果的个数,可能是单元格个数,数组个数和常数个数的相加总和
- Dim i As Long 'nElement的循环参数
-
- Cnt = UBound(arrRng) + 1
- ReDim arr(1 To Cnt) As Variant
-
- For Each Rng In arrRng
- n = n + 1
- arr(n) = Rng
- If IsArray(arr(n)) Then
- '判断是否为一维
- If Dimension(arr(n)) = 1 Then
- nElement = nElement + (UBound(arr(n), 1) - LBound(arr(n), 1) + 1)
- Else
- nElement = nElement + (UBound(arr(n), 1) - LBound(arr(n), 1) + 1) _
- * (UBound(arr(n), 2) - LBound(arr(n), 2) + 1)
- End If
- Else
- nElement = nElement + 1
- End If
- Next Rng
- '组织成为一维数组
- Dim Var As Variant
- ReDim arrRes(1 To nElement) As Variant
- For Each Rng In arr '从arr中提取,避免判断是否为object(range)对象,因为此时都是数组和常量模式
- '但还是需要判断是数组/常量
- If IsArray(Rng) Then
- For Each Var In Rng
- i = i + 1
- arrRes(i) = Var
- Next Var
- Else
- i = i + 1
- arrRes(i) = Rng
- End If
- Next Rng
- Multi2Param = arrRes
- End Function
- '返回数组的维数
- Function Dimension(arr As Variant) As Integer
- On Error Resume Next
- Dim n As Integer
- Dim i As Integer
- If Not IsArray(arr) Then
- Dimension = -1
- Exit Function
- End If
- For i = 1 To 61
- n = UBound(arr, i)
- If Err.Number <> 0 Then
- Dimension = i - 1
- Exit Function
- End If
- Next
- End Function
|