Inconsistencies in when .Sheets.Range works

[Vizeey]

Hello,
I have opened a workbook and stored it in a variable ("MTFwb"). I am using .Sheets.Range to set the value of global variables from values in the opened workbook. This works great most of the time. But for some variables this only works when the workbook is open prior to running the macro. I have added the code, and highlighted the code that dont work properly. (I have left out the errorhandler, but have checked that no errors occour).
I have tested that matchRad2RNA and matchRad2DNA have the correct value. But the value for mtf_konsRNA, mtf_280RNA, mtf230RNA, mtf_konsDNA, mtf_280DNA and mtf_230DNA is always set to "0".
I find it weird that .Sheets.Range works perfectly fine just a few lines above where the problem occour. It is also confusing that the code works perfectly fine if the workbook I open as "MTFwb" is open prior to running the macro.
Anyone have any idea as to why this is the case?
Thank you so much in advance!

Function mtf_f()
Dim MTFwb As Workbook, bane As String, matchRadRNA As Variant, matchRadDNA As Variant, matchRad2RNA As Variant, matchRad2DNA As Variant
Set myWb = ThisWorkbook

On Error GoTo ErrorHandler:

' Use Application.Workbooks.Open to open the workbook in a hidden state
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    bane = myWb.Sheets(1).Range("A3")
    Set MTFwb = Workbooks.Open("K:\Sensitivt\Klinikk\20-17947 UtprovendeDiagnostikk_OU\2. Patient\IPD-" & Right(bane, 4) & "\IPD-" & Right(bane, 4) & "_Material Transit Form InPreD NGS_2025.xlsx", ReadOnly:=True, UpdateLinks:=False)
    
'Set the worksheet and range to search
    Set ws = MTFwb.Sheets("MTF")
    Set rad = ws.Range("I:I")
    Set rad2 = ws.Range("A:A")
    
'Finner IPD fra MTF
    mtf_IPD = MTFwb.Sheets(1).Range("A10")
    
'Bruker workingID for RNA og DNA til å finne radene de ligger i
    matchRadRNA = Application.Match(myWb.Sheets(1).Range("D3"), rad, 0)
    matchRadDNA = Application.Match(myWb.Sheets(1).Range("E3"), rad, 0)
    matchRad2RNA = Application.Match(myWb.Sheets(1).Range("D3"), rad2, 0)
    matchRad2DNA = Application.Match(myWb.Sheets(1).Range("E3"), rad2, 0)
    
'Bruker radene funnet over til å sette workid og sampleID
    If matchRadRNA <> 0 Then
        mtf_wrkIDRNA = MTFwb.Sheets(1).Range("I" & matchRadRNA)
        mtf_smplIDRNA = MTFwb.Sheets(1).Range("J" & matchRadRNA)
        mtf_prepRNA = MTFwb.Sheets(1).Range("B" & matchRadRNA)
    End If
    
    If matchRadDNA <> 0 Then
        mtf_wrkIDDNA = MTFwb.Sheets(1).Range("I" & matchRadDNA)
        mtf_smplIDDNA = MTFwb.Sheets(1).Range("J" & matchRadDNA)
        mtf_prepDNA = MTFwb.Sheets(1).Range("B" & matchRadDNA)
    End If
        
'Bruker radene funnet tidligere til å sjekke om TC% er fylt inn for RNA og DNA, returnere sann eller usann
    If MTFwb.Sheets(1).Range("C" & matchRadRNA) <> "" Then
        If MTFwb.Sheets(1).Range("C" & matchRadDNA) <> "" Then
            mtf_TC = True
        Else: mtf_TC = False
        End If
    Else: mtf_TC = False
    End If

