Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bad code?
Ok, I am going crazy. Below is a macro I recorded and stored in
Personal.xls, in its entirety. It fixes the garbage that comes in from an html data source that I must use frequently (online enterprise software). It seems to cause problems, and I don't know why, it appears fine and simple. Is there anything wrong with this? Or is this just SOP for Excel and other people have the same problems? I would appreciate any insight. (WinXP, Excel 2003, Dell Optiplex) This is my workflow: I copy/paste data, run the macro, copy the data to a preformated area in the same workbook to look pretty, then delete the raw data. I have a coworker with the same job. She does the same workflow, except doing it slowly without a macro. None of these problems surface for her. Here are the problems I have after running this macro: 1) Redo/Repeat stops working. It says "Repeat Macros" in the Edit menu, but doesn't actually DO anything when selected. It never changes from "Repeat Macros", so the Repeat command is lost til I restart Excel. Menu selection, F4, CTRL-Y, nothing works. 2) It affects the size of the file. I always delete the entire columns where the download was pasted, yet the workbook keeps growing as if I leave the full raw data in there every single time I do this. After a few months, the file has quadrupled in size. 3) It 'sticks' to the file somehow. When I send files to certain companies, their over-zealous security deletes any emails with attachments containing macros. These files do not contain this (or any) macro, it is stored in Personal. Yet the firewalls claim there is a macro after I run it on these files, and block my emails. Code: Sub Clean() ' ' Clean Macro ' Macro recorded 1/25/2007 ' ' Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False ActiveSheet.DrawingObjects.Delete Cells.Select Selection.UnMerge Selection.WrapText = False Range("B:IV").Select Selection.Style = "Comma" Range("A1").Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bad code?
1) Redo/Repeat stops working. It says "Repeat Macros" in the Edit
menu, but doesn't actually DO anything when selected. It never changes from "Repeat Macros", so the Repeat command is lost til I restart Excel. Menu selection, F4, CTRL-Y, nothing works. Running *any* macro kills the undo stack so you can't undo/repeat. Once it's done, however, it should be back working, just without any entries from before the macro was run. That's what I would expect, and that would be fine. Yet it kills it til Excel is quit. Of course, I'm trying to duplicate it this morning for comparison and it isn't happening. But it certainly did yesterday (and many, many other days), I had to quit Excel to get the functionality back. So apparently it is not 100% of the time. 2) It affects the size of the file. I always delete the entire columns where the download was pasted, yet the workbook keeps growing as if I leave the full raw data in there every single time I do this. After a few months, the file has quadrupled in size. Maybe doing the Unmerge and Wrap on every cell is causing it. I'm not sure on that, but see the macro below. Thanks, I'll try your code, see if it improves. So, UsedRange makes it work on just cells with data/formatting in them? 3) It 'sticks' to the file somehow. When I send files to certain companies, their over-zealous security deletes any emails with attachments containing macros. These files do not contain this (or any) macro, it is stored in Personal. Yet the firewalls claim there is a macro after I run it on these files, and block my emails. You probably have an empty module in the workbook. Open the VBE (Alt+F11) and the Project Explorer (Ctl+R). Make sure there are no modules, userforms, or class modules in the project. Also, open the Microsoft Excel Objects (like Sheet1) and make sure there's no code in any of those. There isn't a module. There are the standard objects, one for each sheet and the Workbook object. All empty. This only started late last year. I don't know if the other companies upgraded hardware (I think I've narrowed that side to Sonicwalls) then, or if it coincided with a Microsoft update.... Thanks for your comments. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bad code?
On Wed, 23 Jul 2008 09:18:38 -0700 (PDT), Spiky
wrote: Running *any* macro kills the undo stack so you can't undo/repeat. Once it's done, however, it should be back working, just without any entries from before the macro was run. That's what I would expect, and that would be fine. Yet it kills it til Excel is quit. Of course, I'm trying to duplicate it this morning for comparison and it isn't happening. But it certainly did yesterday (and many, many other days), I had to quit Excel to get the functionality back. So apparently it is not 100% of the time. Hmm, strange. If you had a Worksheet_Selection change event macro in an open workbook at the time, it would clear the Undo stack every time you clicked a cell. Otherwise, I can't think of what might cause that. If you get any more clues next time it happens, post back. Maybe doing the Unmerge and Wrap on every cell is causing it. I'm not sure on that, but see the macro below. Thanks, I'll try your code, see if it improves. So, UsedRange makes it work on just cells with data/formatting in them? Right. Sometimes Excel doesn't reset the UsedRange property and it's bigger than it needs to be. But it's never smaller and it's almost always smaller than the whole worksheet. You probably have an empty module in the workbook. Open the VBE (Alt+F11) and the Project Explorer (Ctl+R). Make sure there are no modules, userforms, or class modules in the project. Also, open the Microsoft Excel Objects (like Sheet1) and make sure there's no code in any of those. There isn't a module. There are the standard objects, one for each sheet and the Workbook object. All empty. This only started late last year. I don't know if the other companies upgraded hardware (I think I've narrowed that side to Sonicwalls) then, or if it coincided with a Microsoft update.... You know, that reminds me. I get a file from someone who uses OpenOffice that gives me a macro warning and there's no evidence of code in it at all. -- Dick |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bad code?
Well, I've discovered one more detail. Problem #1 is not related to
this macro, I was apparently wrong before. This problem actually shows up when I use the Subtotal command. And this is repeatable. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Slow code when used as VBA code instead of macro (copying visible columns) | Excel Programming | |||
Shorten code to apply to all sheets except a few, instead of individually naming them, and later adding to code. | Excel Programming | |||
Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read for explainations.... | Excel Programming | |||
Excel code convert to Access code - Concat & eliminate duplicates | Excel Programming |