is activating a hidden worksheet a no-no?

[Brian Murphy]

Hello everyone,

I have a VBA routine in an addin that .Activates a hidden worksheet in
the user's workbook.

Is this an unwise thing to do?

Thanks,

Brian

 

[Dave Peterson]

I created a two worksheet (sheet1 and sheet2) workbook. I hid sheet2 and then
ran this:

Worksheets("Sheet2").Activate
Debug.Print ActiveSheet.Name

I saw this in the immediate window:
Sheet1

I added this:

Worksheets("Sheet2").Activate
ActiveSheet.Range("A1").Value = 999

And the 999 was added to Sheet1. I unhid sheet2 and A1 was empty.

I wouldn't activate a hidden sheet and expect it to work the way I want.

But there are not many things that you do in code that needs to work on the
activesheet. Maybe just working on the sheet directly would be sufficient in
your case:

with worksheets("Sheet2") 'still hidden
.range("a1").value = "some value"
end with

 

[Chip Pearson]

It doesn't cause an error but it doesn't do anything. If you activate a
hidden sheet, it does not change the sheet to which ActiveSheet refers. On
balance, it is probably an unwise thing to do, but how unwise depends on
what your code expects after it does activate the sheet.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Brian Murphy]

Thank you Dave & Chip for the replies. I feel privileged that two of
the group's top guys looked at this.

One thing I can say is that a .Activate statement to a hidden sheet
causes the current ActiveSheet to change to the last sheet in the file
(or maybe it's to a sheet next to the hidden sheet).

I've got Sheet .Activate statements in many places in many routines
(to both hidden and visible sheets). Perhaps they were necessary with
earlier Excel versions. Or maybe not.

I'm trying to figure out why a very old routine of mine (10+ years
old) is causing crashes in Excel 2003. Typically the macro runs with
no problem, but if a worksheet in the file is copied manually using
the mouse or Edit menu, then running the macro leads to a crash. Even
though the copied sheet has nothing to do with the macro.

Tonight, after yet another very long session on this, I've found that
a statement that puts a formula in a cell on a visible sheet is
closely related to the crashes.

I just tried unhiding the hidden sheets in the file (all two of them),
and it didn't make any difference. Actually, it might have made a
small difference. Instead of crashing the first or second time I run
the macro after a sheet copy, it might now take another run or two to
get a crash.

One thing that is definitely common to all the crashes - it has to be
after copying a sheet manually.

I think (hope) that I'll eventually figure this out, but it's sure
taking a long time. I've felt for many years that Excel has deep down
bugs in its code for copying sheets. So the resolution in this case
may be a workaround.

The crash behavior does not appear to happen with Excel 2007.

