Function Macros inspired by the recent "date in excel" thread.

[Salmon Egg]

I vaguely remember the use of function macros from an early version of
Excel. The date in excel thread reminded me of that because that might
offer a simple macro with programming not much different from just
writing a spreadsheet. IIRC this mini-spreadsheet could use data from
the calling spreadsheet to control its calculation.

IIRC, the function macro function is called with arguments just like an
ordinary Excel function is called. The return value or values of the
function is just what you can create using spreadsheet statements.

For example, suppose you have a starting point A and and end point B on
the Earth given by their latitudes and longitudes. A spreadsheet can be
created that will calculate the distance between A and B along a great
circle route and what the bearing is at A to B along such a route. Thus,
a function to solve a simple navigation problem is available to be
called from inside the calling macro.

Is such a capability available in more modern versions of Excel?

--

Sam

Conservatives are against Darwinism but for natural selection.
Liberals are for Darwinism but totally against any selection.

 

[DanielCo]

Have a look at Chip Pearson's page :
http://www.cpearson.com/excel/LatLong.aspx
Daniel

 

[Stan Brown]

I vaguely remember the use of function macros from an early version of
Excel. The date in excel thread reminded me of that because that might
offer a simple macro with programming not much different from just
writing a spreadsheet. IIRC this mini-spreadsheet could use data from
the calling spreadsheet to control its calculation.

IIRC, the function macro function is called with arguments just like an
ordinary Excel function is called. The return value or values of the
function is just what you can create using spreadsheet statements.

For example, suppose you have a starting point A and and end point B on
the Earth given by their latitudes and longitudes. A spreadsheet can be
created that will calculate the distance between A and B along a great
circle route and what the bearing is at A to B along such a route. Thus,
a function to solve a simple navigation problem is available to be
called from inside the calling macro.

Is such a capability available in more modern versions of Excel?

I think you're combining two different memories, both of which are
still supported.

You could indeed have a spreadsheet to do calculations and reference
some other spreadsheet by name. This has the obvious problem that
it's not very flexible.

Or, you could write a function-type macro in Visual Basic; this is
called a user-defined function or UDF, and you call it just like any
other function.

 

[Frederic LE GUEN - MVP Excel]

I think you're combining two different memories, both of which are
still supported.

You could indeed have a spreadsheet to do calculations and reference
some other spreadsheet by name.  This has the obvious problem that
it's not very flexible.

Or, you could write a function-type macro in Visual Basic; this is
called a user-defined function or UDF, and you call it just like any
other function.

I wrote this function to solve its problem
Function InsertDateAndTime()
InsertDateAndTime = Date & " " & Time
End Function

It works

 

[Salmon Egg]

Have a look at Chip Pearson's page :
http://www.cpearson.com/excel/LatLong.aspx
Daniel

Maybe I was not very clear. The object of my post WAS NOT to solve a
trigonometry problem. It was to find out how to use a spreadsheet
snippet to produce a callable function. You, for instance, select a cell
or group of cells to call the function. In the first cell you have a
function call =myFunction(x,y). this calls the spreadsheet snippet. The
snippet calculates the value of the function and returns it in adjacent
cells.

--

Sam

Conservatives are against Darwinism but for natural selection.
Liberals are for Darwinism but totally against any selection.

 

[Salmon Egg]

I don't think that I am confused on that particular point--but there can
be other points.

There were were full fledged macrosheets that would allow complicated
calculations and operations. Among other things, it could do all kinds
of things to set up spread sheets with formatting etc. It amounted to a
full fledged computer language, but not intuitive. It was so unnatural,
that Visual Basic was a snap in comparison.

I am talking about a small spreadsheet that used local variables or
cells to hold data. The call would enter arguments for the function.
There wore numerical results at the end that would be placed in a cell
which listed the call.

--

Sam

Conservatives are against Darwinism but for natural selection.
Liberals are for Darwinism but totally against any selection.

 

[DanielCo]

Sorry for misunderstanding. You can't use a function to do it. A
function returns the result in the cell where it has been entered, like
any inbuilt Excel function. You may use an event macro to be triggered
when you enter data in one or more cells.
Daniel