Call Public constants from other project

[Michiel via OfficeKB.com]

Hi,

Is it possible to refer to / call a Public constant in another project?
If so, how?

The idea:
I have some constants that change often and are used in alsmost all of my
projects.
I want to store them in one place, and modify them (if needed) in one place,
so they can kick in in the other projects that refer them.

Thanks!

M

 

[Peter T]

Most efficent way is to set a reference to the project containing the
constants and call them directly. However that's not always practical,
another way

' in Constants.xls
Public Const cNUM As Long = 123
Public Const cTXT As String = "abc"

Public Function getConstant(sName As String) As Variant
Select Case sName
Case "cNUM": getConstant = cNUM
Case "cTXT": getConstant = cTXT
Case Else: getConstant = -99
End Select
End Function

'in another project

Sub test()
Dim c
c = Application.Run("Constants.xls!getConstant", "cNUM")
MsgBox c
End Sub


But why not put your constants in cells, eg

c = Workbooks("Constants.xls").Worksheets("Sheet1").Range("A1")

Regards,
Peter T

 

[Michiel via OfficeKB.com]

Hi Peter T.

Thank you this is a great solution, thak you for sharing!

And yes putting them in a workbook could be a solution but I actually intend
to put them in the code attached to Personal.xls. Then it will be always
available and not "pollutingly visible".

M.