Call a Visual Basic Function with VLookup

[ajd]

I wrote a few functions in Visual Basic. I want to determine which function
to run based on a vlookup of a list of the functions in a table. So I have a
table that has:

A FunctionA
B FunctionB

I want to do a vlookup on A to run FunctionA, and also provide the variable
for Function (which does not depend on the specific function to be run). I
can't figure out a way for vlookup to not just return text, but return a
function to run, and then also provide the variables for that function to run
on.

Thanks.

 

[CLR]

You would need a ChangeEvent Macro.........
Here's one I got from Jim Tomlinson awhile back, it may point you in the
right direction.

Private Sub Worksheet_Change(ByVal Target As Range)
'By Jim Tomlinson 3/26/06
If Target.Address = "$A$2" Then
If Target.Value = "april" Then Call April
If Target.Value = "may" Then Call May
If Target.Value = "june" Then Call June
End If
End Sub

Vaya con Dios,
Chuck, CABGx3

 

[ajd]

Is there a way to do it without a macro? I'd like to just have a formula in
the cell that calls the functions, if possible.

 

[Luke M]

Is there a way you could have a cell do the VLOOKUP, and then in VBA write
something like

If Range("A1").value = "A" then
Function A
Else Runction B
End If

As for the variables, I'm not quite sure what you mean. Is the result of the
vlookup the variable? If so, you could just refer to the cell value mentioned
above.

 

[Dana DeLouis]

I want to determine which function

to run based on a vlookup of a list of the functions in a table.

Here's one way that comes to mind if I understand the question correctly:

Sub Demo()
Dim n, m
Dim Ans

n = 2
m = WorksheetFunction.Lookup(n, Array(1, 2, 3), Array("Jan", "Feb",
"Mar"))

Ans = Run(m, 2, 3)
End Sub

Function Feb(x, y)
Feb = x ^ 2 + y ^ 2
End Function

 

[Gord Dibben]

Chuck

A VLOOKUP returned value is not a change event.

It would be a calculated event.

Private Sub Worksheet_Calculate()
With Me.Range("A2")
If .Value = "april" Then Call april
If .Value = "may" Then Call may
If .Value = "june" Then Call june
End With
End Sub


Gord Dibben MS Excel MVP

 

[ajd]

I'll be running my functions on each cell in a vertical list of varying
length. So that's why I didn't want to have a macro, it'd be much easier if
it was just a function or a call to a function. I know it's possible with a
macro, just makes things more complicated since the users may not be aware of
the macro and/or could forget to run it.

To clarify, the variables for the functions are the values in a separate,
unrelated cell say in Column A. The function, which I want to run in Column
C, is determined by a value in say column B (via a table that I'm trying to
access in a vlookup).

Also I would like to have the ability to have say 10 different functions
that I can choose from, depending on the value in Column B, which is why I
wanted the vlookup instead of a very lengthy if clause.

So, here's what my table looks like:

VariableX Name Y Formula that runs proper function with VariableX
VariableA Name Z Formula that runs proper function with VariableA
etc

With another table that assigns the name to a function, with:
Name Z FunctionZ
Name Y FunctionY
etc

 

[T. Valko]

Are your variables arguments to the UDF as cell references? For example:

=FunctionA(do_something,A10,B10)

If so, you could use CHOOSE:

A1 = FunctionA
A2 = FunctionB

=CHOOSE(MATCH("FunctionB",A1:A2,0),FunctionA,FunctionB)

In versions of Excel prior to Excel 2007 you'd be limited to 29 functions.

 

[T. Valko]

Clarification:

Are your variables arguments to the UDF as cell references? For example:
=FunctionA(do_something,A10,B10)
If so, you could use CHOOSE:

You can use CHOOSE *only* if your variables as cell references are already
defined in the UDF code.

 

[ajd]

I think you're on to something. But then I'd have to list out the up to 29
functions in the equation. And if I added a function or removed a function
I'd have to go and change the equation in every cell it is used, as opposed
to just editing the lookup table. Any way around that?

 

[T. Valko]

Any way around that?

Unfortunately, no, not from a formulaic aspect.