moved macros to new book now they don't work

[lallen]

This is my third attempt to post this problem. My apologies for any
duplication.

I moved some macros to a new workbook (Excel 2003), so they could be shared
amongst multiple workbooks. In order to run the code in the new location, I
changed the worksheet_activate sub in the originating sheet as follows:

from Call Macro1("Parm1")
to Application.Run "'_Workbook2.xls'!Macro1(""Parm1"")"

Now some of the code doesn't work. For example:

With Worksheets("Sheet1")
.Columns("A:I").ClearContents ' does nothing
.Cells(10, 3) = "line 1" ' works
End With

Any suggestions would be greatly appreciated.

 

[lallen]

Please ignore this post. I found the original posting.

For some reason, the Search isn't finding my recent postings, and I'm not
being notified of responses.

 

[Dave Peterson]

You had some responses at your original post.

I use google to search groups.

http://groups.google.com/advanced_search?&as_ugroup=microsoft.public.excel.*

Give it enough information (you as the author, maybe a keyword or date) and
you'll find it.

======
I don't remember you getting a definitive response, though.

And I'm not sure if anyone could.

But I'd add a line to your code for testing to see if you're processing the
correct worksheet.

With Worksheets("Sheet1")

msgbox .range("a1").address(external:=true)

.Columns("A:I").ClearContents ' does nothing
.Cells(10, 3) = "line 1" ' works
End With

That external:=true portion will show you the workbook name, worksheet name and
address ($a$1 here).

 

[GS]

This can sometimes happen if you turn it off (set False) in a procedure your
code goes into break mode. It has no effect if running in break mode. So if
you are using F8 to step through your code it will appear to not be working.
Understand that in this case it's been disabled and so is normal behavior.

Some other things to know about using ScreenUpdating:
Besides hiding screen activity while code is running, that in itself makes
your code run faster because screen activity is suspended and so doesn't
require refreshing as code executes. Another setting that commonly
accompanies this is Calculation. Even though screen activity is temporarily
suspended, Excel is still calculating whenever cell values change. It's
better to turn that off until all the work your macro is doing is finished,
then turn it on and force a calculation so values update. (A definite 'plus'
when running long procedures or processing large amounts of data)

Excel always turns ScreenUpdating back on after the procedure that turned it
off ends. If this procedure was called from another procedure that also
turned it off then it may have no effect until that procedure ends or turns
it off, ..and so on up the call stack.

It's good practice to control turning it off/on as a 'wrapper' around lines
of code that you want to hide any screen activity that may occur while it's
executing. It's easy to lose track of when you have it on or off, or if Excel
turned it off when you wanted it on. It can get very confusing and makes
debugging a harder chore than necessary. Even though Excel will turn it on
when procedures end, you should get into the habit of controlling this in
your code before&after those code segments that make changes to the UI that
you want to hide until the task is done.

Example:
Here's a reusable procedure that you can call to toggle the settings if you
want to save the typing in every procedure you need this in:

Public Sub EnableFastCode(Optional SetFast As Boolean = True)
With Application
If SetFast Then
.ScreenUpdating = False
.Calculation = xlCalculationManual
Else
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.Calculate '//update changes
End If
End With
End Sub

To use it in a procedure:
'some code...

'Turn off Calculation/ScreenUpdating
EnableFastCode
'do stuff you want to hide screen activity for...
'changes to cells or UI...
'open/close workbooks...
'process lots of data...
'Turn on Calculation/ScreenUpdating, & recalculate
EnableFastCode False

'finish up...

You might want to read up on the various contexts that the Calculate method
can be used so you fully understand what global effect it has, as well as how
to use it on specific worksheets or cells.

HTH
Garry

 

[GS]

Change "..or if Excel turned it off when you wanted it on.""
to read "or if Excel turned it on when you wanted it off."

Sorry about that!

 

[GS]

Sorry!!
Not sure why my post showed up in this thread. (I'm using the website UI not
my news reader)