moved macros to new book now they don't work
in [Programming]
|
From: lallen on 27 Apr 2010 19:40 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.
From: lallen on 27 Apr 2010 20:20 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. "lallen" wrote: > 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.
From: Dave Peterson on 27 Apr 2010 20:27 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). lallen wrote: > > 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. -- Dave Peterson
From: GS on 28 Apr 2010 02:54 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
From: GS on 28 Apr 2010 03:01
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! |