Variable Assignment

[YellowLabPro]

What is an appropriate, or my choices for the correct variable assignment type for the following

iii = ActiveCell.Row


YLP

[mdmackillop]

As this will return a whole number, use Long. Integer will not handle 65536, and I believe gets converted to Long in any case.

[YellowLabPro]

Malcolm,
Ok. But I don't understand something-- as I am still very new, and in my understanding is probably way off.
In your explanation you use it as number-- Long. But it seems to me to be more of Range or an Object... I know this is wrong based on your answer, but I dont understand it....

[mdmackillop]

Row or Column returns the row number or column number. Try stepping through the following


Sub Tests()
Dim rg As Range
Dim Lg As Long

'This line errors
Set rg = ActiveCell.Row

Set rg = ActiveCell.EntireRow
MsgBox rg.Address
Lg = ActiveCell.Row
MsgBox Lg
End Sub

[YellowLabPro]

Type mismatch error

[mdmackillop]

Correct. It errors because ActiveCell.Row is not a range, whereas Activecell.EntireRow is a range, and is the code you would use to manipulate the row.
You could use code like Rows(ActiveCell.Row) if you wanted to deal with related rows, as in

Rows(ActiveCell.Row+1).Interior.ColorIndex = 6
instead using Offset. There are often a few ways to reference a range. Generally choose one which is comprehensible.
This bit of code
ws3.Range(Cells(Rows.Count, "A").End(xlUp).Offset(1), _
Cells(Cells(Rows.Count, 2).End(xlUp).Row, 1)) = "Existing"

while fulfilling the task makes understanding (at least for me) very difficult, and should be commented to explain what's going on.

[YellowLabPro]

Well.... it is going to take a while.... chuckle...

thank you

[malik641]

Another way to put it in addition to what Malcolm said.


In your explanation you use it as number-- Long. But it seems to me to be more of Range or an Object... I know this is wrong based on your answer, but I dont understand it....

Think of it like this, Range is an object. And within this object "range" there are properties, methods, and maybe collections (not sure)... and when you access the property Row of the Range object, it returns an integer as its property. It returns a number cause you asked for it, kinda like if you asked if it is bold it will return a boolean value to you.

And from a coding standpoint it is better to return an integer, as a Range object carries more memory, which results in some slower procedures (not that it's much most of the time, but it could be).

I hope this helps clear things up! :)

[Norie]

malik

It doesn't return a number because that's what you asked for.:)

It returns a number because that's what it is.

[Cyberdude]

Hey, Yelp, at times like this the VBA Help can be an assist to you. If you look up the Row Property, Help says:
"Returns the number of the first row of the first area in the range. Read-only Long."
That will help you choose the type when you aren't sure.

[malik641]

malik

It doesn't return a number because that's what you asked for.:)

It returns a number because that's what it is.
:) Yes, technically speaking. By ask I guess the thought behind that was that you obtain the row property of the range object.....or something.

And I didn't mean to say integer.....I meant long, my bad :whistle:

[YellowLabPro]

Thanks Malik, and Cyberdude.
Both ideas assist in understanding better what is going on, or the underpinnings of VBA.
Cyberdude- that is a very clever and helpful suggestion. One thing, if you keep using Yelp-- it might stick--:motz2: :rotlaugh:


Thanks guys...


YLP

[Zack Barresse]

:) Yes, technically speaking. By ask I guess the thought behind that was that you obtain the row property of the range object.....or something.

And I didn't mean to say integer.....I meant long, my bad :whistle:


I find there is a difference between integer and Integer. I've always been mathematics-heavy in most of my professional life, so integer really means something to me, in VBA, so does Integer. The Integer is a variable type while the integer is expressing a whole number. But the only time I use Integer is in Access. ;)

And Norie, you're splitting hairs ... ;)

[malik641]

I find there is a difference between integer and Integer. I've always been mathematics-heavy in most of my professional life, so integer really means something to me, in VBA, so does Integer. The Integer is a variable type while the integer is expressing a whole number. But the only time I use Integer is in Access. ;)

And Norie, you're splitting hairs ... ;)
I'm with you, Zack. As an mechanical engineering student, I'm always involved with serious math...and to be honest, I think it should be called Short rather than integer.... just my :2p: