IntelliSense with Global Variables (1 Viewer)

[ChrisO]

IntelliSense with Global Variables

I like IntelliSense with its selection capability and I like Global constants…

While strictly speaking these are not constants, they can be written to during the life of the front end session, they are protected from permanent change across a network. That protection is dependent on the programmer not writing code which directly writes to Table tblGlobals.

And so on to the code so that I can refer to it later…

In a Class Module called clsGlobals:-

Option Explicit
Option Compare Text

Private Const conTableName As String = "tblGlobals"
Private Const conFieldName As String = "Item"
Private Const conKeyName   As String = "Key"

Public ColourLocal         As Variant
Public ColourNetwork       As Variant
Public DropboxLocal        As Variant
Public DropboxNetWork      As Variant


Private Sub Class_Initialize()

    ColourLocal = DLookup(conFieldName, conTableName, conKeyName & " = 'ColourLocal'")
    ColourNetwork = DLookup(conFieldName, conTableName, conKeyName & " = 'ColourNetwork'")
    DropboxLocal = DLookup(conFieldName, conTableName, conKeyName & " = 'DropboxLocal'")
    DropboxNetWork = DLookup(conFieldName, conTableName, conKeyName & " = 'DropboxNetWork'")

End Sub

In a Standard Module, for testing:-

Option Explicit
Option Compare Text

Public GV As New clsGlobals

Sub TestIt()

    With GV
        MsgBox .ColourLocal
        MsgBox .ColourNetwork
        MsgBox .DropboxLocal
        MsgBox .DropboxNetWork
    End With

End Sub

Behind a Form, for testing:-

Option Explicit
Option Compare Text

Private Sub Form_Open(ByRef intCancel As Integer)

    Me.Section(acDetail).BackColor = GV.ColourLocal

End Sub

IntelliSense:
Quite simple but difficult to explain…
1.
Upon first reference to GV, as defined in the Standard Module above, GV ‘knows’ its Public variables (members) even though its Class_Initialize has not been called. We can prove that by placing a MsgBox in Class_Initialize. If we then open the attached demo database and go to an existing or new Module, object or otherwise, we get IntelliSense without the Class Module first being called. So, in this case, IntelliSense is working with the Class Module to which GV refers even without the actual Class Module being initialized.

So, IntelliSense works off the basic data structure of that Class and does not require the initialization of the data in that Class.

2.
Initialization of the data in that Class occurs only on the first call to any of the data required from that Class. In essence then, the above GV instance of clsGlobals will only have its Class_Initialize called if the instance of that GV = Nothing.


Speed:
During tests, a reference to one of the Class Module variables (members) took 170 milliseconds for 1 million cycles. For the same number of cycles an equivalent DLookup took 450000 milliseconds. So it ends up being ~ 2600 times faster to reference the Class Module variable (member) rather than continually hitting the underlying Table with a DLookup.

If you have any questions, please ask them in the appropriate Forum.

Regards,
Chris.