'Bruker radene funnet til å returnere konsentrasjoner og renhet
[B]    If matchRad2RNA <> 0 Then
        mtf_konsRNA = MTFwb.Sheets(1).Range("E" & matchRad2RNA)
        Debug.Print MTFwb.Sheets(1).Range("E47")
        mtf_280RNA = MTFwb.Sheets(1).Range("F" & matchRad2RNA)
        mtf_230RNA = MTFwb.Sheets(1).Range("G" & matchRad2RNA)
    End If
    
    If matchRad2DNA <> 0 Then
        mtf_konsDNA = MTFwb.Sheets(1).Range("E" & matchRad2DNA)
        mtf_280DNA = MTFwb.Sheets(1).Range("F" & matchRad2DNA)
        mtf_230DNA = MTFwb.Sheets(1).Range("G" & matchRad2DNA)
    End If[/B]
    
'Close the workbook
    MTFwb.Close False
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
Exit Function

 

[RoryA]

If you are calling this code from a worksheet cell, it cannot open a workbook.

 

[Vizeey]

If you are calling this code from a worksheet cell, it cannot open a workbook.

Thank you for your quick reply, RoryA.
I am calling the code from a button.

 

[RoryA]

Why is it a function, and what was the error handler? (the code as posted shouldn't run at all)

 

[Vizeey]

Why is it a function, and what was the error handler? (the code as posted shouldn't run at all)

I am new to VBA so it is not fully clear to me as to when I should use "Function" vs "Sub". But I reference the function in another module, so I thought it was best to use a function.
The error handler sets all the variables to "1".

 

[RoryA]

If it doesn't return a value, it should be a sub.

Match will not return 0 - it will either return a number greater than 0, or an error value. You should really be testing for that:

If Not IsError(matchRadRNA) Then

and similar for the other 3 tests.

Beyond that, I can't see anything in the code that fits your description of the issue, although you haven't really been specific as to how it's wrong. If the workbook isn't open, your code would error earlier on.

 

[Vizeey]

If it doesn't return a value, it should be a sub.

Match will not return 0 - it will either return a number greater than 0, or an error value. You should really be testing for that:

If Not IsError(matchRadRNA) Then

and similar for the other 3 tests.

Beyond that, I can't see anything in the code that fits your description of the issue, although you haven't really been specific as to how it's wrong. If the workbook isn't open, your code would error earlier on.

The match functions works as they should. The problem is with:

    If matchRad2RNA <> 0 Then
        mtf_konsRNA = MTFwb.Sheets(1).Range("E" & matchRad2RNA)
        Debug.Print MTFwb.Sheets(1).Range("E47")
        mtf_280RNA = MTFwb.Sheets(1).Range("F" & matchRad2RNA)
        mtf_230RNA = MTFwb.Sheets(1).Range("G" & matchRad2RNA)
    End If
    
    If matchRad2DNA <> 0 Then
        mtf_konsDNA = MTFwb.Sheets(1).Range("E" & matchRad2DNA)
        mtf_280DNA = MTFwb.Sheets(1).Range("F" & matchRad2DNA)
        mtf_230DNA = MTFwb.Sheets(1).Range("G" & matchRad2DNA)
    End If

MTFwb.Sheets.Range returns "0", when it should return the value in the opened workbook (MTFwb), in sheet number 1, with the given range.
The workbook is not open prior to running the macro, and then the macro opens it yes. The code then returns "0" for the variables mtf_konsRNA, mtf_konsDNA, mtf_280RNA, mtf_280DNA, mtf_230RNA and mtf_230DNA (which it should not).
But, if I have the workbook open prior to running the macro, the code returns the right values for the variables.

 

[RoryA]

The match functions works as they should.

I think you missed my point. Match will never return 0, so testing for it is pointless.

If the variables contain 0, then they are probably pointing at empty cells. That would suggest that something happens with the source workbook when opened manually that is different (I note that you have told it not to update links, which could be relevant).

 

[Vizeey]

I think you missed my point. Match will never return 0, so testing for it is pointless.

If the variables contain 0, then they are probably pointing at empty cells. That would suggest that something happens with the source workbook when opened manually that is different (I note that you have told it not to update links, which could be relevant).

Ahh okay, thank you. So I should test for error instead of for "0"?
The update links was the issue, changing it fixed my issue.
Thank you for your patience and time RoryA.

 

[RoryA]

So I should test for error instead of for "0"

Correct.

Glad we got it resolved