VBA Formatting/Indenting

[ryancgarrett]

Hi All,

Wondering if someone could point me to some information on proper formatting/indentation of VBA code in Excel. I'm proficient in VBA but never learned proper formatting and my code always looks like junk compared to others.

Thanks in advance,

Ryan

 

[shg]

I use Stephen Bullen's SmartIndenter: Office Automation Ltd. - Smart Indenter

 

[ryancgarrett]

I use Stephen Bullen's SmartIndenter: Office Automation Ltd. - Smart Indenter

I looked at that one, the website says it is for Excel 97-03... does it work with 2010?

 

[Rick Rothstein]

Wondering if someone could point me to some information on proper formatting/indentation of VBA code in Excel. I'm proficient in VBA but never learned proper formatting and my code always looks like junk compared to others.

The general rule I follow is this... if the statement has a closing "companion" (such as "Sub/End Sub", "If/End If", "For/Next", "Open/Close", "Do/Loop", etc.), then I indent everything in between the the same amount. Also, to make things easier, I write the closing "companion" right after I write its "opening" companion. So, using For..Next as an example, I write all three lines (the empty line between them... produced by pressing Enter twice)...

For X = 1 To Whatever

Next

I then then use the up arrow to put the text cursor on the empty line, press my two spaces (what I use to indent my code; I have seen other prefer 4 spaces) and then start typing the code between them. Pressing Enter at the end of each statement helps maintain the offset levels for you. When done with the loop code, I cursor down twice to end up on the line after the Next statement (and again, VB helps keep the indent level correct for you). If you prefer, you can use the Tab key for your indents instead of hitting the space bar multiple times by clicking Tools/Options on the VB editor's menu bar and setting the number of spaces the Tab key should indent in the "Tab Width" field on the Editor tab.

 

[shg]

does it work with 2010?

Yes.