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

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

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.

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.

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

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.

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

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.