# Off Topic > The Water Cooler >  >  Do's and Don'ts So Others Won't

## Mordred

I'm sure that you pros have many don'ts when it comes to VBA programming.  A good example is the use of GoTo Labels (beaten to death I'm sure).  If you have the time and you think of a good don't, please post it here.
I would like to start off by asking about Option Explicit.  I understand what it does ie forcing the programmer to define variables but is it necessary?  I see some use it when they help others with code and some don't use it.  When don't you and when do you use it?

----------


## romperstomper

*ALWAYS* use it. (set it in options)

See also this thread.

----------


## shg

Use a tool (e.g., SmartIndenter) to indent your code. It is much easier to understand, and many compile errors can be avoided just by appearance.

----------


## Mordred

@romperstomper,  good link, thank you.
@shg, where is something like SmartIndenter?  I looked for it in 'Tools' but didn't see it.

----------


## romperstomper

You can get it here.
Code Cleaner is another must.

----------


## shg

> about Option Explicit.



People who don't use it aren't programming, they're just playing with their computer.





> Code Cleaner is another must.



Ditto that. I used to do manually what it automates; I cried when I found it. Not like I cried when I discovered VisiCalc, but a good cry nonetheless.  

I would add this, high on the list: adopt a variable naming convention and never deviate from it, unless you find one you like better.

----------


## Mordred

> People who don't use it aren't programming, they're just playing with their computer.



Too funny,  I've mostly just been playing with my computer.  I'm going to *** through my projects thus far and use Option Explicit.  Once I am done, will I be an official programmer?  :Smilie: 

I'm also going to look into the Code Cleaner you folks are talking about.  

You guys are awesome.

----------


## romperstomper

> I would add this, high on the list: adopt a variable naming convention and never deviate from it, unless you find one you like better.



Agreed. In fact consistency generally in your programming will stand you in very good stead. (unless of course you are trying to remain anonymous on an internet forum: it took one member here 3 posts to work out who I am.  :Wink: )

----------


## Andrew-R

Severely limit your use of global variables - they encourage very sloppy programming.  Every sub and function should be stand-alone (subject to being passed suitable parameters).

Don't code literal values, take the time to either define constants or, better still, pass parameters using the Optional keyword.

Not long ago I would have coded:




```
Please Login or Register  to view this content.
```



Which means that if wanted to use that sub in another workbook I had to remember to move and set the relevant global constants (and Excel gets really cheesed off if you open 2 workbooks that define the same global constants.

So I moved to:




```
Please Login or Register  to view this content.
```



Which is better, but lack flexibility and elegance, so I've progressed on to.




```
Please Login or Register  to view this content.
```



Much, much neater and completely transportable.

Oh, and always remember that you virtually never have to select cells in VBA  :Smilie:

----------


## Mordred

I just installed and ran the Code Cleaner on one my projects but what did it actually do?  Everything looks the same.

----------


## Mordred

> Oh, and always remember that you virtually never have to select cells in VBA



I'm still trying to break the habit of selecting cells and ranges and what-not but my fingers think for them selves sometimes (cursed things).

----------


## romperstomper

> I just installed and ran the Code Cleaner on one my projects but what did it actually do?  Everything looks the same.



Everything should look the same (but maybe a little more sparkly). It cleans out the p-code garbage that builds up as you repeatedly edit VBProjects.

----------


## Mordred

SmartIndenter is sweet!

----------


## broro183

hi Modred,

There's an overlap with what's already been offered, but here is a link to a list of some tools which I find handy: http://www.excelforum.com/2078299-post14.html

Plus another freeware comparision tool that I've found quite helpful for comparing versions of VBA code (just being a self-taught chap, with no specific "version control app" which I've seen some IT guys use for other languages): http://www.formulasoft.com/vba-code-compare.html 

hth
Rob

----------


## shg

Never write a line of code like this:



```
Please Login or Register  to view this content.
```

----------


## romperstomper

As if that could happen...  :Wink:

----------


## Marcol

I wonder who did?......

----------


## Domski

Re Option Explicit, I always use it, just never quote it in my posts.

I don't use any VBA tools but keep meaning to look at them as I gather there are some very useful ones.

Do learn about passing values to and from arrays (sooner than me anyway, they still confuddle me).

Don't keep a bottle of lighter fluid in the booze cupboard  :Wink: 

Dom

----------


## NBVC

> (unless of course you are trying to remain anonymous on an internet forum: it took one member here 3 posts to work out who I am. )



I think I can probably guess who that might have been  :Wink:

----------


## DonkeyOte

I suspect it was Richard Schollar... the same point holds true for formula bods also... everybody has a style to some extent (good/bad).

----------


## NBVC

> I suspect it was Richard Schollar.



I actually would have guessed that it way you  :Cool:

----------


## romperstomper

It was indeed Richard. Unnerved me a bit.

----------


## NBVC

Yeah, it is very hard to hide from your style...

I also guessed a couple of members correctly....who had different names in other forums...

----------


## Domski

I assumed my whack it really hard with a hammer until it gives in style would have been transparent so saw little point in using a different name.

Have we gone a little off topic here maybe?

Dom

----------


## NBVC

> Have we gone a little off topic here maybe?
> 
> Dom



that tends to happen in many watercooler threads.... just like in real world watercooler conversations.... someone else comes into the lunch room and inserts different thoughts based on what they heard when they walked in... :Wink:

----------


## Domski

Some of the tangents that the Lounge at Mr Excel used to go off on were interesting to say the least, usually thanks to a certain Greg Truby.

Dom

----------


## romperstomper

But you have to admire his high-falutin' language.

----------


## davegugg

Alright, pop-quiz hot shots.  Do you re-use variables?  If you have multiple (non-nested) loops, do you re-use i for integer?
What if you use a string to find a workbook name using the application.getopenfilename dialog box.  Would you redefine the string after opening the workbook to get rid of the path in the name?

Examples:




```
Please Login or Register  to view this content.
```

----------


## Paul

Personally, I wouldn't re-use a variable even in separate loops. If I ever needed to use multiple loops in code they would be doing different things, so I'd differentiate them to avoid any confusion.  If this was 40 years ago and memory allocation was more of an issue, perhaps.

----------


## shg

I reuse scratch variables if their scope of use is within a few lines of code, and I identify them as such in the declaration (I comment all my variable declarations as to purpose when I'm not just playing with my computer). 

For variables that index arrays, I name them consistent with the array; iChr indexes asChr, which has nChr elements.

I wouldn't reuse your string variable below because in the first case it's a path and filename, and in the second, just the filename.

----------


## pb71

Going off at a tangent (apologies) makes me think of this:

http://www.excelforum.com/the-water-...urely-not.html

Is there an Option Non-Explicit?  :Wink: 

Anyway, I am guilty of not using Option Explicit and on top of this I have not posted in the Excel Programming Forum yet (respect to Mordred for having the courage to do so), but I am observing and learning (I hope).

Phew, I feel a weight has been lifted from my shoulders!

I would just like to say (from someone who is self-taught like many others) that this topic is a revelation (to me anyway). I wouldn't say I was playing (I didn't know any better). Thanks shg (and I understand your totally justified comment ... now)! Good on you Mordred, for having the ..... to ask what so many of us would like to (without fear of the repercussions).

----------


## Andrew-R

Here's a tip that I've just remembered - read about what the ByVal and ByRef arguments mean in function definitions and apply them.

Some time ago I wrote what I thought was a straightforward function to convert a number to binary ...




```
Please Login or Register  to view this content.
```



The code that called this function then went on to do other things with the binary number returned and the decimal equivalent, except it kept crashing out as if the decimal number was zero, even though I'd already checked for that condition.  It took me *ages* to figure out what the problem was.

I know now that it's bad practice to have a function change any parameter passed to it, but because of that mistake I appreciate why.

----------


## romperstomper

Oh one other one:
If you are not using the Call keyword, or returning a value from a Function, do *not* enclose arguments in parentheses.

----------


## Mordred

> do *not* enclose arguments in parentheses



Why?  Is that a style preference?  I come to actually like parentheses around arguments as it (in my mind) lets me know exactly where an argument is being passed.
@pb71, I'm one for asking questions.  I like to know the whys because I think they are necessary if you want to do well with whatever task at hand.  Knowing how isn't enough for me.   :Smilie:

----------


## romperstomper

No it's syntax not style. Try it with an object variable.
For example:




```
Please Login or Register  to view this content.
```

----------


## Mordred

You'll have to parden my naivety romperstomper, but you are using parentheses in your last example and so I am a little confused.  Specifically where it works ie:



```
Please Login or Register  to view this content.
```


Could you elaborate a little more regarding this?

----------


## Mordred

I just wanted to thank you guys, especially Andrew, who has steered me regarding using .Select.  I took this code,




```
Please Login or Register  to view this content.
```


and changed it to this code,




```
Please Login or Register  to view this content.
```


It was not easy and took me a fair bit of time to do but I'm glad I did it because I learned a lot while fighting throught the code to make it work.  It also reduced the total time to update by about 35 seconds (which seems like forever when you are waiting for code to finish).

----------


## romperstomper

> *If you are not using the Call keyword*, or returning a value from a Function, do *not* enclose arguments in parentheses.



Note the bold part.  :Smilie:

----------


## Mordred

> Originally Posted by romperstomper  
> If you are not using the Call keyword, or returning a value from a Function, do not enclose arguments in parentheses.



Right, I did read that once already didn't I?  I'll just slap myself for you since you can't do it from where you are.  :Smilie:

----------


## Paul

Oh yeah?




```
Please Login or Register  to view this content.
```

----------


## Mordred

:EEK!: 



> .Run "Invite_Mother_In_Law_To_Dinner"



Noooooooooooooo! :Wink:

----------


## Andrew-R

> I just wanted to thank you guys, especially Andrew, who has steered me regarding using .Select.



Thanks, Mordred, it's nice to know I've helped.  On the other hand, I've been using Excel for 15 years and have been the Excel expert in all of the companies I've worked in during that time, but I'm in awe of some of the people on this forum (and in this thread) - you never stop learning.

----------


## broro183

Hi all,

Andrew, I agree, "never stop learning"  :Smilie: 

Mordred,
I'm about to take the thread slightly off track again - the joys of the Watercooler eh?  :Wink: 





> ...It was not easy and took me a fair bit of time to do but I'm glad I did it because I learned a lot while fighting throught the code to make it work.  It also reduced the total time to update by about 35 seconds (which seems like forever when you are waiting for code to finish).



That's a great speed improvement & the code looks better too  :Smilie: 
How fast is it now?
I think we could make your code even faster with a few more tweaks.
- To save some typing time you could incorporate GetOpenFileName into your code (see here or here for Jacob's examples). However, if you have Conditional Formatting on your sheets then also have a read of this thread & the tinyURL's referring to Greg Wilson's findings.
- Each time you interact between a spreadsheet & VBA slows a macro down. The below code seems to suggest that you are looping through every cell in a Range that is 12 columns wide.



```
Please Login or Register  to view this content.
```


 It seems that you currently only work on one file at a time and leave that file open even when the macro finishes. If you are not making any permanent changes in the opened file (ie just grabbing the values), then you could:open it as read onlyrecord a macro that Finds & Replaces ".." with "" (perhaps even do this before setting the first range)then use a combination of .end(xldown) &/or .end(xlright) to identify the size of the rangecopy/transfer the range valuesthen close the file without saving- It seems as if nothing actually "belongs to" the below line object in the With statement, is it redundant?



```
Please Login or Register  to view this content.
```


- Also, (I think this is mentioned in the "What not to do in VBA" thread), each time you use "Set xyz = ..." in your code, many people recommend releasing the memory at the end of your code by using "Set xyz = Nothing" once you have finished using the object.


hth
Rob

----------


## Mordred

Hi broro,
   I timed it at just over 6 seconds to complete with an online stopwatch so there is a +- of about 2 or 3 seconds.  There is no conditional formatting, it is just a straight movement and storage fo data.  I am going comment this out:



```
Please Login or Register  to view this content.
```


to see if it makes a difference or not.  As for the "..", the values to be updated are each month but, we don't have say August yet so the update from our source uses ".." instead of a numerical value.  The user just changed his criteria a little so I am now working on a way to grab all the values in the range except for ".." and 4 other cells in the range.  I've been working on a function that will help but it is still in progress.  The code I finish with will probably not resemble the code I submitted on this thread.  

I really do appreciate your feedback though on that, no doot aboot it!

Best Regards

----------


## broro183

hi Mordred,

If you want any feedback on your final code, feel free to start a new thread asking for feedback & pm'g me a link to it (or even providing a link in this thread)  :Smilie: 

Rob

----------


## Mordred

Here is more for the do or don't thread.  
While in VB editor, there is the option to insert a new module or, the actual worksheet can have code written into it.  I guess my question is, when is it appropriate to insert code into the worksheet?  I mostly use modules but I have inserted code into worksheets when I am designing a worksheet.  Is there a do or don't for code in worksheets compared to modules?  Some insight is appreciated as always.

----------


## romperstomper

As a general rule, event code (for the sheet and its objects) goes in a sheet (or workbook) module. Everything else goes in a normal module. There are different schools of thought, but they're wrong.  :Wink:

----------


## Mordred

> There are different schools of thought, but they're wrong.



Thanks romperstomper, lol, I'll take your word as gospel on the matter then.

----------


## Mordred

@romper, nice carving by the way for your avatar.  Did you carve it?  It is a pumpkin right?

----------


## romperstomper

Yes, and yes. Thank you - my daughter wasn't impressed by any of them!

----------


## teylyn

> my daughter wasn't impressed



Appreciation of art will not develop before the age of 3.

----------


## squiggler47

> Alright, pop-quiz hot shots.  Do you re-use variables?  If you have multiple (non-nested) loops, do you re-use i for integer?
> What if you use a string to find a workbook name using the application.getopenfilename dialog box.  Would you redefine the string after opening the workbook to get rid of the path in the name?



I still use J as a loop variable where the loop is just a counter, if I actually do anything with the loop variable a more meaningful name is appropriate!!

----------


## squiggler47

If you just wanted the total of hours, add column D as a helper to your first sheet :-

=Month(a1)

then you can total for each month with :-

=SUMIF(Sheet1!$D$2:$D$13,9,Sheet1!$C$2:$C$13)-SUMIF(Sheet1!$D$2:$D$13,9,Sheet1!$B$2:$B$13)

Pivot table is the best way to go!!!!!!

----------


## martindwilson

and that answers what question squig?

----------


## squiggler47

oops, blush! erm it answered on question somewhere!

----------


## squiggler47

Ok if the post below answers your question then YEY, otherwise oops!

----------


## Domski

I think you'll find the answer is 42

Dom

----------


## squiggler47

and the question then must be "What is your age?!"

----------


## ChemistB

I was very disappointed in the final book of that series.  Sighhhh

----------


## davegugg

A lot was made previously about using GoTo, but how do you handle errors without using Goto?  Or is error handling the one exception?

----------


## romperstomper

Error handling is the exception - no real choice there.

----------


## blane245

> Error handling is the exception - no real choice there.



With the exception of GoTo 0, I have not used a GoTo in years. They can be completely avoided.

----------


## romperstomper

You mean you don't use error handling blocks, you just use On Error Resume Next?

----------


## blane245

> You mean you don't use error handling blocks, you just use On Error Resume Next?



Correct. In effect, error handling blocks are in line with the code. I will use Exit Sub in the block after setting appropraite error flags, if necessary.

I know this is a style thing. I find it easier to assure myself that I have handed error conditions by leaving the hanlding block near the error. 

If VBA had try...catch like other languages, I would prefer it. On Error Resume Next the closest I can get.

----------


## romperstomper

> If VBA had try...catch like other languages, I would prefer it. On Error Resume Next the closest I can get.



I would have said that on error goto label was closer to Try...Catch, but hey, it's your code.

Of course that assumes you can predict every single error...

----------


## blane245

> I would have said that on error goto label was closer to Try...Catch, but hey, it's your code.
> 
> Of course that assumes you can predict every single error...



You are right about goto label being close to try...catch. You got me on that one. I think the goto label method is definately a good way to protect code for any type of unforseen error, though a bit weak in knowing what to do when one of those unforseen errors occur. In general, doing exception handling is like writing a second program inside on the original one. 

Error prediction is an essential part of robust coding. If you are trying to protect against hardware as well as software faults, then there may no other way than to use on error goto label. But in the VBA environment, I am not sure your code will get control on those types of errors. For example, if you have a read failure on a disk drive, will VBA let your code get control of that, or will VBA or Windows trap that error and forget about you.

----------


## romperstomper

I agree that error prediction is an essential part of coding, but I don't believe it is possible to predict every error that might occur, so I think you should have some generic error handling, even if it's only in the top level sub.

This is purely my opinion, and I've had some "lively" discussions in the past with various programmers (whom I _otherwise_ respect) who disagree with me, so these days I just agree to differ on this point - as long as the error handling isn't simply putting On Error Resume Next at the top of every sub.  :Smilie: 

As we all know, a fair proportion of coding practice is subjective (and the people on each side of any given argument seem to be equally vehement in general) so as long as I don't have to support other people's code I try to leave them to it. Unless of course, I think they are recommending bad practices to others...

----------


## blane245

Well stated. I am in the IV&V business and have done some study into how to determine if error handling is adequate. The research has not yet settled out to any clear means of making this determination. In the meantime, we are stuck with our own opinions.

----------


## romperstomper

I have no idea what IV&V is. I know what IV Guinness is, but I suspect that's quite different...  :Wink:

----------


## blane245

> I have no idea what IV&V is. I know what IV Guinness is, but I suspect that's quite different...



Don't feel bad - there are many who don't have a clue. On the other hand, I know (and love) Guinness, but never heard of IV Guinness.

----------


## romperstomper

Some drink Guinness in cans, some in bottles, others in pints. I take it *I*ntra*V*enously.  :Smilie:

----------


## blane245

Awesome, where can I get some?  :Cool:

----------


## Mordred

> I agree that error prediction is an essential part of coding, but I don't believe it is possible to predict every error that might occur, so I think you should have some generic error handling, even if it's only in the top level sub.



So you us



```
Please Login or Register  to view this content.
```


for every sub?  Dang, I've only used them with forms.

----------


## squiggler47

> So you us
> 
> 
> 
> ```
> Please Login or Register  to view this content.
> ```
> 
> 
> for every sub?  Dang, I've only used them with forms.



On Error Goto label

is better rather than ignore the error handle it!

its also good to  start with :-




```
Please Login or Register  to view this content.
```



I use this any time I use EnableEvents for speed, saves my hair when the code crashes,and I forget to manually turn events back on so then nothing works!, as Enable Events is permenent while ScreenUpdating only lasts until the end of the Sub/Function!

----------


## romperstomper

> So you us
> 
> 
> 
> ```
> Please Login or Register  to view this content.
> ```
> 
> 
> for every sub?  Dang, I've only used them with forms.



Absolutely not. Other than quick and dirty code, I try to only use On Error Resume Next in short single-purpose routines (such as checking if a sheet exists) and use an error handler, rather than suppressor, in main routines (as Darren said).

----------


## blane245

What R. said! I will use multiple Resume Next/GoTo 0 segments if I am trying to trap multiple different errors in the ame routine, but never leave a Resume Next open for more than a single statement and its error handler.

----------


## Mordred

So then I do not have to go into all of my modules and the On Error Resume Next.  Good to know.  I know it is unlikely to catch all errors but I like to use If statements when I know that an error happens.  In my own opinion, taking the time to test code in regards to the process it is to perform is the best way.  It is time consuming (and something that most employers do not like) but it is an effective approach to code design.

----------


## ChemistB

FYI, this discussion is very educational for me.  I've been teaching myself VBA (with the help of everyone here) and most of my brainpower has gone to wrapping my brain around the code.  I'm just now starting to look at error handling.  Thanks to all.

----------


## Domski

If it's an education for you I feel like I'm in primary school  :Wink: 

Dom

----------

