Solved: How does this line Select D5

[YellowLabPro]

I ran across this example, it was explaining it will select "D5", would someone mind explaining how this is working?

Range("B3").Range("C3").Select

[Bob Phillips]

It is the same as



Range("B3").Offset(2,2).Select



or



Range("B3").Cells(3,3).Select



Range is effectively always indexing off a a point, and if that point is not given, as in Range("B3"), then it uses the spreadsheet start, and it is 1 based whereas as Offset is 0 based.

[YellowLabPro]

Thanks Bob, that helps quite a bit.



xld: whereas Offset is 0

One more please-
If the first line is valid, why are neither of the two following valid?


[b3].select is valid

[b3].[c3].select
OR
[b3][c3].select not?

[Bob Phillips]

Don't ask me, I never use that shortcut notation, I find it lazy and irrelevant. Probably because it evaluates each part separately as a cell whereas the Range offsets, but I am guessing and don't care as it will never be an issue for me.

[YellowLabPro]

Fair enough-- the followup question is based on building my foundation knowledge. You brought a good point up not too long ago about me missing a simple Range mismatch, so I have been doing my research and this just happen to be something I stumbled on.

Cheers,

[johnske]

...why are neither of the two following valid?
...
[b3].[c3].select
OR
[b3][c3].select not?Because there's an incorrect property call in the first statement...

Shortcut notation can be used to refer to one of several things, two of these being

1) the Evaluate method and
2) a Range object

Visual Basic needs to determine what is meant by [ ] and the context in which the shortcut notation's being used determines this, with the meaning being resolved by Visual Basic during compilation. The underlying compiled code is then re-written more explicitly and is used to actually run the procedure.

On compilation, [B3] written on its own is resolved as being shortcut notation for "Evaluate("=B3")", and processing of this is then passed over to Excel, with the value contained in B3 being returned.

On the other hand, a subsequent dot (.) signifies that there is a property call, and this is interpreted by the compiler as "[B3] is a range object" of some kind, and any further processing is left for Visual Basic.

Now, for the example you gave, if you look at the intellisense list of properties you'll see that [C3] (or even [ ] generally) is not listed there as a valid subsequent range property, so [B3].[C3].Select is quite simply an incorrect property call that will fail during run-time.

On the other hand, while Range("B3").Range("C3").Select doesn't fail, IMO it's a nonsensical way of doing things when you have other options such as 'Offset' or even 'Cells', and these are nowhere near as obtuse. In other words - Why would you even want to?


**{A simple example that illustrates the differences mentioned above is: During run-time MsgBox [SIN(45)] evaluates the Sine of 45 and returns "0.050903524534118". yet while MsgBox [SIN(45)].Value compiles ok, it gives the run-time error message "Object Required" (note that the validity of a given range or property is only determined during run-time).}

