Excel 2007

I created a regular .xlsm workbook with a user-created function that
calculates the date of the deadline x days from an entered date. The
result depends on whether a weekend intervenes, whether the deadline
would be a holiday (in which case the next day becomes a deadline),
etc. The list of holiday dates, which has to be updated every year,
is found in a named range on a separate sheet in the workbook. The
main function calls a macro, which in turn works out the final
deadline based in part on whether the tentative deadline appears in
the range of holiday dates.

This function turns out to be useful in other worksheets. So I
thought I would store it and the holiday list in an add-in (.xlam) and
keep the orginal .xlsm workbook only for the initial data entry sheet
(date and x number of days gives deadline).

Unfortunately, I can't make this work. I have the add-in loaded and
the references set up properly. I **think** the problem is that the
macro cannot obtain data from the list of holiday dates, now in the
add-in file. Is that worksheet hidden, or simply nonexistent? If
it's hidden, can named ranges on the worksheet be referred to in the
macro stored in the add-in's code? (Another problem: With the holiday
list worksheet invisible and totally inaccessible, apparently, I can't
adjust it year to year.)

What would be a solution? Put the list of holiday dates and central
macro in personal.xlsm? Thanks for any help.

You can refer to anything in an Add-in

The add-in sheets are still there, just hidden.

To see them you must change the IsAddin property from false to true.

Sample code for pulling data from an add-in.

Sub pull_from_addin()
Dim srcerng As Range
Dim trgtrng As Range
Set srcerng = Workbooks("mymacros.xla").Sheets(1).Range("A1:F9")
Set trgtrng = ThisWorkbook.Sheets(1).Range("A1")
srcerng.Copy Destination:=trgtrng
End Sub

You can also add sheets or data to an add-in.

Sub addin_copy()
Dim destWB As Workbook
Dim srcWB As Workbook

Set srcWB = ThisWorkbook
Set destWB = Workbooks("MyMacros.xla")

With destWB
MsgBox .Sheets.Count
.IsAddin = False
srcWB.Sheets("Gord").Copy _
after:=.Sheets(.Sheets.Count)
.IsAddin = True
MsgBox .Sheets.Count
End With
End Sub


Gord

On Thu, 1 Dec 2011 11:50:17 -0800 (PST), wal
wrote:

Excel 2007

I created a regular .xlsm workbook with a user-created function that
calculates the date of the deadline x days from an entered date. The
result depends on whether a weekend intervenes, whether the deadline
would be a holiday (in which case the next day becomes a deadline),
etc. The list of holiday dates, which has to be updated every year,
is found in a named range on a separate sheet in the workbook. The
main function calls a macro, which in turn works out the final
deadline based in part on whether the tentative deadline appears in
the range of holiday dates.

This function turns out to be useful in other worksheets. So I
thought I would store it and the holiday list in an add-in (.xlam) and
keep the orginal .xlsm workbook only for the initial data entry sheet
(date and x number of days gives deadline).

Unfortunately, I can't make this work. I have the add-in loaded and
the references set up properly. I **think** the problem is that the
macro cannot obtain data from the list of holiday dates, now in the
add-in file. Is that worksheet hidden, or simply nonexistent? If
it's hidden, can named ranges on the worksheet be referred to in the
macro stored in the add-in's code? (Another problem: With the holiday
list worksheet invisible and totally inaccessible, apparently, I can't
adjust it year to year.)

What would be a solution? Put the list of holiday dates and central
macro in personal.xlsm? Thanks for any help.