Reply to post: Re: Difficult to describe this one

Re: Difficult to describe this one

I've always found the biggest issue to be the amount of people who use select / activate far too much. You don't need to select sheets, ranges etc to perform actions on them. Just be specific.

i.e.

Sheet.Select

Range.Select

Selection.Copy

Sheet.Select

Sheet.Range.Select

Selection.Paste

Anything that interrupts that flow of selection (especially during debug) ruins your report.

If you're concise and explicit, that problem goes away.

Sheets(name).Range(Range).Copy

Sheets(otherSheet).Range(range).PasteSpecial xlPasteValues

The other one is where you open secondary workbooks. Noone ever seems to instance them into a variable, and as such rely on the filename as a handle, and when saving out date-specific versions to different places, that becomes a major issue just tracking the last name you saved it as.

I used to manage an estate of 90 reports, all run through excel vba, which were triggered through the Win2K task manager (I know, I know.. but it worked, mostly). By making the code concise and explicit, I could have 10 reports running simultaneously on the same machine, without fearing they'd screw each other up.

VBA is not evil in and of itself (the differences between versions, and even the versions used in different Office applications of the same vintage not withstanding...). Bad VBA is, however, and it is endemic, as most coders of it seem to learn only through recording macros and regurgitating what it records.