([b3][c3].select is totally meaningless and won't even get thru to the compile phase)

[Bob Phillips]

On the other hand, while Range("B3").Range("C3").Select doesn't fail, IMO it's a nonsensical way of doing things when you have other options such as 'Offset' or even 'Cells', and these are nowhere near as obtuse. In other words - Why would you even want to?

Blimey, we agree on something :devil2:

[Aussiebear]

Aussie Aussie Aussie Oi Oi Oi!

[mdmackillop]

If I didn't know you Doug, I would have suspected a little :stir: going on here. Safer to sit on the sidelines!

[YellowLabPro]

Hello John,
Thanks for the thorough explanation. I have been backtracking to the beginning to build a solid foundation of the essential elements in understanding and writing VBA code. I am starting over from the beginning, now that I have had some exposure to the program, and have become better familiar w/ it's terms and the nomenclature. It was not up just until a couple of days ago that things really started to become clear for me.

A book I purchased quite a while back, "VBA and Macros for Microsoft Excel", by Jelen, used the example, I posted, in an early chapter discussing ranges, objects,.... to show one of the many ways to refer to a range. I agree w/ Bob and you that why would someone write something like this, it is obtuse and difficult to interpret. It most likely would take someone else or even the author revisiting the code at a later date longer to figure it out in the shorthand notation than had it been written longhand n the first place.

I should mention that another example in the book in the same section uses brackets [ ], to refer to a range, [B5]. (I failed to write this in any earlier posts and why my question might have appeared to come out of left field. But there was some linear thought to the question and the thougth process.) This method of using brackets does not require the use of the keyword Range. Bob's answer and the book's example of the use of brackets led me to the ask the next question; if brackets can be used w/out need of a keyword surely [B3][C3].Select would be valid.....
When it failed, it prompted me to seek a better understanding....

John, In one of your points, you write about looking at intellisense's list of properties. I have not used this before and is completely new to me. I just did a quick search on it-- to see intellisense's properties accurately, I need to load it after a (.) to see the properties, correct?

One thing else that has just begun to unfold for me is the term property. I see it used quite a lot. Will you confirm my whether I do in fact posess an accurate understanding of this term/concept? In the book, "property", is defined as an adjective, used in an example to set a cell height or get the height of a cell. But what I think I am starting to see is the term property also means the relationship of an item to its reference, in other words-
Item is a property of Cell, Cell is a property of Range, Worksheets are property of Workbooks, etc.... in your explanation you write about a Property call. I interpret this to mean the property of the object, would this be a correct interpretation?

Thanks for all the help,

Doug

[YellowLabPro]

Malcolm, Me? Not a chance-- I am out of my league.

[Bob Phillips]

One thing else that has just begun to unfold for me is the term property. I see it used quite a lot. Will you confirm my whether I do in fact posess an accurate understanding of this term/concept? In the book, "property", is defined as an adjective, used in an example to set a cell height or get the height of a cell. But what I think I am starting to see is the term property also means the relationship of an item to its reference, in other words-
Item is a property of Cell, Cell is a property of Range, Worksheets are property of Workbooks, etc.... in your explanation you write about a Property call. I interpret this to mean the property of the object, would this be a correct interpretation?

Property is just an attribute of an object, an object HAS properties. Such as a car has a property of colour, number of wheels, maybe type of wheels. So a range has a property of a value and another of font. Unfortunately, it can get confusing because Range is an object with its own properties, but it is also a property itself of Worksheet (bit like my car analogy, a car has a seat property, and a seat has a type property (bucket, flat), and so on).

ANd then you get methods, a method can be thought of as an action committed on the object, or by the object.

[johnske]

In the VBE window, Tools > Options... > Editor, make sure you have 'Auto List Members' checked - in fact all those items can be checked. (also, While you're in that menu - in 'General', make sure Compile On Demand is checked and Background Compile is NOT checked).

Copy and paste this Sheet1.Range("A1") into a procedure then add a period (.) after it and you'll see a list appear. This is generally referred to as 'intellisense' and this list contains all the valid properties that can be associated with the range property. However, note that while these are all generally loosely referred to as "properties" - some of them are not properties at all, there's also (inbuilt) subs and functions there that are part of the underlying Visual Basic code that's hidden from us - and these are the 'methods'.

To see this - go to the object browser, select Excel, type in Address > search then select Range on the left, and on the right you'll see Members of 'Range'. This is the same list that's shown by intellisense, but in the object browser you can obtain more info... For example, click on 'Activate' and down the bottom you'll see this is actually a Function. Now click Address and you'll see it's a property. Now select ClearComments and you'll see it's a Sub (a procedure).

That's all I've time for now - I'm a OFT and need to get up early in the morning :)

[YellowLabPro]

Thanks John,
That is fantastic. I see now what all the different icons on the left mean. Always wondering how to interpret those guys.

Thanks Bob,
Starting to become ever-clearer.
Copy or Paste would be the idea of Method-- Copy would be the action by the object and Paste would be the action on the object.

Thanks all....

Malcolm, I think it might be Ted doing the drum pounding here....:devil2:

[Bob Phillips]

See the thread by mikerickson on Class modules to add a further layer of consfusion (a class is a custom object)

[Aussiebear]

"Guilty" your honour!

[Bob Phillips]

Starting to become ever-clearer.
Copy or Paste would be the idea of Method-- Copy would be the action by the object and Paste would be the action on the object.

Yes, but
... Copy is AN action by
... Paste is AN action on
there can many or none of either type.

[johnske]

Doug,

Uninstall that other thing I gave you and download this version instead from here (http://xlvba.3.forumer.com/index.php?act=ST&f=15&t=97&st=0#entry113). It's the same thing but I've embedded all those Word docs in a workbook and there are hyperlinks to the contents on the worksheet.

Once you've done that, click on the link that says Chapter 2: Understanding Object Models - that should answer all your questions...

[mdmackillop]

Vey neat John,
I've never used that technique.

[Bob Phillips]

Good idea John, but why use Excel to link to Word docs, why not use Word?

[johnske]

Good idea John, but why use Excel to link to Word docs, why not use Word?Well, first I was going to put it all in one word doc, but the formatting needed to get everything to display properly (as per online) differs in one or two of the chapters and it was just too much trouble to change everything, and then I'm not that familiar with Word hyperlinks and the need for 'bookmarks' either, so I just took the quickest and easiest (for me) way out - a "no-brainer" - embed the docs, open docs, copy headers and paste them onto the sheet as hyperlinks.

It's only meant to be a quick reference to some very handy info, not a 'work of art'. :)

[Bob Phillips]

Agreed it isn't a work of art, but it is useful.

Some years ago I downloaded all of those chapters and created a master workbook sourcing them all, but the hyperlinks is a better way (as long as MS don't change/remove the links).

[mdmackillop]

I can certainly see a few applications for this in storing Contract Documents etc. As I see it they can be stored within the Excel file, opened and updated, and with a bit more code, exported to replace Standard documents, templates etc.

[johnske]

... (as long as MS don't change/remove the lonks).That's what I'm afraid will happen, they've discontinued support for '97 so how long before the links go? So I decided to copy it for when they did... They also had an excellent and very comprehensive article on "classes" at one time, I bookmarked the link meaning to copy the article at some time and the next time I looked they'd completely removed the article - bugger...

[YellowLabPro]

Thanks again John,
Thanks for taking the time to help the newbies gain access to the grail....
my search continues...

[johnske]

I can certainly see a few applications for this in storing Contract Documents etc. As I see it they can be stored within the Excel file, opened and updated, and with a bit more code, exported to replace Standard documents, templates etc.Yep, keeps it all handy in the one location (a single file) :thumb

[debauch]

I ran across this example, it was explaining it will select "D5", would someone mind explaining how this is working?

Range("B3").Range("C3").Select

This would work :


Range("B3:C3").Select

[YellowLabPro]

That actually selects B3 and C3, not D5

[YellowLabPro]

John,
I am working on something else and went to view hidden workbooks, The VBA Programmers Guide shows up here under HIdden, why is that?

[Bob Phillips]

This would work :


Range("B3:C3").Select
They are not the same, try them both and see.

[debauch]

My mistake,
I thought he was trying to select b & c, but using his formula, was getting D by accident.

[johnske]

John,
I am working on something else and went to view hidden workbooks, The VBA Programmers Guide shows up here under HIdden, why is that?Sorry Doug, my big OOPS - just open the Programmers Guide, go to Windows > Unhide and click the X to get rid of it. I was using a couple of windows when working on it and hid it at one time, then forgot all about it being hidden - fixed in the zip file for download now also...

[YellowLabPro]

Done,
Just was not sure if there was reason behind it and you needed it there to behave properly....

thanks John.... Looking forward to getting to read this

[johnske]

The 2000 guide has now been added (same format as '97). This goes into a lot more depth than the '97 guide. Download here (http://xlvba.3.forumer.com/index.php?act=ST&f=15&t=97&st=0#entry452)

Bob - this one is a bit more of a 'work of art', I didn't like the online format for this version so I reformatted everything (inc indenting all the code and formatting as per VBE IDE). Took much much longer than I thought it would (about a week) so I reckon you all owe me a beer... :beerchug:

[mdmackillop]

Well done John, you certainly deserve at least one.:beerchug:

[Bob Phillips]

Took much much longer than I thought it would (about a week) so I reckon you all owe me a beer... :beerchug:

As long as it's English!

[johnske]

Sorry guys - just noticed lots of links to contents weren't working properly (was edited extensively after links were 1st pasted).

To the 5 that've already downloaded - it's fixed here (http://xlvba.3.forumer.com/index.php?act=ST&f=15&t=97&st=0#entry452) if you wanna dowload again :)

[Aussiebear]

dowload? Is that some sort of urban Brissie geek talk?

[YellowLabPro]

Thanks John.