Thank you very much. This has been super helpful!
On Dec 12, 9:22 pm, "Asa Rossoff" <a...@lovetour.info> wrote:
> Hello monkeyboy,
>
> Functions actually can modify worksheets. It's only when the functions are
> called from a worksheet formula that they can't. When afunctionis called
> from a worksheet formula, thefunctionis considered a UDF or
> "user-definedfunction". When thefunctionis called from otherVBAcode, it is
> just
> called afunction, 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 arangeobject, you can also use an array of values. This example
> calculates the same example problem demonstrated in the help topic
> forFORECAST:
>
> FunctionFORECAST_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)
> EndFunction
>
> I used all doubles to demonstrate that that is not a problem.
>
> The result is the same as indicated in theFORECASThelp 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 arangeobject
> 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: MSEXCELANDVBAMACROS
> Subject: $$Excel-Macros$$ WorksheetFunctionRequiresRangeobjectas
> parameter
>
> Hello,
>
> I would like to use theForecastworksheetfunctionin an integration
> routine. It appears to require aRangeobjectas the look up value, my
> code carries this value as a double. Is there a way to wrap the value
> into aRangeobjectwithout 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 toexcel-mac...@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
