# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  What to Avoid in VBA

## pierre08

hi everybody,

i'm trying to do a little summary about all the mistakes or the ways that we should avoid when we write a Macro in Excel.

I hope that everyone participate so we could share our experience. I think it can be really interesting.

Thank you all for you time  :Smilie: .

----------


## tony h

always use : option explicit

----------


## pierre08

Come On guys a little motivation i'm sure this can be really interesting.

It's just about all the mistakes that you do frequently or the wrong ways that you used to write a Macro and what did you do to avoid that.....

There is a lot to discuss

----------


## DonkeyOte

Probably better to look at this from the other direction... ie what are the basic principles that you should always be looking to follow:

http://blogs.msdn.com/excel/archive/...practices.aspx

If you're not doing the above then then avoid whatever it is you _are_ doing.

----------


## teylyn

nice link, DO. Straight into favs!

----------


## Richard Buttrey

Hi, 

In no particular order of merit and springing immediately to mind.

1. Always have the 'Require Variable declaration' ticked.

2. Avoid .Select and .Activate unless absolutely necessary, fully reference an object and address it directly. e.g. Workbooks("Main").Sheets("Sheet1").Range("A1").Copy

3. Wherever you see several lines lines of very similar code look for the common bits and consider how you can use a variable (e.g. a loop counter) and cut out all the common lines.

4. Use small procedures for one specific task only and link them from a main 'Control' procedure. Don't keep adding lines of code to a procedure just because you can.

5. If a loop is jumping back to the Excel App to do something and the loop counter is large, then consider reading all the Excel data into a VB array first, processing it all within the VBE and only writing the resultant array back to the Application at the end of the process.

HTH

----------


## pierre08

One of my bigest problem was the Capital and the miniscule lettre in a Variables, i had a lot of errors because of that, and the solution that i found was to use "Option Compare Text ".

----------


## romperstomper

Option Compare Text is not a good solution. It is better to only use vbTextCompare if absolutely necessary - otherwise it is usually faster to explicitly convert a string to upper or lower case before doing the comparison.

----------


## pierre08

I use Option Compare Text not to compare 2 String but to avoid the problem when i call a variable with a Capital or a miniscul lettre to not have an error
For exemple:



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

----------


## romperstomper

I think you have misunderstood what Option Compare Text does. It has nothing to do with the names of your variables, only the contents, and only when comparing text values.

----------


## pierre08

yeah i guess your right 

Sorry

----------


## tony h

Always be nice to people on ExcelForum

----------


## pierre08

Come on guys is that all !
Come on some motivation please

----------


## StephenR

Dont fill your posts with unnecessary exclamation marks or 'e's.

----------


## shg

> http://blogs.msdn.com/excel/archive/...practices.aspx







> I have 40 shapes on a sheet, and I want to write "Hello" in each of them. Using the slower "selection" method, the code looks like this:
> 
> 
> 
> 
> ```
> Please Login or Register  to view this content.
> ```
> 
> ...



Collections in all prior versions of Excel start with index 1. Has this changed in Excel 2010? (unlikely, methinks).

The TextEffect property refers only to WordArt AFAIK, not drawing objects in general.

----------


## JBeaucaire

1) Don't Loop when you can filter

2) Don't hardcode the last row variable

3) Don't give up so easily on "Option Compare Text"  :Wink: 

4) Shorter code is not always better code

5) Use Error Handling so your code exits gracefully when needed

----------


## broro183

> ...
> 2) Don't hardcode the last row variable
> ...



I'll expand on JB's hardcoding point to say try to hardcode as little as possible.

I've read some authors who suggest that no numbers except 0 or 1* should be hardcoded, all the rest should be defined as either a Variable, or as a Constant if it should not change during execution. *From memory, I think one author was referring to conversion of vb arrays to ranges or other uses of indices for collections/arrays.
By creating constants or variables you assure the code only needs to be changed in a limited number of (usually more identifiable) places, rather than having to search entire projects for hardcoding.

Rob

----------


## romperstomper

> 3) Don't give up so easily on "Option Compare Text"



Feel free to elaborate on why this might be a good idea...  :Smilie:

----------


## JBeaucaire

I use it frequently, it is simple and effective to remove case-sensitivity from a macro rendering it easier to code and easier to read.  Good enough for me.

----------


## romperstomper

I was under the impression this was supposed to be a 'best practices' thread though...  :Wink: 
If you need a specific comparison to be case insensitive, use strcomp or convert to upper/lower case first.

----------


## rwgrietveld

JB,

Have an example on this 



> 1) Don't Loop when you can filter



Very interested as mostly looping (For each) is very readable but slow.

----------


## rwgrietveld

> 4. Use small procedures for one specific task only and link them from a main 'Control' procedure. Don't keep adding lines of code to a procedure just because you can.



Very True, but I would like to add that SUB/FUNCTION description would help as well. Take the black box approach -> input, Output, Function.

Another addition on proper error handling:
Many people take the error handling as a shortcut, instead of checking wether the operation is allowed before proceding.





> Don't use error handling as a shortcut !

----------


## JBeaucaire

Ricardo, are you asking me something specific?

If you have 1000 rows of data and want to parse them into sheets based on the values in column A, many people (me included) start out by evaluating each cell in column A one at a time in a loop and copying the row to the appropriate sheet...

Since the AUTOFILTER can show all the rows in that 1000 row set that match each specific value, you can get the data and copy it in a fraction of the time by using it.  If the 1000 rows of data only have 5 or 10 specific values, then the loop only has to go through those 5-10 options and copy once for each.  Way better than looping 1000 times and copying 1000 times.

----------


## rwgrietveld

OK clear. Something like this?



```

```

----------


## Domski

Avoid using VBA reserved words as variable names.

Dom

----------


## JBeaucaire

> Avoid using VBA reserved words as variable names.



...and variable names that are also possible cell references.  *PT1* or *PT20* seems harmless to us Excel 2003 users, but it's an actual cell reference in 2007+.

----------


## JONvdHeyden

- Always indent your code properly.

- Apply and use a naming convention consistently (e.e.g variable declarations).

- Use sheet CodeName when ever possible, instead of Name or Index.

- Use Early Binding (some execptions to this rule)...

- Explicitly use ByRef or ByVal if a procedure takes arguments.

- Avoid Option Compare Text (sorry JB  :Wink: )

To name but a few....

----------


## JONvdHeyden

> Ricardo, are you asking me something specific?
> 
> If you have 1000 rows of data and want to parse them into sheets based on the values in column A, many people (me included) start out by evaluating each cell in column A one at a time in a loop and copying the row to the appropriate sheet...
> 
> Since the AUTOFILTER can show all the rows in that 1000 row set that match each specific value, you can get the data and copy it in a fraction of the time by using it. If the 1000 rows of data only have 5 or 10 specific values, then the loop only has to go through those 5-10 options and copy once for each. Way better than looping 1000 times and copying 1000 times.



