VBA: Global eNum / Variables or Constants?

[maria90]

Dear all

The workbook I am working with is set to be used with userforms only; there are around 20 of them. I have a number of variables of type byte which are repeatedly used in userforms.

Is it a good idea to create global variables, global constants or enum whose values are set when the workbook opens?

You probably wonder what the variables are used for:

I work with listobjects and in order to keep the whole workbook 'dynamic', I retrieve the column numbers ( based on the header name) and put them into a variable.
For example, the column 'Name' in the table 'Customers' is in column number 3. If a new column is inserted or deleted, I want to make sure that this variable matches/'adapts itself' to the correct column number. I use this number when working with arrays for example.

The function I use to retrieve the column number is:

'-----------------------------------------------------------------
' Author            Maria
' Date              5.1.2016
' Purpose           Retrieves the column number - searches in a
'                   listobject header
' Requires          string to search
'                   range (listobject name)
' Important         needs range with [#headers],  eg
'                   Range("RecyclingTaxiAufträge[#Headers]")
'-----------------------------------------------------------------
Function GetColumn(str As String, rng As Range) As Integer
GetColumn = Application.WorksheetFunction.Match(str, rng, 0)
End Function

For example, in order to set the variable ColumnCustomerName I use

ColumnCustomerName = GetColumn("Name",Range("Customers[#Headers]")

Would you use global variables / enum or would this method use up, despite the fact that the variables are of type byte, too much memory?

Thanks

Maria

 

[Norie]

Maria

If you are using ListObjects why do you need the column number?

Can't you just use the column name?

For example this code will get the range for the column named 'Name' in the first listobject on Sheet1.

Dim rng As Range

    Set rng = Sheet1.ListObjects(1).ListColumns("Name").Range

 

[Kyle123]

No, global variables are always better avoided.

Enums wouldn't solve your problem
Constants wouldn't either, they can't be set at runtime.

I'd do something like this (in a class or module called GLOBALS):

Private p_columnCustomerName As Long

Public Property Get ColumnCustomerName() As Long
    
    If p_columnCustomerName = 0 Then
        p_columnCustomerName = GetColumn("Name", Range("Customers[#Headers]"))
    End If
    
    ColumnCustomerName = p_columnCustomerName
    
End Property

Then use it something like:

Sub test()

    MsgBox GLOBALS.ColumnCustomerName

End Sub

This essentially gives you cached, constant like values that can be set at runtime

 

[maria90]

Maria

If you are using ListObjects why do you need the column number?

Can't you just use the column name?

For example this code will get the range for the column named 'Name' in the first listobject on Sheet1.

Dim rng As Range

    Set rng = Sheet1.ListObjects(1).ListColumns("Name").Range

For example, if I want to add a new record/row to a table, I use the following method:

        Set oNewRow = tbl.ListObject.ListRows.Add(AlwaysInsert:=True)
        With oNewRow
            .Range.Cells(1, 1).Value = lngNewOrderID
            .Range.Cells(1, ColumnCustomer).Value = Me.lstCustomers.List(Me.lstCustomers.ListIndex, 0)   
            .Range.Cells(1, ColumnDate).Value = CDate(txtDate.value)
        End With

or for arrays:

            If arrPickups(i, ColumnDate) >= datStartDate And arrPickups(i, ColumnDate) <= datEndDate And arrPickups(i, ColumnPenultimatePickup) = True Then
                
                
                'a match has been found, put values into a variable
                intCustomerID = arrPickups(i, ColumnCustomer)
                intContractID = arrPickups(i, ColumnContractID)
                
                
                'customer name
                For y = 1 To UBound(arrCustomers)
                    If arrCustomers(y, 1) = intCustomerID Then
                        strCustomer = arrCustomers(y, ColumnCustomerName)
                    End If
                Next y

That is why I use the variable.
This does not seem to have an effect on the speed.. seems to me.

 

[maria90]

No, global variables are always better avoided.

Enums wouldn't solve your problem
Constants wouldn't either, they can't be set at runtime.

I'd do something like this (in a class or module called GLOBALS):

Private p_columnCustomerName As Long

Public Property Get ColumnCustomerName() As Long
    
    If p_columnCustomerName = 0 Then
        p_columnCustomerName = GetColumn("Name", Range("Customers[#Headers]"))
    End If
    
    ColumnCustomerName = p_columnCustomerName
    
End Property

Then use it something like:

Sub test()

    MsgBox GLOBALS.ColumnCustomerName

End Sub

This essentially gives you cached, constant like values that can be set at runtime

This method sounds/looks interesting.
I would have to create around 10 or more Get Columnxxxxx right?

 

[Kyle123]

Yep, as many as you like

 

[maria90]

Yep, as many as you like

It is a pitty that enum cannot be set at run time; it would obviously make it easier to use/find them such as ColumnCustomer.FirstName.

But your solution is good!
As the values are cached once used, they won't use up as much memory as if I were to use global variables, right?

 

[Kyle123]

They'll use the same, but you shouldn't be worrying about the memory consumption of a number - it's negligible

They're cached because worksheet calls are incredibly expensive, this way you do a single worksheet call and only when the value is actually requested.

 

[maria90]

They'll use the same, but you shouldn't be worrying about the memory consumption of a number - it's negligible

They're cached because worksheet calls are incredibly expensive, this way you do a single worksheet call and only when the value is actually requested.

Brilliant.

By the way, I have just noticed that I need to put the code into a module. When the code is put into a class module, only the first function will work.
To be honest, I am not familiar with classes yet but I thought that is where you put this kind of code....?

 

[Kyle123]

Normally you would. There's no reason you can't use a module for this kind of thing though - they're singletons (sounds a bit complicated, but it means there can only be one of them which is what you want).

To use it in a class, you'd need to create an instance of it, so:

Sub test()

    MsgBox GLOBALS.ColumnCustomerName

End Sub

Would become:

Sub test()
    Dim appGlobals as GLOBALS
    Set appGlobals = New GLOBALS
    MsgBox appGlobals.ColumnCustomerName

End Sub

Though you'd probably declare the class publicly somewhere so it exists only once