functions in charts

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

Is it possible to enter a user-defined function (that
returns an array) for values in a chart instead of
specifying a range in the worksheet?
 
Joe -

You can specify a named range, with the formula built in. See the
Dynamic Chart examples and links on my web site:

http://www.geocities.com/jonpeltier/Excel/Charts/Dynamics.html

A good example of a formula in a named range can be found on Stephen
Bullen's site:

http://bmsltd.co.uk

Look for the ChtFmla.xls example. Tushar mehta has a similar example on
his site:

http://tushar-mehta.com

In VBA it's possible to assign an array to the data values in a chart,
but you are limited in the number of points you can include without errors.

- Jon
 
[This followup was posted to microsoft.public.excel.charting with an
email copy to Joe.
Please use the newsgroup for further discussion.]

Yes, in a limited fashion. Named formulas cannot be parameterized.
So, about the best one can do is the following.

Suppose you define a UDF as:

Function YVals(x As Range)
Dim y As Variant, i As Long
ReDim y(1 To x.Cells.Count)
For i = 1 To UBound(y)
y(i) = x(i) ^ 2
Next i
YVals = y
End Function

Then, define an XL name as
YvalsName =yvals(Sheet1!$A$1:$A$12)

Now, plot YvalsName in a chart. As you change A1:A12, the chart will
change.

You can take it a step further. Change the definition of YvalsName to
YvalsName =yvals(OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1))

Now, as you add or delete contiguous data points starting with A1, the
chart will also change.

--
Trouble finding replies to your posts? Use a newsreader. See the
tutorial 'Outlook Express and Newsgroups' on my web site

Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top