"=RESULT()", "=ARGUMENT()", "=RETURN()" ??

[PeteCresswell]

I'm tying to figure out a function whose rows look like this:
----------------------------
SDV_AN
=RESULT(1)
=ARGUMENT("returns",64)
=ARGUMENT("per_yr",1)
=STDEV(returns)*SQRT(per_yr)
=RETURN(R[-1]C)
AR
----------------------------

Seems pretty clear that "SDV_AN" is the name of the function and the
entry point, and I can find STDEV() and "SQRT() in Help, no problem.

I'm guessing that "RETURN()" sets the returned value of the function
to the previous row in the same column.

I'm also guessing that the two "=ARGUMENT()" calls dim two arguments
tb passed to the function in that sequence and, perhaps name them....
"returns" and "per_yr".... and *maybe* the second parm is some kind
of length or item count?

I'm not coming up with anything useful searching on "RESULT",
"ARGUMENT", or "RETURN" - either in Help or Google.

Am I even close on my from-context guesses?

Can anybody point me to some definitions/explainations?

 

[Niek Otten]

Hi Pete,

What you found is a so-called Excel 4 Macro/Function.
As the name suggests, they were introduced in Excel4, and the system was
replaced in Excel5 already, by VBA Macros/Functions.
Although you need an older version of Excel to record macros that way, you
can still develop them (CTRL+F11 inserts a macro sheet) and running them is
still supported in Excel XP. Nobody knows for sure, but that might well be
the last version to do that.

Indeed, SDV_AN is the function name.
Then the arguments are declared ("dimmed"). Both names and celladdresses can
be used in formulas in the function. Your function uses the R1C1 reference
style, which can be set via Tools>Options>General.
The number describes the type of argument:

1 Number
2 Text
4 Logical (Boolean)
8 Reference
16 Error
64 Array

This structure allows you to combine argument types; 72 would mean that both
Array(64) and Reference (8) are allowed.

RESULT declares what data type the result of the function is, in the same
way as the arguments are declared.
RETURN assigns a value to be returned to the function call.

Your function in VBA would something like this (not tested!):

Function SDC_AN (returns as range, per_y as double) as Double
dim a
a = application.
worksheetfunction.stdev(returns)*application.worksheetfunction.sqrt(per_yr)
SDC_AN = a
end function

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel