how to improve coding style and move away from hard coding

[chopperj]

Hi all, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have been dabbling on and off in the VBA world for about 10 years, but i have had no real formal training thus developed my own style.<o></o>
As a recent project will demand lots of VBA i want to approach the coding more intelligently. (in 2003)<o></o>
With this in mind could anyone advise of any online documents or offer any tips / mindset approaches.<o></o>
<o></o>
Also i would be grateful for a explanation of how the VBA architecture ie workbook <o></o>
worksheet<o></o>
modules<o></o>
etc<o></o>
<o></o>
I look forward to your responses <o></o>
<o></o>
Thank you<o></o>

 

[Smitty]

Welcome to the Board!

This may be an overwhelming link for a newbie, but if you've already had a lot of exposure to VBA then you'll have plenty to get you going:

Excel 2003 VBA Language Referencee.

HTH,

 

[=ODIN=]

Hi all, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have been dabbling on and off in the VBA world for about 10 years, but i have had no real formal training thus developed my own style.<o></o>
As a recent project will demand lots of VBA i want to approach the coding more intelligently. (in 2003)<o></o>
With this in mind could anyone advise of any online documents or offer any tips / mindset approaches.<o></o>
<o></o>
Also i would be grateful for a explanation of how the VBA architecture ie workbook <o></o>
worksheet<o></o>
modules<o></o>
etc<o></o>
<o></o>
I look forward to your responses <o></o>
<o></o>
Thank you<o></o>

For starters, never reference sheets by their name. Always use their codename. This way if somebody changes one of the sheets' name, your macro will still work. You can find the codename for any sheet in the VBA editor. Look at the left hand VBA Project pane where it has all the sheets listed in a fashion like the "Sheet1 (The_Sheet's_Name)" The Sheet1 here is the codename for the sheet named "The_Sheet's_Name".

So instead of writing code like this:

worksheets("The_Sheet's_Name").whatever


Do it like this:

sheet1.whatever


Lastly, figure out how to use the Immediate Window in the VBA editor and also how to use Debug.Print with the immediate window and within your code.

 

[Norie]

chopperj

I think if you searched the Internet and/or this forum you'll find plenty of advice.

I don't think there is any comprehensive guide to how you should use code - sometimes it can come down to personal choice/opinion.

 

[Smitty]

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Also i would be grateful for a explanation of how the VBA architecture ie workbook <o></o>
worksheet<o></o>
modules<o></o>
etc<o></o>
<o></o>

****, I have to finish my article on this, but here are the basics:

Workbook level code is for Workbok level events, like Open, BeforeClose, etc. You can also apply Global events to sheets, like WorkBook_SheetChange in which a single event would apply to all sheets.

Worksheet level code is reserved for each sheet and its respective module. E.G. Controls on only that sheet, or a Change event for just that sheet.

The first two are generally Private and can't be called from the Macro list in Excel.

General/Standard Modules are for generic code that can be called from anywhere.

Forms modules are for code specific to their respective forms.

Then there are Class modules which are a whole 'nother thing.

HTH,

 

[chopperj]

great stuff, thank you!