Call a Visual Basic Function with VLookup
 

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.

Call a Visual Basic Function with VLookup
 

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" wrote:

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.

Call a Visual Basic Function with VLookup
 

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.

"CLR" wrote:

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" wrote:

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.

Call a Visual Basic Function with VLookup
 

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.
--
Best Regards,

Luke M


"ajd" wrote:

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.

Call a Visual Basic Function with VLookup
 

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

--
HTH :)
Dana DeLouis
Windows XP & Excel 2007


"ajd" wrote in message
...
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.

Call a Visual Basic Function with VLookup
 

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

On Mon, 17 Dec 2007 10:52:00 -0800, CLR wrote:

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" wrote:

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.

Call a Visual Basic Function with VLookup
 

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



"Luke M" wrote:

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.
--
Best Regards,

Luke M


"ajd" wrote:

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.

Call a Visual Basic Function with VLookup
 

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,Funct ionB)

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


--
Biff
Microsoft Excel MVP


"ajd" wrote in message
...
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



"Luke M" wrote:

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.
--
Best Regards,

Luke M


"ajd" wrote:

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.

Call a Visual Basic Function with VLookup
 

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.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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,Funct ionB)

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


--
Biff
Microsoft Excel MVP


"ajd" wrote in message
...
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



"Luke M" wrote:

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.
--
Best Regards,

Luke M


"ajd" wrote:

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.

Call a Visual Basic Function with VLookup
 

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" wrote:

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.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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,Funct ionB)

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


--
Biff
Microsoft Excel MVP


"ajd" wrote in message
...
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



"Luke M" wrote:

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.
--
Best Regards,

Luke M


"ajd" wrote:

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.

Call a Visual Basic Function with VLookup
 

Any way around that?

Unfortunately, no, not from a formulaic aspect.


--
Biff
Microsoft Excel MVP


"ajd" wrote in message
...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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,Funct ionB)

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


--
Biff
Microsoft Excel MVP


"ajd" wrote in message
...
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



"Luke M" wrote:

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.
--
Best Regards,

Luke M


"ajd" wrote:

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.