CODEWITHSUNDEEP
vbams-accessconstants
René Nyffenegger
René Nyffenegger

Reputation: 40499

How do I declare a constant variable with a value of a function call

In a VBA module I have the following declaration of constants:

Private Const const_abc  =  3000
Private Const const_def  =   900
Private Const const_etc  =    42
'  and so on and so forth

Now, I have to initialize these values with a one time function call, ideally something like so

Private Const const_abc = someFunc(18)
Private Const const_def = someFunc( 7)
Private Const const_etc = someFunc( 5)
'  and so on and so forth

Of course, this won't work in VBA. So, is there a common pattern on how to deal with such a requirement?

I probably could go like so

Private const_abc As Double
Private const_def As Double
Private const_etc As Double

sub initConsts()
    const_abc = someFunc(18)
    const_def = someFunc( 7)
    const_etc = someFunc( 5)
end sub

But then I'd have to make sure that initConsts is called which I'd rather not do.

Edit As per the question of S O, I am using MS-Access.

Upvotes: 3

Views: 23059

Answers (3)

Tom the Toolman
Tom the Toolman

Reputation: 165

Public Function White() as Long
   White = RGB(255,255,255)
End function

Private Sub TestIt()
   Debug.Print "White is " & White
   White = 123       ' <-- compile error
End Sub

Upvotes: 1

Andreas Covidiot
Andreas Covidiot

Reputation: 4765

in a one-liner that works with modules and classes alike for pure constant-like access:

Public Property Get myConst() As Integer:  myConst = 3:  End Property

you would use it like this:

Sub test()
    Debug.Print "myConst: " & myConst  'would print: "myConst: 3"
End Sub

and if it has to be initialized with a custom value once, one could do it with a static property and one or many private variables:

Private ci As Boolean  'constants initialized

Private myConst1_ As Integer
Private myConst2_ As Integer


Static Property Get myConst1() As Integer
    If Not ci Then init
    myConst1 = myConst1_
End Property


Static Property Get myConst2() As Integer
    If Not ci Then init
    myConst2 = myConst2_
End Property


Private Sub init()
    'these can come from anywhere:
    myConst1_ = 3  
    myConst2_ = 5 
    ci = True 
End Sub
  • they are initialized on the first access of the first "constant" property
  • if you have to initialize them earlier one could just call the init function earlier (and optionally remove the ci variable and all related lines if it is ensured that the properties are not accessed earlier)

Upvotes: 0

PeterT
PeterT

Reputation: 8557

Create a class that reads the cell and presents a Get-only interface to the value.

Here's a class called ItsMyValueClass

Option Explicit

Private pMyVal As Integer

Public Property Get MyValue() As Integer
    MyValue = pMyVal
End Property

Private Sub class_initialize()
    'pMyVal = Sheet.Range("somewhere)
    pMyVal = 17
End Sub

And here's the code in your module:

Option Explicit

Sub IsItReadOnly()
    Dim valu As ItsMyValueClass
    Dim x As Integer
    Set valu = New ItsMyValueClass
    x = valu.MyValue
    'valu.MyValue = 23   'compile error "Can't assign to read-only property"
End Sub

Upvotes: 3

Related Questions