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!