Named Ranges In Custom Functions

I have created an add-in with custom functions that are dependent upon named ranges that I have defined in thespreadsheet part of the Excel Add-In File. When using these functions in a new workbook, the ranges are not available. Is there a better way to save named ranges so that they work as part of an add-in or do I have to accomplish this through VBA code?

Thanks

You will need to reference the named ranges using the ThisWorkbook object.

Thanks for the response guys, it's greatly appreciated.

I'm not that familiar with VBA coding for named ranges. Let's say I have a named range from A1:B60 in Sheet1. How would I add the named ranges using code?

If I was to save this code in an Add-In file, would the range "Bob" then be available to anyone adding this Add-In?

The more I read these responses, the more it makes me think I am looking at this the wrong way. Let me try this again. In my custom functions one of the variables I read in is AGE. There are numerous AGE based 2 column static tables that I would like to effectively vlookup from. I would like the function users to be able to pass in the name of a Table (ie : TABLE1, TABLE2, . . ., TABLE50) and each table has over 100 entries. I was hoping to come up with a suitable solution without having to manually type in each item. I currently have "TABLE1", "TABLE2", . . . as named ranges in the spreadsheet.

Thanks again for the responses

My problem is that I can pass "TABLE1" into the function as long as "TABLE1" is defined in the spreadsheet that is open. I would like "TABLE1" to be defined for any spreadsheet that is opened.

I am trying to avoid

Please Login or Register  to view this content.

I am writing a custom function that reads in variables for Age as well as an Age based lookup table. Each table has a corresponding value for each age (from 0 to 120) and there are at least a dozen different tables to choose from.

In the spreadsheet that I am saving as an Add-In, I named the range Sheet1!$A$1:$B$120 to be "TABLE1" hoping that when I passed "TABLE1" into the function from any other spreadsheet it would take the values from [AddIn.xls]Sheet1!$A$1:$B$120. Is there some other way to do this?