在VBA中声明长度为0的字符串数组-不可能吗? [英] Declare a 0-Length String Array in VBA - Impossible?
问题描述
真的不可能在VBA中声明一个长度为0的数组吗?如果我尝试这样做:
Is it really not possible to declare a 0-length array in VBA? If I try this:
Dim lStringArr(-1) As String
我收到一个编译错误,说range没有值.如果我试图欺骗编译器并在运行时像这样重做:
I get a compile error saying range has no values. If I try to trick the compiler and redim at runtime like this:
ReDim lStringArr(-1)
我收到一个下标超出范围的错误.
I get a subscript out of range error.
我略微改变了上面的内容,但是没有运气.
I've varied the above around a bit but with no luck e.g.
Dim lStringArr(0 To -1) As String
用例
我想将变量数组转换为字符串数组.变体数组可能是空的,因为它来自字典的Keys属性. keys属性会返回一组变体.我希望在代码中使用字符串数组,因为我有一些函数可以处理要使用的字符串数组.这是我正在使用的转换函数.由于lMaxIndex = -1,这将导致下标超出范围错误:
Use Case
I want to convert a variant array to a string array. The variant array may be empty as it comes from the Keys property of a dictionary. The keys property gives back an array of variants. I want an array of strings to use in my code, as I have some functions for processing string arrays I'd like to use. Here's the conversion function I'm using. This throws a subscript out of range error due to lMaxIndex being = -1:
Public Function mVariantArrayToStringArray(pVariants() As Variant) As String()
Dim lStringArr() As String
Dim lMaxIndex As Long, lMinIndex As Long
lMaxIndex = UBound(pVariants)
lMinIndex = LBound(pVariants)
ReDim lStringArr(lMaxIndex)
Dim lVal As Variant
Dim lIndex As Long
For lIndex = lMinIndex To lMaxIndex
lStringArr(lIndex) = pVariants(lIndex)
Next
mVariantArrayToStringArray = lStringArr
End Function
Hack
返回包含一个空字符串的单例数组.注意-这不是我们想要的.我们想要一个空数组-这样遍历它就像什么都不做.但是包含空字符串的单例数组通常可以工作,例如如果我们以后想将所有字符串连接到字符串数组中.
Hack
Return a singleton array containing an empty string. Note- this isn't what we want. We want an empty array- such that looping over it is like doing nothing. But a singleton array containing an empty string will often work e.g. if we later want to join all the strings together in the string array.
Public Function mVariantArrayToStringArray(pVariants() As Variant) As String()
Dim lStringArr() As String
Dim lMaxIndex As Long, lMinIndex As Long
lMaxIndex = UBound(pVariants)
lMinIndex = LBound(pVariants)
If lMaxIndex < 0 Then
ReDim lStringArr(1)
lStringArr(1) = ""
Else
ReDim lStringArr(lMaxIndex)
End If
Dim lVal As Variant
Dim lIndex As Long
For lIndex = lMinIndex To lMaxIndex
lStringArr(lIndex) = pVariants(lIndex)
Next
mVariantArrayToStringArray = lStringArr
End Function
从回答开始更新
这是我用来将变量数组转换为字符串数组的函数. Comintern的解决方案似乎更高级,更通用,如果我仍然坚持使用VBA进行编码,那么我可能会切换到这一天:
Update since answer
Here is the function I'm using for converting a variant array to a string array. Comintern's solution seems more advanced and general, and I may switch to that one day if I'm still stuck coding in VBA:
Public Function mVariantArrayToStringArray(pVariants() As Variant) As String()
Dim lStringArr() As String
Dim lMaxIndex As Long, lMinIndex As Long
lMaxIndex = UBound(pVariants)
lMinIndex = LBound(pVariants)
If lMaxIndex < 0 Then
mVariantArrayToStringArray = Split(vbNullString)
Else
ReDim lStringArr(lMaxIndex)
End If
Dim lVal As Variant
Dim lIndex As Long
For lIndex = lMinIndex To lMaxIndex
lStringArr(lIndex) = pVariants(lIndex)
Next
mVariantArrayToStringArray = lStringArr
End Function
注释
- 我使用Option Explicit.这不会改变,因为它可以保护模块中的其余代码.
推荐答案
如注释中所述,您可以通过在vbNullString
上调用Split
来本地"执行此操作,如
As noted in the comments, you can do this "natively" by calling Split
on a vbNullString
, as documented here:
表达式-必需.包含子字符串和定界符的字符串表达式.如果expression是长度为零的字符串("),则Split返回一个空数组,即没有元素和数据的数组.
expression - Required. String expression containing substrings and delimiters. If expression is a zero-length string(""), Split returns an empty array, that is, an array with no elements and no data.
如果您需要更通用的解决方案(即其他数据类型),则可以直接在oleaut32.dll中调用SafeArrayRedim
函数,并要求其将传递的数组重新定义为0个元素.您必须跳过几圈以获得数组的基地址(这是由于VarPtr
函数的古怪造成的).
If you need a more general solution (i.e., other data types, you can call the SafeArrayRedim
function in oleaut32.dll directly and request that it re-dimensions the passed array to 0 elements. You do have to jump through a couple of hoops to get the base address of the array (this is due to a quirk of the VarPtr
function).
在模块声明部分:
'Headers
Private Type SafeBound
cElements As Long
lLbound As Long
End Type
Private Const VT_BY_REF = &H4000&
Private Const PVDATA_OFFSET = 8
Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias _
"RtlMoveMemory" (ByRef Destination As Any, ByRef Source As Any, _
ByVal length As Long)
Private Declare Sub SafeArrayRedim Lib "oleaut32" (ByVal psa As LongPtr, _
ByRef rgsabound As SafeBound)
过程-向其传递一个初始化的数组(任何类型),它将删除其中的所有元素:
The procedure - pass it an initialized array (any type) and it will remove all elements from it:
Private Sub EmptyArray(ByRef vbArray As Variant)
Dim vtype As Integer
CopyMemory vtype, vbArray, LenB(vtype)
Dim lp As LongPtr
CopyMemory lp, ByVal VarPtr(vbArray) + PVDATA_OFFSET, LenB(lp)
If Not (vtype And VT_BY_REF) Then
CopyMemory lp, ByVal lp, LenB(lp)
Dim bound As SafeBound
SafeArrayRedim lp, bound
End If
End Sub
样品用量:
Sample usage:
Private Sub Testing()
Dim test() As Long
ReDim test(0)
EmptyArray test
Debug.Print LBound(test) '0
Debug.Print UBound(test) '-1
End Sub
这篇关于在VBA中声明长度为0的字符串数组-不可能吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!