I also prefer using AutoFilter, since it is considerably more efficient than a loop.  However, a word of caution...  If you want to e.g. Copy or Delete the Visible cells/rows, then you need to be aware of the 8,192 non-contiguous cells limit.  If you attempt to handle a range that violates this limit you will encounter an error message 'The selection is too large'.  See this article: http://support.microsoft.com/?kbid=832293

One way to avoid this problem is to sort the range according to your filter criteria first so that you only deal with a single contiguous range.

I did write an article on AutoFilter before, which seems to have made its' way onto the internet here.

----------


## rwgrietveld

What about the use of global variables?
Avoid as much as possible ?!

How then transfer a UserForm variable to a module or Worksheet (in case of an event)

----------


## Domski

> What about the use of global variables?
> Avoid as much as possible ?!
> 
> How then transfer a UserForm variable to a module or Worksheet (in case of an event)



Another method is explained here: http://www.vbaexpress.com/kb/getarticle.php?kb_id=470

Dom

----------


## teylyn

If I recall advice by romperstomper correctly, global variables are OK as long as you ensure that they are *changed* in only *one* module and *other* modules only *read* them.

----------


## JONvdHeyden

> What about the use of global variables?
> Avoid as much as possible ?!
> 
> How then transfer a UserForm variable to a module or Worksheet (in case of an event)



I don't think the instruction is to avoid them entirely, only to be aware of the risks and avoid them unless absolutely necessary.

This is the 2nd time today that I will provide a quote from PEDII - but that's because it is so nicely explained:





> Public variables are dangerous.  They can be modified anywhere in you application without warning, making their values unpredictable.  They also work against one of the most important programming principles - encapsulation.  Always create variables with the minimum scope possible.
> As with most rules, there are a few cases where the variable scope rule should be broken because the use of public variables is useful and/or necessary:
> 
> - When data must be passed deep into the call stack before it is used.....
> - Certain inherently public classes, such as application-level event handling class,...., so that they never go out of scope while you application is running.



Also some risks outlined here.

----------


## tony h

simplicity wins over efficiency when it comes to support.

Use class modules for global variables

If you are non-general error trap (ie a condition where an error is expected) put that specific code in a submodule or function.

----------


## rwgrietveld

Dear JonvdHeyden,

Referencing to your Filtering instead of looping through ....

In your coding you use the



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


which i think is smart.
I many references (even the one you are refering to "contextures") I see the use of specialcells like:



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


Any preference here ?

----------


## Andy Pope

@tony h




> Use class modules for global variables



what's your thinking behind this approach? 
surely the class instance still needs to be global if used in multiple routines.

----------


## rwgrietveld

... and comming back to 



> How then transfer a UserForm variable to a module or Worksheet (in case of an event)



I use several methods (mostly until one works). 99% it's for own use so I don't care much.

Does someone has a sample coding for
1) Worksheet event
2) UserForm with single textbox is loaded, Shown
3) TextBox value needs to be handled further in the worksheet

----------


## StephenR

AutoFilter - while I recognise its advantage and try to use in place of a loop I have on occasion found it rather buggy. Sometimes, without qualifying the AutoFilter range with SpecialCells(xlCellTypeVisible) it doesn't work, i.e. copies the whole range, whereas sometimes it seems to work without this qualification. What am I missing, or have others experienced this contrariness?

----------


## JONvdHeyden

@Ricardo
I think Debra's method is more reliable.

@StephenR
Not either sure why, but must assume there is logic to it.  To be safe make sure you use SpecialCells(xlCellTypeVisible).

----------


## David Huang

Range.Replace.

If you do find or replace Within Workbook outside of VBA, then run range.replace in VBA, it replaces in whole workbook.

----------


## Domski

> Range.Replace.
> 
> If you do find or replace Within Workbook outside of VBA, then run range.replace in VBA, it replaces in whole workbook.



I was told when using Find and Replace in VBA it's a good idea to specify all the arguments every time. Not doing so can cause problems similar to what you have eluded to.

Dom

----------


## tony h

> @tony h
> 
> 
> what's your thinking behind this approach? 
> surely the class instance still needs to be global if used in multiple routines.




There are a number of advantages (in my mind). By using a class module I tend to call it myGlobals and using properties then:
1. it is quite clear that you are using a global value as the syntax is myglobl.TaxRrate
2. it is easy to trap where the global is used as you can put a breakpoint on the property in the class module
3. when dealing with anything that can go out of scope or lose connectivity (eg connecting to databases especially when updating a spreadsheet) it is easy for the DB objects to be outof scope. By using the myGlobals class the error trapping can be done and the reinstating of the connection effectively automatically by keeping all the code in one place.
4. This also helps with creating logfiles. 

and probably a few more  :Smilie: 

5. and usng the class terminate you can ensure a proper close down of connections

----------


## Ron Coderre

ALWAYS use descriptive variable names.

Which of these is easier to maintain, 6 months after the code is written?:

This



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


or...this:



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

----------


## tony h

Mostly agree with Ron except for transient local variables. If it is just a loop count or a temporay value for string manipulation I will use i,j, str, var

Saves having to think "this value might be important somewhere else".

----------


## JONvdHeyden

