Custom Functions

[djt76010]

I am writing some custom functions to be saved in an Add-In file, but am having trouble saving named ranges. I would like named ranges in the Add-In file to be available in other spreadsheets. Is there a way to do this? Or is there a way to take a range from Excel and save it into a globally available named range using VBA?

 

[djt76010]

Assuming that "Table1" is the named range I want passed through the function, I have got the code to work as follows

=MyFunction(Age,"Table1")

How do I get it to work so that it accepts text as well as a text from a cell reference? i.e. :

=MyFunction(Age,A1)

if cell A1 contains the text "Table1"

 

[RoryA]

It should do that by default. Is it not working? Are you getting an error?

 

[djt76010]

In the function dialog box, next to the Table variable that includes the reference to cell A1 the value is showing up as "Table1", but it is returning a "#VALUE" error.

 

[RoryA]

What is the code you are using? Have you tried stepping through it to see where the error is occurring? (put a breakpoint in and then enter the function in a cell)

 

[djt76010]

Here is the code

Function MyFunction(Age, Table)

    Dim MyRANGE As Range

    Set MyRANGE = ThisWorkbook.Names(Table).RefersToRange
   
    MyFunction = Application.VLookup(Age, MyRANGE, 2, False)

End Function

I tried the breakpoint but I couldn't figure out much other than it doesn't seem to like the named range coming through a cell reference.

 

[djt76010]

Any thoughts??? I am still unable to make my function work with the cell reference to A1 when A1 contains the text "Table1"

 

[mikerickson]

As I understand it, the text "Table" is in A1 and you want the UDF myFunction to act reference that range in the formula =MYFUNCTION(12,A1)
You could try specifying the data type

Function myFunction(Age as Double, Table as String) as Variant

 

[djt76010]

That seems to do the trick. Thank you so much

 

[djt76010]

While this fixes the problem, it causes my "IsMissing" tests for my optional variables to stop working. Is there any way around this?

 

[RoryA]

What optional variables? There aren't any in the code you posted.