How do I test if a Variant argument has been initialized

[Mackeral]

Dim Variant_Arguments() as Variant

Call Add_Val( Variant_Arguments, "One","Two")

Function Add_Val(Variant_Arguments, Pararray LINES() as Variant)
    Knt = UBcount(Variant_Aruguments)
    ...
End Function)

The "Knt" lines gives an error message but the test "IsError(UBcount(Variant_Aruguments)" doesn't work.

And how do I know if "Varaint_Arguments" is initialized rather than have to explicitly initialize it in the calling program?

 

[6StringJazzer]

Typo on this line

    Knt = UBcount(Variant_Aruguments)

I strongly recommend to everyone that they use Option Explicit to require variable declarations. Doing so prevents a lot of bugs and runtime errors.

Otherwise I'm not clear on your syntax of having a function definition embedded in other procedural code, and that ")" at the end.

 

[Artik]

Please show more diligence in publishing the code. There are, after all, a lot of errors here.

I am just guessing

Sub AAA()
    Dim Variant_Arguments() As Variant
    Dim Variant_Arguments2() As Variant

    ReDim Variant_Arguments(1 To 2)

    Call Add_Val(Variant_Arguments, "One", "Two")
    
    Call Add_Val(Variant_Arguments2, "Three", "Four")
End Sub


Function Add_Val(Variant_Arguments(), ParamArray LINES() As Variant)
    Dim Knt As Boolean

    Knt = (Not Not Variant_Arguments) <> 0
    
    MsgBox Knt
End Function

Artik

 

[Mackeral]

I have been trying different versions. This one works if you call it with an already initialized "Variant_Array".
But it is also callable with only a definition

Function Var_Add(Variant_Array As Variant, ParamArray LINES() As Variant)
    ' Add "What" to a "Var Array".

       Knt = UBound(Variant_Array) ' <-- This lines gives an error if "Variant_Array" does not contain any data.
    
    For Each Line In LINES
        Knt = Knt + 1
        ReDim Preserve Variant_Array(Knt)
        Variant_Array(Knt) = Line
    Next Line
    
    Var_Add = Variant_Array
    
End Function ' Var_Add

 

[Alex Blakenburg]

I am unclear as to whether you have it working or not.
This should work:

Function Var_Add(Variant_Array As Variant, ParamArray LINES() As Variant)
    ' Add "What" to a "Var Array".
    Dim Knt As Long
    Dim Line As Variant

    On Error Resume Next
    Knt = UBound(Variant_Array) ' <-- This lines gives an error if "Variant_Array" does not contain any data.
    If Err <> 0 Then Knt = -1
    On Error GoTo 0
   
    For Each Line In LINES
        Knt = Knt + 1
        ReDim Preserve Variant_Array(Knt)
        Variant_Array(Knt) = Line
    Next Line
  
    Var_Add = Variant_Array
  
End Function ' Var_Add

 

[Mackeral]

I am sorry to report that after much searching, I could not find any way to verify with VBA code if a Variant Array had been initialized or not.

But I did find out that if you try to access an empty variant array, VBA stops and reports a nasty a message. So to get around this, I have created an initialization routine "Var_Init" to pre-initialize the variant array about to be used.

If you want to use a new variant array, use this code:

    Dim Var_1(),  Dim Var_2() as Variant
    Var1 = Var_Init(Var1)
    Var2 = Var_Add(Var1, "One", "Two","Three")

This is the code for "Var_Init"

Sub Var_Init(Variant_Array() As Variant)
    ' Initialize an empty "Variant_Array"
    
    ReDim Preserve Variant_Array(0)
    Variant_Array(0) = ""

End Sub ' Var_Init

This is the code for "Var_Add":

Function Var_Add(Variant_Array() As Variant, ParamArray LINES() As Variant)
    ' Add "LINES" to a "Var Array".
    ' 3/18/19 Created, Mac Lingo
    ' 5/15/23 Reworked. WML
    ' 8/24/24 Corrected Variable Array definition in arg list. WML
        
    TS = UBound(Variant_Array)
    ' If ths line produces an error message, it means that "Variant_Array" was  not initialized.
      
    If Variant_Array(0) = "" Then
        Knt = -1
    Else
        Knt = UBound(Variant_Array())
    End If
    
    For Each Line In LINES
        Knt = Knt + 1
        ReDim Preserve Variant_Array(Knt)
        Variant_Array(Knt) = Line
    Next Line
    
    Var_Add = Variant_Array
    
End Function ' Var_Add

The code here I copied off my computer where it runs.
Sorry that this has taken so long, but I wanted to be sure there was not a way to verify a Variable Array" that I have overlooked.
And thank you for your comments.

Mac

 

[Alex Blakenburg]

I can't let that go as a viable solution without a critique. It has a number of errors in it not the least being that you finish up with 2 identical arrays in the calling sub.

If you declare the array initially as a Variant instead of an "array of variants" you can use this:

Sub TestRunAddToArray()

    Dim arr1 As Variant
   
    'arr1 = Array("a", "b")                    ' With this commented out the array is not initialised
    Call Var_Add(arr1, "One", "Two", "Three")
End Sub

Sub Var_Add(Variant_Array As Variant, ParamArray LINES() As Variant)
    ' Add "LINES" to a "Var Array".
    ' This works whether the array is already initialised or not
   
    Dim Knt As Long
    Dim Line As Variant
       
    If IsEmpty(Variant_Array) Then Variant_Array = Array()      ' Initialise the array as empty
   
    Knt = UBound(Variant_Array)                                 ' If set to Array() will return -1
    ReDim Preserve Variant_Array(Knt + UBound(LINES) + 1)
   
    For Each Line In LINES
        Knt = Knt + 1
        Variant_Array(Knt) = Line
    Next Line
   
End Sub

 

[Artik]

The code here I copied off my computer where it runs.

No, the code you presented has no right to work! I asked, show more diligence.


And also a version with an array of variants.

Sub TestRunAddToArray_2()
    Dim arr1() As Variant

    'arr1 = Array("a", "b")                    ' With this commented out the array is not initialised
    
    Call Var_Add_2(arr1, "One", "Two", "Three")
    
End Sub


Sub Var_Add_2(Variant_Array() As Variant, ParamArray LINES() As Variant)
    Dim Knt As Long
    Dim Line As Variant

    If (Not Not Variant_Array) = 0 Then
        Variant_Array = Array()
    End If

    Knt = UBound(Variant_Array)

    ReDim Preserve Variant_Array(Knt + UBound(LINES) + 1)

    For Each Line In LINES
        Knt = Knt + 1
        Variant_Array(Knt) = Line
    Next Line

End Sub

Artik

 

[Mackeral]

i just found a function "IsArrayEmpty".

Lookup "How to check if an array is empty in VBA" in your browser for more information.