> ALWAYS use descriptive variable names.
> 
> Which of these is easier to maintain, 6 months after the code is written?:
> 
> This
> 
> 
> 
> ```
> ...



Couldn't agree more.  It takes no effort to be a little more descript!!

Although I believe the variable prefix should always indicate the variable data type (per my previous suggestion using a naming convention).

----------


## Richard Schollar

> If it is just a loop count or a temporay value for string manipulation I will use i,j, *str*, var



I bet you wouldn't really use a variable named "str", Tony  :Wink:

----------


## Ron Coderre

I stand by my recommendation.  
Using "For i = j to k" is ok for homework, but is a bad habit to get into for production work.
ALWAYS assume your little bit of code will be built upon, expanded, or become part of a larger project.  A few seconds spent up front could save hours later.

I've had to clean up too many instances of other people's code that was like this:



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


50 lines into the code, my head is spinning when I run into a line like:
"If i < z Then"

----------


## Andy Pope

I see. You still have a global variable but it's more obvious and with centralised control over possible bad states.

For scoping I keep it as local as possible with prefixes to identify the scope as well as variable type.




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

----------


## rwgrietveld

I agree with you that using some sort of notation like the hungarian notation would help maintain (and understand) the code.

... and do we now ALL promise to use this in our Forum answers ? or do we consider that as homework?

----------


## Domski

It's homework until I get my honorary doctorate from the University of Excel!!!

Dom

----------


## Ron Coderre

I agree to continue using descriptive variables in posts.

In our responses, we are teaching proper methods for solving Excel issues.

While inefficiencies in formatting, functions, and sheet structure are usuallly easy to correct,
poor programming habits in small projects turn into poor programming in large projects 
that can be a nightmare to fix. Best to prevent them at the beginning.

----------


## tony h

> I bet you wouldn't really use a variable named "str", Tony



Hmmm




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


But you see it is only useful very locally (in this case for one line). Sometimes it takes several lines to compile the select statement.

 :Wink:

----------


## romperstomper

I think Richard was referring to the fact that str is a native VBA function... :Smilie:

----------


## tony h

> There are a number of advantages (in my mind). By using a class module I tend to call it myGlobals and using properties then:
> 1. it is quite clear that you are using a global value as the syntax is myglobl.TaxRrate
> 2. it is easy to trap where the global is used as you can put a breakpoint on the property in the class module
> 3. when dealing with anything that can go out of scope or lose connectivity (eg connecting to databases especially when updating a spreadsheet) it is easy for the DB objects to be outof scope. By using the myGlobals class the error trapping can be done and the reinstating of the connection effectively automatically by keeping all the code in one place.
> 4. This also helps with creating logfiles. 
> 
> and probably a few more 
> 
> 5. and usng the class terminate you can ensure a proper close down of connections



6. which is also a good way of trapping unexpected "out of scope" conditions.

----------


## shg

> Range.Replace.
> 
> If you do find or replace Within Workbook outside of VBA, then run range.replace in VBA, it replaces in whole workbook.







> I was told when using Find and Replace in VBA it's a good idea to specify all the arguments every time. Not doing so can cause problems similar to what you have eluded to.



The error occurs when you do a Replace across the workbook (from the UI, per force), then a range.Replace from VBA. The range is ignored and replacement is performed throughout the workbook. Specifying all the parameters of the Replace won't help; you have to do a dummy Find to reset the behavior:




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


The word Texas is key here ...

Don't know if this has been corrected.

Here's my list:



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

----------


## broro183

:Smilie: 

This thread has started humming since I last saw it!

Shg, I can't remember having ever seen a code snippet on the Forum that uses TypeName or VarType etc & I'm going to have go investigating now!

I may have missed this in amongst the posts, but what is the general consensus on explicitly naming arguments vs using commas as placeholders in argument listings?
For example:



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


I try for the latter, but I know I'm guilty of the former, & it's usually when I can see the OP is likely to have some understanding of the code already. In other words, I'm probably reinforcing existing bad habits & showing the new folk to "do as I say rather than do as I really do"...

Rob

----------


## StephenR

shg: as articulate as ever. It's always a pleasure to read your posts. I wonder if you might expand on your reasoning behind




> prefer iterating elements from a collection (For Each/Next), followed by For/Next and Do/Loop.



and




> Use the Like operator sparingly. Ditto for Regular Expressions.



broro183: fwiw, I'm inconsistent. With Find I explicitly name the arguments I specify (not necessarily all of them), but for Copy/Paste I never specify Destination. I suppose good practice would be to name them all, but I think given most of us have limited time on the forum we tend to cut corners. I have to confess that I am probably more thorough when writing code for my own use.

----------


## romperstomper

Just be aware that TypeOf and TypeName don't do the same thing and TypeName is better in certain situations.

----------


## shg

> Just be aware that TypeOf and TypeName don't do the same thing and TypeName is better in certain situations.



Indeed, and I believe you gave me an good example with controls some weeks ago. 

Maybe this is a better example of avoiding a text comparison:



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

----------


## broro183

Thanks Shg & RS,
"TypeOf" is my little learning for today - it seems to be hidden away in the 2007 Help files under "If...Then...Else Statement".

My investigations/Google shows an incy squincy total of about 100 results in the Forum for the below three data information functions: http://www.google.co.uk/search?hl=en...meta=&aq=f&oq=
Could/should we be more defensive when offering solutions?

StephenR, I'm pleased I'm not the only inconsistent one - your logic is sound too.

----------


## shg

> expand on your reasoning behind
> 
> 
> 
> 
> 
> 
> 			
> 				prefer iterating elements from a collection (For Each/Next), followed by For/Next and Do/Loop.
> ...



My understanding is that iteration and search of collections and dictionaries are very fast and highly optimized. They are also very intuitive stuctures, IMO. Beats the heck out of redimensioning dynamic arrays in a loop.

RegEx and it's little brother, the Like operator, entail complex comparisons; think about the difficulty of writing one, and then stop when you feel a blood vessel approaching the bursting point in your brain. They're great tools, just limit their use to when they are justified.

Like is so easy to use that I abuse it, I confess.

----------


## JONvdHeyden

> My understanding is that iteration and search of collections and dictionaries are very fast and highly optimized. They are also very intuitive stuctures, IMO. Beats the heck out of redimensioning dynamic arrays in a loop.



And use dictionaries instead of collections, unless ordering is required.  Apparently it's quicker.

----------


## Paul

I think one of the best laughs I've had in learning programming was when I read "How to write unmaintainable code" ( http://freeworld.thc.org/root/phun/unmaintain.html ).  It's very long, and I don't think I read the whole thing.

When I was taking a Java course we didn't have to turn in any of our chapter projects until the end of the class.  I waited until the last night to do them all (10 weeks worth, hah) and used many of those tricks just to see if my teacher was worth his mettle.  I still love the code that I wrote that replaced all variables with superhero names.  Ahh, the good old days.

 :Smilie:

----------


## tony h

Paul,
that is brilliant.

It reminded me of a situation I had a few years ago (actually quite a few) I was working on some hardware that had an assembler (a mneumonic version of machine code) because long names took more space and more processing and the odd picosecond made a difference names were understandably short
Unfortunately the first view of the code I had was a printed version that from a teletype. This wonderful device printed all of i, I  as I and 1 was virtually indistingushable from I. As the main function of the processor was to translate inputs to outputs the variables were called i1, I1, ii and o0, O0 and oo all printed II and OO.

It was then I learned to read papertape

----------


## StephenR

Thanks for the clarification shg. RegExp certainly makes my head hurt, but very satisfying on those rare occasions when I get it to work. I was wondering if you meant For Each/Next was better than For/Next and Do loops.

----------


## romperstomper

If you're using a collection, and you don't need to loop backwards (e.g. when deleting things) then generally use For Each, as shg said. If you're using an array, use For...Next.

A couple of others:
1. Don't use IIf.
2. Don't compare Boolean variables explicitly to True/False - it serves no purpose.

----------


## DonkeyOte

> Don't use IIf.



I confess I do use the above on occasion - presumably this is best avoided on performance basis ?

----------


## romperstomper

That, and the fact that it always evaluates _both_ parts.
The former is not usually a problem (as with many of these things) unless you're using it a lot, but that latter can take you by surprise!

----------


## StephenR

Thank you romperstomper.




> Don't compare Boolean variables explicitly to True/False - it serves no purpose.



Could you clarify what you mean by this?

----------


## DonkeyOte

> ... it always evaluates _both_ parts



As usual R, you've taught me something I didn't know.  Thanks.

As for the Booleans - a Boolean variable is by definition either True or False in it's own right - no need to evaluate it - just use the variable:




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

----------


## romperstomper

What DO said.  :Smilie:

----------


## StephenR

OK, thanks both. Someone should collate all these answers. The OP seems to have been frightened off.

----------


## JONvdHeyden

> As usual R, you've taught me something I didn't know. Thanks.



Regarding If, also apparently more efficient to use If...ElseIf...End If instead of Select Case.

----------


## romperstomper

Jon,
See 3. d. in shg's list...  :Smilie:

----------


## JONvdHeyden

> Jon,
> See 3. d. in shg's list...



Ah yes sorry, I did read the list but obviously didn't notice that one was present.  :Smilie:

----------


## StephenR

I think this covers of most of what has gone before, but no doubt I will have missed something.




> 1. Wherever you see several lines lines of very similar code look for the common bits and consider how you can use a variable (e.g. a loop counter) and cut out all the common lines.
> 2. Use small procedures for one specific task only and link them from a main 'Control' procedure. Don't keep adding lines of code to a procedure just because you can.
> 3. Don't Loop when you can filter.
> 4. Use Error Handling so your code exits gracefully when needed.
> 5. Always indent your code properly.
> 6. Apply and use a naming convention consistently (e.g variable declarations).
> 7. Use sheet CodeName when ever possible, instead of Name or Index.
> 8. Use Early Binding (some exceptions to this rule).
> 9. Explicitly use ByRef or ByVal if a procedure takes arguments.
> ...

----------


## DonkeyOte

> Regarding If, also apparently more efficient to use If...ElseIf...End If instead of Select Case.



That _arguably_ raises the delicate (?) issue of efficiency over practicality ... 

I'd nearly always opt for Select Case myself as I much prefer the structure and simplicity of syntax ... ordering of the Cases is of course important (and no Like allowed of course).

In terms of efficiency over practicality I'd throw Evaluate into the mix as an alternative to Iteration (where viable).

Bill Jelen (Excel Gurus Gone Wild) illustrates the potential performance variance using a simple example of inverting the sign of 100,000 cell values.  
Using a Loop this takes around 8 seconds whereas Evaluation takes less than a tenth of a second.
_(please note I'm not saying you would actually iterate to invert the values but still... it's a useful example)_

In reality compared to the most basic of Loop processes Evaluate is neither intuitive nor trivial to adapt [and as R has pointed out to me previously - needs to account for default Ref. Style]
_(you could probably argue the time spent setting it up would exceed the benefits gained at run time )_

So there should be some sort of trade off in IMO.... some practices are no brainers... other choices are not quite so clear cut, at least not for me.

Sorry if I'm simply repeating prior comments...

----------


## JONvdHeyden

> In terms of efficiency over practicality I'd throw Evaluate into the mix as an alternative to Iteration (where viable).
> 
> Bill Jelen (Excel Gurus Gone Wild) illustrates the potential performance variance using a simple example of inverting the sign of 100,000 cell values. 
> Using a Loop this takes around 8 seconds whereas Evaluation takes less than a tenth of a second.



Ah yes, I remember seeing this for the first time in one of your posts: http://www.excelforum.com/excel-gene...ork-sheet.html

----------


## romperstomper

I totally agree. If you were to code for pure performance, you would only ever use inline code rather than separate single-purpose subs/functions and maintaining it would be a nightmare!  :Smilie: 
(of course if you wanted real performance you wouldn't be using VBA, but that's by-the-by.)

----------


## Paul

Hearing that If...ElseIf...Else is more efficient than Select Case is interesting, as every one of my programming teachers in college said the exact opposite.  They're reasoning was that Select Case structures only check for the variable's value once and then perform each of the cases in order, whereas an If/ElseIf has to test it for every "case" as it were.  If you have a Select Case with 20 cases and one variable test at the beginning vs If..ElseIf..Else with those same 20 tests done in order, one (ok, me) would think the Select Case is faster.

Have I been led astray?!?   :Confused:

----------


## shg

Regarding IIf: I use the structure frequently if the evaluation is trivial. Compactness and clarity have their own value:




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



I am never writing five lines of code to do that, or even this one:




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



Re Case vs If:

One of the first computers I programmed was an HP minicomputer in Fortran. The compiler had a toggle that would generate a mixed listing as output: Each line or two of Fortran was followed by the exact assembly code it generated. It taught me how to both write efficient Fortran and how to write in HP Assembler (... and how to write self-modifying code, but ignore that). To bad VBA doesn't do the same, even if the output were not assembler but whatever virtual machine code the compiler uses.

And all that said, I use Select Case in preference to If/ElseIf unless I really feel it's important. It's more visually apparent.





> 5. Always indent your code properly.



Stephen, I couldn't agree more. It makes program flow much more apparent, and makes orderly code _look_ orderly, which is one of the nicest gifts you can give yourself or the other poor dog that picks up your code a year later.

----------


## broro183

Where is the sub-quoted section of Stephen's when I try to "Quote" the Shg's last post?
Is this a known issue...?
(I think I'll add it to the feedback thread & see what happens  :Wink: )





> ...
> _Insert: no sign of Stephen's subquote?_ 
> Stephen, I couldn't agree more. It makes program flow much more apparent, and makes orderly code _look_ orderly, which is one of the nicest gifts you can give yourself or the other poor dog that picks up your code a year later.



Yep, me too & I haven't had much chance to gift my work to others... yet! ;-)
I try to run people's code through Bovey's smart indenter.xla before posting, whenever the code is of reasonable length/levels. When I can see people are really keen to learn (& I remember) I suggest Smart Indenter, Code Cleaner & MZ Tools (esp after RS pointing out some extra tricks that it contains).

----------


## StephenR

Thanks shg, but I merely collated the list from others' contributions. However, I do observe that stricture. The aesthetics of VBA is a much overlooked topic...

----------


## romperstomper

I think with many of these things, the old adage about theory and practice comes into play!  :Smilie:

----------


## shg

IMO, it's like playing bridge; play third-hand high, and return your partner's leads, until you clearly understand why _not to_ on any given occasion.

----------


## tony h

Learn to use the :
- debug tools especially F8
- the object browser
- help

and when nameing variables think about how they will read eg:
*not*



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


 *but either of*



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

----------


## pierre08

I think it's complet, i don't think that there are any missing point in this:





> 1. Wherever you see several lines lines of very similar code look for the common bits and consider how you can use a variable (e.g. a loop counter) and cut out all the common lines.
> 2. Use small procedures for one specific task only and link them from a main 'Control' procedure. Don't keep adding lines of code to a procedure just because you can.
> 3. Don't Loop when you can filter.
> 4. Use Error Handling so your code exits gracefully when needed.
> 5. Always indent your code properly.
> 6. Apply and use a naming convention consistently (e.g variable declarations).
> 7. Use sheet CodeName when ever possible, instead of Name or Index.
> 8. Use Early Binding (some exceptions to this rule).
> 9. Explicitly use ByRef or ByVal if a procedure takes arguments.
> ...




What i want to add is just this, it turn off Evrything but the Essentials while your Code is Runnig, doing this should help improve the performance of your code:



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


From http://blogs.msdn.com/excel/archive/...practices.aspx

What do you think we should add to this to have a complet page of all the things that we should avoid in Vba

----------


## Domski

Something else just thought of...

If replacing formula with values rather than use copy...paste special...values use .value = .value e.g.:




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


Dom

----------


## JBeaucaire

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


This is one of my favorite tricks to use.  Any arguments against it?

----------


## StephenR

I use it too, but what is the argument for it? Is it quicker?

----------


## Domski

I remember seeing a discussion about it on Mr Excel which I'm sure suggested it should be used in preference to paste special.

I'll see if I can find it and post the link.

Dom

----------


## DonkeyOte

It avoids use of Clipboard.

----------


## 6StringJazzer

> Thank you romperstomper.
> Could you clarify what you mean by this?



I didn't see an answer to this. He means:



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


Do this instead



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

----------


## broro183

To add to DO's comment, by avoiding the clipboard, we remove the risk of a booboo caused by changing the clipboard contents in another app' if you are multi-tasking.

Hopefully I'm not quoting too far out of context here - from Charles William's site:




> It is faster (15-20%) to use the Range.Value2 property rather than the (default) Range.Value property.



http://www.decisionmodels.com/calcsecretsj.htm

So this suggests we can go one step further & faster than 


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


Rob

----------


## JBeaucaire

Maybe I'm reading that wrong, but I think the *.Value2* being discussed there relates to moving a range of cell values into an array.

Are there any benefits with use .Value2 in this context, where we are flattening values?

----------


## shg

Value2 just avoids the automatic conversion of dates and currency, and just passes Doubles.

----------


## 6StringJazzer

I'm to the party but I'll add:

*Put code to manage data on a Sheet in the module for that Sheet.*  This encapsulates the format of the sheet in one place, even though you can code it directly into Modules or other Sheets.  This makes it easier to make changes to the Sheet--you only have one place to look for impact to code.

*Don't write Functions with side effects.*  A Function's _raison d'__Ãªtre_ is to return a value.  Any other work is considered a side effect and can complicate debugging and maintenance.  (C programmers have developed a culture of taking a procedure and turning it into a function where the return value is a status indicator of whether it was successful; this is tolerable when that's the culture but is generally not a best practice.)

*Don't bend over backwards to increase efficiency when the user will never benefit.*  If a Click handler takes 0.1 seconds to execute, don't worry about getting it down to 0.01.

----------


## shg

> ... a function where the return value is a status indicator of whether it was successful; this is tolerable when that's the culture but is generally not a best practice.)



And why is that?

----------


## romperstomper

It's a very useful practice, IMO - particularly if you have a routine that calls several others and you want to exit the whole thing if any part fails.

----------


## shg

I code almost any procedure that modifies its arguments as a Boolean function.

----------


## 6StringJazzer

This will turn into a religious war quickly but I'll tell you my opinion.  If you code it as a function, it looks like a function.  It looks like code whose job is to determine a condition and return a Boolean describing that condition.  If the return value is really a status/success flag, then I code it as a Sub with a ByRef argument to return.  I also prefer the readability of naming Functions after the thing they are returning and Subs with a verb phrase.  To me




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


just doesn't work grammatically.

This is a matter of style, and not strict right/wrong, so I am not going to die fighting for this.  Like I said, if it's embraced by a culture then it will be easily recognized by others within that culture and may not be a problem.  But I prefer to code a Function as returning a value based on inputs and state, with no side effects.

----------


## pierre08

Hi guys,
Select Case is more performant than If..Else If..End If, but how can i replace If..Else If..End If with Select Case if i have several conditions for exemple:




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


Or 




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


How can i use replace If with a Select Case here??

----------


## Richard Schollar

Perhaps:




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


Richard

----------


## romperstomper

You have it the wrong way round - If... End If is faster than Select Case.

----------


## pierre08

Then why using Select Case in better Than If..End If???

----------


## Richard Schollar

> Then why using Select Case in better Than If..End If???



It's much more readable and thus easier to modify and maintain.

----------


## DonkeyOte

> Then why using Select Case in better Than If..End If???



This was covered vaguely from around post #72 ... ie efficiency versus practicality.

----------


## 6StringJazzer

The point is somewhat academic unless you've got the Select statement inside a loop executing a zillion times.  It will take the next programmer more time to figure out what efficient but weird code does than time saved in execution for the user.

----------


## Paul

> Hearing that If...ElseIf...Else is more efficient than Select Case is interesting, as every one of my programming teachers in college said the exact opposite.  They're reasoning was that Select Case structures only check for the variable's value once and then perform each of the cases in order, whereas an If/ElseIf has to test it for every "case" as it were.  If you have a Select Case with 20 cases and one variable test at the beginning vs If..ElseIf..Else with those same 20 tests done in order, one (ok, me) would think the Select Case is faster.
> 
> Have I been led astray?!?



Since it came up again I thought I'd re-ask (since I didn't get a response)... why would all my instructors tell me what I've stated above?  Are they stupid, hence making me stupid through having listened to them?

----------


## JBeaucaire

Not sure about the instructors, probably just teaching what they've been taught.

But when I'm using F8 to step through my code, *Select Case* and *If...ElseIf...Else...End If* both do the same thing...they go through the options in order listed in the code and as soon as a TRUE evaluation occurs...it skips out thus not testing any IFs or CASEs that followed.

So I wonder where the increased efficiency for one over the other even comes from?

----------


## pike

I always assumed the the difference was that "Case" exits when the first true statment is found 
 and If...ElseIf...Else... evaluates all the statments ..

----------


## JBeaucaire

No, I just ran this and it skipped to the end when it found "2" in [A1]



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

----------


## shg

> I always assumed ...



Both evaluate only to the first True condition. You know that, you just didn't stop to think it through.

----------


## 6StringJazzer

Hard to say without seeing the generated code, but in both statements you have to evaluate dynamic expressions.  Hard to say why either one would have a significant advantage.  I suspect if there's a difference it would be somewhat academic.  The IF statement require it to evaluate one expression per branch, but the Select requires it to evaluate one expression on the Select line, plus one expression per branch, so it has to do a little more evaluation.

(You compile VBA so I assume it must generate code, though I have this nagging feeling it's interpreted.)

----------


## tony h

A nice simple illustration here. If you step through it you will notice that the function myF() gets called twice in the  "If ... elseif" and only once in the case statement. Which must mean more work is being done in the "if ... elseif". This will be compounded by more complex conditions and increasing numbers of elseif.

But of potentially greater concern is that the value of test maybe different at each elseif. For example a time based test will have a different value for the comparison at each point. Whereas the "case" statement will only use one value for comparison.

Additionally there is always the possibility that the elseif is not testing exactly the same condition a myF1("0") and myF1("O") may easily be overlooked. Using a select case ensures that only the one value is being used as the source.




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

----------


## tony h

just in case (excuse the pun) it needs saying: it is also obvious that the conditions in the "case" statements could give rise to further dynamic conditions. These should, in my view, be avoided. 

My main point was to illustrate that the myF function gets evaluated on each line of the "IF" but only once in the "case"

----------


## romperstomper

A slightly contrived example, IMO, since in reality you would store the value in a variable at the start then compare the variable in the If statements, just as in the Select Case, unless you actually had a need to re-evaluate it each time.  :Smilie: 
Note: in languages like C++, I believe that switch statements *are* more efficient due to the way the compiler optimises them.

----------


## martindwilson

don't eat yellow snow !

----------


## shg

> A slightly contrived example, IMO ...



And if the return value of myF() could vary within the same code block (e.g., Rnd()), it would be a very wacky piece of code.

----------


## tony h

> And if the return value of myF() could vary within the same code block (e.g., Rnd()), it would be a very wacky piece of code.



I have seen it happen  :EEK!:  

Romperstomper : you are right I would store the value in a variable, but not everyone else does.

----------


## romperstomper

That's true, but it's not really a demonstration of the performance of Select Case versus If... Else.  :Smilie:

----------


## Rick_Stanich

Deleted.
Already covered...

----------


## Rick_Stanich

> As usual R, you've taught me something I didn't know.  Thanks.
> 
> As for the Booleans - a Boolean variable is by definition either True or False in it's own right - no need to evaluate it - just use the variable:
> 
> 
> 
> 
> ```
> Please Login or Register  to view this content.
> ```



Lost me on this one.
If boolTest is false then you return "Hello" or if its true you return "Hello"?
What if the condition of boolTest is unknown and you only want an action if it is "true"?

Is a Boolean always testing for one or the other value by default? (True or False)

----------


## romperstomper

Yes, a Boolean is always True or False. Hence this:



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


translates as: "If True = True (which equals True)"
or "If False = True (which equals False)"
so the comparison is unnecessary since the result is always the same as the original variable. So rather than this:



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


you should use this:



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


Make sense?

----------


## Rick_Stanich

Yes and no...
I understand what you have written but I dont understand how a condition is known?
If the condition is false dont do the msgbox
If the condition is true do the msgbox

If you you dont compare the variable how do you know what the condition is?
IE: if boolTest = True then

To me it reads that msgbox "Hello" will trigger whether the condition is true or false?

----------


## romperstomper

Because the variable's value is already either True or False.
The If test evaluates the _expression_ to see if it evaluates to True or False, but as I demonstrated, the evaluation will always be the same as the variable's initial value. In other words, if the variable's value is True, then the expression (True = True) will evaluate to True. If the variable's value is False, then the expression evaluates to False. As a result the check is completely unnecessary.
In code terms, you are doing either:



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


or:



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

----------


## pierre08

Thank you all for your help
If anyone have some extra ideas it will be great

----------


## pierre08

hi guys,
I have a new one:



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


I was a little surpise when i saw it, i didn't know that

----------


## Domski

True. It's a mistake quite a lot of people make.

Dom

----------


## tony h

> Because the variable's value is already either True or False.
> The If test evaluates the _expression_ to see if it evaluates to True or False, but as I demonstrated, the evaluation will always be the same as the variable's initial value. In other words, if the variable's value is True, then the expression (True = True) will evaluate to True. If the variable's value is False, then the expression evaluates to False. As a result the check is completely unnecessary.



You are obviously quite right but I think their is an issue of legibility. Sometimes I think putting in the =FALSE just reduces risk.

There are a number of things I tend to do because I think there is less opportunity for mis-reading code later. The aspect of supportability is, in most case, more important than trivial performance improvement.

Now in the boolean value question, as I posted earlier in this thread, I try to ensure the variable is named so that there is no doubt how the value is set. A variable called "boolTest" can be misinterpreted whereas calling the variable IsBoolTestTrue leaves no room for doubt.

Similarly I usually prefer the form of:



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


when you could write



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

----------


## Paul

> hi guys,
> I have a new one:
> 
> 
> 
> ```
> Please Login or Register  to view this content.
> ```
> 
> ...



Due to the way the VB engine handles variable types, you should forego using Integer in the first place.  Instead, use Long.



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


http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

----------


## pierre08

Avoid overuse of worksheet functions in code 

Here's an example. VBA doesn't have a Max or Min function, but Excel does. So you could write.. 



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


or do it the hard way in code like this 



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


Now you won't believe the next part if you haven't seen it already. The code method is about 150 times faster (on my PC, anyway). So if you are doing some big loops, avoid worksheet functions if you can, or at least test comparative speed. 

Some built-in VBA functions are real slow too. It's worth testing the speed on any you use a lot, just to be sure. 

From http://http://www.avdf.com/apr98/art_ot003.html

This is really intersting.

----------


## batman

If speed of execution is important, try to minimizes interaction with a worksheet, especially writing to a worksheet - that's one of the VBA's slowest tasks. If you need to juggle and massage a block of data, read it into an array, work on it there with VBA code, and then write the array back to the worksheet.

----------


## pierre08

Hi Batman, do you have the code to do this, to copy a worksheet in an array and then write the array back to the worksheet

Thanks for your help

----------


## broro183

hi Pierre08,

Here's an example of reading a range to an array & then writing it back to the worksheet + some comments from knowledgeable Excel whizzes: http://www.dailydoseofexcel.com/arch...nge-using-vba/ (note the links about limitations that Erik & Patrick have included in their comments)
There's also quite a long thread (I haven't read it all yet) here which may be of interest too: http://www.mrexcel.com/forum/showthread.php?t=389275 

hth
Rob

----------


## Paul

At a very basic level, you could use the following.  You can do much more with it, though..



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

----------


## JBeaucaire

I was given and have been using this syntax for quite some time....apparently when you read in a column of data into an array, it needs to be transposed:




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


And there is some reason I cannot recall to use Value2 instead of Value, though both work.  I'll poke around and see if I can find the thread where I was given these....

----------


## DonkeyOte

Re: value2

http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

Re: transposing... a vertical vector pushed into a variant array would become a 2 dimensional array by default - transposing a vertical vector once will dispense with the 2nd dimension... to do the same for a horizontal vector you transpose twice over.

----------


## JBeaucaire

Re: Value2

Based on that info, Don, I can't recall the benefits derived from using it.  I was encouraged to do so on some macro long ago, and have used it since with no known problems, but now it escapes me why it is good/useful to do so.

 :Confused:   :Roll Eyes (Sarcastic):

----------


## shg

All numeric values on a worksheet are Doubles; Currency and Date are uniquely VBA formats. Trying to mix the two can cause problems (e.g., lookups into a variant array).

Here's what I've been using lately:



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


For example,



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

----------


## pierre08

Don't use GoSub unless you have a clarity and maintainability in your code, performance gains come at the expense of legibility

----------


## Domski

> Don't use GoSub



I wondered if you might add that  :Smilie:

----------


## pierre08

Hey guys,

Just a reminder that this topic exist if you would like to add something new :D.

And thanks for all who participate.

----------


## pierre08

What do you think about the Object-oriented programming in VBA for Excel. I have never done it. So if anyone use it i would really like to know how does it work and if it's more effective than the standard programing, or if you just have some recomandations to give.

Thanks all.

----------


## broro183

hi Pierre08,

Do you have any definitions or links for what you consider (or have read) to be "Object-oriented programming in VBA for Excel" & what you consider as "standard programming"?

Rob

----------


## snb

> I was given and have been using this syntax for quite some time....apparently when you read in a column of data into an array, it needs to be transposed:
> 
> 
> 
> 
> ```
> Please Login or Register  to view this content.
> ```
> 
> ...



@Jerry
This is my syntax:



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


I use this code often because it turns a multidimensional array into a 1-dimensional one.
And then you can use 'join', 'filter' and 'split'

Or the other way around: if you want to fill a column using a 1-dimensional array




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


or in combination with evaluate:




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

----------


## shg

JB,

If a cell has a number formatted as currency, reading that value into a Variant with the Value property will coerce the result to a Currency data type. 

If the cell has a number formatted as a date, it will be coerced into a Date data type.

The Value2 property doesn't perform those conversions.

----------


## broro183

Hi everyone,





> ```
> Please Login or Register  to view this content.
> ```
> 
> 
> I use this code often because it turns a multidimensional array into a 1-dimensional one.



Here is an old thread which discusses (or links to) the limitations of the two Transpose methods: http://www.excelforum.com/excel-prog...transpose.html

hth
Rob

----------


## JBeaucaire

Thanks SHG, good info to have!


@snb, thanks for the first example.

That second is so nested I can't even wrap my head around it.  Oh well.  Someday.

----------


## snb

@JB

The second one was only for fun (see what it does).

But it contains another functionality of transpose : the shortest way to convert the values in a column/row into a string




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

----------


## 6StringJazzer

> hi Pierre08,
> 
> Do you have any definitions or links for what you consider (or have read) to be "Object-oriented programming in VBA for Excel" & what you consider as "standard programming"?
> 
> Rob



VBA is an object-oriented language (more or less). To use it effectively for Excel you have to understand the Excel object model (e.g., Range is a class). So if you are using VBA, you are using object-oriented programming whether you realize it or not. 

However, most people take a procedural approach to writing VBA. (A _procedural_ language is a more traditional programming language like C where you have data types and functions, rather than objects, attributes, and methods.) That is, they use the Excel objects but then write a bunch of Subs that call other Subs.

You can also define your own classes in VBA, and instantiate objects using those classes. That would be true object-oriented programming. Here is an example of how to do this in Access but exactly the same principles apply in Excel.

BTW the word "standard" used to describe programming really doesn't have any meaning, because the meaning would change about every six months.  :Smilie:

----------


## shg

> the shortest way to convert the values in a column/row into a string
> 
> 
> 
> 
> ```
> Please Login or Register  to view this content.
> ```



Hmm.



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

----------


## Leith Ross

@ SixStringJazzer (Post #150)

VBA is an Object Based Programming language. There are several tests a language must meet to be considered as Object Oriented.

The language must support: Dynamic Dispatch Polymorphism Inheritance Open Recursion
VBA supports all of these except Polymorphism (2). This classifies VBA as Object Based rather than Object Oriented.

----------


## Domski

That's one of those posts I just read and think..."Nope, not a clue what they're talking about."  :Confused: 

Dom

----------


## jaslake

Me too...I actually looked it up...still have no clue. :Smilie:

----------


## Domski

Polymorphism sounds painful whatever objects are involved  :Wink: 

Dom

----------


## snb

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



mmm  ...   mmm



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

----------


## shg

Sorry, I didn't mean fewest characters, but the fewest functions -- two versus four (including the Evaluate)

----------


## snb

You quoted 'shortest'    :Wink:

----------


## broro183

Thanks Leith (post # 152) :-)

I had pretty much the same thoughts as 6StringJazzer (post # 150), but I thought there may have been some sort of catch which is why I asked what Pierre08's current understanding was.
- Now I have another phrase to read up on ;-)

6StringJazzer, I agree with your comments on "standard programming" when used so broadly, but if we/Pierre08 limited the scope to VBA, then we could _possibly_ (I'm preparing to be shot down!) say that many _principles (as outlined throughout this thread)_ have remained the same for a number of years & the main changes are just a few methods/properties in 2007-2010 editions. 

Rob

----------


## Obfuscated

I want ALL you folks to know that this thread has taught me BUNCHES. I appreciate all the time you've all given to helping guys like me out with answers to specific questions as well as threads like this that no amount of searching would have turned up. Thanks again, and please continue...

Doug

----------


## pierre08

Hey guys,
Thanks all for your answer.
What i meant by "Object-oriented programming in VBA for Excel" is:
can we write codes in VBA Excel like we do in C++, what i mean is would it be better for exemple in steed of writing diffrent function in different modules, would it be better to call from the main or other function or faster in the execution of the code if we put them in classes (for exemple a function of personiliz sorting of data...).

What do you think?
Does anyone use "Classes" in VBA, and how do you use them and what for?

The purpose of these questions is that i have a really big project in VBA where i should have a dozens of new functions and commands, so i really rather to know what is the best way to do it.

Thanks all for your answers.

----------


## romperstomper

Big topic. See Chip's page here for starters.

----------


## snb

To give you an impression on using a class for events in userformelements see this attachment.

----------


## pierre08

Thanks guys, this might be very usefull.

----------


## pierre08

Hi Guys!!!!!
It's been a long time since this thread hasn't been update!!!
This is a little summury of all the points that we passed on. So what to avoid in VBA  :Wink: 






> I think this covers of most of what has gone before, but no doubt I will have missed something.







> 1. Wherever you see several lines lines of very similar code look for the common bits and consider how you can use a variable (e.g. a loop counter) and cut out all the common lines.
> 2. Use small procedures for one specific task only and link them from a main 'Control' procedure. Don't keep adding lines of code to a procedure just because you can.
> 3. Don't Loop when you can filter.
> 4. Use Error Handling so your code exits gracefully when needed.
> 5. Always indent your code properly.
> 6. Apply and use a naming convention consistently (e.g variable declarations).
> 7. Use sheet CodeName when ever possible, instead of Name or Index.
> 8. Use Early Binding (some exceptions to this rule).
> 9. Explicitly use ByRef or ByVal if a procedure takes arguments.
> ...




What i want to add is just this, it turn off Evrything but the Essentials while your Code is Runnig, doing this should help improve the performance of your code:



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


From http://blogs.msdn.com/excel/archive/...practices.aspx

What do you think we should add to this to have a complet page of all the things that we should avoid in Vba





> Something else just thought of...
> 
> If replacing formula with values rather than use copy...paste special...values use .value = .value e.g.:
> 
> 
> 
> 
> ```
> Please Login or Register  to view this content.
> ...







