CODEWITHSUNDEEP
excelvba
rohrl77
rohrl77

Reputation: 3337

Excel Class Object containing range looses data once source range in different workbook is closed

I want to copy a range into a class object i have written. This works as long as the range source object (workbook) is open. As soon as I close the range source object, my class looses the information it has for that object... other information in the class is maintained.

Why does this happen? Shouldn't the range have been copied into the class object? Is there anyway to maintain the range information in the class with the source object closed?

Here my example. Copy this into a module:

Sub Test_Class()
Dim cls As Class1
Dim arr
Set cls = New Class1

Set cls.myrange = ThisWorkbook.Worksheets(1).Range("E9:E12")
arr = cls.myrange 'Works
ws01.Range("G9:G12") = arr

Set cls = Nothing
Set cls = New Class1
Set cls.myrange = Workbooks("Other").Worksheets(1).Range("E9:E12")
Workbooks("Other").Close
arr = cls.myrange 'Fails on this line because object is lost
ws01.Range("G9:G12") = arr

End Sub

Copy this into a class module called Class1:

Option Explicit

Private m_objmyrange As Object

Public Property Get myrange() As Object
    Set myrange = m_objmyrange
End Property

Public Property Set myrange(ByVal objNewValue As Object)
    Set m_objmyrange = objNewValue
End Property

Upvotes: 1

Views: 35

Answers (1)

Alex K.
Alex K.

Reputation: 175748

Set cls.myrange = ThisWorkbook.Worksheets(1).Range("E9:E12")

This assigns a reference to the range the variable myrange rather than a copy.

As myrange points to the Range when the Range goes away so does your reference.

You can't store an instance of a Range on its own - it can only exist in relation to a WorkSheet, If you want a copy of the data in the range you can copy it to a different sheet or get a 2D array using a Variant that contains the data.

Upvotes: 1

Related Questions