Addin vs. Embedded references
 

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

Addin vs. Embedded references
 

"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" wrote in message
ups.com...
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

Addin vs. Embedded references
 

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

Addin vs. Embedded references
 

On Jul 6, 5:49 pm, Dave Peterson 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

Addin vs. Embedded references
 

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 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

Addin vs. Embedded references
 

On Jul 9, 2:09 pm, Dave Peterson 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 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

Addin vs. Embedded references
 

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