Hi Every One

There are two user defined VBA functions in my Excel sheet. The logic is
such that it requires the functions to be executed in a sequence. For
example. Function A should run and then Function B should be executed.
However, Excel fires both functions simultanously, causing problems.

One possible solution that comes to my mind is a time delay of a fraction of
second for Function B. In this way, Function A can be executed before
Function B. Can any one help me how to achieve this?

Or Alternatively, if you have got some other option, please share it with me.

Many Thanks

Mallick

If you have two dependent functions (and it sounds like you have) then
consider combining these to avoid any unwanted interactions. Introducing
delays does not sound like a very good solution to me.



--

Regards,
Nigel




"Mallick" wrote in message
...
Hi Every One

There are two user defined VBA functions in my Excel sheet. The logic is
such that it requires the functions to be executed in a sequence. For
example. Function A should run and then Function B should be executed.
However, Excel fires both functions simultanously, causing problems.

One possible solution that comes to my mind is a time delay of a fraction
of
second for Function B. In this way, Function A can be executed before
Function B. Can any one help me how to achieve this?

Or Alternatively, if you have got some other option, please share it with
me.

Many Thanks

Mallick

Thanks Nigel for your quick reply.

The relationship between the two function is such that the output of
function B helps Function A to reach a decision.

These cannot be combined because of the limitation of Functions. It passes
one value to its calling cell only. Hence, a Function can not do any other
operation or pass value to any other cell in the worksheet. Its infact hell
of a restriction. Can't use subroutine because of interface problems with
other software.

Mallick

"Nigel" wrote:

If you have two dependent functions (and it sounds like you have) then
consider combining these to avoid any unwanted interactions. Introducing
delays does not sound like a very good solution to me.



--

Regards,
Nigel




"Mallick" wrote in message
...
Hi Every One

There are two user defined VBA functions in my Excel sheet. The logic is
such that it requires the functions to be executed in a sequence. For
example. Function A should run and then Function B should be executed.
However, Excel fires both functions simultanously, causing problems.

One possible solution that comes to my mind is a time delay of a fraction
of
second for Function B. In this way, Function A can be executed before
Function B. Can any one help me how to achieve this?

Or Alternatively, if you have got some other option, please share it with
me.

Many Thanks

Mallick

Yes, I understand the limitations of functions. But from what you say
function B must run before A, therefore function C combining B then A would
give the desired result? Unless you are breaking the rules by letting
function A read access many other B function results on the sheet.

To delay a function you could add a simple counter loop, but again I think
to rely on this to delay A is not a good option.



--

Regards,
Nigel




"Mallick" wrote in message
...
Thanks Nigel for your quick reply.

The relationship between the two function is such that the output of
function B helps Function A to reach a decision.

These cannot be combined because of the limitation of Functions. It passes
one value to its calling cell only. Hence, a Function can not do any other
operation or pass value to any other cell in the worksheet. Its infact
hell
of a restriction. Can't use subroutine because of interface problems with
other software.

Mallick

"Nigel" wrote:

If you have two dependent functions (and it sounds like you have) then
consider combining these to avoid any unwanted interactions. Introducing
delays does not sound like a very good solution to me.



--

Regards,
Nigel




"Mallick" wrote in message
...
Hi Every One

There are two user defined VBA functions in my Excel sheet. The logic
is
such that it requires the functions to be executed in a sequence. For
example. Function A should run and then Function B should be executed.
However, Excel fires both functions simultanously, causing problems.

One possible solution that comes to my mind is a time delay of a
fraction
of
second for Function B. In this way, Function A can be executed before
Function B. Can any one help me how to achieve this?

Or Alternatively, if you have got some other option, please share it
with
me.

Many Thanks

Mallick

If function B refers to function A's cell, it will update after function A
updates that cell.

--
__________________________________
HTH

Bob

"Mallick" wrote in message
...
Thanks Nigel for your quick reply.

The relationship between the two function is such that the output of
function B helps Function A to reach a decision.

These cannot be combined because of the limitation of Functions. It passes
one value to its calling cell only. Hence, a Function can not do any other
operation or pass value to any other cell in the worksheet. Its infact
hell
of a restriction. Can't use subroutine because of interface problems with
other software.

Mallick

"Nigel" wrote:

If you have two dependent functions (and it sounds like you have) then
consider combining these to avoid any unwanted interactions. Introducing
delays does not sound like a very good solution to me.



--

Regards,
Nigel




