In a VB application, I am referencing an Excel workbook. I need to know the
row number of the toprow and bottomrow of any range selection. Therefore I
want to use the selection object. However, the 'selection' object is not
available for the Excel.worksheet method and is only available for the
Excel.application and it doesn't show any method after selection. For
example I can't use selection.rows. How can I change this ?

Oscar

Oscar,

Selection can only apply to the activesheet of the activeworkbook, so this
gets the first selected row

selection.row

and this is the last

selection.rows.count+selection.row-1

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Oscar" wrote in message
.nl...
In a VB application, I am referencing an Excel workbook. I need to know
the
row number of the toprow and bottomrow of any range selection. Therefore I
want to use the selection object. However, the 'selection' object is not
available for the Excel.worksheet method and is only available for the
Excel.application and it doesn't show any method after selection. For
example I can't use selection.rows. How can I change this ?

Oscar

Oscar,

Are you saying that you are writing code in VB (not VBA) and that you have an object variable referring to an active Excel workbook
(say oxlWkb)? If so, one way to refer to the current selection is:

oxlWkb.Application.Selection

--

John Green - Excel MVP
Sydney
Australia


"Oscar" wrote in message .nl...
In a VB application, I am referencing an Excel workbook. I need to know the
row number of the toprow and bottomrow of any range selection. Therefore I
want to use the selection object. However, the 'selection' object is not
available for the Excel.worksheet method and is only available for the
Excel.application and it doesn't show any method after selection. For
example I can't use selection.rows. How can I change this ?

Oscar

John,

yes, that's the case. I am able to reference the selection in this way, but
I can't adress any members such as .row or .rows inside the 'selection'
method, The VB IDE doesn't show them and in case I write

oxlWkb.Application.Selection.Row or
oxlWkb.Application.Selection.Rows or
oxlWkb.Application.Selection.Range

the compiler fires an error in runtime. Could you please explain what I need
to do after the oxlWkb.Application.Selection statement in order to find out
the row numbers of the toprow and bottomrow.
regards,
Oscar




"John Green" schreef in bericht
...
Oscar,

Are you saying that you are writing code in VB (not VBA) and that you have
an object variable referring to an active Excel workbook
(say oxlWkb)? If so, one way to refer to the current selection is:

oxlWkb.Application.Selection

--

John Green - Excel MVP
Sydney
Australia


"Oscar" wrote in message
.nl...
In a VB application, I am referencing an Excel workbook. I need to know
the
row number of the toprow and bottomrow of any range selection. Therefore
I
want to use the selection object. However, the 'selection' object is not
available for the Excel.worksheet method and is only available for the
Excel.application and it doesn't show any method after selection. For
example I can't use selection.rows. How can I change this ?

Oscar

Bob,

I can adress activeworksheet after activeworkbook, but the VB IDE doesn't
show any members such as the selection method after the dot after
'activesheet' method. In case that I write it by hand I receive an error
during runtime.

So I can't have myExcelApp.Activeworkbook.Activesheet.Selection.ro w
interpreted by the compiler.
Oscar




"Bob Phillips" schreef in bericht
...
Oscar,

Selection can only apply to the activesheet of the activeworkbook, so this
gets the first selected row

selection.row

and this is the last

selection.rows.count+selection.row-1

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Oscar" wrote in message
.nl...
In a VB application, I am referencing an Excel workbook. I need to know
the
row number of the toprow and bottomrow of any range selection. Therefore
I
want to use the selection object. However, the 'selection' object is not
available for the Excel.worksheet method and is only available for the
Excel.application and it doesn't show any method after selection. For
example I can't use selection.rows. How can I change this ?

Oscar

Oscar,

The code supplied by Bob Phillips should work, when adjusted as follows:

FirstRow = oxlWkb.Application.Selection.Row
LastRow = oxlWkb.Application.Selection.Row + oxlWkb.Application.Selection.Rows.Count - 1

--

John Green - Excel MVP
Sydney
Australia


"Oscar" wrote in message . nl...
John,

yes, that's the case. I am able to reference the selection in this way, but
I can't adress any members such as .row or .rows inside the 'selection'
method, The VB IDE doesn't show them and in case I write

