Named range in add-in

Re: Named range in add-in

Ed,

Normally when I reference a named range (when the macro code is in the workbook that has the named range) I use something like the following:

Set rRange = Workbooks([B]ThisWorkbook[/B].Name).Names("MyRange").RefersToRange

If you move that code to an add-in, I would expect to change that bit of code to:

Set rRange = Workbooks([B]ActiveWorkbook[/B].Name).Names("MyRange").RefersToRange

Remembering that the workbook that actually has the named range needs to be active when you call that bit of code via your add-in.

Re: Named range in add-in

Ah...yes, I see where you are coming from. Sorry, I mistook your original question to be "macro" related.

I gather from your example that you are trying to have a set of holiday dates located separately (or "hidden") from the active workbook.

I have done this another way in the past by using a "named array" to hold the dates of public holidays. This is like using a named constant except it is in array format. For example, this might look like:
={39392,39441,39442,39448,39475,39517,39528,39531,39563,39608,39756} if you use serial format and I believe you can also use something like:
={"1-JAN-2001","31-DEC-2021"} etc
You define this array (in Excel 2003, Insert --> Name --> Define) exactly as is with the curly brackets.

Then in your formulas, you can still say:
=IF(D6>0,WORKDAY(D6,HLOOKUP(F6,WhenDue,2,FALSE),Holidays),"")
assuming you named the named array as "Holidays" of course.

This is a bit cumbersome because you need to type (or paste) all this in to Excel but it is quite effective for "hiding" and/or centralising these types of values.

However, if you want to move those holiday dates to a named range in an add-in then I believe the following will work:
1. Create a new spreadsheet with the holidays somewhere on it.
2. Create the named range called "Holidays".
3. Save the spreadsheet as an add-in. Let's call the file "Holidays.xla"
Refer here if you have not done this before:
http://www.ozgrid.com/VBA/excel-add-in-create.htm
4. You can now reference the Holidays named range in the add-in (a) directly or (b) indirectly:
4a. If you make the add-in available to Excel (via Tools --> Add-Ins in excel 2003), you change your formula to:
=IF(D6>0,WORKDAY(D6,HLOOKUP(F6,WhenDue,2,FALSE),Holidays.xla!Holidays),"")
4b. If you do not make the add-in available, you need to know the exact path of where the add-in was placed on the computer and then the formula looks something like:
=IF(D6>0,WORKDAY(D6,HLOOKUP(F6,WhenDue,2,FALSE),'C:\Users\MyName\AppData\Roaming\Microsoft\AddIns\Holidays.xla'!Holidays),"")

Note that if you go down the option 4b path, you will probably find that Excel will want to update the "external links" each time you open the workbook. If you then make the add-in available, Excel will correct the formula but you may end up with "broken" links.

Hope this helps!