"Mallick" wrote in message
...
Hi Every One

There are two user defined VBA functions in my Excel sheet. The logic
is
such that it requires the functions to be executed in a sequence. For
example. Function A should run and then Function B should be executed.
However, Excel fires both functions simultanously, causing problems.

One possible solution that comes to my mind is a time delay of a
fraction
of
second for Function B. In this way, Function A can be executed before
Function B. Can any one help me how to achieve this?

Or Alternatively, if you have got some other option, please share it
with
me.

Many Thanks

Mallick

why not make function B dependent on the results of Function A?

Cell A1 contains =FuncA( ...)
Cell B10 contains =FuncB(A1, ...)

Or if you want to have a function that returns 2 values

Function FuncC( ...) as variant
dim Ansa(2,1) as variant

Ansa(1,1)=FuncA(...)
..
..
Ansa(2,1)=Funcb(...)
FuncC=Ansa
end Function

and array enter (Control-Shift-Enter) FuncC into 2 cells

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Mallick" wrote in message
...
Thanks Nigel for your quick reply.

The relationship between the two function is such that the output of
function B helps Function A to reach a decision.

These cannot be combined because of the limitation of Functions. It passes
one value to its calling cell only. Hence, a Function can not do any other
operation or pass value to any other cell in the worksheet. Its infact
hell
of a restriction. Can't use subroutine because of interface problems with
other software.

Mallick

"Nigel" wrote:

If you have two dependent functions (and it sounds like you have) then
consider combining these to avoid any unwanted interactions. Introducing
delays does not sound like a very good solution to me.



--

Regards,
Nigel




"Mallick" wrote in message
...
Hi Every One

There are two user defined VBA functions in my Excel sheet. The logic
is
such that it requires the functions to be executed in a sequence. For
example. Function A should run and then Function B should be executed.
However, Excel fires both functions simultanously, causing problems.

One possible solution that comes to my mind is a time delay of a
fraction
of
second for Function B. In this way, Function A can be executed before
Function B. Can any one help me how to achieve this?

Or Alternatively, if you have got some other option, please share it
with
me.

Many Thanks

Mallick

Thanks Bob for your post.

Tried that but it gave circular reference error as Function A already refers
to Function B.

"Bob Phillips" wrote:

If function B refers to function A's cell, it will update after function A
updates that cell.

--
__________________________________
HTH

Bob

"Mallick" wrote in message
...
Thanks Nigel for your quick reply.

The relationship between the two function is such that the output of
function B helps Function A to reach a decision.

These cannot be combined because of the limitation of Functions. It passes
one value to its calling cell only. Hence, a Function can not do any other
operation or pass value to any other cell in the worksheet. Its infact
hell
of a restriction. Can't use subroutine because of interface problems with
other software.

Mallick

"Nigel" wrote:

If you have two dependent functions (and it sounds like you have) then
consider combining these to avoid any unwanted interactions. Introducing
delays does not sound like a very good solution to me.



--

Regards,
Nigel




"Mallick" wrote in message
...
Hi Every One

There are two user defined VBA functions in my Excel sheet. The logic
is
such that it requires the functions to be executed in a sequence. For
example. Function A should run and then Function B should be executed.
However, Excel fires both functions simultanously, causing problems.

One possible solution that comes to my mind is a time delay of a
fraction
of
second for Function B. In this way, Function A can be executed before
Function B. Can any one help me how to achieve this?

Or Alternatively, if you have got some other option, please share it
with
me.

Many Thanks

Mallick

Thanks Charles for your post.

The first option gives a circular reference error as Function A already
refers to Function B.

Your second option is new to me as previously thought that 1 Function can
only return one value. Can you please explain that a bit more? specially the
subscripts in Ansa. Many Thanks

"Charles Williams" wrote:

why not make function B dependent on the results of Function A?

Cell A1 contains =FuncA( ...)
Cell B10 contains =FuncB(A1, ...)

Or if you want to have a function that returns 2 values

Function FuncC( ...) as variant
dim Ansa(2,1) as variant

Ansa(1,1)=FuncA(...)
..
..
Ansa(2,1)=Funcb(...)
FuncC=Ansa
end Function

and array enter (Control-Shift-Enter) FuncC into 2 cells

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Mallick" wrote in message
...
Thanks Nigel for your quick reply.

The relationship between the two function is such that the output of
function B helps Function A to reach a decision.

