Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Difference between Range and Range object

0

I'm trying to figure out when to use quotes when using a range variable and when not to.  It has something to do with vba range and vba range object.    

Below are two sets of code, one produces an error while the other does not.

In my examples RngInsertRow has been declared as a range.

The following produces an error on the second line.

CODE

Set RngInsertRow = headerRow.Offset(1, 0)

Range("RngInsertRow").EntireRow.Insert Shift:=xlShiftDown, CopyOrigin:=xlFormatFromRightOrBelow

END CODE

On the next two lines, the alternate does not produce an error.

CODE

Set RngInsertRow = headerRow.Offset(1, 0)

RngInsertRow.EntireRow.Insert Shift:=xlShiftDown, CopyOrigin:=xlFormatFromRightOrBelow

END CODE

Please explain in detail and if anyone can supply further examples that might be helpful.  TIA

excel
MS Office Version: Home and Business 2021
posted by: SusanUser (rep: 22) on Tue Jun 11, 2024 at 8:23 pm
Answer
Discuss

Answers

0
Selected Answer

Hi again Susan.

In VBA (and some other computer languages), we have Objects which have Properties and Methods we can use/ change. 

There are many different Objects, in a heirachy. The Excel application is an Object itself and has sub-objects like Workbooks and Worksheets. These can have further Objects, e.g. a Worksheet can contain several Range objects.

You can tell VBA directly to do something to or get something from a range by using a string to say where it is. E.g. you might say:

MsgBox ActiveSheet.Range("A1:D20").Count

(which will return 20) since the text within the quotation marks tells VBA where the range is (and it knows how cells are labelled A1 etc.) 

Elsewhere in your code you've created (Set) a range object called headerRow (so you no longer need the quotation marks) and your line:

Set RngInsertRow = headerRow.Offset(1, 0)

creates a new (Range) object called RngInsertRow. Now VBA knows what that is, you can apply any Methods and Properties associated with Ranges. In your second code, you use the EntireRow property to indicate the whole same row as your object then the Insert method to insert a new row (specifying the parameters for that operation). 

Hope this helps. 

posted by: John_Ru (rep: 6417) on Wed Jun 12, 2024 at 11:23 am
Discuss

Discussion

I think I understand.

In my own words:  although RngInsertRow has been declared as a Range (not shown in my original question), VBA does not know where the range is.  Once the line

  Set RngInsertRow = headerRow.Offset(1, 0)   


is encountered, VBA knows the rows and columns associated with RngInsertRow so I can apply methods and properties to it and no need to use the quotation marks.

Is this a correct understanding?
SusanUser (rep: 22) Jun 12, '24 at 2:09 pm
Yes, when you declare a variable, you tell VBA it's going to be a Range say. An appropriate amount of memory is allocated in anticipation but VBA does not know "where" (or in the case of a Variant, what) it is. Once you define it (your Set line), VBA has access the Properties and can use the Methods.

If you look in the VBA Editor, you should find the Object Browser (if not, press F2) which lists all the various Objects and shows Methods and Properties for whichever you select.
John_Ru (rep: 6417) Jun 12, '24 at 5:14 pm
BTW, try adding this line after your set line:
MsgBox RngInsertRow.Cells(7).Address

which should tell you the address of the seventh cell in that object (going left to right). Here Cells is a Property of your Range object but returns a Range which itself has an Address Property.
John_Ru (rep: 6417) Jun 12, '24 at 6:18 pm
John_Ru   Thank you!  My understanding is improved now.
SusanUser (rep: 22) Jun 13, '24 at 3:19 pm
Good. Thanks for selecting my Answer Susan. 
John_Ru (rep: 6417) Jun 13, '24 at 3:32 pm
Add to Discussion
0

Hello again Susan,

First of all, you have posted your question twice so you should remove the other one.

There is a bit of information missing to fully diagnose this. You haven't included what you have declared/defined the variables "RngInsertRow" and "headerRow" as (ie: Variant, Range, Boolean, Double, etc).

But in general, in your first example, the way you have " Range("RngInsertRow") " - VBA is expecting to find a specific range (such as "A1:D1", or "A1:A4" or "A1:D4", or the name of a named range "SalesData") inside the brackets. You have "Set" RngInsertRow to something other than a range which is causing the error.

In your second example you are telling VBA to insert a new row below the headerRow – clear instructions which VBA understands and therefore no error.

 Hope this helps.

 Cheers   :-)

posted by: WillieD24 (rep: 587) on Tue Jun 11, 2024 at 9:55 pm
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login