Excel SpecialCells property returns error when empty

Add-in Express™ Support Service
That's what is more important than anything else

Excel SpecialCells property returns error when empty
How can I avoid this eror with an empty sheet. 
Nicholas Glasier


Guest


I'm calling the method: ExcelApp.Activeworksheet.Cells.SpecialCells(xlCellType, xlCellValue)

If there are cells on the worksheet of the type I'm looking for then everything's fine, but if there are none then the following message is returned.

ADX Extensions error (TAdxForm.WndProc(msg= 202, wparam= 0, lparam= 5D001A). No cells were found): the add-in has fired an exception.
No cells were found

According to Microsofts help this method is supposed to return a range object, but even checking to see if there are any special cells to look at (i.e is the range object = nil) returns an error.

Can you tell me what the error message parameters mean? Is there a way to avoid it?

TIA, Nick


Posted 03 Jun, 2008 19:03:08 Top
Dmitry Kostochko


Add-in Express team


Posts: 2887
 Joined: 2004-04-05
Hi Nicholas,

The SpecialCells method may raise an exception. You can use the try except block in your code: 

try
  MyRange := ExcelApp.Activeworksheet.Cells.SpecialCells(xlCellType, xlCellValue);
except
  MyRange := nil;
end;



Posted 04 Jun, 2008 10:37:43 Top
Nicholas Glasier


Guest


So it's an Excel problem not something I'm doing wrong. Thats good to know, I had put in a try except block but I wasn't sure whose fault it was. Thanks Dmitri
Posted 04 Jun, 2008 17:57:24 Top
Nicholas Glasier


Guest


I also find that after using the SpecialCells method, I'm asked if I want to save changes to the workbook when I close it, even though nothing has been changed. Have you noticed this behaviour too? This is with Office 2007

Nick
Posted 05 Jun, 2008 04:13:35 Top
Andrei Smolin


Add-in Express team


Posts: 19210
 Joined: 2006-05-11
Hi Nick,

I assume that Excel marks a workbook as dirty when you change the visual representation of its content. Say, I have a worbook with AutoFilter turned on. Whenever I change its settings (choose some data or sort) it asks me to save the workbook. It's Excel 2003.


Andrei Smolin
Add-in Express Team Leader
Posted 05 Jun, 2008 06:40:55 Top
Nicholas Glasier


Guest


Thanks Andrei. I don't think I'm changing anything, just reading values returned by the SpecialCells method, However, if that info is refreshed by such a call, and is stored in the workbook when it's saved, perhaps that's why Excel says things are changed.

Nick
Posted 05 Jun, 2008 08:19:55 Top