Cycle through variables

[Ruegen]

Is there a quick way in vba code to cycle through a list of variants i.e. var1, var2, var3 and set them all to null

or do I have to

var1 = null
var2 = null

I'd like to group it all and also do things like check if they are null too.

[June7]

AFAIK, must address each individually, cannot 'cycle' variables.

Unless there is some very advanced code I've never encountered, cannot dynamically build a variable name. This won't work:

For i = 1 to 10
"var" & i = Null
Next

[Ruegen]

I don't want to build a variable name - I already have public variables.

I'll explain the situation.

I've made a new class which has many variables and a few methods, the last saving to the database using dao. But I encountered an issue that if I gave a data type to the variables like say date then I would get a variable with a date whether I had set one or not when calling and setting the class in any sub. This means I can't use if is null - I wanted it so that before the save method on the class could be used it would check through each var in the newly made class and made sure there are no missing data.

e.g.

dim Booking as BookingClass
set Booking = new bookingclass

booking.init
booking.BookingDate = now()
booking.ShowID = 4

if booking.verify = false then
else
booking.save
end if

so I found I couldn't check using isEmpty, isnull, isnothing etc because as soon as you make the class object you init the variables.

So I swapped each type to variant to hold null then use an .init method to null each of them before you can use them (there is a bool in the init method that gets set to true and if true all other methods etc will work)

so there are a lot of vars in the .init method and I would rather have something like

var1, varf, varxyz, varbooking, varstatusid = null

Currently I have to

var1 = null
varf = null
varxyz = null

etc

I'd rather just group them all some how and set to null.

[June7]

Doesn't change my comments.

[Ruegen]

Doesn't change my comments.

*sadface*

[thebigthing313]

I know of no way to indirectly reference a variable as well. However, the issue that brought you here in the first place possibly can be addressed?

You can try writing a custom IsNull type function, and using VarType to test dates, longs, doubles, ints, etc against 0, strings against "", etc.

[Ruegen]

I know of no way to indirectly reference a variable as well. However, the issue that brought you here in the first place possibly can be addressed?

You can try writing a custom IsNull type function, and using VarType to test dates, longs, doubles, ints, etc against 0, strings against "", etc.

Yeah I already thought of doing that, putting dates to a date no one would use like 1/1/1950 however that's cumbersome....

[Ruegen]

I've provided some code below, it's a function that checks variants and returns a bool

'is variable null (any data type)
Public Function isNullVar(variable As Variant) As Boolean


isNullVar = False


    Select Case VarType(variable)
        
        'null check
        Case 0, 1
        isNullVar = True
        
        'number check
        Case 2, 3, 4, 5, 6
        If variable <= 0 Then
        isNullVar = True
        End If
        
        'date check
        Case 7
        If variable <= #1/1/1999# Then
        isNullVar = True
        End If
                
        'string check
        Case 8
        If Trim(variable) = "" Then
        isNullVar = True
        End If
            
    End Select


End Function

[amrut]

Can't you use a dynamic array ?
Dim var() as variant

[Ruegen]

Can't you use a dynamic array ?
Dim var() as variant

I could store variable into an array and make a loop but then I still have to place them into the array.... which means I'm coding them in.... the point is less coding...?