Newbie questions about range objects in VBA

Posted on 2009-12-31
Last Modified: 2012-05-08
I have some newb questions about comparing ranges.

Say I have a pair of ranges: rng1 = wks1.Range("A1") and rng2 = wks2.Range("A2"). When I write "If rng1 = rng2 Then", does the computer check the values in those cells or that the references point to the same cell?

Is there a way to see which worksheet and cells a range object is referring to? When I add a range object to the watch window and dig around in the properties, I can't find the properties that refer to the worksheet and the cells.
Question by:ltdanp22
    LVL 59

    Accepted Solution

    Hello ltdanp22,

    To check the address ranges use for example

        Set rng1 = ActiveSheet.Range("a1:a2")
        Set rng2 = ActiveSheet.Range("a3:a4")
        Set rng3 = ActiveSheet.Range("a1:a2")
        Debug.Print rng1.Address = rng2.Address ' will return false
        Debug.Print rng3.Address = rng1.Address ' will return true


    LVL 21

    Expert Comment

    does the computer check the values in those cells or that the references point to the same cell<---it references the cell, not the the value can change...

    2) don't really understand...if you creating an instance of the worksheet, don't you already know...there's no x and y property?
    LVL 5

    Expert Comment

    It does actually compare the values of the ranges because the "Range" object has a hidden default property "_Default" and is returns the values. Depending on your ranges this can be different types or arrays, so it might fail to compare.

    You can see what cells are in the range by using the "Row", "Col", "Rows.Count" and "Columns.Count" properties. Row and Col are the upper left cell and the count give the width and height of the range.
    LVL 5

    Assisted Solution

    The "Col" property is named "Column", sorry.
    Ah and there is a Worksheet property in the Range object.

    If your declare your variables as type Range they might be more easy to use and VBA will autocomplete if you access their properties.

    Dim myRange As Range
    Set myRange = wks1.Range("A1")
    myRange.               <autocomplete working now and you see the properties

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Suggested Solutions

    Title # Comments Views Activity
    DO Loop not working 4 52
    Help in WHSCRIPT 9 31
    Saving history changes to sub form 4 16
    Microsoft Access combo box help 2 11
    Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
    Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now