Thread: What to Avoid in VBA

Originally Posted by JONvdHeyden

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...

Originally Posted by DonkeyOte

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

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!
(of course if you wanted real performance you wouldn't be using VBA, but that's by-the-by.)

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?!?

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

Code:

sName = IIf(bFlag, "Bob", "Joe")

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

Code:

If bFlag Then sName = "Bob" Else sName = "Joe"

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.

Originally Posted by StephenR

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.

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 )

Originally Posted by shg

...
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).

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

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.

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

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

Code:

    bReset = TRUE        'does this mean the reset has been done or need to do a reset
       if bReset then

but either of

Code:

    bIsReset = TRUE
       if bIsReset then

    bDoReset = TRUE
       if bDoReset then

Something else just thought of...

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

Code:

With Range("A1:A10")

    .Value = .Value
    
End With

Dom

Code:

.value = .value

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

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