What is the difference between variables and object variables?

[ShieBoon]

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!

 

[xenou]

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.

Dim TotalRow As Long
TotalRow = ActiveSheet.Rows.Count

Mostly - you'll get used to it. Don't worry and keep coding.

 

[ShieBoon]

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

 

[ShieBoon]

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

thanks

 

[xenou]

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

 

[ShieBoon]

Nice. Thanks man!