oxlWkb.Application.Selection.Row or
oxlWkb.Application.Selection.Rows or
oxlWkb.Application.Selection.Range

the compiler fires an error in runtime. Could you please explain what I need
to do after the oxlWkb.Application.Selection statement in order to find out
the row numbers of the toprow and bottomrow.
regards,
Oscar




"John Green" schreef in bericht
...
Oscar,

Are you saying that you are writing code in VB (not VBA) and that you have
an object variable referring to an active Excel workbook
(say oxlWkb)? If so, one way to refer to the current selection is:

oxlWkb.Application.Selection

--

John Green - Excel MVP
Sydney
Australia


"Oscar" wrote in message
.nl...
In a VB application, I am referencing an Excel workbook. I need to know
the
row number of the toprow and bottomrow of any range selection. Therefore
I
want to use the selection object. However, the 'selection' object is not
available for the Excel.worksheet method and is only available for the
Excel.application and it doesn't show any method after selection. For
example I can't use selection.rows. How can I change this ?

Oscar

Oscar,

To reference the selection, you will need to select something. This means
that a workbook will need to be activated, as will a worksheet, and a range
will need to be selected.

However, it is rarely necessary to select in Excel VBA, let alone VB. What
exactly are you trying to do?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Oscar" wrote in message
. nl...
John,

yes, that's the case. I am able to reference the selection in this way,
but
I can't adress any members such as .row or .rows inside the 'selection'
method, The VB IDE doesn't show them and in case I write

oxlWkb.Application.Selection.Row or
oxlWkb.Application.Selection.Rows or
oxlWkb.Application.Selection.Range

the compiler fires an error in runtime. Could you please explain what I
need
to do after the oxlWkb.Application.Selection statement in order to find
out
the row numbers of the toprow and bottomrow.
regards,
Oscar




"John Green" schreef in bericht
...
Oscar,

Are you saying that you are writing code in VB (not VBA) and that you
have
an object variable referring to an active Excel workbook
(say oxlWkb)? If so, one way to refer to the current selection is:

oxlWkb.Application.Selection

--

John Green - Excel MVP
Sydney
Australia


"Oscar" wrote in message
.nl...
In a VB application, I am referencing an Excel workbook. I need to
know
the
row number of the toprow and bottomrow of any range selection.
Therefore
I
want to use the selection object. However, the 'selection' object is
not
available for the Excel.worksheet method and is only available for the
Excel.application and it doesn't show any method after selection. For
example I can't use selection.rows. How can I change this ?

Oscar

ActiveSheet is not a property of the workbook object but of the application object, which is why your code fails.

Selection is tricky. It is a property of the Application or the Window object. If you already have an object variable referring to
the Excel application, use:

FirstRow = myExcelApp.Selection.Row

--

John Green - Excel MVP
Sydney
Australia


"Oscar" wrote in message . nl...

Bob,

I can adress activeworksheet after activeworkbook, but the VB IDE doesn't
show any members such as the selection method after the dot after
'activesheet' method. In case that I write it by hand I receive an error
during runtime.

So I can't have myExcelApp.Activeworkbook.Activesheet.Selection.ro w
interpreted by the compiler.
Oscar




"Bob Phillips" schreef in bericht
...
Oscar,

Selection can only apply to the activesheet of the activeworkbook, so this
gets the first selected row

selection.row

and this is the last

selection.rows.count+selection.row-1

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Oscar" wrote in message
.nl...
In a VB application, I am referencing an Excel workbook. I need to know
the
row number of the toprow and bottomrow of any range selection. Therefore
I
want to use the selection object. However, the 'selection' object is not
available for the Excel.worksheet method and is only available for the
Excel.application and it doesn't show any method after selection. For
example I can't use selection.rows. How can I change this ?

Oscar

I've written a VB Active-X dll that exposes methods which are called within
the VBA macros in a Excel worksheet. I've done that in order to protect the
source and to gain speed since compiled active-X dll are faster than VBA
internal codes. Up to now I've been able to adress anything that I needed by
referencing to the Excel.worksheet object. I have even been able to
reference controls such as comboboxes on top of the Excel worksheet by the
dll. Now I need to do some processing with selections made by the user in
this way :

