2

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)
Improve this question
11
  • Dim arrString As Variant works - The Array function returns a variant. Is there a reason why this does not work for you? Commented Jun 22, 2022 at 17:23
  • 1
    Then you cannot use Array - What's the problem with it being a variant? Commented Jun 22, 2022 at 18:26
  • 1
    @Albin You cannot assign arrays that have different data types to each other. See my answer for Byte. Same is applicable for String. Or you could arrString = Split(Join(Array("a", "b", "c"),","),",") but that's just ugly. Commented Jun 22, 2022 at 19:55
  • 2
    @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. Commented Jun 23, 2022 at 7:48
  • 2
    @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. Commented Jun 23, 2022 at 10:04

2 Answers 2

Reset to default
3

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

Dim arrString() As String
arrString= Split("a,b,c", ",")
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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.
2

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
Improve this answer

1 Comment

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.