+ Reply to Thread
Results 1 to 181 of 181

What to Avoid in VBA

  1. #1
    Forum Contributor pierre08's Avatar
    Join Date
    02-16-2010
    Location
    Paris
    MS-Off Ver
    Excel 2003
    Posts
    166

    Thumbs up What to Avoid in VBA

    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 .
    Last edited by pierre08; 02-17-2010 at 07:10 AM.

  2. #2
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,186

    Re: What to Avoid in VBA!!!

    always use : option explicit


    click on the * Add Reputation if this was useful or entertaining.

  3. #3
    Forum Contributor pierre08's Avatar
    Join Date
    02-16-2010
    Location
    Paris
    MS-Off Ver
    Excel 2003
    Posts
    166

    Re: What to Avoid in VBA!!!

    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

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: What to Avoid in VBA!!!

    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.

  5. #5
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: What to Avoid in VBA!!!

    nice link, DO. Straight into favs!

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: What to Avoid in VBA!!!

    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
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  7. #7
    Forum Contributor pierre08's Avatar
    Join Date
    02-16-2010
    Location
    Paris
    MS-Off Ver
    Excel 2003
    Posts
    166

    Re: What to Avoid in VBA!!!

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

  8. #8
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What to Avoid in VBA!!!

    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.
    Remember what the dormouse said
    Feed your head

  9. #9
    Forum Contributor pierre08's Avatar
    Join Date
    02-16-2010
    Location
    Paris
    MS-Off Ver
    Excel 2003
    Posts
    166

    Re: What to Avoid in VBA!!!

    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.

  10. #10
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What to Avoid in VBA!!!

    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.

  11. #11
    Forum Contributor pierre08's Avatar
    Join Date
    02-16-2010
    Location
    Paris
    MS-Off Ver
    Excel 2003
    Posts
    166

    Re: What to Avoid in VBA!!!

    yeah i guess your right

    Sorry

  12. #12
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,186

    Thumbs up Re: What to Avoid in VBA!!!

    Always be nice to people on ExcelForum

  13. #13
    Forum Contributor pierre08's Avatar
    Join Date
    02-16-2010
    Location
    Paris
    MS-Off Ver
    Excel 2003
    Posts
    166

    Re: What to Avoid in VBA!!!

    Come on guys is that all !
    Come on some motivation please
    Last edited by pierre08; 02-17-2010 at 11:13 AM.

  14. #14
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: What to Avoid in VBA!!!

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

  15. #15
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: What to Avoid in VBA!!!

    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.
    The much faster method is to avoid selection completely and directly reference the shape:

    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.
    Entia non sunt multiplicanda sine necessitate

  16. #16
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: What to Avoid in VBA!!!

    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"

    4) Shorter code is not always better code

    5) Use Error Handling so your code exits gracefully when needed
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  17. #17
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: What to Avoid in VBA!!!

    Quote Originally Posted by JBeaucaire View Post
    ...
    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
    Rob Brockett
    Kiwi in the UK
    Always learning & the best way to learn is to experience...

  18. #18
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What to Avoid in VBA!!!

    Quote Originally Posted by JBeaucaire View Post
    3) Don't give up so easily on "Option Compare Text"
    Feel free to elaborate on why this might be a good idea...

  19. #19
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: What to Avoid in VBA

    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.

  20. #20
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What to Avoid in VBA

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

  21. #21
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    Re: What to Avoid in VBA

    JB,

    Have an example on this
    Quote Originally Posted by JB
    1) Don't Loop when you can filter
    Very interested as mostly looping (For each) is very readable but slow.
    Looking for great solutions but hate waiting?
    Seach this Forum through Google

    www.Google.com
    (e.g. +multiple +IF site:excelforum.com/excel-general/ )

    www.Google.com
    (e.g. +fill +combobox site:excelforum.com/excel-programming/ )

    Ave,
    Ricardo

  22. #22
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    Re: What to Avoid in VBA

    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 !
    Last edited by rwgrietveld; 02-18-2010 at 04:53 AM.

  23. #23
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: What to Avoid in VBA

    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.

  24. #24
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    Re: What to Avoid in VBA

    OK clear. Something like this?
    HTML Code: 

  25. #25
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: What to Avoid in VBA

    Avoid using VBA reserved words as variable names.

    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    Use code tags when posting your VBA code: [code] Your code here [/code]

    Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.

  26. #26
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: What to Avoid in VBA

    Quote Originally Posted by Domski View Post
    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+.

  27. #27
    Forum Contributor
    Join Date
    04-16-2009
    Location
    Stellenbosch, South Africa
    MS-Off Ver
    Excel 2003; Excel 2007; Excel 2010; Excel 2013
    Posts
    136

    Re: What to Avoid in VBA

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

    To name but a few....
    Last edited by JONvdHeyden; 02-18-2010 at 06:56 AM.
    Regards

    Jon (Excel 2003, 2007, 2010, 2013)

  28. #28
    Forum Contributor
    Join Date
    04-16-2009
    Location
    Stellenbosch, South Africa
    MS-Off Ver
    Excel 2003; Excel 2007; Excel 2010; Excel 2013
    Posts
    136

    Re: What to Avoid in VBA

    Quote Originally Posted by JBeaucaire View Post
    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.
    Last edited by JONvdHeyden; 02-18-2010 at 07:45 AM.

  29. #29
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    Re: What to Avoid in VBA

    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)

  30. #30
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: What to Avoid in VBA

    Quote Originally Posted by rwgrietveld View Post
    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
    Last edited by Domski; 02-18-2010 at 08:21 AM. Reason: Wrong link

  31. #31
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: What to Avoid in VBA

    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.

  32. #32
    Forum Contributor
    Join Date
    04-16-2009
    Location
    Stellenbosch, South Africa
    MS-Off Ver
    Excel 2003; Excel 2007; Excel 2010; Excel 2013
    Posts
    136

    Re: What to Avoid in VBA

    Quote Originally Posted by rwgrietveld View Post
    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.
    Last edited by JONvdHeyden; 02-18-2010 at 08:34 AM.

  33. #33
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,186

    Re: What to Avoid in VBA

    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.

  34. #34
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    Re: What to Avoid in VBA

    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 ?
    Last edited by rwgrietveld; 02-18-2010 at 08:49 AM.

  35. #35
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,244

    Re: What to Avoid in VBA

    @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.
    Cheers
    Andy
    www.andypope.info

  36. #36
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    Re: What to Avoid in VBA

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

  37. #37
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: What to Avoid in VBA

    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?

  38. #38
    Forum Contributor
    Join Date
    04-16-2009
    Location
    Stellenbosch, South Africa
    MS-Off Ver
    Excel 2003; Excel 2007; Excel 2010; Excel 2013
    Posts
    136

    Re: What to Avoid in VBA

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

  39. #39
    Registered User
    Join Date
    01-12-2010
    Location
    Sydney
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: What to Avoid in VBA

    Range.Replace.

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

  40. #40
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: What to Avoid in VBA

    Quote Originally Posted by David Huang View Post
    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

  41. #41
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,186

    Re: What to Avoid in VBA

    Quote Originally Posted by Andy Pope View Post
    @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

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

  42. #42
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: What to Avoid in VBA

    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.
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  43. #43
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,186

    Re: What to Avoid in VBA

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

  44. #44
    Forum Contributor
    Join Date
    04-16-2009
    Location
    Stellenbosch, South Africa
    MS-Off Ver
    Excel 2003; Excel 2007; Excel 2010; Excel 2013
    Posts
    136

    Re: What to Avoid in VBA

    Quote Originally Posted by Ron Coderre View Post
    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.
    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).

  45. #45
    Valued Forum Contributor Richard Schollar's Avatar
    Join Date
    05-23-2006
    Location
    Hampshire UK
    MS-Off Ver
    Excel 2002
    Posts
    1,264

    Re: What to Avoid in VBA

    Quote Originally Posted by tony h View Post
    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
    Richard Schollar
    Microsoft MVP - Excel

  46. #46
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: What to Avoid in VBA

    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"

  47. #47
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,244

    Re: What to Avoid in VBA

    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.

  48. #48
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    Re: What to Avoid in VBA

    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?

  49. #49
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: What to Avoid in VBA

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

    Dom

  50. #50
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: What to Avoid in VBA

    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.

  51. #51
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,186

    Re: What to Avoid in VBA

    Quote Originally Posted by RichardSchollar View Post
    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.


  52. #52
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What to Avoid in VBA

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

  53. #53
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,186

    Re: What to Avoid in VBA

    Quote Originally Posted by tony h View Post
    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.

  54. #54
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: What to Avoid in VBA

    Quote Originally Posted by David
    Range.Replace.

    If you do find or replace Within Workbook outside of VBA, then run range.replace in VBA, it replaces in whole workbook.
    Quote Originally Posted by Dom
    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.

  55. #55
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: What to Avoid in VBA



    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

  56. #56
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: What to Avoid in VBA

    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.

  57. #57
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What to Avoid in VBA

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

  58. #58
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: What to Avoid in VBA

    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.
    Last edited by shg; 02-18-2010 at 06:30 PM.

  59. #59
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: What to Avoid in VBA

    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.

  60. #60
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: What to Avoid in VBA

    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.
    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.
    Last edited by shg; 02-18-2010 at 06:31 PM.

  61. #61
    Forum Contributor
    Join Date
    04-16-2009
    Location
    Stellenbosch, South Africa
    MS-Off Ver
    Excel 2003; Excel 2007; Excel 2010; Excel 2013
    Posts
    136

    Re: What to Avoid in VBA

    Quote Originally Posted by shg View Post
    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.

  62. #62
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881

    Re: What to Avoid in VBA

    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.


  63. #63
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,186

    Re: What to Avoid in VBA

    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

  64. #64
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: What to Avoid in VBA

    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.

  65. #65
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What to Avoid in VBA

    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.

  66. #66
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: What to Avoid in VBA

    Quote Originally Posted by R
    Don't use IIf.
    I confess I do use the above on occasion - presumably this is best avoided on performance basis ?

  67. #67
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What to Avoid in VBA

    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!

  68. #68
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: What to Avoid in VBA

    Thank you romperstomper.
    Don't compare Boolean variables explicitly to True/False - it serves no purpose.
    Could you clarify what you mean by this?

  69. #69
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: What to Avoid in VBA

    Quote Originally Posted by romperstomper View Post
    ... 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.
    Last edited by DonkeyOte; 02-19-2010 at 09:45 AM. Reason: removed formula bit - irrelevant - and added in the Boolean type (duh)

  70. #70
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What to Avoid in VBA

    What DO said.

  71. #71
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: What to Avoid in VBA

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

  72. #72
    Forum Contributor
    Join Date
    04-16-2009
    Location
    Stellenbosch, South Africa
    MS-Off Ver
    Excel 2003; Excel 2007; Excel 2010; Excel 2013
    Posts
    136

    Re: What to Avoid in VBA

    Quote Originally Posted by DonkeyOte View Post
    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.

  73. #73
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What to Avoid in VBA

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

  74. #74
    Forum Contributor
    Join Date
    04-16-2009
    Location
    Stellenbosch, South Africa
    MS-Off Ver
    Excel 2003; Excel 2007; Excel 2010; Excel 2013
    Posts
    136

    Re: What to Avoid in VBA

    Quote Originally Posted by romperstomper View Post
    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.

  75. #75
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: What to Avoid in VBA

    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.
    10. Avoid Option Compare Text.
    11. Use class modules for global variables.
    12. If you have a non-general error trap (ie a condition where an error is expected) put that specific code in a submodule or function.
    13. Use descriptive variable names (with possible exception of transient local variables).
    14. Keep scoping as local as possible with prefixes to identify the scope as well as variable type.
    15. Declare all variables appropriate to usage.
    16. Use Variants only when necessary ((i) To receive an argument that may be of varying type (string, Boolean, number, or arrays of same); (ii) To receive a value from a cell than may be of varying type; (iii) To receive the value of a multi-cell range from a worksheet; (iv) To receive an indefinite number of arguments (ParamArray is a variant); (v) To receive the results from many worksheet functions, like MMult and Array; (vi) To receive the value of a property that may return Null; (vii) To receive the keys of a dictionary; (viii) To be the control variable of a For Each loop when not a more closely-aligned data type is available (e.g., a Range); (ix) To be the return type of a function that returns data of varying type, or (on Mac) an array of variable size).
    17. Don’t select cells, sheets, workbooks, or other objects except as necessary.
    18. Generally, use built-in (worksheet) functions when possible (there are some exceptions).
    19. Don’t use loops if there’s a way around it; if there isn’t, prefer iterating elements from a collection (For Each/Next), followed by For/Next and Do/Loop.
    20. Select Case statements are prettier, If/ElseIf/Else/EndIf statements are faster. Either should be arranged to test cases from most likely to least.
    21. Use collections and dictionaries in preference to other data structures when possible, and use dictionaries instead of collections, unless ordering is required.
    22. Use the Like operator sparingly. Ditto for Regular Expressions. Avoid text comparison entirely when there are other options (e.g., TypeOf versus TypeName).
    23. The Len function is among Excel's fastest; all it has to do is grab the first word of a string, where the length is stored. So rather than If s <> "", use If Len(s) (and there’s no need to coerce a number to a Boolean so it can be converted back to a number).
    24. Write logical, clear code first, and then optimize for speed. Don’t obscure the structure and intent of code for speed unless you don’t mind having the person that has to maintain it later casting aspersions on your lineage.
    25. Comment your code minimally beyond the procedure level; it’s impossible to keep code and comments in sync. Instead, make code shine in clarity and simplicity.
    26. Set Calculation to manual during execution. If worksheet calculation is necessary in the course of macro execution, calculate only the minimum range necessary.
    27. Turn off ScreenUpdating during execution.
    28. Turn off events if necessary to avoid triggering change event macros and getting other listeners excited.
    29. And use dictionaries instead of collections, unless ordering is required.
    30. Don't use IIf.
    31. Don't compare Boolean variables explicitly to True/False - it serves no purpose.

  76. #76
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: What to Avoid in VBA

    Quote Originally Posted by JONvdHeyden View Post
    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...
    Last edited by DonkeyOte; 02-19-2010 at 11:54 AM.

  77. #77
    Forum Contributor
    Join Date
    04-16-2009
    Location
    Stellenbosch, South Africa
    MS-Off Ver
    Excel 2003; Excel 2007; Excel 2010; Excel 2013
    Posts
    136

    Re: What to Avoid in VBA

    Quote Originally Posted by DonkeyOte View Post
    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

  78. #78
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What to Avoid in VBA

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

  79. #79
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881

    Re: What to Avoid in VBA

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

  80. #80
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: What to Avoid in VBA

    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.

    Quote 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.
    Last edited by shg; 02-19-2010 at 01:46 PM.

  81. #81
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: What to Avoid in VBA

    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 )

    Quote Originally Posted by shg View Post
    ...
    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).

  82. #82
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: What to Avoid in VBA

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

  83. #83
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What to Avoid in VBA

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

  84. #84
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: What to Avoid in VBA

    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.

  85. #85
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,186

    Re: What to Avoid in VBA

    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.

  86. #86
    Forum Contributor pierre08's Avatar
    Join Date
    02-16-2010
    Location
    Paris
    MS-Off Ver
    Excel 2003
    Posts
    166

    Re: What to Avoid in VBA

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

    Quote Originally Posted by StephenR View Post
    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.
    10. Avoid Option Compare Text.
    11. Use class modules for global variables.
    12. If you have a non-general error trap (ie a condition where an error is expected) put that specific code in a submodule or function.
    13. Use descriptive variable names (with possible exception of transient local variables).
    14. Keep scoping as local as possible with prefixes to identify the scope as well as variable type.
    15. Declare all variables appropriate to usage.
    16. Use Variants only when necessary ((i) To receive an argument that may be of varying type (string, Boolean, number, or arrays of same); (ii) To receive a value from a cell than may be of varying type; (iii) To receive the value of a multi-cell range from a worksheet; (iv) To receive an indefinite number of arguments (ParamArray is a variant); (v) To receive the results from many worksheet functions, like MMult and Array; (vi) To receive the value of a property that may return Null; (vii) To receive the keys of a dictionary; (viii) To be the control variable of a For Each loop when not a more closely-aligned data type is available (e.g., a Range); (ix) To be the return type of a function that returns data of varying type, or (on Mac) an array of variable size).
    17. Don’t select cells, sheets, workbooks, or other objects except as necessary.
    18. Generally, use built-in (worksheet) functions when possible (there are some exceptions).
    19. Don’t use loops if there’s a way around it; if there isn’t, prefer iterating elements from a collection (For Each/Next), followed by For/Next and Do/Loop.
    20. Select Case statements are prettier, If/ElseIf/Else/EndIf statements are faster. Either should be arranged to test cases from most likely to least.
    21. Use collections and dictionaries in preference to other data structures when possible, and use dictionaries instead of collections, unless ordering is required.
    22. Use the Like operator sparingly. Ditto for Regular Expressions. Avoid text comparison entirely when there are other options (e.g., TypeOf versus TypeName).
    23. The Len function is among Excel's fastest; all it has to do is grab the first word of a string, where the length is stored. So rather than If s <> "", use If Len(s) (and there’s no need to coerce a number to a Boolean so it can be converted back to a number).
    24. Write logical, clear code first, and then optimize for speed. Don’t obscure the structure and intent of code for speed unless you don’t mind having the person that has to maintain it later casting aspersions on your lineage.
    25. Comment your code minimally beyond the procedure level; it’s impossible to keep code and comments in sync. Instead, make code shine in clarity and simplicity.
    26. Set Calculation to manual during execution. If worksheet calculation is necessary in the course of macro execution, calculate only the minimum range necessary.
    27. Turn off ScreenUpdating during execution.
    28. Turn off events if necessary to avoid triggering change event macros and getting other listeners excited.
    29. And use dictionaries instead of collections, unless ordering is required.
    30. Don't use IIf.
    31. Don't compare Boolean variables explicitly to True/False - it serves no purpose. .

    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

  87. #87
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: What to 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.
    Dom

  88. #88
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: What to Avoid in VBA

    Please Login or Register  to view this content.
    This is one of my favorite tricks to use. Any arguments against it?

  89. #89
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: What to Avoid in VBA

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

  90. #90
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: What to Avoid in VBA

    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

  91. #91
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: What to Avoid in VBA

    It avoids use of Clipboard.

  92. #92
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,647

    Re: What to Avoid in VBA

    Quote Originally Posted by StephenR View Post
    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.
    Jeff
    | | |?| |?| |?| |?| | |:| | |?| |?|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  93. #93
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: What to Avoid in VBA

    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

  94. #94
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: What to Avoid in VBA

    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?

  95. #95
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: What to Avoid in VBA

    Value2 just avoids the automatic conversion of dates and currency, and just passes Doubles.
    Last edited by shg; 02-22-2010 at 05:41 PM.

  96. #96
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,647

    Re: What to Avoid in VBA

    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.

  97. #97
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: What to Avoid in VBA

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

  98. #98
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What to Avoid in VBA

    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.

  99. #99
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: What to Avoid in VBA

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

  100. #100
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,647

    Re: What to Avoid in VBA

    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.

  101. #101
    Forum Contributor pierre08's Avatar
    Join Date
    02-16-2010
    Location
    Paris
    MS-Off Ver
    Excel 2003
    Posts
    166

    Re: What to Avoid in VBA

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

  102. #102
    Valued Forum Contributor Richard Schollar's Avatar
    Join Date
    05-23-2006
    Location
    Hampshire UK
    MS-Off Ver
    Excel 2002
    Posts
    1,264

    Re: What to Avoid in VBA

    Perhaps:

    Please Login or Register  to view this content.
    Richard

  103. #103
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What to Avoid in VBA

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

  104. #104
    Forum Contributor pierre08's Avatar
    Join Date
    02-16-2010
    Location
    Paris
    MS-Off Ver
    Excel 2003
    Posts
    166

    Re: What to Avoid in VBA

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

  105. #105
    Valued Forum Contributor Richard Schollar's Avatar
    Join Date
    05-23-2006
    Location
    Hampshire UK
    MS-Off Ver
    Excel 2002
    Posts
    1,264

    Re: What to Avoid in VBA

    Quote Originally Posted by pierre08 View Post
    Then why using Select Case in better Than If..End If???
    It's much more readable and thus easier to modify and maintain.

  106. #106
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: What to Avoid in VBA

    Quote Originally Posted by pierre08
    Then why using Select Case in better Than If..End If???
    This was covered vaguely from around post #72 ... ie efficiency versus practicality.

  107. #107
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,647

    Re: What to Avoid in VBA

    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.

  108. #108
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881

    Re: What to Avoid in VBA

    Quote Originally Posted by Paul View Post
    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?

  109. #109
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: What to Avoid in VBA

    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?

  110. #110
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,327

    Re: What to Avoid in VBA

    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 ..
    If the solution helped please donate to RSPCA

    Site worth visiting: Rabbitohs

  111. #111
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: What to Avoid in VBA

    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.
    Last edited by JBeaucaire; 02-26-2010 at 05:33 PM.

  112. #112
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: What to Avoid in VBA

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

  113. #113
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,647

    Re: What to Avoid in VBA

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

  114. #114
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,186

    Re: What to Avoid in VBA

    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.

  115. #115
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,186

    Re: What to Avoid in VBA

    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"

  116. #116
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What to Avoid in VBA

    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.
    Note: in languages like C++, I believe that switch statements are more efficient due to the way the compiler optimises them.
    Last edited by romperstomper; 02-26-2010 at 07:43 PM.

  117. #117
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: What to Avoid in VBA

    don't eat yellow snow !
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  118. #118
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: What to Avoid in VBA

    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.

  119. #119
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,186

    Re: What to Avoid in VBA

    Quote Originally Posted by shg View Post
    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

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

  120. #120
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What to Avoid in VBA

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

  121. #121
    Forum Contributor Rick_Stanich's Avatar
    Join Date
    11-21-2005
    Location
    Eastlake Ohio
    MS-Off Ver
    2018
    Posts
    1,106

    Re: What to Avoid in VBA

    Deleted.
    Already covered...
    Last edited by Rick_Stanich; 03-01-2010 at 11:25 AM.
    Regards

    Rick
    Win10, Office 365

  122. #122
    Forum Contributor Rick_Stanich's Avatar
    Join Date
    11-21-2005
    Location
    Eastlake Ohio
    MS-Off Ver
    2018
    Posts
    1,106

    Re: What to Avoid in VBA

    Quote Originally Posted by DonkeyOte View Post
    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)
    Last edited by Rick_Stanich; 03-01-2010 at 11:26 AM.

  123. #123
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What to Avoid in VBA

    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?

  124. #124
    Forum Contributor Rick_Stanich's Avatar
    Join Date
    11-21-2005
    Location
    Eastlake Ohio
    MS-Off Ver
    2018
    Posts
    1,106

    Re: What to Avoid in VBA

    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?

  125. #125
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What to Avoid in VBA

    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.

  126. #126
    Forum Contributor pierre08's Avatar
    Join Date
    02-16-2010
    Location
    Paris
    MS-Off Ver
    Excel 2003
    Posts
    166

    Re: What to Avoid in VBA

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

  127. #127
    Forum Contributor pierre08's Avatar
    Join Date
    02-16-2010
    Location
    Paris
    MS-Off Ver
    Excel 2003
    Posts
    166

    Re: What to Avoid in VBA

    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
    Last edited by pierre08; 04-02-2010 at 06:25 AM.

  128. #128
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: What to Avoid in VBA

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

    Dom

  129. #129
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,186

    Re: What to Avoid in VBA

    Quote Originally Posted by romperstomper View Post
    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.
    Last edited by tony h; 04-02-2010 at 08:19 AM.

  130. #130
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881

    Re: What to Avoid in VBA

    Quote Originally Posted by pierre08 View Post
    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
    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

  131. #131
    Forum Contributor pierre08's Avatar
    Join Date
    02-16-2010
    Location
    Paris
    MS-Off Ver
    Excel 2003
    Posts
    166

    Re: What to Avoid in VBA

    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.

  132. #132
    Registered User
    Join Date
    12-05-2006
    Location
    Philadelphia PA
    MS-Off Ver
    Office 365
    Posts
    78

    Re: What to Avoid in VBA

    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.

  133. #133
    Forum Contributor pierre08's Avatar
    Join Date
    02-16-2010
    Location
    Paris
    MS-Off Ver
    Excel 2003
    Posts
    166

    Re: What to Avoid in VBA

    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

  134. #134
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: What to Avoid in VBA

    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

  135. #135
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881

    Re: What to Avoid in VBA

    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.

  136. #136
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: What to Avoid in VBA

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

  137. #137
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: What to Avoid in VBA

    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.

  138. #138
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Question Re: What to Avoid in VBA

    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.


  139. #139
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: What to Avoid in VBA

    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.

  140. #140
    Forum Contributor pierre08's Avatar
    Join Date
    02-16-2010
    Location
    Paris
    MS-Off Ver
    Excel 2003
    Posts
    166

    Re: What to Avoid in VBA

    Don't use GoSub unless you have a clarity and maintainability in your code, performance gains come at the expense of legibility
    Last edited by pierre08; 06-30-2010 at 03:38 AM.

  141. #141
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: What to Avoid in VBA

    Quote Originally Posted by pierre08 View Post
    Don't use GoSub
    I wondered if you might add that

  142. #142
    Forum Contributor pierre08's Avatar
    Join Date
    02-16-2010
    Location
    Paris
    MS-Off Ver
    Excel 2003
    Posts
    166

    Re: What to Avoid in VBA

    Hey guys,

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

    And thanks for all who participate.
    Last edited by pierre08; 04-20-2011 at 11:42 AM.

  143. #143
    Forum Contributor pierre08's Avatar
    Join Date
    02-16-2010
    Location
    Paris
    MS-Off Ver
    Excel 2003
    Posts
    166

    Re: What to Avoid in VBA

    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.
    Last edited by pierre08; 04-29-2011 at 09:44 AM.

  144. #144
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: What to Avoid in VBA

    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

  145. #145
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: What to Avoid in VBA

    Quote Originally Posted by JBeaucaire View Post
    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....
    @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.



  146. #146
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: What to Avoid in VBA

    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.

  147. #147
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: What to Avoid in VBA

    Hi everyone,

    Quote Originally Posted by snb View Post
    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

  148. #148
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: What to Avoid in VBA

    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.

  149. #149
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: What to Avoid in VBA

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

  150. #150
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 2209 64-bit
    Posts
    21,647

    Re: What to Avoid in VBA

    Quote Originally Posted by broro183 View Post
    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.

  151. #151
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: What to Avoid in VBA

    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.

  152. #152
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,255

    Re: What to Avoid in VBA

    @ 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:
    1. Dynamic Dispatch
    2. Polymorphism
    3. Inheritance
    4. Open Recursion

    VBA supports all of these except Polymorphism (2). This classifies VBA as Object Based rather than Object Oriented.
    Last edited by Leith Ross; 05-14-2011 at 01:40 PM. Reason: Added post number for reference
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  153. #153
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: What to Avoid in VBA

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

    Dom

  154. #154
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: What to Avoid in VBA

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

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  155. #155
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: What to Avoid in VBA

    Polymorphism sounds painful whatever objects are involved

    Dom

  156. #156
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: What to Avoid in VBA

    Hmm.

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

  157. #157
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: What to Avoid in VBA

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

  158. #158
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: What to Avoid in VBA

    You quoted 'shortest'

  159. #159
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: What to Avoid in VBA

    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

  160. #160
    Registered User
    Join Date
    12-20-2008
    Location
    Arkansas
    MS-Off Ver
    2010
    Posts
    87

    Re: What to Avoid in VBA

    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

  161. #161
    Forum Contributor pierre08's Avatar
    Join Date
    02-16-2010
    Location
    Paris
    MS-Off Ver
    Excel 2003
    Posts
    166

    Re: What to Avoid in VBA

    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.

  162. #162
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: What to Avoid in VBA

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

  163. #163
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: What to Avoid in VBA

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

  164. #164
    Forum Contributor pierre08's Avatar
    Join Date
    02-16-2010
    Location
    Paris
    MS-Off Ver
    Excel 2003
    Posts
    166

    Re: What to Avoid in VBA

    Thanks guys, this might be very usefull.