Tool for building user-defined functions without knowing VBA: usef

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
 
J

John

Hi Jocelyn
I believe it would be very useful, but what's its limitation and at what price ?
I'm starting to learn VBA, but no practice at this time.
I normally stay away from those things because to many time they are full of
bugs.
Just my thought.
Regards
John
 
N

Niek Otten

Sure it would be useful. But difficult to realise!

I tried one similar product and it failed bitterly. Nested formulas, not
just arithmetic operators but functions too, different addressing styles,
defined names, ....................

For more than 20 years I asked Microsoft (every now and then) to develop
this option. Including good HELP facilities (same level as built in
functions), translation options, etc.

I admire your courage. But I'm not too optimistic about your chances. Please
prove me wrong!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top