Please help! 'get end' bug?

[St!ff M!ttens]

the following code returns predictable results:

tell application "Microsoft Excel"
set theSelection to (get selection)
set theSelectedRange to (item 1 of (get areas of (get properties of
theSelection)))
end tell

==>range "'[Workbook1]Sheet1'!$I$23" of application "Microsoft Excel"

now, the Excel dictionary has this entry:

get end: Returns a range object that represents the cell at the end of
the region that contains the source range.
get end range
direction toward the bottom/toward the left/toward the right/toward
the top -- The direction in which to move.
Result: range

So, I would think that the following code would then return 23, but:

tell application "Microsoft Excel"
set theSelection to (get selection)
set theSelectedRange to (item 1 of (get areas of (get properties of
theSelection)))
set theEndRange to first row index of (get end theSelectedRange
direction toward the bottom)
end tell

==>65536

Okay, I think I see what's going on: "Returns a range object that
represents the cell at the end of the region that contains the source
range" means that really all this command is going to do for me is
return the last cell in the worksheet, and what possible use is that to
me?

But wait, the following code returns something different:

tell application "Microsoft Excel"
first row index of (get end (used range of sheet 1 of active
workbook) direction toward the bottom)
end tell

==>10

the result 10, in my case, is exactly what I originally thought the
'get end' command should do. But the source range (in this case, 'used
range of sheet 1 of active workbook') is contained by the entire
worksheet in the same way that the selected range is in the previous
code, isn't it? So why do these two return different results? And more
importantly, how can I get my script to look at the current selection
and determine how many rows it spans?

St!ff

 

[Paul Berkowitz]

the following code returns predictable results:

tell application "Microsoft Excel"
set theSelection to (get selection)
set theSelectedRange to (item 1 of (get areas of (get properties of
theSelection)))
end tell

==>range "'[Workbook1]Sheet1'!$I$23" of application "Microsoft Excel"

now, the Excel dictionary has this entry:

get end: Returns a range object that represents the cell at the end of
the region that contains the source range.
get end range
direction toward the bottom/toward the left/toward the right/toward
the top -- The direction in which to move.
Result: range

So, I would think that the following code would then return 23, but:

tell application "Microsoft Excel"
set theSelection to (get selection)
set theSelectedRange to (item 1 of (get areas of (get properties of
theSelection)))
set theEndRange to first row index of (get end theSelectedRange
direction toward the bottom)
end tell

==>65536

I got 7 (having selected a few rows ending with row 6). I think you'll find
that you must have lost your selection before you ran the script. There's a
distinction in Excel between the 'active cell' - the cell surrounded by a
border which you get when you click once in a cell, or the cell where the
cursor was (it looks "whiter") within a selected larger range, and the
'selection', which is what you get when you drag the cursor over a larger
range and they are all highlighted (except for the active cell, which is
still within the selection border but is not highlighted). If you had a
larger selection, then clicked in a cell, you no longer have a selection,
just an active cell.

The peculiarity in AppleScript is that (get selection) will return the
active cell when there is no larger selection, but (get end theSelection)
understands that there is in fact no selection and defaults to the whole
worksheet. The bug, such as it is, might be that (get selection) returns the
active cell.

In any case, it is really more user error (yours) than a bug, although I
agree that you should get an error or missing value for (get selection) when
there is none and (get end) on that result should also error. Or if (get
selection) really is meant to return the active cell, then (get end) should
act on that same result and not play funny business.

First click just one cell and run the script. Then select a larger range and
re-run it. You'll see the first run returns 65536, and the second run
returns 23 or whatever is the next row.

Okay, I think I see what's going on: "Returns a range object that
represents the cell at the end of the region that contains the source
range" means that really all this command is going to do for me is
return the last cell in the worksheet, and what possible use is that to
me?

But wait, the following code returns something different:

tell application "Microsoft Excel"
first row index of (get end (used range of sheet 1 of active
workbook) direction toward the bottom)
end tell

==>10

the result 10, in my case, is exactly what I originally thought the
'get end' command should do. But the source range (in this case, 'used
range of sheet 1 of active workbook') is contained by the entire
worksheet in the same way that the selected range is in the previous
code, isn't it? So why do these two return different results? And more
importantly, how can I get my script to look at the current selection
and determine how many rows it spans?

St!ff

--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.