将值"directly"传递到 VBA 中的数组

Question

可以指定"数组"directly"对VBA中的可变变量(通常用于读写整个范围，例如varRange = Range("A1:A3").Value):

Dim varVariable As Variant
varVariant = Array("a", "b", "c")
Dim arrVariant() As Variant
arrVariantVarSize = Array("d", "e", "f")

是否有可能这样做与一个数组组成的常规数据类型(不一定只是字符串或整数)?与此类似(它不起作用，因为array()返回一个不能赋值给字符串或整数数组的变体数组):

Dim arrString(2) As String
arrString = Array("a", "b", "c")  '-> throws an exception
Dim arrInteger (2) As Integer
arrInteger = Array(1, 2, 3)  '-> throws an exception

而不是:

Dim arrString(2) As String
arrString(0) = Array("a")
arrString(1) = Array("b")
arrString(2) = Array("c")
Dim arrInteger(2) As String
arrInteger(0) = Array(1)
arrInteger(1) = Array(2)
arrInteger(2) = Array(3)

Answer 1

在使用VBA的时候需要稍微调整一下。

Dim arrString() As String
arrString= Split("a,b,c", ",")

Answer 2

返回字符串数组中的变量数组值

• 无论这样做的动机是什么(性能问题，保持明确，在一行代码中完成等)，您都可以使用StrArray函数。

Option Explicit
'Option Base 1 ' try and see that 'sArr' will always be zero-based
' To ensure that 'vArr' is zero-based, use 'vArr = VBA.Array("a", "b", "c")'.
Sub StrArrayTEST()

' Note that the 'vArr' parentheses are necessary to prevent
' 'Compile error: Type mismatch: array or user-defined type expected'...
Dim vArr() As Variant: vArr = Array("a", "b", "c") ' try 'vArr = Array()'
' ... in the following 'sArr=...' line, where 'vArr' is highlighted.
Dim sArr() As String: sArr = StrArray(vArr)

' The following line instead, doesn't compile with the same error
' (because of 'ByRef' in the function?) with 'Array' highlighted.
'Dim sArr() As String: sArr = StrArray(Array("a", "b", "c"))

Debug.Print "String Array Values"
Debug.Print "Index", "String"

Dim n As Long
For n = 0 To UBound(sArr)
Debug.Print n, sArr(n)
Next n

Debug.Print "Array   LB/UB       Vartype TypeName"
Debug.Print "Variant [LB=" & LBound(vArr) & ",UB=" & UBound(vArr) & "]" _
& " VT=" & VarType(vArr) & " TN=" & TypeName(vArr)
Debug.Print "String  [LB=" & LBound(sArr) & ",UB=" & UBound(sArr) & "]" _
& " VT=" & VarType(sArr) & " TN=" & TypeName(sArr)

End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Returns the values from a variant array ('VariantArray'),
'               converted to strings, in a zero-based string array.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function StrArray( _
VariantArray() As Variant) _
As String() ' 'ByVal VariantArray() As Variant' is not possible
Const ProcName As String = "StrArray"
Dim AnErrorOccurred As Boolean
On Error GoTo ClearError ' turn on error-trapping

Dim LB As Long: LB = LBound(VariantArray)
Dim UB As Long: UB = UBound(VariantArray)

Dim StringArray() As String: ReDim StringArray(0 To UB - LB)

Dim n As Long

For n = LB To UB
StringArray(n - LB) = CStr(VariantArray(n))
Next n

ProcExit:
On Error Resume Next ' defer error-trapping (to prevent endless loop)
If AnErrorOccurred Then
' Ensure the result is a string array.
StrArray = Split("") ' LB = 0, UB = -1
Else
StrArray = StringArray
End If
On Error GoTo 0 ' turn off error-trapping (before exiting)

Exit Function
ClearError:
Debug.Print "'" & ProcName & "' Run-time error '" _
& Err.Number & "':" & vbLf & "    " & Err.Description
AnErrorOccurred = True
Resume ProcExit ' continue error-trapping
End Function