Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Formula to run VBA code
I find it much easier to think in VBA code than in the style necessary to
write formulas for cells. Is there a function that I can "call" from within a cell formula to run a sub? (i.e., such as I do "= IF D5="", "", D5*C5") (can I do "= IF D5="", "", run MySub)? I hope this question is clear. TIA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Formula to run VBA code
Hi
you can create a user defined function e.g. Public Function foo() 'some code foo = some value end function Now you can use =IF(D5="", "", FOO()) -- Regards Frank Kabel Frankfurt, Germany "zSplash" <zNOSPAMSplash@ gci.net schrieb im Newsbeitrag ... I find it much easier to think in VBA code than in the style necessary to write formulas for cells. Is there a function that I can "call" from within a cell formula to run a sub? (i.e., such as I do "= IF D5="", "", D5*C5") (can I do "= IF D5="", "", run MySub)? I hope this question is clear. TIA |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Formula to run VBA code
"zSplash" <zNOSPAMSplash@ gci.net wrote in message
... Is there a function that I can "call" from within a cell formula to run a sub? No, that's impossible. Sorry. -- HTH. Best wishes Harald Followup to newsgroup only please. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Formula to run VBA code
"zSplash" wrote...
I find it much easier to think in VBA code than in the style necessary to write formulas for cells. Is there a function that I can "call" from within a cell formula to run a sub? (i.e., such as I do "= IF D5="", "", D5*C5") (can I do "= IF D5="", "", run MySub)? The good news is you can only use VBA to do this. You'd need to use a Calculate or SheetCalculate event handler to do this. Formulas, even those calling udfs, can't do anything to the Excel environment. However, udfs can alter anything outside Excel (e.g., creating or deleting files from your drives, launching other processes, etc.). -- To top-post is human, to bottom-post and snip is sublime. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Formula to run VBA code
"Frank Kabel" wrote in message ...
Hi you can create a user defined function e.g. Public Function foo() 'some code foo = some value end function Now you can use =IF(D5="", "", FOO()) Or perhaps even simpler =FOO(D5,...) David |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Formula to run VBA code
"David Byrne" wrote...
"Frank Kabel" wrote... .... Now you can use =IF(D5="", "", FOO()) Or perhaps even simpler =FOO(D5,...) Possibly too simple. Wrapping FOO() inside IF() ensures that FOO() would only be run if the IF condition were False. If you make the condition the first argument to FOO(), you require additional logic in FOO() to check its first argument as well as reducing the usable arguments by one. Since the Excel/VBA udf interface is quite slow, it's best to avoid calling udfs unless necessary. Therefore, your proposed usage is inferior to Frank's as a general design approach. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Formula to run VBA code
Thanks, guys, for all the input.
What I need to do is something like this: =IF(B15<"",IF(D15<""+OR(F15<"",IF(F15="H",B15,I F(F15="W","",IF(F15="SPLIT ",B15/2,IF(F15="DIVIDE",B15/2,IF(F15="50/50",B15/2,"")))))),B15*D15,"")) It's just so blasted tricky. I guess I rely much more on the Intellisense in VBA than I thought I did, because I love writing the code to do this in VBA, but when I try to write a cell's formula, I get all confused and almost always get an error. For example, when I want to code: ..IF(InStr(1,F15,"/")=True, B15/F15,"")))))).... Evidentally I can't use "Instr" (or, IsNumeric) functions in cell formulas. Such a pain. Does anyone have any suggestions for how to more easily put formulas in cells? TIA "zSplash" <zNOSPAMSplash@ gci.net wrote in message ... I find it much easier to think in VBA code than in the style necessary to write formulas for cells. Is there a function that I can "call" from within a cell formula to run a sub? (i.e., such as I do "= IF D5="", "", D5*C5") (can I do "= IF D5="", "", run MySub)? I hope this question is clear. TIA |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Formula to run VBA code
"zSplash" wrote...
What I need to do is something like this: =IF(B15<"",IF(D15<""+OR(F15<"",IF(F15="H",B15, IF(F15="W","",IF(F15="SPLIT ",B15/2,IF(F15="DIVIDE",B15/2,IF(F15="50/50",B15/2,"")))))),B15*D15,"")) ... ...IF(D15<""+OR(F15<"",IF(F15="H",... is almost certainly not what you want, though it's syntactically valid. It looks like you want the result to be "" if B15 = "", D15 = "" and F15 = "" or none of "H", "SPLIT", "DIVIDE" or "50/50". If so, try =IF(B15="","",IF(OR(F15={"SPLIT","DIVIDE","50/50"}),B15/2,IF(F15="H",B15, IF(AND(D15<"",F15=""),B15*D15,"")))) -- To top-post is human, to bottom-post and snip is sublime. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing Options | Excel Discussion (Misc queries) | |||
Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing | Excel Discussion (Misc queries) | |||
How to code the formula? | Excel Discussion (Misc queries) | |||
How to convert cell formula functions to code functions | Excel Discussion (Misc queries) |