Time Delay Between Execution of Two Functions

[Mallick]

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

 

[Nigel]

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.

 

[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]

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
(e-mail address removed)

 

[Bob Phillips]

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

 

[Charles Williams]

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]

Thanks Bob for your post.

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

 

[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]

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

 

[Charles Williams]

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

 

[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