title: How do you store a worksheet reference in a VBA object?
body: <p>This is going to seem trivial to those of you steeped in Excel object programming but it's beat me. </p>
  
  <p>In the past, I've done the following in Excel's vba to restore the activesheet before exiting a subroutine..</p>
  
  <pre><code>sub foo()
  dim cursheet
  cursheet = ActiveSheet
  
  someOtherSheet.activate
  ....
  cursheet.activate
  end sub
  </code></pre>
  
  <p>That works fine. I attempted to do something similar using objects and after several different approaches, wrote the following in a new Problem class...</p>
  
  <pre><code>''''''''''''''''''''''
  ' 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
  </code></pre>
  
  <p>In my code, I invoke the methods this way...</p>
  
  <pre><code>Sub TallyQuizScore() 
  Dim curStudent As Problem 
  Set curStudent = New Problem
  
  curStudent.saveCursheet
  
  Worksheets("QuizTallies").Activate
  ...
  curStudent.activateSheet
  
  End Sub
  </code></pre>
  
  <p>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.</p>
  
  <p>The frustrating thing is what I really wanted to write in my caller was 
     curStudent.sheet = Activesheet<br>
    and
     curStudent.sheet.Activate</p>
  
  <p>by somehow inheriting the builtin worksheet methods but that led to a rabbit's warren of code as I tried to make it work. </p>
  
  <p>So three questions:</p>
  
  <ol>
  <li>Why did the sheet I stored in saveCursheet disappear?</li>
  <li>What do I need to change to make the code work?</li>
  <li>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?</li>
  </ol>