How do I test if a Variant argument has been initialized

Mackeral

Board Regular
Joined
Mar 7, 2015
Messages
249
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
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?
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Typo on this line
Rich (BB code):
    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.
 
Upvote 0
Please show more diligence in publishing the code. There are, after all, a lot of errors here.

I am just guessing
VBA Code:
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
 
Upvote 0
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

VBA Code:
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
 
Upvote 0
I am unclear as to whether you have it working or not.
This should work:
VBA Code:
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
 
Upvote 0
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:
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"
Code:
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":
Code:
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
 
Upvote 0
Solution
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:

VBA Code:
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
 
Upvote 0
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.
VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top