Passing Values "directly" to an array in VBA

Question

It's possible to assign "whole" arrays "directly" to an Variant variable in VBA (usually used for reading/writing whole ranges e.g. varRange = Range("A1:A3").Value):

Dim varVariable As Variant
varVariant = Array("a", "b", "c")

Dim arrVariant() As Variant
arrVariantVarSize = Array("d", "e", "f")

Is it possible to do that with an array consisting of a regular data type (not necessarily just string or integer)? Similar to this (which does not work since array() returns a variant array that can't be assigned to a string or integer 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

Instead of this:

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)

`Dim arrString As Variant` works - The `Array` function returns a variant. Is there a reason why this does not work for you? — braX, Jun 22 '22 at 17:23
`arrString(0) = Array("a")` should also give you an error. `arrString(0) = "a"` is how you would put a string value into an array of strings. — Toddleson, Jun 22 '22 at 17:42
@braX I know that `Dim arrString as Variant` works as I already put it into my question (`Dim varVariable as Variant`) - same thing different name. I'm trying to avoid using Variant. — Albin, Jun 22 '22 at 18:23
Then you cannot use `Array` - What's the problem with it being a variant? — braX, Jun 22 '22 at 18:26
@braX I prefer strict declaration (for performance reasons etc.) — Albin, Jun 22 '22 at 18:29
@Albin You cannot assign arrays that have different data types to each other. See [my answer](https://stackoverflow.com/a/64620933/8488913) for Byte. Same is applicable for String. Or you could ```arrString = Split(Join(Array("a", "b", "c"),","),",")``` but that's just ugly. — Cristian Buse, Jun 22 '22 at 19:55
@CristianBuse why do you think trying to assign different data types? I'm trying to add strings to a string array. — Albin, Jun 23 '22 at 06:32
@Albin The ```Array``` function always returns an array of ```Variant``` regardless if you pass arguments of type ```String``` or other types. For example ```Array("a", "b", "c")``` and ```Array(1, 2, 3)``` both return an array of ```Variant``` type. So, you cannot just assign it to an array of ```String``` type. All you can do is to assign to a ```Variant``` or an array of ```Variant``` which is exactly why your first 2 examples work. — Cristian Buse, Jun 23 '22 at 07:48
@CristianBuse ah, I see the problem using array(), thanks. That being said, I'm not hell bend on using the array function, it was just it illustrate what does not work (and how I would have liked it). So if you have any other ideas, let me know, otherwise, VBasic gave me a solution, that might work for me. — Albin, Jun 23 '22 at 10:04
@Albin I would also use an auxiliary function, hence me linking to my old answer but rather than adding a new answer I knew it would be easy for you to update my old function to work with String instead of Byte. My own preference would be to raise an error if a wrong data type is passed in the argument list. Alternatively, if you want actual compilation checking then create an auxiliary function with a fixed number of optional string parameters: ```Function StringArray(ByRef s1 As String, Optional ByRef s2 As String, ..., Optional ByRef s20 As String) As String()```. — Cristian Buse, Jun 23 '22 at 10:59
@Albin This second option would require at least one String to be passed and would also force type checking. However, the code would not be the cleanest and you would have a limit of parameters. I would still go with option 1. — Cristian Buse, Jun 23 '22 at 11:01

score 3 · Answer 1 · answered Jun 22 '22 at 17:49

3

Kind of have to finagle it a little more with VBA.

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

answered Jun 22 '22 at 17:49

data_sc

459
1
6

thanks for the suggestion, my question was more of a general nature (not just for string, this was just an example), but this is a good workaround at least for string. – Albin Jun 23 '22 at 06:43

score 2 · Accepted Answer · answered Jun 23 '22 at 01:24

Return Variant Array Values in a String Array

No matter what the motives are for doing this (performance issues, keeping it explicit, doing it in one line of code, etc.), you could use the StrArray function.

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

I was hoping that I just didn't use the correct notation but if it's not possible writing a function is a good idea, thx. I just have to make it a little bit more flexible so it will work for all data types. thx! — Albin, Jun 23 '22 at 07:04

Passing Values "directly" to an array in VBA

2 Answers2

Return Variant Array Values in a String Array