Saving VBA variables for next time workbook is opened

[Special K]

I have VBA variables of types range, long, and string that I would like to save before the workbook is closed so their values will be preserved for the next time the workbook is opened. I have done some searching and so far the only method I can find for doing this is to assign the variables to the value of a cell in the worksheet. Assume I have a variable active_row declared as:

Dim active_row As Range

I have tried to save this variable to another cell in the workbook using the following code tied to a button:

Private Sub SaveData_Click()
    Sheets("save").Cells(27, 1).Value = active_row
End Sub

The code executes without error, but there is nothing in the cell afterward. I then save the spreadsheet and close it.

I have another button that is supposed to restore all data saved in this manner:

Private Sub RestoreData_Click()
    active_row = Sheets("save").Cells(27, 1).Value
End Sub

When I reopen the spreadsheet and click this button, I receive the following error:

Run-time error '91':
Object variable or With block variable not set

I suppose this isn't surprising given that nothing was copied into the cell that was supposed to save my data, but how can I save VBA variables of types range, long, and string before closing the workbook so they can be restored the next time the workbook is opened?

 

[Fluff]

Try

Private Sub SaveData_Click()
    Sheets("save").Cells(27, 1).Value = active_row.Address
End Sub
Private Sub RestoreData_Click()
    active_row = Range(Sheets("save").Cells(27, 1).Value)
End Sub

 

[Special K]

Try

Private Sub SaveData_Click()
    Sheets("save").Cells(27, 1).Value = active_row.Address
End Sub
Private Sub RestoreData_Click()
    active_row = Range(Sheets("save").Cells(27, 1).Value)
End Sub

Thanks. The save portion now works (I see an text representation of the range in the indicated location), but when I reopen the workbook and click the RestoreData button, I receive the same error:

Run-time error '91':
Object variable or With block variable not set

Note that active_row is declared at the top of the code file (right after "Option Explicit") as:

Dim active_row As Range

Is it possible active_row is not being created before the RestoreData button code is executed?

 

[Fluff]

Oops the second one should be

Private Sub RestoreData_Click()
    [COLOR=#ff0000]Set [/COLOR]active_row = Range(Sheets("save").Cells(27, 1).Value)
End Sub

 

[Special K]

Oops the second one should be

Private Sub RestoreData_Click()
    [COLOR=#ff0000]Set [/COLOR]active_row = Range(Sheets("save").Cells(27, 1).Value)
End Sub

Thanks! I just figured that out before I saw your reply.