Gord,

Your and Mikes answers were sure the only straightforward answer to the
most probable content of OPs question.

My last remark was only a hint, evoked by the form of MsgBox, in case the OP
was still hesitating how to tackle the task.

I must admit, with my basic sentence I could confuse anybody. It depends how
you define the term of macro. If it is a procedure Sub() with empty brackets,
which is appearing in the Macro bar, then you certainly can, by means of it,
set any variable on the module level, without the claim to trigger further
changes dependent on this variable.

Dim sText As String
Sub TextYes()
sText = "Yes"
End Sub

Sub TextNo()
sText = "No"
End Sub

Why not to use it now in a function:
Function SayYesOrNo(YesNo As Boolean) As String
If YesNo Then TextYes Else TextNo
SayYesOrNo = sText €˜or anything
End Function

Why to build and run such mutilated macros separately? For example for
intermediate changing the flag (shortkey!), previously set in running the
Function, before running another macro
Sub DoSomething()
€¦ If sText = "No" Then €¦€¦..

Of course, we talk still about the changing of some variables, no other
activity, as I was stressing in my text.

Playing with module level variables may be sometimes useful (there may be
even such cases) but fulminately dangerous, owing to the loss of control with
a bad fixing.

I apologize for the discussion that was evidently out of the topic. Take it
as a slip of brain.

Petr
--
Petr Bezucha


"Gord Dibben" wrote:

This is interesting.

Could you post something that would take care of OP's original question,
which was to call a macro from a formula in C4 that evaluates a yes/no in C3


Gord

On Wed, 14 Jan 2009 02:26:05 -0800, PBezucha
wrote:

Just to prevent misapprehension:

A VBA FUNCTION can call a SUB. The function can be triggered by a change of
an argument, for example just your deciding yes/no (as Boolean variable). The
SUB can take values even from elsewhere, not only from the inner of the
function, but €“ there is the hell of restriction €“ it cannot pass any values
other than over this function output.

Remark: You are allowed to place a MsgBox into a function body, where you
can execute your deferred deciding step.

Regards