Named Ranges and VBA Add-ins
 

You have the following code in the Workbook_Open event of which workbook -
the XLA or the workbook where you will use the function.

If the workbook that uses the function, have you tried to see if the name is
returning the values you expect

in a cell
=LondonHolidays


In code
msgbox Typename(Range("LondonHolidays"))

are you getting a date or an error?


--
Regards,
Tom Ogilvy





"Mark" wrote in message
om...
Hello Experts,

I need an answer which I have been unable to find on Usenet or msdn.

Headline1:
As an add-in my program does not seem to be able to reference a named
range.

Problem Summary1:
System: NT4/Excel97
Add-in Name - GBCMCROLIB
Sheet Name - LDN - contains 2 ranges with calendar information

I have the following code in Workbook_Open()

ThisWorkbook.Names.Add Name:="LondonHolidays",
RefersTo:="=[gbcmcrolib.xla]LDN!$B$2:$B$24"
ThisWorkbook.Names.Add Name:="NonReportingFridays",
RefersTo:="=[gbcmcrolib.xla]LDN!$G$2:$G$16"

LondonHolidays is used in the following way in the following code
module

Public Function calFunc01_CalculateLondonBusinessDays( _
startDate As String, endDate As String) As Integer

calFunc01_CalculateLondonBusinessDays = Networkdays(startDate,
endDate, _
Range("LondonHolidays")) - 1

End Function

I have tried to call this function from another macro without success
and have got to the point where I have entered 2 dates in adjacent
cells of the ActiveSheet of a new workbook and am calling the function
from a 3rd cell as follows:

=calFunc01_CalculateLondonBusinessDays(A1,A2)

The result is #VALUE!

Before I changed the ranges from hard-coded to names, everything
worked fine. I started with the file as a .xls file before converting
to a .xla and it worked fine then. What happens to named ranges in an
Add-in? How can I get it to work with names?

Thanks everyone,

Mark