应用场景:
从数据库获取数据暂存,多表汇总,数据分析等,因为数组是存在于内存的,所以运行速度会非常快。
一、先总结数组的特性
- 数组转置必须在同过程或同函数内redim 否则报无效 redim;
- 起始标必须为1,不能大于1也不能小于1,否则报类型不匹配错误;
- 数组不改变值的情况下只能增加列的数,如果要增加行,只能先转置,再增加,然后转置还原;
- 如果数组存储了null,则不能被转置,因此在存入空值时需要过滤;
- 经过多次实验证明:VBA的数组起始标只能以1开始,否则无法转置
二、列表设计思路
- 利用类模块,当成对象开发;
- 生成数组是二维数组,每个维度的长度都可以通过参数设置;
- 通过行标列表添加数据;
- 直接最后一行后默认加入一行数据;
- 可以获取行和列的长度;
- 注意:如果是空值则不存入数组,否则数组将无法转置,将导致无法动态变动。
三、代码实现
定义私有变量
Private qh_row_count
Private qh_cloumn_count
Private qh_row_star
Private qh_cloumn_star
Private qh_array
Private qh_array_bool As Boolean
Private qh_XieRu_msg As String '阙_写入数据
Private qh_XieRu_flag As Boolean '阙_写入数据
Private qh_MoJiaHang_msg As String '阙_末行后增加数据
Private qh_MoJiaHang_flag As Boolean '阙_末行后增加数据
设置传入行列的长度
Property Let 阙_行长度(qh_data)
qh_row_count = qh_data
End Property
Property Let 阙_列长度(qh_data)
qh_cloumn_count = qh_data
End Property
创建二维数组
Property Get 阙_创建数组()
'默认值 阙辉
If qh_row_count = "" Then qh_row_count = 1
If qh_cloumn_count = "" Then qh_cloumn_count = 1
If qh_row_star = "" Then qh_row_star = 1
If qh_cloumn_star = "" Then qh_cloumn_star = 1
qh_row_star = 1
qh_cloumn_star = 1
'创建数组 阙辉
ReDim qh_array(qh_row_star To qh_row_count, qh_cloumn_star To qh_cloumn_count)
qh_array_bool = True '如果为真表示数组已创建,否则没创建
'阙_创建数组 = qh_array
End Property
按行列写入数值(可获取写入的状态和返回的消息)
Property Get 阙_写入数据(qh_row, qh_cloumn, qh_value)
Dim qh_row_flag As Boolean
Dim qh_cloumn_flag As Boolean
'初始化默认值
qh_XieRu_flag = False
qh_row_flag = False
qh_cloumn_flag = False
qh_XieRu_msg = ""
qh_msg = ""
qh_row_last = UBound(qh_array, 1)
qh_row_star = LBound(qh_array, 1)
qh_cloumn_last = UBound(qh_array, 2)
qh_cloumn_star = LBound(qh_array, 2)
'如果行不在行标中则qh_row_flag为真
If qh_row >= qh_row_star And qh_row <= qh_row_last Then
qh_row_flag = True
qh_msg = "行不在范围,QH!"
End If
qh_XieRu_msg = qh_msg
'如果行不在列标中则qh_cloumn_flag为真
If qh_cloumn >= qh_cloumn_star And qh_cloumn <= qh_cloumn_last Then
qh_cloumn_flag = True
qh_msg = "列不在范围,QH!"
End If
If qh_XieRu_msg <> "" Then
qh_XieRu_msg = qh_XieRu_msg & "_" & qh_msg
Else
qh_XieRu_msg = qh_msg
End If
'防止行标和列标越界 越界则不写入数据,且不会报错,在写程序时建议先预制检验行列是否在范围内
If qh_row_flag And qh_cloumn_flag Then
If qh_value <> "" Then '空值写入数组后是null,会导致数组无法转置
qh_array(qh_row, qh_cloumn) = qh_value
qh_XieRu_flag = True '返回真
qh_XieRu_msg = "成功写入数组,QH!"
End If
End If
阙_写入数据 = qh_array
End Property
Property Get 阙_写入数据_消息() '应紧跟阙_写入数据后面获取,否则消息被更新
阙_写入数据_消息 = qh_XieRu_msg
End Property
Property Get 阙_写入数据_状态() '应紧跟阙_写入数据后面获取,否则状态被更新
阙_写入数据_状态 = qh_XieRu_flag
End Property
默认在最后添加行
Property Get 阙_末行后增加数据(qh_array_data) '不改变数组结构情况下,在最后一行添加数据
Dim qh_LBound_XiuZheng
qh_MoJiaHang_flag = False
qh_MoJiaHang_msg = ""
qh_add_count = 1 '默认增加一行
qh_cloumn_star_n = LBound(qh_array, 2) '结果数组的起始列标
qh_array_data_cloumn_star_n = LBound(qh_array_data) '传入数组的数组的起始列标
qh_array_data_l = qh_len_arr(qh_array_data)
qh_array_l = qh_len_arr_duo(qh_array)(1)
If qh_cloumn_star_n = qh_array_data_cloumn_star_n Then
qh_LBound_XiuZheng = 0
Else
qh_LBound_XiuZheng = qh_array_data_cloumn_star_n - qh_cloumn_star_n
End If
qh_array = qh_add_row(qh_array, qh_add_count)
qh_row_last = UBound(qh_array, 1)
If qh_array_l >= qh_array_data_l Then '判断写入的列数是否小于二维数组的列数
For qh_i = qh_cloumn_star_n To qh_array_l
qh_row_array = qh_array_data(qh_i + qh_LBound_XiuZheng)
If qh_row_array <> "" Then
qh_array(qh_row_last, qh_i) = qh_array_data(qh_i + qh_LBound_XiuZheng)
qh_MoJiaHang_flag = True
qh_MoJiaHang_msg = "成功写入数组,QH!"
End If
Next
End If
阙_末行后增加数据 = qh_array
End Property
获取列表的属性值
Property Get 阙_起始行标()
qh_row_star = LBound(qh_array, 1)
阙_起始行标 = qh_row_star
End Property
Property Get 阙_最末行标()
qh_row_last = UBound(qh_array, 1)
阙_最末行标 = qh_row_last
End Property
Property Get 阙_起始列标()
qh_cloumn_star = LBound(qh_array, 2)
阙_起始列标 = qh_cloumn_star
End Property
Property Get 阙_最末列标()
qh_cloumn_last = UBound(qh_array, 2)
阙_最末列标 = qh_cloumn_last
End Property
Property Get 阙_行的长度()
qh_len = qh_len_arr_duo(qh_array)(0)
阙_行的长度 = qh_len
End Property
Property Get 阙_列的长度()
qh_len_2 = qh_len_arr_duo(qh_array)(1)
阙_列的长度 = qh_len_2
End Property
以上功能需要调用通用函数方法
Private Function qh_add_row(qh_array_new0, qh_add_count0) '增加二维数组行函数
'1.数组转置必须在同过程或同函数内redim 否则报无效 redim 阙辉
'2.起始标必须为1,不能大于1也不能小于1,否则报类型不匹配错误 阙辉
'3.数组不改变值的情况下只能增加列的数,如果要增加行,只能先转置,再增加,然后转置还原
'4.如果数组存储了null,则不能被转置,因此在存入空值时需要过滤 阙辉
Dim qh_array_new
Dim qh_array_new_new
qh_array_new = qh_array_new0
qh_add_count = qh_add_count0 '传入需要增加的行
'qh_row_last_n = UBound(qh_array_new, 1)
'qh_row_star_n = LBound(qh_array_new, 1)
'qh_cloumn_last_n = UBound(qh_array_new, 2)
'qh_cloumn_star_n = LBound(qh_array_new, 2)
qh_len = qh_len_arr_duo(qh_array_new)(0)
qh_len_2 = qh_len_arr_duo(qh_array_new)(1)
ReDim qh_array_new_new(1 To qh_len, 1 To qh_len_2)
qh_array_new_new = qh_array_new
'qh_array_new = qh_array_new0
'qh_array_new = qh_array_new0 '传入的数组
qh_array_new_row = qh_len + qh_add_count
'MsgBox qh_array_new_row
qh_array_new_new = Application.Transpose(qh_array_new_new)
ReDim Preserve qh_array_new_new(1 To qh_len_2, 1 To qh_array_new_row)
qh_array_new_new = Application.Transpose(qh_array_new_new)
qh_add_row = qh_array_new_new
End Function
Private Function qh_add_cloumn(qh_array_new0, qh_add_count0) '增加二维数组列函数
'1.数组转置必须在同过程或同函数内redim 否则报无效 redim 阙辉
'2.起始标必须为1,不能大于1也不能小于1,否则报类型不匹配错误 阙辉
'3.数组不改变值的情况下只能增加列的数,如果要增加行,只能先转置,再增加,然后转置还原
'4.如果数组存储了null,则不能被转置,因此在存入空值时需要过滤 阙辉
Dim qh_array_new
Dim qh_array_new_new
qh_array_new = qh_array_new0
qh_add_count = qh_add_count0 '传入需要增加的列
'qh_row_last_n = UBound(qh_array_new, 1)
'qh_row_star_n = LBound(qh_array_new, 1)
'qh_cloumn_last_n = UBound(qh_array_new, 2)
'qh_cloumn_star_n = LBound(qh_array_new, 2)
qh_len = qh_len_arr_duo(qh_array_new)(0)
qh_len_2 = qh_len_arr_duo(qh_array_new)(1)
ReDim qh_array_new_new(1 To qh_len, 1 To qh_len_2)
qh_array_new_new = qh_array_new
'qh_array_new = qh_array_new0
'qh_array_new = qh_array_new0 '传入的数组
qh_array_new_cloumn = qh_len_2 + qh_add_count
'MsgBox qh_array_new_row
'qh_array_new_new = Application.Transpose(qh_array_new_new)
ReDim Preserve qh_array_new_new(1 To qh_len, 1 To qh_array_new_cloumn)
'qh_array_new_new = Application.Transpose(qh_array_new_new)
qh_add_row = qh_array_new_new
End Function
Private Function qh_len_arr_duo(qh_array0) '计算数组的长度 作者:阙辉 2021.02.24
Dim qh_array
Dim qh_array_1, qh_array_2
qh_array = qh_array0
qh_array_1 = UBound(qh_array, 1) - LBound(qh_array, 1) + 1
qh_array_2 = UBound(qh_array, 2) - LBound(qh_array, 2) + 1
qh_len_arr_duo = Array(qh_array_1, qh_array_2)
End Function
Function qh_len_arr(qh_array0) '计算数组的长度 作者:阙辉 2021.02.24
Dim qh_array
Dim qh_array_l
qh_array = qh_array0
qh_array_l = UBound(qh_array) - LBound(qh_array) + 1
qh_len_arr = qh_array_l
End Function
四、完整的代码展示
'经过多次实验证明:VBA的数组起始标只能以1开始,否则无法转置
Private qh_row_count
Private qh_cloumn_count
Private qh_row_star
Private qh_cloumn_star
Private qh_array
Private qh_array_bool As Boolean
Private qh_XieRu_msg As String '阙_写入数据
Private qh_XieRu_flag As Boolean '阙_写入数据
Private qh_MoJiaHang_msg As String '阙_末行后增加数据
Private qh_MoJiaHang_flag As Boolean '阙_末行后增加数据
Property Let 阙_行长度(qh_data)
qh_row_count = qh_data
End Property
Property Let 阙_列长度(qh_data)
qh_cloumn_count = qh_data
End Property
'Property Let 阙_行起始(qh_data)
'qh_row_star = qh_data
'End Property
'Property Let 阙_列起始(qh_data)
'qh_cloumn_star = qh_data
'End Property
Property Get 阙_创建数组()
'默认值 阙辉
If qh_row_count = "" Then qh_row_count = 1
If qh_cloumn_count = "" Then qh_cloumn_count = 1
If qh_row_star = "" Then qh_row_star = 1
If qh_cloumn_star = "" Then qh_cloumn_star = 1
qh_row_star = 1
qh_cloumn_star = 1
''修正长度值 阙辉
'If qh_row_star = 0 Then
' qh_row_count = qh_row_count - 1
'ElseIf qh_row_star > 1 Then
' qh_row_count = qh_row_count + qh_row_star
'ElseIf qh_row_star < 0 Then
' qh_row_count = qh_row_count + qh_row_star - 1
'End If
'If qh_cloumn_star = 0 Then
' qh_cloumn_count = qh_cloumn_count - 1
'ElseIf qh_cloumn_star > 1 Then
' qh_cloumn_count = qh_cloumn_count + qh_cloumn_star
'ElseIf qh_cloumn_star < 0 Then
' qh_cloumn_count = qh_cloumn_count + qh_cloumn_star - 1
'End If
'创建数组 阙辉
ReDim qh_array(qh_row_star To qh_row_count, qh_cloumn_star To qh_cloumn_count)
qh_array_bool = True '如果为真表示数组已创建,否则没创建
'阙_创建数组 = qh_array
End Property
Property Get 阙_起始行标()
qh_row_star = LBound(qh_array, 1)
阙_起始行标 = qh_row_star
End Property
Property Get 阙_最末行标()
qh_row_last = UBound(qh_array, 1)
阙_最末行标 = qh_row_last
End Property
Property Get 阙_起始列标()
qh_cloumn_star = LBound(qh_array, 2)
阙_起始列标 = qh_cloumn_star
End Property
Property Get 阙_最末列标()
qh_cloumn_last = UBound(qh_array, 2)
阙_最末列标 = qh_cloumn_last
End Property
Property Get 阙_行的长度()
qh_len = qh_len_arr_duo(qh_array)(0)
阙_行的长度 = qh_len
End Property
Property Get 阙_列的长度()
qh_len_2 = qh_len_arr_duo(qh_array)(1)
阙_列的长度 = qh_len_2
End Property
Property Get 阙_写入数据(qh_row, qh_cloumn, qh_value)
Dim qh_row_flag As Boolean
Dim qh_cloumn_flag As Boolean
'初始化默认值
qh_XieRu_flag = False
qh_row_flag = False
qh_cloumn_flag = False
qh_XieRu_msg = ""
qh_msg = ""
qh_row_last = UBound(qh_array, 1)
qh_row_star = LBound(qh_array, 1)
qh_cloumn_last = UBound(qh_array, 2)
qh_cloumn_star = LBound(qh_array, 2)
'如果行不在行标中则qh_row_flag为真
If qh_row >= qh_row_star And qh_row <= qh_row_last Then
qh_row_flag = True
qh_msg = "行不在范围,QH!"
End If
qh_XieRu_msg = qh_msg
'如果行不在列标中则qh_cloumn_flag为真
If qh_cloumn >= qh_cloumn_star And qh_cloumn <= qh_cloumn_last Then
qh_cloumn_flag = True
qh_msg = "列不在范围,QH!"
End If
If qh_XieRu_msg <> "" Then
qh_XieRu_msg = qh_XieRu_msg & "_" & qh_msg
Else
qh_XieRu_msg = qh_msg
End If
'防止行标和列标越界 越界则不写入数据,且不会报错,在写程序时建议先预制检验行列是否在范围内
If qh_row_flag And qh_cloumn_flag Then
If qh_value <> "" Then '空值写入数组后是null,会导致数组无法转置
qh_array(qh_row, qh_cloumn) = qh_value
qh_XieRu_flag = True '返回真
qh_XieRu_msg = "成功写入数组,QH!"
End If
End If
阙_写入数据 = qh_array
End Property
Property Get 阙_写入数据_消息() '应紧跟阙_写入数据后面获取,否则消息被更新
阙_写入数据_消息 = qh_XieRu_msg
End Property
Property Get 阙_写入数据_状态() '应紧跟阙_写入数据后面获取,否则状态被更新
阙_写入数据_状态 = qh_XieRu_flag
End Property
Property Get 阙_末行后增加数据(qh_array_data) '不改变数组结构情况下,在最后一行添加数据
Dim qh_LBound_XiuZheng
qh_MoJiaHang_flag = False
qh_MoJiaHang_msg = ""
qh_add_count = 1 '默认增加一行
qh_cloumn_star_n = LBound(qh_array, 2) '结果数组的起始列标
qh_array_data_cloumn_star_n = LBound(qh_array_data) '传入数组的数组的起始列标
qh_array_data_l = qh_len_arr(qh_array_data)
qh_array_l = qh_len_arr_duo(qh_array)(1)
If qh_cloumn_star_n = qh_array_data_cloumn_star_n Then
qh_LBound_XiuZheng = 0
Else
qh_LBound_XiuZheng = qh_array_data_cloumn_star_n - qh_cloumn_star_n
End If
qh_array = qh_add_row(qh_array, qh_add_count)
qh_row_last = UBound(qh_array, 1)
If qh_array_l >= qh_array_data_l Then '判断写入的列数是否小于二维数组的列数
For qh_i = qh_cloumn_star_n To qh_array_l
qh_row_array = qh_array_data(qh_i + qh_LBound_XiuZheng)
If qh_row_array <> "" Then
qh_array(qh_row_last, qh_i) = qh_array_data(qh_i + qh_LBound_XiuZheng)
qh_MoJiaHang_flag = True
qh_MoJiaHang_msg = "成功写入数组,QH!"
End If
Next
End If
阙_末行后增加数据 = qh_array
End Property
Private Function qh_add_row(qh_array_new0, qh_add_count0) '增加二维数组行函数
'1.数组转置必须在同过程或同函数内redim 否则报无效 redim 阙辉
'2.起始标必须为1,不能大于1也不能小于1,否则报类型不匹配错误 阙辉
'3.数组不改变值的情况下只能增加列的数,如果要增加行,只能先转置,再增加,然后转置还原
'4.如果数组存储了null,则不能被转置,因此在存入空值时需要过滤 阙辉
Dim qh_array_new
Dim qh_array_new_new
qh_array_new = qh_array_new0
qh_add_count = qh_add_count0 '传入需要增加的行
'qh_row_last_n = UBound(qh_array_new, 1)
'qh_row_star_n = LBound(qh_array_new, 1)
'qh_cloumn_last_n = UBound(qh_array_new, 2)
'qh_cloumn_star_n = LBound(qh_array_new, 2)
qh_len = qh_len_arr_duo(qh_array_new)(0)
qh_len_2 = qh_len_arr_duo(qh_array_new)(1)
ReDim qh_array_new_new(1 To qh_len, 1 To qh_len_2)
qh_array_new_new = qh_array_new
'qh_array_new = qh_array_new0
'qh_array_new = qh_array_new0 '传入的数组
qh_array_new_row = qh_len + qh_add_count
'MsgBox qh_array_new_row
qh_array_new_new = Application.Transpose(qh_array_new_new)
ReDim Preserve qh_array_new_new(1 To qh_len_2, 1 To qh_array_new_row)
qh_array_new_new = Application.Transpose(qh_array_new_new)
qh_add_row = qh_array_new_new
End Function
Private Function qh_add_cloumn(qh_array_new0, qh_add_count0) '增加二维数组列函数
'1.数组转置必须在同过程或同函数内redim 否则报无效 redim 阙辉
'2.起始标必须为1,不能大于1也不能小于1,否则报类型不匹配错误 阙辉
'3.数组不改变值的情况下只能增加列的数,如果要增加行,只能先转置,再增加,然后转置还原
'4.如果数组存储了null,则不能被转置,因此在存入空值时需要过滤 阙辉
Dim qh_array_new
Dim qh_array_new_new
qh_array_new = qh_array_new0
qh_add_count = qh_add_count0 '传入需要增加的列
'qh_row_last_n = UBound(qh_array_new, 1)
'qh_row_star_n = LBound(qh_array_new, 1)
'qh_cloumn_last_n = UBound(qh_array_new, 2)
'qh_cloumn_star_n = LBound(qh_array_new, 2)
qh_len = qh_len_arr_duo(qh_array_new)(0)
qh_len_2 = qh_len_arr_duo(qh_array_new)(1)
ReDim qh_array_new_new(1 To qh_len, 1 To qh_len_2)
qh_array_new_new = qh_array_new
'qh_array_new = qh_array_new0
'qh_array_new = qh_array_new0 '传入的数组
qh_array_new_cloumn = qh_len_2 + qh_add_count
'MsgBox qh_array_new_row
'qh_array_new_new = Application.Transpose(qh_array_new_new)
ReDim Preserve qh_array_new_new(1 To qh_len, 1 To qh_array_new_cloumn)
'qh_array_new_new = Application.Transpose(qh_array_new_new)
qh_add_row = qh_array_new_new
End Function
Private Function qh_len_arr_duo(qh_array0) '计算数组的长度 作者:阙辉 2021.02.24
Dim qh_array
Dim qh_array_1, qh_array_2
qh_array = qh_array0
qh_array_1 = UBound(qh_array, 1) - LBound(qh_array, 1) + 1
qh_array_2 = UBound(qh_array, 2) - LBound(qh_array, 2) + 1
qh_len_arr_duo = Array(qh_array_1, qh_array_2)
End Function
Function qh_len_arr(qh_array0) '计算数组的长度 作者:阙辉 2021.02.24
Dim qh_array
Dim qh_array_l
qh_array = qh_array0
qh_array_l = UBound(qh_array) - LBound(qh_array) + 1
qh_len_arr = qh_array_l
End Function
五、调用测试
Sub testarray()
Dim qh_arr As New 阙_数组_DataFrame
'qh_arr.阙_行起始 = 2
'qh_arr.阙_列起始 = 2
qh_arr.阙_行长度 = 1
qh_arr.阙_列长度 = 3
aa = qh_arr.阙_创建数组
aa = qh_arr.阙_写入数据(1, 2, 12)
qh_q1 = qh_arr.阙_写入数据_消息
qh_q2 = qh_arr.阙_写入数据_状态
a = qh_arr.阙_起始行标
b = qh_arr.阙_最末行标
c = qh_arr.阙_起始列标
d = qh_arr.阙_最末列标
aa = qh_arr.阙_末行后增加数据(Array(2, "quehui", 3))
g = qh_arr.阙_列的长度
End Sub