Simple question, complicated answer. Why when I run this code from an
add-in does it not clear anything and error on the last line with
"Subscript out of Range"? It's vague I know but it doesn't make any
sense to me.
The code works completely fine when ran separately from the macros
which are part of my addin.


Dim sh As Worksheet
wks = ActiveSheet.Name
For Each sh In ThisWorkbook.Sheets
sh.Activate
If Not sh.Name = "Formulas" Then
Range(Rows(1), Rows(65536)).Clear
ActiveSheet.DrawingObjects.Delete
End If
Next
Sheets("Formulas").Activate

"ThisWorkbook" is not equivalent to "ActiveWorkbook" when running code from
an xla.
ThisWorkbook refers to the xla itself.

Always good practice to explicitly qualify all references to worksheets by
including a workbook reference.

ie. don't use just
Sheets("Formulas").Activate
if you have a specific workbook in mind.


Tim

"Kigol" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Simple question, complicated answer. Why when I run this code from an
> add-in does it not clear anything and error on the last line with
> "Subscript out of Range"? It's vague I know but it doesn't make any
> sense to me.
> The code works completely fine when ran separately from the macros
> which are part of my addin.
>
>
> Dim sh As Worksheet
> wks = ActiveSheet.Name
> For Each sh In ThisWorkbook.Sheets
> sh.Activate
> If Not sh.Name = "Formulas" Then
> Range(Rows(1), Rows(65536)).Clear
> ActiveSheet.DrawingObjects.Delete
> End If
> Next
> Sheets("Formulas").Activate
>

When your code is in the Addin, then ThisWorkbook refers to that addin.