> I'm to the party but I'll add:
> 
> *Put code to manage data on a Sheet in the module for that Sheet.*  This encapsulates the format of the sheet in one place, even though you can code it directly into Modules or other Sheets.  This makes it easier to make changes to the Sheet--you only have one place to look for impact to code.
> 
> *Don't write Functions with side effects.*  A Function's _raison d'__Ãªtre_ is to return a value.  Any other work is considered a side effect and can complicate debugging and maintenance.  (C programmers have developed a culture of taking a procedure and turning it into a function where the return value is a status indicator of whether it was successful; this is tolerable when that's the culture but is generally not a best practice.)
> 
> *Don't bend over backwards to increase efficiency when the user will never benefit.*  If a Click handler takes 0.1 seconds to execute, don't worry about getting it down to 0.01.







> hi guys,
> I have a new one:
> 
> 
> 
> ```
> Please Login or Register  to view this content.
> ```
> 
> ...







> Avoid overuse of worksheet functions in code 
> 
> Here's an example. VBA doesn't have a Max or Min function, but Excel does. So you could write.. 
> 
> 
> 
> ```
> Please Login or Register  to view this content.
> ```
> ...

----------


## pierre08

This is the summury of eveything we have past in this thread. I used all that in Excel 2003, so i don't know is there is some new points to add to this thread for Excel 2010 & 2011.

