1

I try to access public variables in other Excel files (which are referenced) without hard coding the excel file/VBA project and need help in achieving this. I need the qualification as multiple files with similar variables can be open.

Assume you have a VBA project Test(Test.xlsb) which defined a public variable myVar.

In another VBA project (which has a reference to Test) I can access this variable , e.g.

debug.print Test.myVar

However I have been unable to achieve the same without using the qualifier Test. I tried to use aworkBook, VBProject and Reference as qualifier but none of these options works out. I also cannot assign the object addressed by the code name to a variable. The code set myObj = Test throws a compiler error, while set mySheet = Test.Sheet1 does work.

Any help appreciated.

Dschuli
  • 309
  • 3
  • 10

1 Answers1

0

It appears that you need to define the public variable outside of the module, so to be placed in the worksheet object, outside of any functions.

A similar question is asked here Using Excel VBA variable for multiple workbooks

So, if you place Public myVar as String (assumng the type is string) in the external workbooks' ThisWorkbook and set your object to the workbook as you have been doing, you can then achieve this:

 Dim wb As Workbook
 Set wb = Workbooks("Test.xlsb")
 MsgBox wb.myVar
Joe
  • 616
  • 2
  • 12
  • 27
  • Thx. Your approach seems to be working. It does, however, not work for custom datatypes, as I cannot define them in an object module (sigh). – Dschuli Aug 22 '19 at 12:17
  • Yes, I think you might struggle with that one. If there are regular types that you wish to extract from your custom type, could you create a sub in the child workbook (e.g. `Sub SetGlobalVars()`) that gets your required variables and allocates them to variables of regular type in ThisWorkbook, then call this procedure from your parent one using `Application.Run "'Test.xlsb'!SetGlobalVars"` first. You can then reference the allocated variables using the original method above. – Joe Aug 22 '19 at 12:40