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?

 

[xenou]

What kind of range are you naming that you'd use in workbooks that don't yet exist?

Seems a bit unusual.

Alex.

 

[djt76010]

There are a number of age based tables that contain values for each age. We need to be able to pass in the name of the table into a custom built function that will be saved as an Add-In. Is this possible?

 

[RoryA]

If the function and the named range are both in the add-in then there's no need to pass the range as an argument to the function unless I missed something?

 

[djt76010]

I tried just having the named range in the Add-In file, but then the range is not available globally (in other workbooks).

 

[RoryA]

But it is available to the functions in the add-in, which is where I thought you needed access to it?

 

[djt76010]

I'm not sure I am explaining this properly . . . let me try again.

In my Add-In file I am writing custom functions ("MyFunction") and there are named ranges ("Table1", "Table2", . . . ). Once I add the Add-In to Excel, I would like to be able to open any spreadsheet and have =MyFunction(Age, "Table1") access the correct value from "Table1", but it doesn't work. I would like to somehow make "Table1" available to all spreadsheets that I open.

Thanks for the responses

 

[mikerickson]

If Table1 is a range in the addin, you might write a UDF that returns the range of Table1.

Function Table1Range() as Range
    Set Table1Range = Range("Table1")
Exit Function

Table1Range() can be used by any workbook.

 

[RoryA]

Sub MyFunction(lngAge as Long, strTableName as string)
Dim rngTable as Range
' Get table from the add-in workbook
Set rngTable = ThisWorkbook.Names(strtablename).RefersToRange
' do whateve you need
'....
End Function

is the sort of thing I meant.

 

[djt76010]

Thanks so much for the help . . . you guys are lifesavers!!!