Up to you guys.

----------


## pierre08

Hey guys,

It's been a long time. Since this topics is full of good advise for VBA i just remenbered one more thing very useful: it's a toolbar that you can install in your Visual Basic: *MZ-Tools*, it contains a lot of useful and helpful functions.

And don't forget if you have other things to add to this topic about the recent version of Excel, or the changes between the versions it'll be very helpful.

Thanks guys.

----------


## pierre08

Hello guys, it has been a while!!!!

Just to relaunch the topic. With Excel 2007, 2010 & 2013, is there any evolutions or optimization of the code, and what are the things to avoid and the recommended ways of writing the macros.

Thanks guys/

----------


## JosephP

vba hasn't changed much other than adding 64bit compatibility :-)

----------


## ecoplan

Excel AutoFilter & Range("A65536").End(xlUp).Row +1 are extremely dangerous when combined. It will get you the last filtered value +1, not the actual first empty cell! For safety, use "insert table rows above" or "insert row" for adding data to a sheet. (your new data will be on top, which is nice and more practical. Also, it should be more efficient since there is no calculation involved by dropping Range("A65536").End(xlUp).Row)

----------


## shg

Or turn the filter off first.

----------


## ecoplan

> Or turn the filter off first.



True! But with 650+ controls, I want to play it safe and learn another way to code (safest IMO) and not hating myself if I once forget to turn off filters!