user makes a range selection.
user activates a macro by pressing F8 (this macro calls the processing
routine within a class within the Active-X dll)
the class method performs the processing within the range that was selected
by the user.

Oscar


"Bob Phillips" schreef in bericht
...
Oscar,

To reference the selection, you will need to select something. This means
that a workbook will need to be activated, as will a worksheet, and a
range
will need to be selected.

However, it is rarely necessary to select in Excel VBA, let alone VB. What
exactly are you trying to do?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Oscar" wrote in message
. nl...
John,

yes, that's the case. I am able to reference the selection in this way,
but
I can't adress any members such as .row or .rows inside the 'selection'
method, The VB IDE doesn't show them and in case I write

oxlWkb.Application.Selection.Row or
oxlWkb.Application.Selection.Rows or
oxlWkb.Application.Selection.Range

the compiler fires an error in runtime. Could you please explain what I
need
to do after the oxlWkb.Application.Selection statement in order to find
out
the row numbers of the toprow and bottomrow.
regards,
Oscar




"John Green" schreef in bericht
...
Oscar,

Are you saying that you are writing code in VB (not VBA) and that you
have
an object variable referring to an active Excel workbook
(say oxlWkb)? If so, one way to refer to the current selection is:

oxlWkb.Application.Selection

--

John Green - Excel MVP
Sydney
Australia


"Oscar" wrote in message
.nl...
In a VB application, I am referencing an Excel workbook. I need to
know
the
row number of the toprow and bottomrow of any range selection.
Therefore
I
want to use the selection object. However, the 'selection' object is
not
available for the Excel.worksheet method and is only available for
the
Excel.application and it doesn't show any method after selection.
For
example I can't use selection.rows. How can I change this ?

Oscar

Okay, I stand corrected <vbg.

John has explained why you might be getting an error, but you might also
consider an alternative. Instead of having the user select a range and then
activating the DLL, you could invoke the DLL, and then use
Application.InputBox with a Type argument of 8, so the user can then select
a range, and you can reference that. Just a thought.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Oscar" wrote in message
. nl...

I've written a VB Active-X dll that exposes methods which are called
within
the VBA macros in a Excel worksheet. I've done that in order to protect
the
source and to gain speed since compiled active-X dll are faster than VBA
internal codes. Up to now I've been able to adress anything that I needed
by
referencing to the Excel.worksheet object. I have even been able to
reference controls such as comboboxes on top of the Excel worksheet by the
dll. Now I need to do some processing with selections made by the user in
this way :

user makes a range selection.
user activates a macro by pressing F8 (this macro calls the processing
routine within a class within the Active-X dll)
the class method performs the processing within the range that was
selected
by the user.

Oscar


"Bob Phillips" schreef in bericht
...
Oscar,

To reference the selection, you will need to select something. This
means
that a workbook will need to be activated, as will a worksheet, and a
range
will need to be selected.

However, it is rarely necessary to select in Excel VBA, let alone VB.
What
exactly are you trying to do?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Oscar" wrote in message
. nl...
John,

yes, that's the case. I am able to reference the selection in this
way,
but
I can't adress any members such as .row or .rows inside the
'selection'
method, The VB IDE doesn't show them and in case I write

oxlWkb.Application.Selection.Row or
oxlWkb.Application.Selection.Rows or
oxlWkb.Application.Selection.Range

the compiler fires an error in runtime. Could you please explain what
I
need
to do after the oxlWkb.Application.Selection statement in order to
find
out
the row numbers of the toprow and bottomrow.
regards,
Oscar




"John Green" schreef in bericht
...
Oscar,

Are you saying that you are writing code in VB (not VBA) and that
you
have
an object variable referring to an active Excel workbook
(say oxlWkb)? If so, one way to refer to the current selection is:

oxlWkb.Application.Selection

--

John Green - Excel MVP
Sydney
Australia