These cannot be combined because of the limitation of Functions. It passes
one value to its calling cell only. Hence, a Function can not do any other
operation or pass value to any other cell in the worksheet. Its infact
hell
of a restriction. Can't use subroutine because of interface problems with
other software.

Mallick

"Nigel" wrote:

If you have two dependent functions (and it sounds like you have) then
consider combining these to avoid any unwanted interactions. Introducing
delays does not sound like a very good solution to me.



--

Regards,
Nigel




"Mallick" wrote in message
...
Hi Every One

There are two user defined VBA functions in my Excel sheet. The logic
is
such that it requires the functions to be executed in a sequence. For
example. Function A should run and then Function B should be executed.
However, Excel fires both functions simultanously, causing problems.

One possible solution that comes to my mind is a time delay of a
fraction
of
second for Function B. In this way, Function A can be executed before
Function B. Can any one help me how to achieve this?

Or Alternatively, if you have got some other option, please share it
with
me.

Many Thanks

Mallick

Assuming you want to return 2 values to range C3:C4 9 (2 rows, 1 column)
then Ansa needs to be dimensioned as 2 rows and 1 column:

Dim Ansa(2,1) as variant

Then Ansa(1,1) should contain the value you want to return to C3, and
Ansa(2,1) should contain the value you want to return to C4, and to enter
the function into the worksheet you need to select cells C3 and C4, type the
formula =FuncC(...) and then press Control-Shift-Enter all at the same time.
The formula should then show up as an array formula with curly brackets:
{=FuncC(...)}

Of course you can extend this approach so that the function returns a number
of columns and a number of rows.

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Mallick" wrote in message
...
Thanks Charles for your post.

The first option gives a circular reference error as Function A already
refers to Function B.

Your second option is new to me as previously thought that 1 Function can
only return one value. Can you please explain that a bit more? specially
the
subscripts in Ansa. Many Thanks

"Charles Williams" wrote:

why not make function B dependent on the results of Function A?

Cell A1 contains =FuncA( ...)
Cell B10 contains =FuncB(A1, ...)

Or if you want to have a function that returns 2 values

Function FuncC( ...) as variant
dim Ansa(2,1) as variant

Ansa(1,1)=FuncA(...)
..
..
Ansa(2,1)=Funcb(...)
FuncC=Ansa
end Function

and array enter (Control-Shift-Enter) FuncC into 2 cells

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Mallick" wrote in message
...
Thanks Nigel for your quick reply.

The relationship between the two function is such that the output of
function B helps Function A to reach a decision.

These cannot be combined because of the limitation of Functions. It
passes
one value to its calling cell only. Hence, a Function can not do any
other
operation or pass value to any other cell in the worksheet. Its infact
hell
of a restriction. Can't use subroutine because of interface problems
with
other software.

Mallick

"Nigel" wrote:

If you have two dependent functions (and it sounds like you have) then
consider combining these to avoid any unwanted interactions.
Introducing
delays does not sound like a very good solution to me.



--

Regards,
Nigel




"Mallick" wrote in message
...
Hi Every One

There are two user defined VBA functions in my Excel sheet. The
logic
is
such that it requires the functions to be executed in a sequence.
For
example. Function A should run and then Function B should be
executed.
However, Excel fires both functions simultanously, causing problems.

One possible solution that comes to my mind is a time delay of a
fraction
of
second for Function B. In this way, Function A can be executed
before
Function B. Can any one help me how to achieve this?

Or Alternatively, if you have got some other option, please share it
with
me.

Many Thanks

Mallick

Also I forgot to say put
Option Base 1
at the top of your module before any Subs


"Charles Williams" wrote in message
...
Assuming you want to return 2 values to range C3:C4 9 (2 rows, 1 column)
then Ansa needs to be dimensioned as 2 rows and 1 column:

Dim Ansa(2,1) as variant

Then Ansa(1,1) should contain the value you want to return to C3, and
Ansa(2,1) should contain the value you want to return to C4, and to enter
the function into the worksheet you need to select cells C3 and C4, type
the formula =FuncC(...) and then press Control-Shift-Enter all at the same
time. The formula should then show up as an array formula with curly
brackets: {=FuncC(...)}

Of course you can extend this approach so that the function returns a
number of columns and a number of rows.

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Mallick" wrote in message
...
Thanks Charles for your post.

The first option gives a circular reference error as Function A already
refers to Function B.

Your second option is new to me as previously thought that 1 Function can
only return one value. Can you please explain that a bit more? specially
the
subscripts in Ansa. Many Thanks

