What is the difference between variables and object variables?

ShieBoon

Board Regular
Joined
May 3, 2011
Messages
111
Hi all, i have some difficulty in understanding the purpose of both types of variables in excel macro. Furthermore, i have a problem in my codes right now,

Dim TotalRow As Integer
Set TotalRow = ActiveSheet.Rows.Count
ActiveCell.Copy
For i = 1 To TotalRow
ActiveCell.Resize(TotalRow).Select
ActiveSheet.Paste
Next i
End Sub

When i compile it i get a "Compile Error: Object Required" msgbox. What does this msgbox means exactly? The words in red font are the ones highlighted by excel.

Thanks!
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Variables are either primitives or objects.

Primitives (discrete bits of data) are:
Integer data types (such as Long)
Floating point data types (such as Double)
Strings
Dates
Boolean

Object references are everything else (Workbooks, worksheets, etc. etc.)

VBA requires the Set Statement for assigning objects.

Rows.Count returns a number and should be assigned to a primitive data type (Long) or to a variant. In this particular case, I suspect that the return value of Rows.Count has been determined by the Excel Programming model to be a Long data type (or something like it, in more basic terms appropriate to Windows programming). Therefore the compiler "knows" you have made a mistake - to wit, Rows.Count cannot be an object, and cannot be used with Set.

Code:
Dim TotalRow As Long
TotalRow = ActiveSheet.Rows.Count

Mostly - you'll get used to it. Don't worry and keep coding.
 
Last edited:
Upvote 0
Hi xenou, thanks for the reply. I understand better now too, thanks! :) 1 more question, i seem to get an error with this code:

ActiveCell.Offset(1).Resize(TotalRow).Select

Error '1004'
Application-defined or object-defined error

Because i'm trying to resize the activecell to the entire 65536 rows of the whole worksheet to copy and paste a formula. But excel doesn't recognize the TotalRow variable in the property of Resize. Anything i can do about it?

Thanks
 
Upvote 0
And xenou, do you know how i can get the row number and column number of the activecell?

thanks
 
Upvote 0
If you resize a cell to the TotalRow (in absolute terms) be sure you have room for all the rows!

Simpler:
ActiveCell.Offset(1).EntireColumn.Select

But this is the same as:
ActiveCell.EntireColumn.Select
If you select the Entire column it doesn't matter what row you start in.

Did you want all the rows below the active cell? Then:
Range(ActiveCell.Offset(1),Cells(Rows.Count,ActiveCell.Column).Select
Which answers your last question too ... ;)

ActiveCell.Row --> Row Number of ActiveCell
ActiveCell.Column --> Column number of Active Cell
 
Upvote 0

Forum statistics

Threads
1,226,729
Messages
6,192,696
Members
453,747
Latest member
tylerhyatt04

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top