----------


## Kamolga

> I use it frequently, it is simple and effective to remove case-sensitivity from a macro rendering it easier to code and easier to read.  Good enough for me.



I don't get it and try to understand what option compare text do.

I basically pay attention to upper/lower case when I dim only as when I code I systematicaly type 2 letters and use CTRL+space to select command,variable,long,etc. (otherwise I have a mispell every 2 lines)

----------


## pierre08

Hello guys,

It has been almost 6 years that this thread is open, with a lot of good advice.
You have the summary here of what to avoid in VBA:
http://www.excelforum.com/showthread...=1#post2625072

This has been done mainly on Excel 2003.
Do you have any new recommandations, best practices, what to avoid in the next versions? 2007, 2010, 2013 & 2016?

It would be very interesting to share your experiences  :Cool: 

Thanks guys  :Smilie:

----------


## pierre08

Hello Everyone!

Long time since I've read this thread again. Still very useful, even in today's versions. Thank you all for this.

One year ago I asked you if you have any new recommandations, best practices, what to avoid in the next versions? 2007, 2010, 2013 & 2016?

Still waiting for your feedback, it would be very interesting to share your experiences  :Cool:  for all the new members.

Thank you  :Smilie:

----------


## Marc L

Hi !

General rules whatever the version :

• Think Excel Before VBA : Excel inner functions are often more efficients than some _loop_ codes …