In my next session, I'm going to remove the sheet .Activate
statements, and double check that all range references are fully
qualified (I'm pretty sure they are, though).

Time for bed,

Brian

 

[Dave Peterson]

You may want to create a test workbook with just enough test data to make the
code work. Then try all your code to see if it works ok in that test workbook.

If it does, then you may want to consider recreating the real workbook.

It sounds to me that the original workbook may be corrupted.

I haven't had that much experience with really corrupted workbooks, but with one
troublesome workbook, I could move a worksheet to another workbook, but I
couldn't copy it to another workbook.

I got lucky and didn't have to recreate the entire workbook--just that problem
worksheet. And that was a pain!

 

[Brian Murphy]

Dave,

I'm following your suggestion about a corrupt workbook. So far it's
certainly within the realm of possibility.

My excel app works with "user workbooks" that contain user inputs and
all results calculated by my app. I of course have lots of "user
workbooks," and so far I've found that only ones containing a certain
type of user data trigger the crash. So I've got a lead to follow.
Which is better than anything else I've come up with. I have rebuilt
a new workbook from a "crashing" workbook, but even the rebuilt file
triggers the crash. Darn! So I've got more work to do.

Brian

 

[Dave Peterson]

I don't know what caused the corruption in my worksheet.

But my gut feeling (no scientific testing here!) was that the worksheet got
corrupted because it had too many comments in it--and maybe because of all the
formatting.

After I recreated the worksheet, the formatting wasn't anywhere as complex and
the comments had been deleted.

I have no real idea if those things caused the corruption, though.

 

[Brian Murphy]

I just did some testing on another computer with excel 2003-SP3. The
result is not good. I'm pretty much back to square one. I thought
the crash happened only with recent file versions containing certain
things. But this time I got the crash with old files.

I need to regroup and figure what to do next. The sequence of steps
required to get a crash are steps I take quite frequently when I used
the app for my own purposes, and year ago I could swear these crashes
weren't happening. I'm really stumped.

Brian

 

[Dave Peterson]

I don't have any more suggestions--except to rebuild a small test version and do
your testing. If it still crashes on that small test version, then at least you
didn't waste too much time rebuilding the entire workbook/worksheet.

 

[Brian Murphy]

Thanks for your help, Dave. It'll be two weeks before I can do any
more on this, and I won't have internet access, either. But then I'll
be determined to fix whatever it is.

Brian

 

[Brian Murphy]

Well, it looks like the trouble may involve putting formulas into
worksheet cells. My routines that do this run fine until a worksheet
has been copied. Then things become unstable and excel may throw up a
variety of run time errors and always crashes either right away or
soon after.

When I put formulas into cells, this is generally what I do:
1. Create a range object to a named range of cells,
eg. Set therange = Range("nameofrange")
2. Use assignment statements to put in the formulas.
eg. therange(1).Formula = "=INDEX(sxl,INDEX(stnum,$A2))"

Should I be doing this some other way?

Sometimes I put a single formula into a single cell.
Sometimes I put a formula into a range of cells with one statement.
Sometimes I use the .Autofill method to replicate formulas throughout
a range.
This all works fine before a sheet is copied manually, but after a
copy leads to crashes.

A really weird thing that has happened several times after a sheet has
been copied and a troublesome macro stops running correctly, is I'll
see a formula in a cell which is correct, suddenly get changed by
Excel to something that is totally wrong.

For instance, this cell formula (the correct one)
=INDEX(length,MATCH(A22,stnum,0))
suddenly changes to
=length A22 stnum 'Roots Damped (3)'!#REF!

This happens while my macro is running, and this is after a worksheet
has been copied manually, and the name of sheet that excel created was
"Roots Damped (3)". It looks like excel stripped out the intrinsic
functions and punctuations, and appended an invalid reference to
something on the copied worksheet. The copied worksheet has
absolutely nothing to do with the macro that is running. This strange
behavior is not precisely repeatable. I have noticed it happening
only a few times through all my work on this.

I an inclined to think that the problem is not a corrupt workbook. I
have been able to produce the crash with workbooks that are years
old. Also, once I do the sheet copy and a macro no longer runs, and
excel is unstable and about to crash, I can save the suspect workbook,
and then close excel (which always crashes excel). Then restart excel
with the workbook that was just saved, and everything is back to
normal until another sheet is copied.

Brian

 

[Dave Peterson]

I've used all those techniques and they work ok for me (filling cells with
formulas).

I don't think I've seen my formulas get changed like yours did. But if I did
use an invalid name, then I could get funny results--but they were reproducible.

When you tried this stuff, did you try it against a brand new workbook to see if
it worked there?

I don't have any real insight to share with you.

Sorry.

And good luck. If you find a solution (even rebuilding), please post back.
Then google will have the answer and it may help the next victim(!).

 

[Brian Murphy]

Dave,

I have created a small workbook that contains one short macro that
crashes excel. It is repeatable, although excel doesn't crash in
exactly the same way every time. If you want to try it, here's what
to do.

