+ Reply to Thread
Results 1 to 4 of 4

blog on using variables

  1. #1
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    blog on using variables

    I could probably write a book on variables, but this short note might help some of the novice coders.

    Not every object needs to be put into an object variable, and neither do all values need to be in variables, even if used in a function.

    Use object variables when lengthy references will be needed multiple times, otherwise just use the actual reference. For example:
    Your code deals with two workbooks and multiple sheets which will need to be accessed or refenced three or more times, Instead of writing Workbooks(1).Sheets(1).Range...etc., you can:
    Please Login or Register  to view this content.
    the sh1 and sh2 variables can be used multiple times to transact between the two workbooks without having to spell out the whole parent child chain each time. Additional sheet varibles can be created and used the same way. But don't over use variables and cloud up the code. Another misuse of variables is creating a new variable that equals an existing variable. That really confuses the issue, not only for a reviewer, but for the user as well. Once you have created the variable, use it consistently without bouncing back and forth between the variable and the full reference. That is also confusing to anyone attemting to analyze code, because it interrupts the thought process by having to confirm the object is logically correct at that point in the code. Variables are tools, not absolute requirements in coding. By being consistent, yet frugal, in their use, code can be more efficiently created and operated.
    Last edited by JLGWhiz; 01-24-2020 at 09:43 AM.
    Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
    Just when I think I am smart, I learn something new!

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

    Re: blog on using variables

    You forgot to mention the use of With .... End With
    It improves the readability of your code, since the Object it is referring to is alwasy in the With line

    Most of the time the use of redundant Object variables seems to be a remnant of VB-coding practices.

    A second recommendation:

    If you structure your code in 3 phases/stages:

    - reading data into variables (no Object variables)

    - adjust / edit/ calculate/ filter/ adapt /work with the data

    - writing the results into a workbook/document/table/presentation/item

    you only have to refer to Objects in the reading and writing part of your code.



  3. #3
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: blog on using variables

    @snb - Like I said, there could be a whole book written on the subject. I just thought that I would mention one of the areas that I see most often that can be improved by new code writers. And you are correct about those styles being residual to the old Basic coding and from learning by using the recorder. But I haven't seen any really good books on how to use variables in code. The authors who write programming texts do show coding styles, but they don't give a lot of detail about the advantages of using those styles as it applies to variables. I have seen some variables that are longer than the object they refer to. Personally, I try to make the variables as short as possible, while still pointing to the type of object it is. I suppose each code writer has their own preferences and as long as the code works, it is good code, even if it could be improved on visually and effectively.

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

    Re: blog on using variables

    The most important criterion is consistency in the use of variables and the application of the principle of parsimony.
    Maybe we should fill the gap by writing an 'introduction to the use of variables'.
    For your information: http://www.snb-vba.eu

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Extract all URLs from a blog
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-29-2016, 08:39 PM
  2. New for this blog...
    By mikki_hiiiri in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 01-22-2015, 11:04 AM
  3. Wanna say Hi to all the friends in this blog
    By Kushi.Krishnappa in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 05-16-2014, 08:16 AM
  4. we can now blog
    By martindwilson in forum The Water Cooler
    Replies: 11
    Last Post: 12-12-2011, 05:40 AM
  5. One useful blog to learn excel
    By Gwenith in forum Excel General
    Replies: 0
    Last Post: 04-23-2009, 09:24 AM
  6. important Excel blog
    By chillpill in forum Excel General
    Replies: 1
    Last Post: 01-05-2009, 03:40 PM
  7. Replies: 0
    Last Post: 07-28-2006, 11:35 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1