Excel loses data when second workbook is closed

Question

EDIT at the bottom of the question

I have a function in my code, that gets a dictionary of samples, fills in data from another workbook and returns filled dictionary. Everything worked fine, but once I changed the opening of the file to be ReadOnly, I experience problems.

Here is the code (which has been simplified to remove redundant parts):

Function get_samples_data(ByVal instructions As Scripting.Dictionary, ByRef patients_data As Scripting.Dictionary) As Scripting.Dictionary
    'takes a dictionary of samples and fills their data from the file <0 GL all RL>
    Dim wb          As Workbook
    Dim ws          As Worksheet
    Dim data_start  As Long
    Dim data_end    As Long
    Dim rw          As Range
    Dim rw_nr       As String
    
    'open <GP all> in ReadOnly mode based on path and filename in specific cells
    Application.ScreenUpdating = False
    Set wb = Workbooks.Open(ThisWorkbook.Sheets(1).Cells(13, 2).Value2 & ThisWorkbook.Sheets(1).Cells(13, 1).Value2, False, True)
    Set ws = wb.Worksheets("ALL")
    
    'get row nr. of the first and the last sample to export
    data_start = ws.Columns("A:A").Find(what:=instructions("from_sample"), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows).Row
    data_end = ws.Columns("A:A").Find(what:=instructions("to_sample"), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows).Row
        
    'main loop
    For i = data_start To data_end
        Set rw = ws.Rows(i)
        rw_nr = rw.Cells(1, 1).Value
        If rw.Cells(1, 11).Value = instructions("group") Then
            If patients_data.Exists(rw_nr) Then
                Set patients_data(rw_nr) = fetch_sample_data(rw, patients_data(rw_nr))
            End If
        End If
    Next

    'close <GP all> without saving
    wb.Close (False)

    Set get_samples_data = patients_data
End Function

When I debugged, I noticed, that the data is lost on the call of wb.Close(False). Until that point data is intact, but once the source workbook is closed, the data (which is a range object) is turned blank. Not set to nothing, which happens when the data is not found in the source workbook, but all properties of the range object can be seen in debugger, but all have a value of .

Before I changed the openmode to ReadOnly, everything worked and data stayed there. What did I miss? Why are data, stored in a different variable, lost?

EDIT: Fetch sample data indeed returns a range object.

Private Function fetch_sample_data(ByVal rw As Range, ByRef sm As sample) As sample
    Dim data As Range
    
    Set data = Range(rw.Cells(1, 19), rw.Cells(1, 63))
    Set sm.data = data
    Set fetch_sample_data = sm
    
End Function

I tried changing the order of closing and setting the return value, but the error prevails.

Is it so then, that a Range object is always only a reference to a range in a worksheet? If I want the data to stay, do I need to change all Range objects in question to arrays? Or is there a way to create a Range object independent of a workbook (I do not want to copy the range into any sheet in the main workbook carrying the macro)?

Below is the main sub, as @Pᴇʜ asked for it. I will not add the remaining functions, because the whole code is scattered over 1 form, 2 modules and 14 classes (many carrying long methods). The two commented open commands are those that caused everything to work properly. The closing commands were at the end of main sub, so in regards to the comment of @Pᴇʜ, if range object is always only a reference to an actual range of cells, they were available for the whole duration of the program.

Sub RL_creator_GP_main()
    Dim instructions    As New Scripting.Dictionary
    Dim samples         As Scripting.Dictionary
    Dim result_list     As Variant
    Dim rep             As cReport
    Dim scribe          As New descriptor
    
    Application.ScreenUpdating = False
    
    'get instructions from inputboxes (group, from sample, to sample)
    Set instructions = procedures.input_instructions()
    If instructions.Exists("terminated") Then
        Exit Sub
    End If
    
    'get <GP all> and <RL headers> ready
    'Call procedures.prepare_file("GP all.xlsx", pth:=ThisWorkbook.Sheets(1).Cells(12, 2).Value)
    'Call procedures.prepare_file("RL headers.xlsx", pth:=ThisWorkbook.Sheets(1).Cells(13, 2).Value)
    
    'get patients data from <RL headers>, closes the file afterwards
    Set samples = procedures.get_patients_data(instructions)
    
    'get patients data from <GP all>, closes the file afterwards
    Set samples = procedures.get_samples_data(instructions, samples)

