• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Difference between Named range and range which defined as " Set" keyword.

C

Chirag R Raval

Member
Dear All Experts,

If ( in VBA) we can easily access range , defined base on Set keyword,
Then what is the importance Or benifit Or difference to use named range?

Suppose
Code: 
Dim myrng as range

Set myrng=activesheet.range("A1:"D5")

Myrng.font.bold=true

We can easily access this range in VBA to do
Whatever possible with it.

If same range access through named range
Then what it major benifit or difference between
This two approach?

Hope some precise concept found behind this to
As guideline as which approach should use in which condition.

Regards,

Chirag Raval
 
Hi !

First, if within my code I need to use a range only one time,
I ever do not need to set any range object variable,
it's just a waste of time and memory !

For example your code revisited : [A1:D5].Font.Bold = True …

A must see in VBA inner help : With (avoiding too object variable).

As a range access is the same whatever the way used, so no matter.

The benefit of a named range is not for VBA but for Excel
as the range grows up automatically with new data in the range table …

The benefit of a named cell is when this cell is moved when inserting
a new row or deleting an old one : its address changes but not its name …

And obviously a well named range has more meaning than just an address !
For example : B8 vs VAT or B9 vs TOTAL …

Named ranges exist first for Excel (more sense in a formula) : =TOTAL-VAT …
​
 
Dear Sirs & @MarcL,

Thanks for reply,

"Set" used due to often need to reference, or access
It more then 1 time for various purpose & also more important ,if there are
More then 1 ranges exist on sheet , so precisely pointing
Or reference perticular range , 1 time set each range with
Some related word (this word construct / type as a range variable)
& Reference it when & where require , if it construct as dynamic ,
Through .through strat to ".End,, as it's autoadjust.

Please spread some focus on " Avoid too object variables".
Then which approach should use to access Ranges, if require ,more then 1 time?

Regards,

Chirag Raval
 
Last edited:

It depends on the code algorithm so via With statement
or via a range variable …
If you use a variable, do not forget to free it (Set var = Nothing)
before the procedure ends.
Benefit of With statement : nothing to free, automatic releasing !
​
 
Dear Sir @MarcL,

Thanks for short answer but perfactly cover this thread"s requirement.

If not use "Set" (may be it Heavy - Object) but if use "With ,....End With " approach
(Simply store range in in range type variable without set)

Then how to refer or pointing or access this range variable without fail ?

Theoretical if written ..
"Activesheet.Range("Range Variable").it's property or method "

How to access?

Regards,

Chirag Raval
 
Last edited:
Dear Sir,

Thank you for reply,

It means if we want to Access/ Pointing/ Access some range through use of range variable , then , we should use "Set" keyword. that main point is it construct only 1 time & then we can reference/ pointing/ access that range multiple time , as & when require, with "With...End With" statement OR without "With ...End With".. (Direct reference) like below

Code: 
Sub testMyrng()

Dim myrng As Range

Set myrng = ActiveSheet.Range("A1:D5")

With myrng
      .Font.Bold = True
      .Font.Italic = True
End With

'OR AS PER BELOW without "With..End With"

myrng.Font.Size = 14

End Sub

Otherwise if you want just "With...End with" without "Set" any range...
then you must cover that range in "With ...End with " statement..
when & where you want to use that range, must follow
to cover that range every & each time.."With ...End with " statement..

am I right ?

Regards,

Chirag Raval
 
First, you forgot to free the range object variable …

In fact there is no obligation, the main goal is reaching the expected result.
With just avoids to repeat an object variable …

But you can write :​
Code: 
Sub testMyrng()
    Dim myrng As Range
    Set myrng = ActiveSheet.Range("A1:D5")
        myrng.Font.Bold = True
        myrng.Font.Italic = True
        myrng.Font.Size = 14
    Set myrng = Nothing
End Sub
Or better :​
Code: 
Sub testMyrng()
    Dim oRF As Font
    Set oRF = Range("A1:D5").Font
        oRF.Bold = True
        oRF.Italic = True
        oRF.Size = 14
    Set oRF = Nothing
End Sub
On my side my favorite :​
Code: 
Sub testMyrng()
    With [A1:D5].Font
        .Bold = True
        .Italic = True
        .Size = 14
    End With
End Sub

See also my demonstration in the thread
Vba Code To Automatically Print Data In Sheet For Each Date In Table …

Third way using a string variable for the range address :​
Code: 
Sub testMyrng()
    Const AD = "A1:D5"
    Range(AD).Font.Bold = True
    Range(AD).Font.Italic = True
    Range(AD).Font.Size = 14
End Sub
 
Dear Sir @Marc L ,

Amazing, thank you very much for guide to many way to work on range..

Again thanks for provide more deep understanding on things
I will study that link.

Regards,
Chirag Raval
 
See new Demo2 in the link …

Is it my code the best one ?
Not always because the better is the one you understand
and above all the one you are the more able to maintain in case of any mod …
​
 
My 2 cents - I generally use Set within For Loops for Variable ranges. I noticed that Do Until/ Do While run a lot slower than For Loops, so if I'm not deleting any rows then I use Set with For Loop.
 
Marc L said:
It depends on the code algorithm so via With statement
or via a range variable …
If you use a variable, do not forget to free it (Set var = Nothing)
before the procedure ends.
Benefit of With statement : nothing to free, automatic releasing !
​
Click to expand...

Dear Sir @Marc L ,

If unused or After used, Memory Variables remains in memory, that Hanged Excel so on Module To Module, How to check without run macro , that there are something remains in memory?

Any tools available to check it except Local Window?

So that we can firmly remove objects from memory produced from every module & be sure there are nothing remains in memory before close excel.

Regards,

Chirag Raval
 
In about 99% of case objects are destroyed and memory freed up when it's out of context (i.e. when sub terminates).

It's just good practice to set object to nothing, for avoiding any issues, even if it is rare. Though I've never had issue myself without setting objects to nothing (except for external objects).
 
Dear Sir @Chihiro ,

Thanks you very much for you kind effort to focus on subject..
now I firm to always release objects at end of sub.

Another reason to know that, in 64 bit , can not run Code Cleaner Add-in for VBA. (till now, I actually/really don't know what is Code Cleaner actually do in VBA? just can see ,when run , running over & flickering module to module...)

hope you can also guide for Code Cleaner Add-in.

Thank you very much sir

Regards,
Chirag Raval
 
Add-in only for 32 bit Excel version maybe …
As Microsoft itself advises to install 32 bit Excel version !
64 bit version is Ok for Excel stuff but on VBA side,
so many things can't work anymore (some ActiveX only in 32 bit).

If you can mod the code, a must read & apply :

Declaring API functions in 64 bit Office …
​
 
It's explicitly stated in their site, that it does not support 64bit.
So answer is no. You can't run it on 64 bit Excel. And since it's COM add-in. Not easy to modify it.

64 bit is not default install as Marc pointed out for many reasons including above.

I use 64 bit, but then I don't use many add-in (xlwings & DAX studio are the only non-standard add-in), and have enough RAM (and complex models) to take advantage of increased memory usage limit.

There's only few limited case where 64 bit should be installed.
https://technet.microsoft.com/en-us/library/ee681792.aspx
 
You must log in or register to reply here.
Back
Top