In Excel, how do I make a lookup table that returns a function?
 

In column A, I have a numerical code that corresponds to some function. In
column B, I have a parameter of that function (either a numeric constant or a
cell reference, depending on the function.) How do I make a lookup table
that returns the function that must operate on the parameter?

In Excel, how do I make a lookup table that returns a function?
 

This doesn't seem well-specified. What is the nature of the number to
function correspondence? Are the functions worksheet functions? Are
they all of 1 variable? What criteria do you use to determine "the
function that must operate on the parameter"? I would think that most
parameters have the property that many functions could operate on them.
Finally, what do you mean by "return"? Do you want a string
representing the funtion? Do you want the function inserted into some
cell (which is easy enough in VBA if one can figure out *which* funtion
applied to *which* parameter.) Or - do you want a user-defined function
which, given a numerical code and given a parameter "runs" the
corresponding function on the parameter? Assuming that it is easy to
decode your number code into a string name this shouldn't be to hard
with the Evaluate method. You need to give more details about what you
are trying to do.

Zim wrote:
In column A, I have a numerical code that corresponds to some function. In
column B, I have a parameter of that function (either a numeric constant or a
cell reference, depending on the function.) How do I make a lookup table
that returns the function that must operate on the parameter?

In Excel, how do I make a lookup table that returns a function
 

Sorry... In my C world, it makes sense, but in the bigger VB world, maybe it
makes more sense to rephrase the challenge this way:

I would like to pass three parameters to a function, and have the function
operate on the first two, and return a value. What the function does when it
operates on them is defined by the third parameter, which is a number.

I am unskilled at VB, so if I were to try to tackle this challenge with
standard Excel functions, I could probably do it with a huge
if/then/else/if/then/else/etc cell formula, but that would be hard to
maintain for large numbers of possible operations. It seems that VB is a
good way to go, but I don't know where to begin.

"John Coleman" wrote:

This doesn't seem well-specified. What is the nature of the number to
function correspondence? Are the functions worksheet functions? Are
they all of 1 variable? What criteria do you use to determine "the
function that must operate on the parameter"? I would think that most
parameters have the property that many functions could operate on them.
Finally, what do you mean by "return"? Do you want a string
representing the funtion? Do you want the function inserted into some
cell (which is easy enough in VBA if one can figure out *which* funtion
applied to *which* parameter.) Or - do you want a user-defined function
which, given a numerical code and given a parameter "runs" the
corresponding function on the parameter? Assuming that it is easy to
decode your number code into a string name this shouldn't be to hard
with the Evaluate method. You need to give more details about what you
are trying to do.

Zim wrote:
In column A, I have a numerical code that corresponds to some function. In
column B, I have a parameter of that function (either a numeric constant or a
cell reference, depending on the function.) How do I make a lookup table
that returns the function that must operate on the parameter?

In Excel, how do I make a lookup table that returns a function
 

Zim wrote:
Sorry... In my C world, it makes sense, but in the bigger VB world, maybe it
makes more sense to rephrase the challenge this way:

I would like to pass three parameters to a function, and have the function
operate on the first two, and return a value. What the function does when it
operates on them is defined by the third parameter, which is a number.

How does this differ from a function of three variables, one of which
is a number? It seems like you are thinking of it as a 1 variable
family of 2 variable functions. I gather that the 2-variable functions
are excel worksheet functions, perhaps combined in various ways.
There is a VBA method called Application.Evaluate that might help. If
you pass it a string of the sort that would appear in the function bar
then Application.Evaluate returns the value that Excel would return if
it evaluated the expression. For example,

Sub test()
MsgBox Application.Evaluate("=BINOMDIST(20,40,0.5,TRUE)")
End Sub

Results in 0.5626... which is what you would get if you used Excel's
BINOMDIST function with those parameters. If you could write a VBA
function Decode(arg1,arg2,num) which returns a string which represents
the expression that you are interested in evaluating then you could
write the following:

Function EvalF(arg1 as variant,arg2 as variant,num as variant) as
variant
EvalF = Application.Evaluate(Decode(arg1,arg2,num))
End Function

If this (and the decode function) are stored in a standard code module
then EvalF would appear in the list of available work-sheet functions
(under the User Defined submenu) and could be used like any other
worksheet function.

There are other approaches possible. VBA has a Select Case statement
which is similar to C's switch statement. If the num parameter takes on
a relatively small number of values you can hard-wire the corresponding
functions into the EvalF. In this case Applcation.WorkSheetFunctions
might help (though not all worksheet functions are thus available to
VBA - sometimes you need to use the VBA equivalent e.g. VBA has its own
sine function and its own random number function)

sketch:

Function EvalF(arg1,arg2,num)

Select Case num
Case num1
EvalF = Application.Worksheetfunction.f1(arg1,arg2)
Case num2
EvalF = Application.Worksheetfunction.f2(arg1,arg2)

In Excel, how do I make a lookup table that returns a function
 

John,

That works most excellently! I used the Select Case statement, as you
sketched above. The only issue now is that the function does not reevaluate
itself when the argument cells change, until I click on the function in the
editing bar and press enter. Any hints?

--zim

In Excel, how do I make a lookup table that returns a function
 

Zim,

I'm not sure what is going on here. If the user defined function is a
function of certain inputs and those inputs are changed then the
function value should change automatically *unless* either:
1) Your calculation mode has been set to manual. Go to Tools - Options
- Calculation and make sure that it is automatic.

or

2) The user defined function is only implicitly a function of some
cells, which are playing the role of parameters. Excel won't detect a
change in implict arguments. For example, if in VBA you define a
function

Function f(arg as long) as long
f = Range("A1").Value*arg
End Function

And you put the formula =f(B1) in cell C1 then changing the contents of
B1 will automatically change the contents of C1 but changing the
contents of A1 won't. If this is the situation then you could either 1)
change the definition of your function to make its dependence on its
parameters explicit, or 2) add an event handler. In the Sheet1 code
module (or Sheet2 if this is taking place in Sheet2, etc.) add the
following event handler:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.Calculate
End Sub

You really only have to type Application.Calculate, since if you change
the list box in the IDE from (general) to worksheet then the listbox on
the right gives code shells for all of the sheet-events your code can
respond to.

If neither of these suggestions work then maybe you can post your code
so we can (maybe) see what is going on.

HTH

-John Coleman


Zim wrote:
John,

That works most excellently! I used the Select Case statement, as you
sketched above. The only issue now is that the function does not reevaluate
itself when the argument cells change, until I click on the function in the
editing bar and press enter. Any hints?

--zim