VBA UDF Named Range

[djt76010]

I have created a .xlam file with a tab named 'Tables' in the spreadsheet that contains named ranges that are passed into the various UDFs that I have created. If the value passed into the function through 'Table' or 'Table2' does not point to a valid named range the function returns the #VALUE! error and gets hung up on the line below assigning a range to 'RANGE_1' or 'RANGE_2'. In this case I would like the function to indicate which named range it has a problem with. How do I test to make sure the named ranges are valid?

Function test_function(Number, Optional Table As String = "missing", _
                               Optional Table2 As String = "missing")
    .
    .
    .
    If Table <> "missing" Then
        RANGE_1 = ThisWorkbook.Sheets("Tables").Range(Table).Value
    End If

    If Table2 <> "missing" Then
        RANGE_2 = ThisWorkbook.Sheets("Tables").Range(Table2).Value
    End If
    .
    .
    .
End Function

 

[djt76010]

Bump

 

[pgc01]

Hi

This is a simple test to see which name causes the error.

Function TestN(s1 As String, s2 As String)
Dim v1 As Variant, v2 As Variant
 
On Error Resume Next
 
v1 = ThisWorkbook.Sheets("Sheet1").Range(s1).Value
If Err > 0 Then
    TestN = "Error in first Name"
    Exit Function
End If
 
v2 = ThisWorkbook.Sheets("Sheet1").Range(s2).Value
If Err > 0 Then
    TestN = "Error in second Name"
    Exit Function
End If
 
On Error GoTo 0
TestN = "No errors in name"
End Function

 

[djt76010]

Thanks for the help. Is there a way to exclude named ranges like "A1" that by default point to cells in Excel. All of the named ranges that I would like to reference cover multiple cells.

 

[shg]

Why not just pass the ranges by their names -- then Excel will tell you if they are not recognized (you'll get a #NAME? error).


Also, if you're using a UDF and pass range names as strings, Excel will see no dependency, and so will not recalculate automatically when they change.

 

[djt76010]

My problem is that I am making the UDF for other people in my office to use. I know what the #NAME error means and how to fix it, but I would like to provide other users an error message that will point them to the source of the problem.

 

[pgc01]

Why not just pass the ranges by their names -- then Excel will tell you if they are not recognized (you'll get a #NAME? error).


Also, if you're using a UDF and pass range names as strings, Excel will see no dependency, and so will not recalculate automatically when they change.

Hi shg

Thank you for stepping in. I'm always insisting that the udf should include in its parameters all the info necessary to determine the dependencies and here I am happily passing named ranges as strings. This was really a sad moment.

 

[djt76010]

Here is my problem. There are multiple named ranges contained in the .xlam file and these named ranges each contain 100 numbers. When the user passes a string into the function, if the text contained in that string matches one of the named ranges contained in that file, there are a series of calculations that are performed that depend upon numbers pulled from that particular named range.

 

[shg]

This was really a sad moment. <!-- / message --><!-- sig -->

You can get the name of the range that's passed in the procedure:

Sub x()
    Range("A1:A3").Name = "bob"
    Yessirree Range("bob")
End Sub
 
Function Yessirree(r As Range)
    MsgBox r.Name.Name
End Function

 

[djt76010]

In my .xlam file there is a Tab named "Tables" that contains the named ranges with labels that are passed into my UDF. Let's say "Bob" refers to A1:A3 and "Doug" refers to B1:B3. It is much easier to let people enter "Doug" as a function argument and have that point to B1:B3 than to have them learn that entering B1:B3 will point to the numbers pertaining to Doug saved in cells B1, B2 and B3, especially since in my actual UDF there are 20+ named ranges with 100+ cells each.

Is there a way to test the size of a named range? In the example above I would want any string other than "Bob" or "Doug" to return an error message telling the user that they have entered an invalid name, but "A1" or any default cell name is currently not tripping up the UDF. I was thinking I could test to make sure the named range includes more than one cell.