Hello monkeyboy,
Functions actually can modify worksheets. It's only when the functions are
called from a worksheet formula that they can't. When a function is called
from a worksheet formula, the function is considered a UDF or "user-defined
function". When the function is called from other VBA code, it is just
called a function, and doesn't have the same restrictions on what it can do.
However, the solution to your query is straightforward. You don't have to
use a range object, you can also use an array of values. This example
calculates the same example problem demonstrated in the help topic for
FORECAST:
Function FORECAST_Example()
Dim X As Double
Dim Known_ys As Variant, Known_xs As Variant
X = 30#
Known_ys = Array(6#, 7#, 9#, 15#, 21#)
Known_xs = Array(20#, 28#, 31#, 38#, 40#)
FORECAST_Example = Application.WorksheetFunction.Forecast(X, Known_ys,
Known_xs)
End Function
I used all doubles to demonstrate that that is not a problem.
The result is the same as indicated in the FORECAST help topic -- 10.60725
(actually, 10.60725309).
Alternatively, you can actually use the following form (although it might be
slower):
FORECAST_Example =
Application.Caller.Parent.Evaluate("FORECAST(30,{6,7,9,15,21},{20,28,31,38,4
0})")
In the above form, Application.Caller.Parent refers to the worksheet the UDF
was called from, because Application.Caller refers to a range object
referring to the cell containing the formula that called the UDF.
The Evaluate method can refer to a valid formula as if it were contained in
a cell. The curly braces enclose the arrays of values.
This form also returns the same answer.
Asa
-----Original Message-----
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of monkeyboy
Sent: Sunday, December 11, 2011 9:11 AM
To: MS EXCEL AND VBA MACROS
Subject: $$Excel-Macros$$ Worksheet Function Requires Range object as
parameter
Hello,
I would like to use the Forecast worksheet function in an integration
routine. It appears to require a Range object as the look up value, my
code carries this value as a double. Is there a way to wrap the value
into a Range object without having to reference the worksheet
explicitly?
Functions can't change the calling environment, so trying to just
write the value to a cell fails
--
FORUM RULES (934+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (934+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
