problem to access sheet from add-in

[Nader]

Hello,

I've createad an add-in (with a function called ratings) in which I get some
value from a sheet that have to be created in order to use my add-in. It
worked perfectly for a few days but now whenever I open my excel sheet and
the use the function from my add-in I get an error because excel cannot find
the sheet I use to make some calculation in my excel file. I think that my
add-in is tryin to the sheet within my add-in ...

Please someone help me ! Thanks in advance.

I use the following code to make sure I can acces a specific sheet in my
add-in:

Dim wSheet As Worksheet

On Error Resume Next

Set wSheet = Sheets("ratings_sheet")

if wSheet is Nothing then
msgbox "i've got a serious problem"
else
msgbox "everything is cool"
end if

 

[Chip Pearson]

Try prefixing the Sheets collection object with ActiveWorkbook.

Set wSheet = ActiveWorkbook.Sheets("ratings_sheet")


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[keepITcool]

Chip's solution may be problematic if your workbook is an addin.

Since an addin is defacto hidden it is very unlikely it will ever be
the activeworkbook. (unless you toggle the IsAddin property.)

Following WILL work:
Set wSheet= Thisworkbook.Sheets("Ratings_sheet")

BUT it may be more practical to use the 'CODEname' of your worksheet.
if that belongs to the same VBAproject iso using worksheet variables

e.g. if the codename of your worksheet = wksRatings

then debug.print wksRating.Range("A1") will work perfectly.

 

[witek]

use thisworkbook.
if you want to refer to addin worksheets.