Concerned_Citizen
Concerned_Citizen

Reputation: 6835

Unable to Access Public Variables - Excel VBA

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?

enter image description here

Upvotes: 1

Views: 472

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

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.

Upvotes: 1

C. White
C. White

Reputation: 800

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).

Upvotes: 1

Related Questions