"Charles Williams" wrote:

why not make function B dependent on the results of Function A?

Cell A1 contains =FuncA( ...)
Cell B10 contains =FuncB(A1, ...)

Or if you want to have a function that returns 2 values

Function FuncC( ...) as variant
dim Ansa(2,1) as variant

Ansa(1,1)=FuncA(...)
..
..
Ansa(2,1)=Funcb(...)
FuncC=Ansa
end Function

and array enter (Control-Shift-Enter) FuncC into 2 cells

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Mallick" wrote in message
...
Thanks Nigel for your quick reply.

The relationship between the two function is such that the output of
function B helps Function A to reach a decision.

These cannot be combined because of the limitation of Functions. It
passes
one value to its calling cell only. Hence, a Function can not do any
other
operation or pass value to any other cell in the worksheet. Its infact
hell
of a restriction. Can't use subroutine because of interface problems
with
other software.

Mallick

"Nigel" wrote:

If you have two dependent functions (and it sounds like you have)
then
consider combining these to avoid any unwanted interactions.
Introducing
delays does not sound like a very good solution to me.



--

Regards,
Nigel




"Mallick" wrote in message
...
Hi Every One

There are two user defined VBA functions in my Excel sheet. The
logic
is
such that it requires the functions to be executed in a sequence.
For
example. Function A should run and then Function B should be
executed.
However, Excel fires both functions simultanously, causing
problems.

One possible solution that comes to my mind is a time delay of a
fraction
of
second for Function B. In this way, Function A can be executed
before
Function B. Can any one help me how to achieve this?

Or Alternatively, if you have got some other option, please share
it
with
me.

Many Thanks

Mallick

Thanks Charles.

Your approach allows me to return more than one value from the function,
greatly increasing the programming options. I hope I woould be able to solve
my problem now.

Thanks again.

Mallick



"Charles Williams" wrote:

Assuming you want to return 2 values to range C3:C4 9 (2 rows, 1 column)
then Ansa needs to be dimensioned as 2 rows and 1 column:

Dim Ansa(2,1) as variant

Then Ansa(1,1) should contain the value you want to return to C3, and
Ansa(2,1) should contain the value you want to return to C4, and to enter
the function into the worksheet you need to select cells C3 and C4, type the
formula =FuncC(...) and then press Control-Shift-Enter all at the same time.
The formula should then show up as an array formula with curly brackets:
{=FuncC(...)}

Of course you can extend this approach so that the function returns a number
of columns and a number of rows.

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Mallick" wrote in message
...
Thanks Charles for your post.

The first option gives a circular reference error as Function A already
refers to Function B.

Your second option is new to me as previously thought that 1 Function can
only return one value. Can you please explain that a bit more? specially
the
subscripts in Ansa. Many Thanks

"Charles Williams" wrote:

why not make function B dependent on the results of Function A?

Cell A1 contains =FuncA( ...)
Cell B10 contains =FuncB(A1, ...)

Or if you want to have a function that returns 2 values

Function FuncC( ...) as variant
dim Ansa(2,1) as variant

Ansa(1,1)=FuncA(...)
..
..
Ansa(2,1)=Funcb(...)
FuncC=Ansa
end Function

and array enter (Control-Shift-Enter) FuncC into 2 cells

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Mallick" wrote in message
...
Thanks Nigel for your quick reply.

The relationship between the two function is such that the output of
function B helps Function A to reach a decision.

These cannot be combined because of the limitation of Functions. It
passes
one value to its calling cell only. Hence, a Function can not do any
other
operation or pass value to any other cell in the worksheet. Its infact
hell
of a restriction. Can't use subroutine because of interface problems
with
other software.

Mallick

"Nigel" wrote:

If you have two dependent functions (and it sounds like you have) then
consider combining these to avoid any unwanted interactions.
Introducing
delays does not sound like a very good solution to me.



--

Regards,
Nigel




"Mallick" wrote in message
...
Hi Every One

There are two user defined VBA functions in my Excel sheet. The
logic
is
such that it requires the functions to be executed in a sequence.
For
example. Function A should run and then Function B should be
executed.
However, Excel fires both functions simultanously, causing problems.

One possible solution that comes to my mind is a time delay of a
fraction
of
second for Function B. In this way, Function A can be executed
before
Function B. Can any one help me how to achieve this?

Or Alternatively, if you have got some other option, please share it
with
me.

Many Thanks

Mallick