Maybe you wanted:
For Each sh In activeworkbook.WorkSheets
(Notice that I'm looping through the .worksheets, too)

You could do the same without selecting:

Dim sh As Worksheet
For Each sh In activeworkbook.worksheets
if lcase(sh.name) = lcase("Formulas") then
'do nothing
else
.cells.clear
.drawingobjects.delete
end if
next sh
'you may not even need this line--since you didn't change the active sheet.
worksheets("Formulas").activate



Kigol wrote:
>
> Simple question, complicated answer. Why when I run this code from an
> add-in does it not clear anything and error on the last line with
> "Subscript out of Range"? It's vague I know but it doesn't make any
> sense to me.
> The code works completely fine when ran separately from the macros
> which are part of my addin.
>
> Dim sh As Worksheet
> wks = ActiveSheet.Name
> For Each sh In ThisWorkbook.Sheets
> sh.Activate
> If Not sh.Name = "Formulas" Then
> Range(Rows(1), Rows(65536)).Clear
> ActiveSheet.DrawingObjects.Delete
> End If
> Next
> Sheets("Formulas").Activate

--

Dave Peterson

On Jul 6, 5:49 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> When your code is in the Addin, then ThisWorkbook refers to that addin.
>
> Maybe you wanted:
> For Each sh In activeworkbook.WorkSheets
> (Notice that I'm looping through the .worksheets, too)
>
> You could do the same without selecting:
>
> Dim sh As Worksheet
> For Each sh In activeworkbook.worksheets
> if lcase(sh.name) = lcase("Formulas") then
> 'do nothing
> else
> .cells.clear
> .drawingobjects.delete
> end if
> next sh
> 'you may not even need this line--since you didn't change the active sheet.
> worksheets("Formulas").activate
>
>
>
>
>
> Kigol wrote:
>
> > Simple question, complicated answer. Why when I run this code from an
> > add-in does it not clear anything and error on the last line with
> > "Subscript out of Range"? It's vague I know but it doesn't make any
> > sense to me.
> > The code works completely fine when ran separately from the macros
> > which are part of my addin.
>
> > Dim sh As Worksheet
> > wks = ActiveSheet.Name
> > For Each sh In ThisWorkbook.Sheets
> > sh.Activate
> > If Not sh.Name = "Formulas" Then
> > Range(Rows(1), Rows(65536)).Clear
> > ActiveSheet.DrawingObjects.Delete
> > End If
> > Next
> > Sheets("Formulas").Activate
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -

That makes sense. This is most likely the cause of all of my reference
errors in this new addin module. Thanks for the help guys. I ended up
with this. Good enough for my purposes.


Dim sh As Worksheet
wks = ActiveWorkbook.ActiveSheet.Name
For Each sh In ActiveWorkbook.Worksheets
sh.Activate
If Not sh.Name = "Formulas" Then
sh.Cells.Clear
ActiveWorkbook.ActiveSheet.DrawingObjects.Delete
End If
Next sh

Why the .activate?

Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
If Not sh.Name = "Formulas" Then
sh.Cells.Clear
sh.DrawingObjects.Delete
End If
Next sh

I like to compare strings using lcase().

You may have trouble if someone renames Formulas to formulas or FormUlas or
FORMulas or...




Kigol wrote:
>
> On Jul 6, 5:49 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > When your code is in the Addin, then ThisWorkbook refers to that addin.
> >
> > Maybe you wanted:
> > For Each sh In activeworkbook.WorkSheets
> > (Notice that I'm looping through the .worksheets, too)
> >
> > You could do the same without selecting:
> >
> > Dim sh As Worksheet
> > For Each sh In activeworkbook.worksheets
> > if lcase(sh.name) = lcase("Formulas") then
> > 'do nothing
> > else
> > .cells.clear
> > .drawingobjects.delete
> > end if
> > next sh
> > 'you may not even need this line--since you didn't change the active sheet.
> > worksheets("Formulas").activate
> >
> >
> >
> >
> >
> > Kigol wrote:
> >
> > > Simple question, complicated answer. Why when I run this code from an
> > > add-in does it not clear anything and error on the last line with
> > > "Subscript out of Range"? It's vague I know but it doesn't make any
> > > sense to me.
> > > The code works completely fine when ran separately from the macros
> > > which are part of my addin.
> >
> > > Dim sh As Worksheet
> > > wks = ActiveSheet.Name
> > > For Each sh In ThisWorkbook.Sheets
> > > sh.Activate
> > > If Not sh.Name = "Formulas" Then
> > > Range(Rows(1), Rows(65536)).Clear
> > > ActiveSheet.DrawingObjects.Delete
> > > End If
> > > Next
> > > Sheets("Formulas").Activate
> >
> > --
> >
> > Dave Peterson- Hide quoted text -
> >
> > - Show quoted text -
>
> That makes sense. This is most likely the cause of all of my reference
> errors in this new addin module. Thanks for the help guys. I ended up
> with this. Good enough for my purposes.
>
> Dim sh As Worksheet
> wks = ActiveWorkbook.ActiveSheet.Name
> For Each sh In ActiveWorkbook.Worksheets
> sh.Activate
> If Not sh.Name = "Formulas" Then
> sh.Cells.Clear
> ActiveWorkbook.ActiveSheet.DrawingObjects.Delete
> End If
> Next sh

--

Dave Peterson

On Jul 9, 2:09 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> Why the .activate?
>
> Dim sh As Worksheet
> For Each sh In ActiveWorkbook.Worksheets
> If Not sh.Name = "Formulas" Then
> sh.Cells.Clear
> sh.DrawingObjects.Delete
> End If
> Next sh
>
> I like to compare strings using lcase().
>
> You may have trouble if someone renames Formulas to formulas or FormUlas or
> FORMulas or...
>
>
>
>
>
> Kigol wrote:
>
> > On Jul 6, 5:49 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > When your code is in the Addin, then ThisWorkbook refers to that addin.
>
> > > Maybe you wanted:
> > > For Each sh In activeworkbook.WorkSheets
> > > (Notice that I'm looping through the .worksheets, too)
>
> > > You could do the same without selecting:
>
> > > Dim sh As Worksheet
> > > For Each sh In activeworkbook.worksheets
> > > if lcase(sh.name) = lcase("Formulas") then
> > > 'do nothing
> > > else
> > > .cells.clear
> > > .drawingobjects.delete
> > > end if
> > > next sh
> > > 'you may not even need this line--since you didn't change the active sheet.
> > > worksheets("Formulas").activate
>
> > > Kigol wrote:
>
> > > > Simple question, complicated answer. Why when I run this code from an
> > > > add-in does it not clear anything and error on the last line with
> > > > "Subscript out of Range"? It's vague I know but it doesn't make any
> > > > sense to me.
> > > > The code works completely fine when ran separately from the macros
> > > > which are part of my addin.
>
> > > > Dim sh As Worksheet
> > > > wks = ActiveSheet.Name
> > > > For Each sh In ThisWorkbook.Sheets
> > > > sh.Activate
> > > > If Not sh.Name = "Formulas" Then
> > > > Range(Rows(1), Rows(65536)).Clear
> > > > ActiveSheet.DrawingObjects.Delete
> > > > End If
> > > > Next
> > > > Sheets("Formulas").Activate
>
> > > --
>
> > > Dave Peterson- Hide quoted text -
>
> > > - Show quoted text -
>
> > That makes sense. This is most likely the cause of all of my reference
> > errors in this new addin module. Thanks for the help guys. I ended up
> > with this. Good enough for my purposes.
>
> > Dim sh As Worksheet
> > wks = ActiveWorkbook.ActiveSheet.Name
> > For Each sh In ActiveWorkbook.Worksheets
> > sh.Activate
> > If Not sh.Name = "Formulas" Then
> > sh.Cells.Clear
> > ActiveWorkbook.ActiveSheet.DrawingObjects.Delete
> > End If
> > Next sh
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -

The activate is there for debuging purposes. Once I complete sections
of code I comb through them and remove all the excess. For now I want
to see what it is doing as it works. The lcase is a good idea, I will
begin using it. Thanks again

Just a note...

Sometimes, the worse thing your code can do is select or activate a worksheet.
By doing this, you may be hiding errors that will only appear when the sheet
isn't active.

dim myRng as range
with worksheets("sheet9999")
set myrng = .range(cells(1,1),cells(8,9))
end with

will work fine if the sheet9999 is active (and the code is in a general module).

But I've used this technique when I wanted to verify that my code was working on
the correct range (selecting before deleting???).


Kigol wrote:
<<snipped>>
>
> The activate is there for debuging purposes. Once I complete sections
> of code I comb through them and remove all the excess. For now I want
> to see what it is doing as it works. The lcase is a good idea, I will
> begin using it. Thanks again

--

Dave Peterson