Answer 1

because samples is submitted ByRef to get_samples_data you don't need to return it:

Sub RL_creator_GP_main()
    'your code here …

    'get patients data from <RL headers>, closes the file afterwards
    Set samples = procedures.get_patients_data(instructions)
    
    'get patients data from <GP all>, closes the file afterwards
    procedures.get_samples_data instructions, samples 'this call will change the original samples because it is ByRef!

In fetch_sample_data you add a range to your dictionary. But a Range object is only a reference to the worksheet and does not contain data itself. So instead of that turn the range into an array to add the actual data instead of only a reference:

Private Sub fetch_sample_data(ByVal rw As Range, ByRef sm As sample)
    Dim data() As Variant
    data = Range(rw.Cells(1, 19), rw.Cells(1, 63)).Value
    Set sm.data = data
    'again you don't need a function to return the sample as it is ByRef 
End Sub

Finally get_samples_data should be a sub not a function. And call fetch_sample_data as a sub like fetch_sample_data rw, patients_data(rw_nr)

Sub get_samples_data(ByVal instructions As Scripting.Dictionary, ByRef patients_data As Scripting.Dictionary)
    'takes a dictionary of samples and fills their data from the file <0 GL all RL>
    Dim wb          As Workbook
    Dim ws          As Worksheet
    Dim data_start  As Long
    Dim data_end    As Long
    Dim rw          As Range
    Dim rw_nr       As String
    
    'open <GP all> in ReadOnly mode based on path and filename in specific cells
    Application.ScreenUpdating = False
    Set wb = Workbooks.Open(ThisWorkbook.Sheets(1).Cells(13, 2).Value2 & ThisWorkbook.Sheets(1).Cells(13, 1).Value2, False, True)
    Set ws = wb.Worksheets("ALL")
    
    'get row nr. of the first and the last sample to export
    data_start = ws.Columns("A:A").Find(what:=instructions("from_sample"), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows).Row
    data_end = ws.Columns("A:A").Find(what:=instructions("to_sample"), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows).Row
        
    'main loop
    For i = data_start To data_end
        Set rw = ws.Rows(i)
        rw_nr = rw.Cells(1, 1).Value
        If rw.Cells(1, 11).Value = instructions("group") Then
            If patients_data.Exists(rw_nr) Then
                fetch_sample_data rw, patients_data(rw_nr)
            End If
        End If
    Next

    'close <GP all> without saving
    wb.Close (False)
End Sub

---

Background explanation

Calling functions and subs:
First of all the Call statement is not needed. Parameters in functions are always in parenhesis, the function is used to return a value.

Result = MyFunction(Param1, Param2) ' functions return a result and parameters are in parentesis

MySub Param1, Param2 ' subs don't return a result and don't use parentesis

Call MySub(Param1, Param2) ' But with the Call statement they need parentesis

What does ByRef do:
If you declare a parameter ByRef that means you don't submit data to the sub but only a reference (By Reference) to that data in the memory. So if you have the following sub:

Sub MySub(ByVal Param1, ByRef Param2)
    Param1 = 1
    Param2 = 2
End Sub

And use it like

Sub Example()
    Dim Var1 As Long: Var1 = 10
    Dim Var2 As Long: Var2 = 20

    MySub Var1, Var2 'note Var2 is submitted ByRef!

    Debug.Print Var1, Var2 'returns 10,  2 the value in Var2 got changed by MySub without returning anything
End Sub

So when you submit the variabe by reference that means MySub changes the value in Var2 when performing Param2 = 2 because Param2 and Var2 reference the same space in memory. While if you submit ByVal (by value) you actually make a copy of the data in the memory and Param1 and Var1 reference different places in the memory.

That is why you don't need a function to return something if you submit it ByRef you already changed the data in the memory.

So in your code if you declare Sub get_samples_data(ByVal instructions As Scripting.Dictionary, ByRef patients_data As Scripting.Dictionary) then calling it like procedures.get_samples_data instructions, samples makes patients_data and samples point to the same space in memory. So because the data is only once in the memory and there is only 2 links pointing to them any changes made in one of the links actually edits the exact same data in memory. Therefore you don't need to return data.