Excel Add-in

[S. Daum]

I developed some code in an Excel Workbook. It all worked fine. I did "Save
as" - and selected "add-in." I'm using Excel 2002. In another sheet - I try
to select this add-in from Tools/Addins. I get errors when my code tries to
reference any sheet in the original workbook. For example a line like this:

Dim xs as Excel.Worksheet
Set xs = Application.Workbooks("MyCodeWorkBook.xls")

This line causes an error - subscript out of range in the add-in but works
from my "code sheet."

It is like the "add-in" does not see it's own workbook - when running as a
add-in. Any advice appreciated.

Steve

 

[Harald Staff]

A workbook is made up of many worksheets. So when you say that a worksheet is a workbook
then you ask for trouble.

 

[ijb]

Steve,
further to Neils comment I do not think you can access worksheets in an
add-in - they do not logically exist

ijb

 

[ijb]

Thanks for that, as I was replying it occured to me that there was no real
reason for them not to exist. I stand corrected.

 

[S. Daum]

This has been fixed. The problem was the file extension. Some code like
this, in the Workbook_Open event solves the problem:

If ThisWorkbook.IsAddin Then
g_sMyWbName = "example.xla"
Else
g_sMyWbName = "example.xls"
End If

then later...

Dim xwb as Excel.Workbook
Set xwb = Application.Workbooks(g_sMyWbName)

Thanks again...