How to combine Workbook, Worksheet and Range variables into one Range, in Excel VBA?

Question

Here is an example setting:

Dim wb As Workbook
Dim ws As Worksheet
Dim rn As Range

More specifically in my scenario:

The call:

Select Case CheckInDb(Range("Username"), "\..\_DataBase_\", "UserBase.xlsm", Sheets("UD_Base"), Range("UD_Base[U_ID]"))

The function header:

Function CheckInDb(What As Variant, Folder As String, FileName As String, Ws As Worksheet, Rn As Range)

I wanted to do something like this within the function (after opening the file, etc.): (note: Wb is generated within the function)

CheckInDb = IsError(Application.Match(What,Wb.Ws.Rn, 0))

---

I have tried in several ways to precombine them step by step instead, but did not succeed with any for example

set ws = wb.Sheets("RangeAdding")

would work manually, but Ws=Wb.Ws NOT. How to work around this, to achieve all 3 parts coming from variables?

Adding more information

I will post my full function to show what the real issue is. I have modded it a bit according to your suggestions, and the problem is:

The Workbook only gets opened inside the function, however if it is not open already when I write the full range in the calling it will give me an error, that it does not exist. Therefore I wanted to first ever use the range "live" inside the function, when the reference is already open.

Sub checkUser()
    Select Case CheckInDb(Range("Username"), "\..\_DataBase_\", "UserBase.xlsm", Workbooks("Userbase.xlsm").Sheets("UD_Base").Range("UD_Base[U_ID]"))
        Case True: Range("Status_U").Value = ("Szabad")
        Case False: Range("Status_U").Value = ("Foglalt")
    End Select
End Sub
Function CheckInDb(What As Variant, Folder As String, FileName As String, Rng As Range)
    Dim Wb As Workbook
    Dim wasOpen As Boolean
    Dim File As String, Path As String
    ' Relative path:
    Path = ThisWorkbook.Path & Folder
    File = Path & FileName

    On Error Resume Next
         Set Wb = Workbooks(FileName)
         wasOpen = True
    On Error GoTo 0
    If Wb Is Nothing Then
         Set Wb = Workbooks.Open(File, , True) 'with settings: (File, true,true,  , PW)
         wasOpen = False
    End If

    '...

 CheckInDb = IsError(Application.Match(What, Rng, 0))

    '...

    Select Case wasOpen
            Case True
                'Wb.Save
            Case False
                'Wb.Save
                Wb.Close (False)
    End Select

    Set Wb = Nothing
End Function

Answer 1

Your rn object already knows which Worksheet object it belongs to. (You can use rn.Parent to refer to that worksheet.) And the worksheet knows which Workbook object it belongs to. (You can use rn.Parent.Parent to refer to that workbook.)

So your code should be:

CheckInDb = IsError(Application.Match(What, Rn, 0))

This means there is no need to pass the workbook and worksheet information to your function (because it is inherent in the Range you are passing), i.e.:

Select Case CheckInDb(Range("Username"), Range("UD_Base[U_ID]"))

and

Function CheckInDb(What As Variant, Rn As Range)

---

Based on your edit to the question, which now makes it clear that all the objects in the calling procedure don't exist when you execute the call (and therefore the call fails), you should pass the names of your workbook (as you already do), worksheet and range, i.e.:

Sub checkUser()
    Select Case CheckInDb(Range("Username"), _
                          "\..\_DataBase_\", "UserBase.xlsm", _
                          "UD_Base", _
                          "U_ID")
        Case True: Range("Status_U").Value = ("Szabad")
        Case False: Range("Status_U").Value = ("Foglalt")
    End Select
End Sub

Function CheckInDb(What As Variant, _
                   Folder As String, FileName As String, _
                   wsName As String,
                   rngName As String) As Boolean
    '...
    '... existing code to create wb object
    '...
    Dim rn As Range
    Set rn = wb.Worksheets(wsName).Range(rngName)
    CheckInDb = IsError(Application.Match(What, Rn, 0))