none
Unnecessary variables? RRS feed

  • Question

  • Dim sourceRange As Range
    
            Set sourceRange = Range("HS")
            CODE 1
            .
            .
            .
            .
        
            Set sourceRange = Range("LS")
            CODE 2
            .
            .
            .
            .
    
        
            Set sourceRange = Range("T")
            CODE 3
            .
            .
            .

    Is it better to have more variables(sourceRangeOne, sourceRangeTwo, sourceRangeThree) or should I just clear the value of sourcerange each time?

    Thanks

    PS Sorry in advance if this sounds like a stupid question!

    Tuesday, October 25, 2016 9:58 AM

Answers


  • PS Sorry in advance if this sounds like a stupid question!

    Stupid questions doesn't exists, only stupid answers. ;-)

    If you declare more variables you need more memory... but only a few bytes for each (4 on 32-bit, resp. 8 on 64-bit office in your case). So in general it is better to use only one variable, but on today's PCs we have so much memory...

    If you need to access the different cells often in your code, you should use more variables, because your code becomes more readable for humans and it is much easier to change, resp. to adapt if your sheet layout changes.

    I recommend to use more (speaking) variables any time to make your code more readable for you.

    Andreas.


    • Edited by Andreas Killer Tuesday, October 25, 2016 10:23 AM
    • Marked as answer by VBNovice01 Tuesday, October 25, 2016 10:42 AM
    Tuesday, October 25, 2016 10:22 AM

All replies


  • PS Sorry in advance if this sounds like a stupid question!

    Stupid questions doesn't exists, only stupid answers. ;-)

    If you declare more variables you need more memory... but only a few bytes for each (4 on 32-bit, resp. 8 on 64-bit office in your case). So in general it is better to use only one variable, but on today's PCs we have so much memory...

    If you need to access the different cells often in your code, you should use more variables, because your code becomes more readable for humans and it is much easier to change, resp. to adapt if your sheet layout changes.

    I recommend to use more (speaking) variables any time to make your code more readable for you.

    Andreas.


    • Edited by Andreas Killer Tuesday, October 25, 2016 10:23 AM
    • Marked as answer by VBNovice01 Tuesday, October 25, 2016 10:42 AM
    Tuesday, October 25, 2016 10:22 AM
  • I use the rule if you can give the variable a meaningful name then make it unique if you can't reuse it.  For example I have a lot SQL strings I create.  It is impossible to give each a meaningful name so I create a global variable and reuse it.
    Tuesday, October 25, 2016 11:40 AM
  • just out of interest, from my excerpt of code above, what would be th effect of leaving it as it is? Should I not clear the value of sourceRange after each 'CODE' block? Or will overwrite the value  by itself automatically if that makes sense
    Wednesday, October 26, 2016 9:07 AM
  • Should I not clear the value of sourceRange after each 'CODE' block?

    "Clear" in this context means the code:

    Set SourceRange = Nothing

    And that makes (in 99.9% of all cases) no sense.

    SET means not that you "fill" something, you only refer to an existing object, means SET creates a pointer to that object, the address only is stored in your variable. (technical details, don't worry about).

    For the sake of completeness: In only a very few cases (if you create an object with SET WhatEver = NEW WhatEverClass) you have to set the WhatEver variable to nothing.

    The memory manager knows by itself if he can release an object from memory (this is the case if the code doesn't refer to an object anywhere).

    VBA is very comfortable on that point, not like other compilers (C, Delphi, etc.)

    Andreas.

    Wednesday, October 26, 2016 10:14 AM