Excel VBA - Use Named Range as Parameter in a VBA Function

Question

Trying to use Named Ranges as parameter in a function but facing issues when I execute the function in a worksheet different that where the Named Range is located.

Tried to search within the forum but couldn't find a solution as most posts have the Named Range or the Worksheet where same is located hard-coded in VBA. Would like to avoid this limitation as the function is supposed to be working with different Named Ranged from different Worksheets...

The following code works fine when entered in the same worksheet as the selected Named Range. Unfortunately it falls apart when the selected Named Range is in another worksheet (even though all ranges that I'm using as parameters are "Workbook specific").

In this particular line:

Set FullRange = NamedRange

Have been experimenting for some time trying to create a reference that would work from any worksheet but unfortunately with no luck...

Any help will be much appreciated!

The concept is to declare a Named Range as parameter. In turn, the function finds this Named Range and creates a new range from a part of it (see SpecificRange). Finally it performs certain calculations with the data from this new range. All of these work fine but only in the same worksheet... Here is the code:

Function myResult(NamedRange As Range, Vessel As String, FromDate As Date, ToDate As Date)

'declare variables in addition to the function parameters declared above
Dim SpecificRange As Range
Dim FullRange As Range
Dim Result As Double
Dim i As Byte

'find the row within the declared "NamedRange" range which contains information for the declared "Vessel"
Set FullRange = NamedRange
Set SpecificRange = Range(FullRange.Find(Vessel, , xlValues, xlWhole).Address, FullRange.Find(Vessel, , xlValues, xlWhole).Offset(0, FullRange.Columns.Count - 1).Address)

i = 1
Result = 0

For i = 1 To FullRange.Columns.Count - 2
    If FullRange(2, i) = "Date" Then
        With WorksheetFunction
            Result = Result + .Max(0, .Min(ToDate, SpecificRange(1, i + 2).Value) - .Max(FromDate, SpecificRange(1, i).Value)) * SpecificRange(1, i + 1).Value
        End With
    End If
Next

myResult = Result

End Function

Many thanks!

+++++

To add some more details, please note that when entered in a worksheet different than where the NamedRange is located, the function returns zero (0.00) and not an error.

Also, when I have the exact same function (a) in the worksheet where the NamedRange is located (say "Sheet1") and (b) in another worksheet (say "Sheet2"), then, when I run the function in Sheet1, the function in Sheet2 is updated correctly! But when I run the function directly in Sheet2 it returns zero...

It seems like it cannot locate the NamedRange when the relevant worksheet is not active...

Here is a screeshot: https://i.stack.imgur.com/RpHf3.png

The Named Range is entered as a parameter by the user in the function (see first parameter) The second parameter in the function (ie Vessel as String), refers to the first column shown in the screenshot.

So when the user enters the formula, the function finds the NamedRange and then creates another range (ie SpecificRange) which is essentially the row where the second parameter was found (Criteria 4 in the example screenshot).

Then it is just a matter of calculations based on the remaining two parameters but this does not seem relevant to this issue.

Answer 1

You are collecting the named range successfully, your issue is that later in your code you are setting a range based on Address, but not specifying the sheet. In other words, your code is looking at ActiveSheet.Range(.ADDRESS).

You can actually collect the sheet from a range variable, here's an example: Set WS = ThisWorkbook.Sheets(NamedRange.Parent.Name)

UPDATED v3 with a few more comments (PGC:)

Function myResult(NamedRange As Range, Vessel As String, FromDate As Date, ToDate As Date)

'declare variables in addition to the function parameters declared above
Dim SpecificRange As Range
Dim FullRange As Range 'PGC: not sure you need this
Dim Result As Double
Dim i As Long 'PGC: use long instead of byte https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long/51689021#51689021
Dim WS As Worksheet 'PGC: this will be used to narrow your approach.


'find the row within the declared "NamedRange" range which contains information for the declared "Vessel"
Set FullRange = NamedRange 'I don't think you need to change this variable. Using NamedRange should work if you use below.

'PGC: set the WS to be the parent of the range you're working with:
Set WS = ThisWorkbook.Sheets(NamedRange.Parent.Name)

'PGC: since you're using the address text to set the range (probably not ideal), you'll need to specify which WS this Range is on.
Set SpecificRange = WS.Range(FullRange.Find(Vessel, , xlValues, xlWhole).Address, FullRange.Find(Vessel, , xlValues, xlWhole).Offset(0, FullRange.Columns.Count - 1).Address)

'PGC: alternative untested approach that avoids using the address property that is probably best method:
'Set SpecificRange = Range(FullRange.Find(Vessel, , xlValues, xlWhole), FullRange.Find(Vessel, , xlValues, xlWhole).Offset(0, FullRange.Columns.Count - 1))


i = 1
Result = 0

For i = 1 To FullRange.Columns.Count - 2
    If FullRange(2, i) = "Date" Then
        With WorksheetFunction
            Result = Result + .Max(0, .Min(ToDate, SpecificRange(1, i + 2).Value) - .Max(FromDate, SpecificRange(1, i).Value)) * SpecificRange(1, i + 1).Value
        End With
    End If
Next

myResult = Result

End Function