Access Public Constant from another workbook's vba

[francoisferreira]

Hi, does anybody know how I can refer to a public constant in a different workbook's (which is already open) VBA from the workbook which I am currently working on's VBA?

 

[Legacy 98055]

I see what you are saying from a "logical" point of view. But, in practice, in a VBA project, I usually determine placement by scope. If a worksheet uses a constant, I use a private constant within the worksheet class. If two or more worksheets within the same workbook utilize some constant, then it becomes a workbook friend property. If it is needed by another project, it becomes a public property of the workbook. Bear in mind that the entire VBProject is a property of the workbook. All in all, IMO, it does not really matter unless your code is going to be called from outside of the project. In my own practice, because of the way I have decided to structure my code, right or wrong, I rarely use a standard module period.

 

[pgc01]

Hi Tom

Sorry for the delay, I've been busy these last few days.

Thank you for sharing. That's not my personal choice, I'd use that approach in an OO language but not here. In vba I prefer a more procedural approach. Anyway, this is a choice, you are technically very strong and so I'm sure you chose the approach that works best for you. And, with your approach, I would also pass the constant as a property.

 

[erik.van.geit]

Note that my appoach seems to cause some problems when you change the workbook names (not the proect names)
I just test that and needed to update the reference in VBA

 

[SliderJeff]

Tom,

If you had 60+ constants that you had defined that you needed to use across workbooks, worksheets, etc... does that mean you would need to define 60 different Get_XXX functions which return that constant's value to whichever file needs access to it?

Thanks,
Jeff

 

[SliderJeff]

Tom,

Also, what do you mean by "Create Book1.xls and place this code into the workbook class:"? Do you mean create a new class object in the VBAProject "Book1.xls" and paste the code within?

Thanks,
Jeff

 

[SliderJeff]

Tom,

Also, what do you mean by "Create Book1.xls and place this code into the workbook class:"? Do you mean create a new class object in the VBAProject "Book1.xls" and paste the code within?

Thanks,
Jeff

To answer my own question in the hopes of helping someone else. You need to put the globals and the functions which return their values into the "ThisWorksheet" object of Book1.XLS. And yes, you need to define a function for each global constant which returns that constant's value.

Regs,
Jeff

 

[SliderJeff]

Alternatively, a much cleaner way to do it (provided my MVP Domenic) instead of creating a new function for each global const is as follows:

Private Const FILE_OFFSET As Integer = 23
Private Const FILENAME_COL As Integer = 1

Public Property Get GetConstant(s As String) As Integer
    Select Case UCase(s)
        Case "FILE_OFFSET"
            GetConstant = FILE_OFFSET
        Case "FILENAME_COL"
            GetConstant = FILENAME_COL
        'etc
        '
        '
    End Select
End Property

Regards,
Jeff