CODEWITHSUNDEEP
excelvba
MichaelGreene
MichaelGreene

Reputation: 73

How do you store a worksheet reference in a VBA object?

This is going to seem trivial to those of you steeped in Excel object programming but it's beat me.

In the past, I've done the following in Excel's vba to restore the activesheet before exiting a subroutine..

sub foo()
dim cursheet
cursheet = ActiveSheet

someOtherSheet.activate
....
cursheet.activate
end sub

That works fine. I attempted to do something similar using objects and after several different approaches, wrote the following in a new Problem class...

''''''''''''''''''''''
' sheet property
''''''''''''''''''''''
Public Property Get sheet() As Worksheet

  Set sheet = psheet
End Property
Public Property Let sheet(Value As Worksheet)

   Set psheet = Value
End Property

Public Sub saveCursheet()
Me.sheet = ActiveSheet
End Sub
Public Sub activateSheet()
Me.sheet.Activate
End Sub

In my code, I invoke the methods this way...

Sub TallyQuizScore() 
Dim curStudent As Problem 
Set curStudent = New Problem

curStudent.saveCursheet

Worksheets("QuizTallies").Activate
...
curStudent.activateSheet

End Sub

When I attempt to execute curStudent.activateSheet, I get an error saying I need an object. So I reran the calling code and stepped through the saveCursheet method. I see the activesheet get stored but notice that the sheet object disappears as soon as I hit the setter's end property line. I don't know if that's an artifact of the debugger or if the sheet really does get tossed when I hit the end property line but whatever it is, the object is gone when I attempt to reactivate it when I'm done.

The frustrating thing is what I really wanted to write in my caller was curStudent.sheet = Activesheet
and curStudent.sheet.Activate

by somehow inheriting the builtin worksheet methods but that led to a rabbit's warren of code as I tried to make it work.

So three questions:

  1. Why did the sheet I stored in saveCursheet disappear?
  2. What do I need to change to make the code work?
  3. What do I need to do differently from the above approach to make the curStudent.sheet = Activesheet and it's partner, curStudent.sheet.Activate approach work?

Upvotes: 1

Views: 2188

Answers (1)

Doug Glancy
Doug Glancy

Reputation: 27478

You need a module-level variable to store the value while your code is doing other things. Note that it's private.

Also, as caught by ja72, in the case of objects it's Set, not Let:

UNTESTED:

Private m_Sheet as Worksheet

Public Property Get Sheet() As Worksheet
  Set sheet = m_Sheet
End Property

Public Property Set Sheet(Value As Worksheet)
   Set m_Sheet = Value
End Property

Public Sub saveCursheet()
Me.Sheet = ActiveSheet
End Sub

Public Sub activateSheet()
Me.m_Sheet.Activate
End Sub

Upvotes: 1

Related Questions