I am working on a rather large Excel application via VBA. I want to place all my public variables in one module. Then I would assign and use those variables in my other modules in the same workbook. Here's what I have.
Option Explicit
Public wdApp As Word.Application
Public wdDoc As Word.Document
But when I tried to assign those variables in my other modules, I got the following message. Shouldn't public variables be accessible to all modules? What am I missing?
The declarations need to be in a standard module, not in any of the worksheet/workbook/class modules, otherwise they are treated as public properties of a class (namely the worksheet, workbook, or class).
You're missing the concept of objects, which are instances of a class module.
I'm betting my shirt that you've written that code in a class module (note, ThisWorkbook and Sheet42 modules are class modules).
Add a new standard module (.bas), and move your global/public variables there.
In order to access the public members of a class module, you need to New up an instance of that class first.
