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

 

[shg]

You can refer to named ranges by name in formulas:

=SUM(bob)

I can't figure out why you would want to pass them as strings.

 

[djt76010]

Is there a VBA function that returns the number of elements in an array?

I can see why you wouldn't to pass in named ranges from the file you are working in, but I am not understanding why it is a bad idea to do this with named ranges in the add in file. These named ranges are hard coded, are not going to change over time, cannot be accessed by the user and contain numbers that will only be used in the UDF calculations. In my example "Bob" will always refer to the same 3 values in cells A1, A2 & A3 in the .xlam file. I am writing functions for my whole office to use and it is much easier to teach people to use the function like this

=MyFunction(A1,B1,"Bob")

or

=MyFunction(A1,B1,"Doug")

than to explain to them "If you want the numbers for Bob, enter A1:A3 . . . but that doesn't refer to A1:A3 in your spreadsheet, it refers to A1:A3 in an add in file that you cannot see. If you want to switch to the numbers for Doug change that to B1:B3."

I realize that the "Bob" and "Doug" example doesn't make much sense, but I was just using your example as a simplified description of what my function is doing. The actual functions that I am writing are far more complex than this with 20 arguments passed into the function (4 of which are these named ranges). I know that this explanation will not make a lot of sense without me describing what these calculations are actually doing, but I have the calculations working well and now I am trying to make the functions more user friendly.

 

[shg]

Is there a VBA function that returns the number of elements in an array?

For a range,

range("bob").cells.count

For an array, see the UBound function.

Good luck.

 

[djt76010]

Works like a charm. Thanks so much for the help.