Notation of Excel Range

[Mark Heuckeroth]

Hello Everyone,

I got a question related to code structure.
(I got a fairly thorough understanding of Excel VBA and the Object Model)

to refer to range, in a subroutine, one could either Type

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Things can get more detailed, for example:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

While method 2 is shorter, i would prefer method one, because it is easier to understand, and more clear as to which objects i refer to, code wise.

My question is:
if there is not any difference in speed, why use method 2?

[snb]

or

Please Login or Register  to view this content.

method 2 should be 'slower' than method 1.
If speed differences are indiscernable you could use the criterion 'readability' to choose between the methods.

[DonkeyOte]

Use of [ ] is as you imply utilising Evaluate

Worth mentioning on that basis that if you use shorthand (without the Evaluate statement) you can affect the output, eg:

Please Login or Register  to view this content.

As for range referencing ... I think most would argue [ ] to be "bad-practice" and some say slower than using Range / Cells etc but I confess I don't have the knowledge to back either up I'm afraid so will let others argue that one.

[snb]

v2 is equal to v3 if...

Please Login or Register  to view this content.

[DonkeyOte]

I thought it was quite apparent that I was highlighting the differences that occur when passing Strings...

[Mark Heuckeroth]

Thanks for the reply's so far.

As to learn VBA the web has been of great help to me, as for notation it sometimes raises questions for what to use

for example the use of With...End With

sometimes i read:
- try to avoid double with statements
and i do agree with that.. but on the other hand you read
-- use with...end with instead of object variables.
And i also agree with that, but sometimes you get into that situation.. and then which of the following would be preferred?

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

and why.

EDIT: i always prefer to use single With statements

[romperstomper]

I would use variables for both objects and I would have no problem with nested With statements.

[Mark Heuckeroth]

I would use variables for both objects.

Because of late binding, or in general?

[snb]

@DonkeyOte

I read your post as if you wanted to illustrate that using [] sometimes gives a different result from using Evaluate. We are on the same track again

[romperstomper]

Because of late binding, or in general?

In general. Makes debugging easier and the code more readable IMO.

[Mark Heuckeroth]

Thanks for the feedback everyone.
I'ts good to know of a place where you can get feedback to more advanced questions.

Mark.