Declaring a variable public in VBA

[perkin_warbeck]

I declared a Public variable at the top of the Workbook module (immediately after Option Explicit)

and initialized it in Workbook_open.  I added a Debug.Print statement to Workbook_open to make sure the variable was being initialized when I opened the Workbook.  That much is working correctly.

The variable is used by a subroutine in a different module, "module1".  But when that subroutine is called, VBA flags the variable as unknown.  The only way I was able to get it to work was by putting an identical Public declaration at the top of module1.

Nothing I have read indicates that a Public variable must be declared in every module in which it is used.  Is that true?  Or is there something special about the case I described?

[#2]

@perkin_warbeck 

With your permission, if I can recommend. It can help us all if you upload an Excel file (without sensitive data), no picture.

Even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel, on the contrary in some cases.

You could get a precise solution much faster with a file (w/out sensitive data).

This would also be a blessing for all of us, as we can understand the problem much better, a win-win situation for everyone.

It is also helpful to know the operating system and Excel version, as different approaches may be required depending on the version and OS.

Thank you for your understanding and patience

Nikolino

I know I don't know anything (Socrates)

[#3 · Accepted solution]

@perkin_warbeck 

To declare a public variable, do so in a standard module (the kind that you create by selecting Insert > Module) instead of in the ThisWorkbook module. You only need to do this in one module.

Make sure that you use the keyword Public:

Public variablename

You can still initialize it in Workbook_Open in the ThisWorkbook module.

(You can, in fact, declare it in ThisWorkbook, but then you must refer to it as ThisWorkbook.variablename instead of just variablename)

[#4]

@Hans Vogelaar Thank you.  Your solution worked perfectly.