J
Jocelyn Paine
Would this be useful? I've prototyped a program
that enables one to create Excel user-defined
functions from templates in a worksheet that use
only Excel formulae, without needing to know VBA.
I'd love to know what others think of this.
I've written this up in my Dobbs Code Talk blog
at http://www.j-paine.org/dobbs/udfs.html . The posting includes screen
shots showing what the function definitions look like.
In essence, a function definition is just an
Excel formula or chain of formulae that calculate the function's
result from values in cells that represent its arguments.
These argument cells must be in fixed places in
the worksheet. So must the cell representing the result.
Intermediate formulae can go anywhere in the worksheet.
There must also be a cell containing the function's name
and one saying how many arguments it has.
For example, in this worksheet, the arguments
are represented by B2 and B3. The function's name
is in B1, and C1 says that it has 2 arguments.
The result is represented by B4, which holds
the formula =B2+B3:
A | B | C
1 Foo 2
2
3
4 =B2+B3
This tells my program to define a function
called Foo with 2 arguments. It does so by
taking the result formula =B2+B3, and
replacing the cell addresses by VBA variable
names that it generates. It then adds a VBA function
header that uses the same variable names, so that
they represent the arguments. So it generates
this:
public function Foo( var_1 as Double, var_2 as Double ) as Double
Foo = var_1+var_2
end function
It then inserts this into a new code module
in the spreadsheet, and you can then call Foo
in a formula. (I've tried!)
The syntax and semantics of VBA differ from those
of Excel formulae, so in general, I'd have to
convert formulae subexpression by subexpression,
rather than copying their operators and functions
verbatim. But that is a straightforward problem
in compiler design.
What do people think? I think this would
be useful, if it means Excel coders don't
need to learn VBA in order to write user-defined
functions. Of course, VBA will still be needed
for GUIs, macros, and such things; but for
small spreadsheets that are mainly calculation,
perhaps this could help sidestep it. At the
least, it could be used in a "VBA formula
wizard", by helping VBA novices to code
VBA from something they understand better,
namely formulae.
-- Jocelyn Paine,
http://www.j-paine.org
that enables one to create Excel user-defined
functions from templates in a worksheet that use
only Excel formulae, without needing to know VBA.
I'd love to know what others think of this.
I've written this up in my Dobbs Code Talk blog
at http://www.j-paine.org/dobbs/udfs.html . The posting includes screen
shots showing what the function definitions look like.
In essence, a function definition is just an
Excel formula or chain of formulae that calculate the function's
result from values in cells that represent its arguments.
These argument cells must be in fixed places in
the worksheet. So must the cell representing the result.
Intermediate formulae can go anywhere in the worksheet.
There must also be a cell containing the function's name
and one saying how many arguments it has.
For example, in this worksheet, the arguments
are represented by B2 and B3. The function's name
is in B1, and C1 says that it has 2 arguments.
The result is represented by B4, which holds
the formula =B2+B3:
A | B | C
1 Foo 2
2
3
4 =B2+B3
This tells my program to define a function
called Foo with 2 arguments. It does so by
taking the result formula =B2+B3, and
replacing the cell addresses by VBA variable
names that it generates. It then adds a VBA function
header that uses the same variable names, so that
they represent the arguments. So it generates
this:
public function Foo( var_1 as Double, var_2 as Double ) as Double
Foo = var_1+var_2
end function
It then inserts this into a new code module
in the spreadsheet, and you can then call Foo
in a formula. (I've tried!)
The syntax and semantics of VBA differ from those
of Excel formulae, so in general, I'd have to
convert formulae subexpression by subexpression,
rather than copying their operators and functions
verbatim. But that is a straightforward problem
in compiler design.
What do people think? I think this would
be useful, if it means Excel coders don't
need to learn VBA in order to write user-defined
functions. Of course, VBA will still be needed
for GUIs, macros, and such things; but for
small spreadsheets that are mainly calculation,
perhaps this could help sidestep it. At the
least, it could be used in a "VBA formula
wizard", by helping VBA novices to code
VBA from something they understand better,
namely formulae.
-- Jocelyn Paine,
http://www.j-paine.org