arrays - Array 2d VBA 可选元素
问题描述
如何在 VBA 的二维数组中添加可选元素?工作示例:
sub test
Dim arr As Variant, ix as variant
arr = Array( _
Array("01probe", 1, 2, True), _
Array("02datum", 3, 4) _
)
'... lots more
' true optional, nothing or just "false" as alternative
For Each ix In arr
Debug.Print ix(3) '<---- index out of range
Next ix
end sub
谢谢大家
解决方案
您的数组不是二维类型。它是一个数组数组(锯齿状数组)......
但我无法想象为什么需要一个可选参数。
如果您的数组被声明As Variant
,它可以保留任何类型的变量,包括对象。也没有必要声明它的尺寸。什么都可以考虑Optional
...
请参阅下一个代码:
Sub testOptionalArray()
Dim arr As Variant, dbInteger As Integer, strProbeNr As Long, rng As Range
Dim rng2 As Range, dbDate As Date, dDate As Date
dbInteger = 11: strProbeNr = 1000: dbDate = Date + 1: dDate = Now
Set rng = Range("A1:A2"): Set rng2 = Range("B1:B2")
arr = Array( _
Array("01probe", dbInteger, strProbeNr, rng), _
Array("02datum", dbDate, dDate, rng2))
Debug.Print arr(0)(1), arr(0)(3).cells(1, 1).value
Debug.Print arr(1)(1), arr(1)(UBound(arr(1))).cells.count, arr(1)(UBound(arr(1))).Address
End Sub
编辑,在您发布返回错误的代码后:
讨论中的代码必须以下一种方式理解。首先,总是适当地声明所有变量是一个好习惯......
Sub testJaggedArray()
Dim arr As Variant, ix As Variant
arr = Array( _
Array("01probe", 1, 2, True), _
Array("02datum", 3, 4))
'The above array must be understood like an array of two arrays,
'first of them having 4 elements (ubound = 4, because it starts from 0)
' and a second one with only 3 elements.
For Each ix In arr
'When your code tries to print the fourth element of an array with only
'three elements, of course VBA return 'subscript out of range'.
Debug.Print ix(3) '<---- subscript out of range
Next ix
'You can return an element of an array inside a jagged array in this way:
Debug.Print arr(1)(ubound(arr(1)))' which means returning of the last element
' of the second array without returning an error
'You may use an array of two arrays having different number of element,
'but you cannot expect to an array to return an INEXISTENT item.
'There is no any connection between an hypothetical need of OPTIONAL element.
'It is only a matter of EXISTING element...
End Sub
推荐阅读
- php - 如何提取特殊字符之间的字符串?
- java - Appioum:无法使用(URL,功能)创建 AndroidDriver
- docker - Docker:在 alpine linux 发行版上安装 python 密码学
- php - Laravel 4.2应用程序登录后重定向到不受信任的站点
- java - 在 Java 中设置 COA MQ 的 CorrelationID 的替代方法
- node.js - Node js 应用程序不回复 API 调用,因为 Web 套接字正在使用所有资源
- perl - 如何在没有任何提示的情况下使用 Perl 脚本在 FreeBSD 中自动安装 Perl 包?
- sql-server - 替代 ROW_NUMBER() 来获取行位置?
- jquery - ajax加载表单时Jquery验证功能不起作用
- node.js - 将文件设置为状态