Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visual Basic | Excel Discussion (Misc queries) | |||
Visual Basic Function works fine in Excell 2003 but not in Excel 2 | Excel Discussion (Misc queries) | |||
Vlookup Error in Visual Basic | Excel Discussion (Misc queries) | |||
Microsoft Visual Basic: Compile error: Sum or Function not defined | Excel Worksheet Functions | |||
changing the visual basic in office 2003 to visual studio net | Excel Discussion (Misc queries) |