"Oscar" wrote in message
.nl...
In a VB application, I am referencing an Excel workbook. I need to
know
the
row number of the toprow and bottomrow of any range selection.
Therefore
I
want to use the selection object. However, the 'selection' object
is
not
available for the Excel.worksheet method and is only available for
the
Excel.application and it doesn't show any method after selection.
For
example I can't use selection.rows. How can I change this ?

Oscar

that's a nice approach Bob, maybe I will need this in the future.
Meanwhile I've been able to solve the problem by

firstrow = objsheet.Application.Selection.Row
lastrow = firstrow + objsheet.Application.Selection.Rows.Count - 1

My initial problem was that I didn't have a good argument list within the
procedure that called the class method. After that it showed that
selection.rows needed to be extended by the count property. In case that the
objects would have been early-binding I wouldn't have to find this out by
trial and error.

Bob and John, thanks both of you for your quick help.
Oscar



"Bob Phillips" schreef in bericht
...
Okay, I stand corrected <vbg.

John has explained why you might be getting an error, but you might also
consider an alternative. Instead of having the user select a range and
then
activating the DLL, you could invoke the DLL, and then use
Application.InputBox with a Type argument of 8, so the user can then
select
a range, and you can reference that. Just a thought.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Oscar" wrote in message
. nl...

I've written a VB Active-X dll that exposes methods which are called
within
the VBA macros in a Excel worksheet. I've done that in order to protect
the
source and to gain speed since compiled active-X dll are faster than VBA
internal codes. Up to now I've been able to adress anything that I
needed
by
referencing to the Excel.worksheet object. I have even been able to
reference controls such as comboboxes on top of the Excel worksheet by
the
dll. Now I need to do some processing with selections made by the user
in
this way :

user makes a range selection.
user activates a macro by pressing F8 (this macro calls the processing
routine within a class within the Active-X dll)
the class method performs the processing within the range that was
selected
by the user.

Oscar


"Bob Phillips" schreef in bericht
...
Oscar,

To reference the selection, you will need to select something. This
means
that a workbook will need to be activated, as will a worksheet, and a
range
will need to be selected.

However, it is rarely necessary to select in Excel VBA, let alone VB.
What
exactly are you trying to do?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Oscar" wrote in message
. nl...
John,

yes, that's the case. I am able to reference the selection in this
way,
but
I can't adress any members such as .row or .rows inside the
'selection'
method, The VB IDE doesn't show them and in case I write

oxlWkb.Application.Selection.Row or
oxlWkb.Application.Selection.Rows or
oxlWkb.Application.Selection.Range

the compiler fires an error in runtime. Could you please explain
what
I
need
to do after the oxlWkb.Application.Selection statement in order to
find
out
the row numbers of the toprow and bottomrow.
regards,
Oscar




"John Green" schreef in bericht
...
Oscar,

Are you saying that you are writing code in VB (not VBA) and that
you
have
an object variable referring to an active Excel workbook
(say oxlWkb)? If so, one way to refer to the current selection is:

oxlWkb.Application.Selection

--

John Green - Excel MVP
Sydney
Australia


"Oscar" wrote in message
.nl...
In a VB application, I am referencing an Excel workbook. I need
to
know
the
row number of the toprow and bottomrow of any range selection.
Therefore
I
want to use the selection object. However, the 'selection'
object
is
not
available for the Excel.worksheet method and is only available
for
the
Excel.application and it doesn't show any method after
selection.
For
example I can't use selection.rows. How can I change this ?

Oscar

Hi Oscar,

In a VB application, I am referencing an Excel workbook. I need to know the
row number of the toprow and bottomrow of any range selection. Therefore I
want to use the selection object. However, the 'selection' object is not
available for the Excel.worksheet method and is only available for the
Excel.application and it doesn't show any method after selection. For
example I can't use selection.rows. How can I change this ?

The reason that VB isn't showing any Intellisense is that the Selection
property could return any type of Object, and not just a range. So you need
to check that it is a range selected and then cast it to a Range object:

Dim oSelectedRange As Excel.Range
Dim oXL As Excel.Application

'Initialise stuff

If TypeOf oXL.Selection Is Excel.Range Then
Set oSelectedRange = oXl.Selection

'Do stuff with oSelectedRange
Set oSelectedRange = Nothing
End If


Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk