How to retrieve range in formula?
 

I need to create a chart of values that are contained in a cell's formula.
For example, if cell A1 has this formula:

=STDEV(Sheet1!H1:H10)

I want to be able select that cell, click a button, and produce a chart
based on the range H1:H10. I will not know what range is specified in the
cell's formula until the cell is selected. Is there any way to retrieve the
range that's contained in the cells formula?

Thanks in advance.

How to retrieve range in formula?
 

You can use the directprecedents property

demo'd from the immeditate window.

? ActiveCell.Formula
=STDEV(Sheet1!H1:H10)

set rng = ActiveCell.DirectPrecedents

? rng.Address
$H$1:$H$10

? rng.Address(0,0,,True)
[Book1]Sheet1!H1:H10

--
Regards,
Tom Ogilvy


"deko" wrote in message
om...
I need to create a chart of values that are contained in a cell's formula.
For example, if cell A1 has this formula:

=STDEV(Sheet1!H1:H10)

I want to be able select that cell, click a button, and produce a chart
based on the range H1:H10. I will not know what range is specified in the
cell's formula until the cell is selected. Is there any way to retrieve
the
range that's contained in the cells formula?

Thanks in advance.

How to retrieve range in formula?
 

"Tom Ogilvy" wrote in message
...
You can use the directprecedents property

demo'd from the immeditate window.

? ActiveCell.Formula
=STDEV(Sheet1!H1:H10)

set rng = ActiveCell.DirectPrecedents

? rng.Address
$H$1:$H$10

? rng.Address(0,0,,True)
[Book1]Sheet1!H1:H10

--
Regards,
Tom Ogilvy


"deko" wrote in message
om...
I need to create a chart of values that are contained in a cell's
formula.
For example, if cell A1 has this formula:

=STDEV(Sheet1!H1:H10)

I want to be able select that cell, click a button, and produce a chart
based on the range H1:H10. I will not know what range is specified in
the
cell's formula until the cell is selected. Is there any way to retrieve
the
range that's contained in the cells formula?

Thanks in advance.

How to retrieve range in formula?
 

? ActiveCell.Formula
=STDEV(Sheet1!H1:H10)

set rng = ActiveCell.DirectPrecedents

? rng.Address
$H$1:$H$10

? rng.Address(0,0,,True)
[Book1]Sheet1!H1:H10

Thanks for the quick reply. I'll give it a shot and post back.

How to retrieve range in formula?
 

great.
as I understand direct precedents is the formula
what is meant by directdependents
thanks and regards

Tom Ogilvy wrote in message
...
You can use the directprecedents property

demo'd from the immeditate window.

? ActiveCell.Formula
=STDEV(Sheet1!H1:H10)

set rng = ActiveCell.DirectPrecedents

? rng.Address
$H$1:$H$10

? rng.Address(0,0,,True)
[Book1]Sheet1!H1:H10

--
Regards,
Tom Ogilvy


"deko" wrote in message
om...
I need to create a chart of values that are contained in a cell's
formula.
For example, if cell A1 has this formula:

=STDEV(Sheet1!H1:H10)

I want to be able select that cell, click a button, and produce a chart
based on the range H1:H10. I will not know what range is specified in
the
cell's formula until the cell is selected. Is there any way to retrieve
the
range that's contained in the cells formula?

Thanks in advance.

How to retrieve range in formula?
 

You can use the directprecedents property

I put this code in a module and call it from a button on Sheet1 - seems to
be working okay...

Public Sub CreateChart(bytChart As Byte)
Dim sChart As Chart
Dim sRange As Range
Set sRange = Application.ActiveWindow.ActiveCell.DirectPreceden ts
Sheet2.ChartObjects(1).Delete
Set sChart = Charts.Add
With sChart
.chartType = xlBarClustered
.SetSourceData Source:=sRange, PlotBy:=xlRows
.HasLegend = False
.ApplyDataLabels Type:=xlDataLabelsShowValue
.Location xlLocationAsObject, "Sheet2"
End With
With Sheet2.ChartObjects(1)
.Width = 600
.Height = 400
.Top = 20
.Left = 100
End With
End Sub

Thanks for the help!

How to retrieve range in formula?
 

If your asking what is directprecedents, it is any ranges that the formula
in the cell depends on.

However, It doesn't work for linked to cells - cells on other worksheets, on
cells/ranges on the same sheet.

--
Regards,
Tom Ogilvy

"R.VENKATARAMAN" $$$ wrote in message
...
great.
as I understand direct precedents is the formula
what is meant by directdependents
thanks and regards

Tom Ogilvy wrote in message
...
You can use the directprecedents property

demo'd from the immeditate window.

? ActiveCell.Formula
=STDEV(Sheet1!H1:H10)

set rng = ActiveCell.DirectPrecedents

? rng.Address
$H$1:$H$10

? rng.Address(0,0,,True)
[Book1]Sheet1!H1:H10

--
Regards,
Tom Ogilvy


"deko" wrote in message
om...
I need to create a chart of values that are contained in a cell's
formula.
For example, if cell A1 has this formula:

=STDEV(Sheet1!H1:H10)

I want to be able select that cell, click a button, and produce a
chart
based on the range H1:H10. I will not know what range is specified in
the
cell's formula until the cell is selected. Is there any way to
retrieve
the
range that's contained in the cells formula?

Thanks in advance.

How to retrieve range in formula?
 

However, It doesn't work for linked to cells - cells on other worksheets,
on
cells/ranges on the same sheet.

Yes, I discovered this limitation - which conflicts with the requirements I
was given. I don't see why the Excel team couldn't make this work.

How to retrieve range in formula?
 

thanks Mr. Ogilvy.
I have understood the term <directprecedents. But when i went to help(vba)
of this term I find another term <directdependents in <see also of this
help.
I would like to know the utility of this function. regards

mine excel 2000
===============
Tom Ogilvy wrote in message
...
If your asking what is directprecedents, it is any ranges that the formula
in the cell depends on.

However, It doesn't work for linked to cells - cells on other worksheets,
on
cells/ranges on the same sheet.

--
Regards,
Tom Ogilvy

"R.VENKATARAMAN" $$$ wrote in message
...
great.
as I understand direct precedents is the formula
what is meant by directdependents
thanks and regards

Tom Ogilvy wrote in message
...
You can use the directprecedents property

demo'd from the immeditate window.

? ActiveCell.Formula
=STDEV(Sheet1!H1:H10)

set rng = ActiveCell.DirectPrecedents

? rng.Address
$H$1:$H$10

? rng.Address(0,0,,True)
[Book1]Sheet1!H1:H10

--
Regards,
Tom Ogilvy


"deko" wrote in
message
om...
I need to create a chart of values that are contained in a cell's
formula.
For example, if cell A1 has this formula:

=STDEV(Sheet1!H1:H10)

I want to be able select that cell, click a button, and produce a
chart
based on the range H1:H10. I will not know what range is specified
in
the
cell's formula until the cell is selected. Is there any way to
retrieve
the
range that's contained in the cells formula?

Thanks in advance.

How to retrieve range in formula?
 

I would surmise the problem is somehow concerned with the fact that it
returns a single range object. This would work if all the precedents were
on a single sheet - either the same sheet as the formula or on the same
external sheet. However, if the arguments involved a mix of sheets, then a
single range object can not address multiple sheets. Perhaps the function
should return an array.

--
Regards,
Tom Ogilvy

"deko" wrote in message
m...
However, It doesn't work for linked to cells - cells on other
worksheets,
on
cells/ranges on the same sheet.

Yes, I discovered this limitation - which conflicts with the requirements
I
was given. I don't see why the Excel team couldn't make this work.

How to retrieve range in formula?
 

DirectDependents are those cells for which the cell in question is a
DirectPrecedent

if A1 had the formula
=C1

Range("A1").DirectPrecedents
would return a range reference to C1

Range("C1").DirectDependents
would return a range reerence to A1

as shown from the immediate window:


? Range("A1").Formula
=C1
? Range("A1").DirectPrecedents.Address
$C$1
? Range("C1").DirectDependents.Address
$A$1

--
Regards,
Tom Ogilvy


"R.VENKATARAMAN" $$$ wrote in message
...
thanks Mr. Ogilvy.
I have understood the term <directprecedents. But when i went to
help(vba)
of this term I find another term <directdependents in <see also of this
help.
I would like to know the utility of this function. regards

mine excel 2000
===============
Tom Ogilvy wrote in message
...
If your asking what is directprecedents, it is any ranges that the
formula
in the cell depends on.

However, It doesn't work for linked to cells - cells on other
worksheets,
on
cells/ranges on the same sheet.

--
Regards,
Tom Ogilvy

"R.VENKATARAMAN" $$$ wrote in message
...
great.
as I understand direct precedents is the formula
what is meant by directdependents
thanks and regards

Tom Ogilvy wrote in message
...
You can use the directprecedents property

demo'd from the immeditate window.

? ActiveCell.Formula
=STDEV(Sheet1!H1:H10)

set rng = ActiveCell.DirectPrecedents

? rng.Address
$H$1:$H$10

? rng.Address(0,0,,True)
[Book1]Sheet1!H1:H10

--
Regards,
Tom Ogilvy


"deko" wrote in
message
om...
I need to create a chart of values that are contained in a cell's
formula.
For example, if cell A1 has this formula:

=STDEV(Sheet1!H1:H10)

I want to be able select that cell, click a button, and produce a
chart
based on the range H1:H10. I will not know what range is
specified
in
the
cell's formula until the cell is selected. Is there any way to
retrieve
the
range that's contained in the cells formula?

Thanks in advance.