Cell Formula to run VBA code

Z

zSplash

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
 
F

Frank Kabel

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())
 
H

Harlan Grove

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)?
Click to expand...

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.).
 
D

David Byrne

Frank Kabel said:
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())
Click to expand...

Or perhaps even simpler

=FOO(D5,...)

David
 
H

Harlan Grove

David Byrne said:
Or perhaps even simpler

=FOO(D5,...)
Click to expand...

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.
 
Z

zSplash

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,IF(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
 
H

Harlan Grove

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,""))
Click to expand...
...

...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,""))))
 
Top