• Think, But Think Objects : respecting Excel model object is far efficient than using _Activate_, _Select_, loops, …

• Think International : a local formula does not work in another language Excel version so stay in VBA native english.

----------


## blakec4

> hi everybody,
> 
> i'm trying to do a little summary about all the mistakes or the ways that we should avoid when we write a Macro in Excel.
> 
> I hope that everyone participate so we could share our experience. I think it can be really interesting.
> 
> Thank you all for you time .



Don't use static references to workbooks.  Use dynamic named ranges.

----------


## pierre08

Hello Everyone!

Long time since I've read this thread again. Still very useful, even in today's versions. Thank you all for this.

More than one year ago I asked you if you have any new recommandations, best practices, what to avoid in the next versions? 2007, 2010, 2013, 2016 & Office 365?

Still waiting for your feedback, it would be very interesting to share your experiences  for all the new members.

Thank you

----------


## LIL2606

This might be common sense, but I found the most important thing (especially if you are still just learning) to understand your code. This site is super useful and people often help out with a fully working code if you ask a question, but I think its important that you don't just copy paste it into your workbook, and see that it does what you asked and acknowledge it, but actually understand how does the code do what it does, and for that I think it would be useful if people did comment their code more..

----------


## Richard Buttrey

> This might be common sense,.........and for that I think it would be useful if people did comment their code more..



That is probably correct during the learning process, however I saw an interesting comment the other day which said that good code should be its 'own comment' and capable of easy interpretation and understanding which avoids the necessity for additional narrative.
In any case if commenting code at all it should be restricted to the procedure in which it resides and not to any other higher or lower levels.

----------


## teylyn

On that topic:  https://rubberduckvba.wordpress.com/...-1-bad-habits/





> VBA is essentially stuck in 1998. Most of its commonly agreed-upon best practices are from another era, and while developers in every single other language moved on to more modern conventions, a lot of VBA folks are (sometimes firmly) holding on to coding practices that are pretty much universally considered harmful today: this has to be part of why so many programmers dread maintaining VBA code so much.

----------

