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?

 

[cornflakegirl]

You need to set a reference (Tools - References) from one book to the other. See this thread: http://www.mrexcel.com/archive2/69100/80109.htm

 

[RoryA]

You can also use a public property in the ThisWorkbook module of the other workbook to return the constant value. Then you don't need a reference.

 

[erik.van.geit]

Hi,

Assuming you have two workbooks: "test1.xls" and "test2.xls"

open VBA editor

select the project of "test1" and get to the properties (menu Tools or rightclick the project)
name it PROJECT1

select the project of "test2"
get to the reference list via menu tools/references
check PROJECT1

Now you can call the public variable "MyValue"

MsgBox PROJECT1.MyValue

kind regards,
Erik

 

[cornflakegirl]

You can also use a public property in the ThisWorkbook module of the other workbook to return the constant value. Then you don't need a reference.

Ooh sir, please sir, how sir? (I was eavesdropping on someone else's thread the other day, and I thought from that that the only way to do it was with a reference - like in Erik's post.)

 

[Richard Schollar]

Hi Emma

Assume in the source workbook you have defined a constant:

Const MYCONSTANT = 100

In the ThisWorkbook module of this other workbook, define a Property Get:

Property Get ConstantValue()
ConstantValue = MYCONSTANT
End Property

Then in the other workbook you can access this:

Sub GetVal()
MsgBox Workbooks("OtherWorkbook").ConstantValue
End Sub

 

[Legacy 98055]

"how?"

Hi Emma.

Create Book1.xls and place this code into the workbook class:

Private Const MyConstant As Integer = 10

Public Property Get GetConstant() As Integer
    GetConstant = MyConstant
End Property

Save the workbook.

Create another workbook and insert this code anywhere you can run it.

Sub Example()
    'read property from Book1.xls
    MsgBox Workbooks("Book1.xls").GetConstant
End Sub

Run example.

For a better view of how it works, place the cursor within the Example procedure and press F8 several times to watch the execution jump from one workbook to another.

 

[cornflakegirl]

Thanks guys!

(Does this only work if it's a constant?)

 

[RoryA]

No, a property can return anything you like.

 

[pgc01]

Hi all

Now for some controversy (if anyone is still reading this thread).

I think that a property should always be related to the design of the object. It's part of the encapsulation feature, you define the data necessary to the implementation of the object internally.

It's true that from the syntax point of view you can define a property in an object and use for something not related to the design of the object. This is not, however, why the property feature of an object was created for.

From what I understood the OP wants to access a simple constant, nothing to do with the workbook object. If he is working in a physics problem the constant can be the speed of light. I don't see why my workbook object should have a property that has the value of the speed of light. This is not related to the workbook object definition.

I surely prefer either the reference and to declare the constant as public in a standard module (simplest solution), or, if you don't want the reference, to use a normal constant in a standard module and use a function to export its value.