I didn't notice anything in particular other than that you didn't declare
your function as a range object, which is a good idea to not only declare
your variables fully (if feasible), but also your functions. The RefersTo
part of your Names.Add Method needs to be in String setting, not in a Range
Object setting., of which you can use the Address Property after the Range
Object such as adding the following to your range object:

..Address(ReferenceStyle:=xlR1C1)

One other thing that I had done early on as I was learning with the macro
recorder, but weeded out later due to issues that I ran into (note, this was
not a bug of Excel), the code used a lot of active objects in it's
recording, which worked for a while, but as I got deeper into the code and
started working with the code, some of those active objects were starting to
cause issues, so I went back, gone through my code and changed what all I
could from the active objects to either actual objects (static) or to
variables which referenced the objects (dynamic).

To get a better understanding of XLAs, you may want to look at the VBA help
file dealing with the AddIn Object. That serves as a good starting point
from the programming side of things. I no longer use XL97 as all of my
users has been switched to XLXP within the last 2 weeks, but I suspect that
the help file is in it. The only thing that I really like about XL97 over
the later versions is the fact that the help files gives more information.
However, I didn't like the bugginess of XL97 that I had to contend with that
XL2K was greatly improved on.

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"Mark" wrote in message
om...
Experts,

I have one specific and one general question about add-ins.

System: NT4/XL97
Files: Swift accord matrix.xls
gbcmcrolib.xla
otcmcrolib.xla

I use an add-in (otcmcrolib.xla) to highlight rows in a worksheet
which meet certain criteria. The criteria are stored in an XLS file
(Swift accord matrix.xls). This XLS file has the following code in
the Workbook_BeforeClose event:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim rngLastCell As Range
Dim iRowNumber As Integer
Dim wksMessageTimings As Worksheet

Set wksMessageTimings = Workbooks("Swift accord
matrix.xls").Worksheets("Sheet1")
'Locate the last cell in the sheet.
Set rngLastCell = rngOp01_IdentifyLastCell(wksMessageTimings)

'Get the row number from this.
iRowNumber = rngLastCell.Row

'Add the range as a name.
ThisWorkbook.Names.Add Name:="MessageTimings", _
RefersToR1C1:=Range(Cells(3, 4), Cells(iRowNumber, 8))
End Sub

rngOp01_IdentifyLastCell is an add-in function which accepts a
worksheet object and returns a range object (the add-in gbcmcrolib.xla
is included in the references of the project).

otcmcrolib.xla contains the following code in a code module:

Dim wbk as Workbook
Set wbk = Workbooks.Open("[path]\Swift accord matrix.xls")

Code which uses one of the names in Swift accord matrix.xls in a
VLOOKUP

wbk.Close

When executing the line of code

Set rngLastCell = rngOp01_IdentifyLastCell(wksMessageTimings)

in the Workbook_BeforeClose() event above I get the following error:

Run time error '91';
Object variable or with block variable not set

When I step through the code, it seems like the Worksheet gets passed
to rngOp01_IdentifyLastCell (ie ws.Application.ActiveCell.Value is the
value in Sheet1!A1 of Swift accord matrix.xls) only the code

Public Function rngOp01_IdentifyLastCell(ws As Worksheet)
Dim lLastRow As Long
Dim iLastCol As Integer

'Error-handling in case no data in the worksheet. _
Needs to be enhanced.
On Error Resume Next

With ws

'Find the last real row
lLastRow = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row

'Find the last real column
iLastCol = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
End With

'Return statement
Set rngOp01_IdentifyLastCell = ws.Cells(lLastRow, iLastCol)
End Function

can't operate on it.

All this code works fine when I open and close the XLS file directly.
It is just when it is opened and closed via the add-in
(otcmcrolib.xla) in the manner described above that it doesn't work.

Can anyone give me any insight?

General add-in question:
Is there a good source of information about add-ins which goes beyond
the introductory 'all you have to do is SaveAs .xla and your Workbook
becomes an add-in and users can't see your data and won't be prompted
for saving' etc etc because I have come across more than a few
infuriating little things like my problem described above and presume
there is a great deal I am missing on the subject?

Thanks everyone,

Mark