CODEWITHSUNDEEP
excelvbaglobal-variables
James
James

Reputation: 356

Global variables cleared in Workbook_Open Event

I am trying to set some default global ranges that are set during the Workbook_Open() method.

I have drilled down to the following simple example that fails.

I have a global range variable and the Workbook_Open() sub in the "ThisWorkbook" module in the Microsoft Excel Objects folder (see picture for reference). It looks like the following:

 Public testCell As Range
 --------------------------------------------------------------------------
 Public Sub Workbook_Open()
 Set testCell = Range("F1")
 End Sub

I then try to access this assigned variable in a sheet module sub:

 Public sub mySub()
 testCell.Value = "Hello World!"
 End Sub

On trying to execute the above I get a '424' Object Required error. Are there any obvious errors in approach or syntax?

For reference here's my file structure:

enter image description here

Thanks in advance.

Upvotes: 1

Views: 115

Answers (1)

rohrl77
rohrl77

Reputation: 3337

You have to insert the following line into your code module Module1: 

 Public testCell As Range

After that, your code should work. The reason for this is explained in the linke that @SJR provided. As he already mentioned, public declarations of variables can be placed in code modules, not workbook or worksheet objects.

Upvotes: 1

Related Questions