Can an object reference be public?

maxpotters · March 6th, 2005, 11:46 AM

Hi all,

Look at the following example:

Public Sub set_wrb
Set wrb = ThisWorkbook.Sheets("Sheet1")
End sub

As far as I can see, wrb is only known within the module where it is declared. Even if I declare wrb Public, the object is not known outside the routine set_wrb (Runtime error 424: Object Required).

How can I access wrb outside set_wrb without repeating the Set statement in every routine?

Thanks in advance

-Max

gcianfanelli · March 6th, 2005, 11:53 AM

Declare it as a public function, or property

eg.

Code:

Public Property Get Wrb() As Excel.Worksheet
  Set Wrb = ThisWorkbook.Sheets("Sheet1")
End Property

or

Code:

Public Function Wrb() As Excel.Worksheet
  Set Wrb = ThisWorkbook.Sheets("Sheet1")
End Function

maxpotters · March 6th, 2005, 01:22 PM

this works excellent.

thank you