Cell Formula to run VBA code

  • Thread starter Thread starter zSplash
  • Start date Start date
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
 
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())
 
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.).
 
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
 
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.
 
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
 
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,""))))
 
Back
Top