Download www.xlrotor.com/temp/book6.xls
When you open the file the active sheet will be Roots UCS
Press Alt-F8 and then Enter to run the macro named "aaa".
You won't see anything happen, but the macro rebuilds a bunch of cell
formulas on another sheet.
Then use your mouse to make a copy of the sheet Roots UCS.
Run the aaa macro again (sometimes 2 or 3 runs may be required), and
you'll get a run time error.
The r/t error is usually on either the clearcontents line or the
autofill line.
If you try to close the workbook and close Excel, excel will crash.

The sheet Roots UCS has nothing to do with the macro, and it contains
no cell formulas or any named ranges or any conditional formatting.

The macro is short and simple.
It runs fine before copying the sheet.
The macro code is shown below.

If you think I should repost this in a new thread focused on the crash
behavior, please let me know.

Cheers,

Brian

Sub aaa()
Dim nbm%
Dim stnum As Range, oda As Range, odb As Range, idb As Range
Dim geobeam_no As Range, geox As Range, geoa As Range, geob As
Range, geoam As Range, geobm As Range
Dim geospring_no As Range, geospring_x As Range, geospring_y As
Range
Dim a As Range, b As Range

10 nbm = Range("stnum").Count '\number of beams on the shaft input
sheet

'\get object pointers for the required ranges on the GeoPltData
sheet.
20 Set geobeam_no = Range("geobeam_no")
30 Set geox = Range("geox")
40 Set geoa = Range("geoa")
50 Set geoam = Range("geoam")
60 Set geospring_no = Range("geospring_no")
70 Set geospring_x = Range("geospring_x")
80 Set geospring_y = Range("geospring_y")
90 Set stnum = Range("stnum")
100 Set oda = Range("oda")

'\Clear out the ranges, except first beam.
110 Set a = geobeam_no(1)
120 Set b = geoam(geoam.Count)
130 Range(a, b).ClearContents

'\Rebuild the formulas
140 geobeam_no(1).Value = 1
150 geox(1).Formula = "=INDEX(sxl,INDEX(stnum,$A2))"
160 geox(2).Formula = "=B2"
170 geox(3).Formula = "=INDEX(sxl,INDEX(stnum,$A2)+1)"
180 geox(4).Formula = "=B4"
190 geox(5).Formula = "=B2"
200 geoa(1).Formula = "=INDEX(ida,$A2)"
210 geoa(2).Formula = "=INDEX(oda,$A2)"
220 geoa(3).Formula = "=C3"
230 geoa(4).Formula = "=C2"
240 geoa(5).Formula = "=C2"
250 geoam(1).Formula = "=-C2"
260 geoam(2).Formula = "=-C3"
270 geoam(3).Formula = "=-C4"
280 geoam(4).Formula = "=-C5"
290 geoam(5).Formula = "=-C6"

300 Set a = geobeam_no(1)
310 Set b = geoam(6)
320 If nbm >= 2 Then Range(a, b).AutoFill
Destination:=Range(geobeam_no(1), geoam(nbm * 6)), Type:=xlFillDefault

330 Range(geobeam_no(1), geoam(nbm * 6)).Calculate '\must
evaluate formulas after the autofill

End Sub

 

[Dave Peterson]

I got the same crash.

But if I deleted that chart, then I could copy lots of times and no crashes.

You may want to see if recreating the chart clears the error--or just bite the
bullet and do the chart differently (create it after you do the copy???).

 

[Brian Murphy]

Hmmmm. Ever since excel 97, excel has had problems with sheet copying
when it involves charts which display data using defined names for the
XValues and Values ranges.

The chart that's in the small test file is always present in the
user's workbook, and it does use defined names, and those ranges are
being cleared out and refilled with formulas by the macro that's
crashing.

So I'll work on how I do the chart or the ranges of formulas, and
maybe come up with something that eliminates the crashes. I'm
starting to see some light at the end of the tunnel.

I'll post back when I have more info.

Thanks,

Brian