delaying output of a function

[GM]

Hello,

maybe someone can help me with this. I'm trying to delay the output of
a function in an Excel Worksheet. For now I tried to use the
Application.Wait method within a VBA UDF. VBA isn't terribly nice here
and just ignores my 10 second suspension. Is there any other way I
could achieve my goal?

My VBA is that right now:

Function myUDF() As String
Application.Wait (Now + TimeValue("0:00:10"))
myUDF = "suspended output"
End Function


Thanks for your answers.

Michael Gruner

 

[Harald Staff]

Hi Michael

A worksheet function is not allowed to do anything except return a value to
itself. Not at all ever anywhere tamper with the Excel environment. Not a
VBA problem, rather a design choice for good reasons.

So you will need a macro for this.

What's it for? Excel too fast? <bg>

Best wishes Harald

 

[JoeU2004]

I would use:

Public Declare Sub Sleep Lib "kernel32" (ByVal msec As Long)

Note: Although the resolution of the Sleep parameter is milliseconds,
generally expect an error of up to about 15.6 msec. Here is an example that
demonstrates the minimum sleep time. Enter =minsleep() into many cells and
average the result.


Public Declare Sub Sleep Lib "kernel32" (ByVal msec As Long)
Public Declare Function QueryPerformanceFrequency Lib "kernel32" (ByRef freq
As Currency) As Boolean
Public Declare Function QueryPerformanceCounter Lib "kernel32" (ByRef cnt As
Currency) As Boolean

Function minsleep() As Double
Dim freq As Currency, sc As Currency, ec As Currency, dc As Currency
Dim x As Long
x = QueryPerformanceFrequency(freq)
Call Sleep(1) ' sync with system "tick"
x = QueryPerformanceCounter(sc)
Call Sleep(1)
x = QueryPerformanceCounter(ec)
dc = ec - sc
minsleep = dc / freq
End Function


----- original message -----

 

[JoeU2004]

Errata....

Although the resolution of the Sleep parameter
is milliseconds, generally expect an error of
up to about 15.6 msec.

"Up to" is wrong. The sleep time could be much longer, depending the
cumulative running time of interrupt routines and any higher priority
processes that are ready. Moreover, the sleep time can be much as about
15.6 msec less than expected, unless you sync your Sleep call with the
system "tick".


----- original message -----

 

[GM]

Hi Harald,

HiMichael

A worksheet function is not allowed to do anything except return a value to
itself.

Of course the function should return a value just with a little
suspension.

What's it for?Exceltoo fast? <bg>

Yeah, you got it. Of course not, but I'd like to give the
impression of some randomness to the user of the sheet. Therefor I'd
variate the time to delay.

Michael

 

[GM]

Hi Joe,

thank you very much for the code. It works very well except one little
thing: If I use the delayed function in more than one cell the time
gets multiplied by the number of cells I put it into. Is there a way
to have Excel process the next function without waiting for the
completion of the first one? Like you know in a multi-threaded
environment where each thread is independent of the others?

cheers,
michael

 

[Dave Peterson]

You could add a dummy do nothing loop.

dim iCtr as long
dim myVal as long
ictr = 0
do
ictr = ictr + 1
if ictr >= 100000 then
exit do
end if
loop

As a user, I wouldn't want this slowdown no matter how you did it. If I'm
working on multiple workbooks, I could get frustrated pretty quickly.

 

[JoeU2004]

Hi Joe,

thank you very much for the code. It works very well except one little
thing: If I use the delayed function in more than one cell the time
gets multiplied by the number of cells I put it into. Is there a way
to have Excel process the next function without waiting for the
completion of the first one? Like you know in a multi-threaded
environment where each thread is independent of the others?

cheers,
michael

 

[JoeU2004]

[Sorry about my previous posting (at 4:17 PM PT). It got away from me. I'm
having some "technical difficulties".]

thank you very much for the code. It works very
well except one little thing: If I use the delayed
function in more than one cell the time gets
multiplied by the number of cells I put it into.

Well, that is what you asked for. I must admit: I was mystified by your
request. But ass-u-me-d you knew what you were doing.

Is there a way to have Excel process the next
function without waiting for the completion of
the first one?

No, not as stated. Also, I would not rely on the order of cell
calculations. You would think that Excel would follow the order of a
directed graph; and perhaps it does, generally. But I encountered some
anomalies when I explored this some time ago. I no longer remember the
details. They might only arise when using volatile functions like RAND().
Nevertheless, I would not trust it.

Like you know in a multi-threaded environment where
each thread is independent of the others?

No. However, you might be able to use event macros to accomplish whatever
it is you want. (I'm still not clear on what that is.) Unfortunately, I am
very familiar with event macros myself; and I am having trouble finding
canonical documentation. (I find some; but I'm not sure the list is
complete.) So I cannot provide a specific example. Perhaps other readers
can.

At a minimum, you might be able to put your delay code in a SheetCalculate
event macro. I believe that is invoked after all calculations are completed
for a worksheet. Caveat: It might also be invoked at other times when you
might not want a delay. But I believe you can use the event macro parameter
to distinguish them.

It might be ideal to subtract the worksheet calculation time from the delay
so that the total time -- calculation plus delay -- is predictable. But I
don't find a "BeforeSheetCalculate" or "BeforeWorkbookCalculate" event.
Perhaps I'm missing something.


----- original message -----

 

[GM]

Well, that is what you asked for.  I must admit:  I was mystified by your
request.  But ass-u-me-d you knew what you were doing.

Sometimes I do, but I'm not really sure at the moment.

No.  However, you might be able to use event macros to accomplish whatever
it is you want.  (I'm still not clear on what that is.)  

Well you're right, I really should describe more precisely what I'd
like to do:
I've got an application that feeds data into an excel sheet. Within
the excel-sheet there are some cells I can put values into and the
application will interprete them and start acting in some way. What
I'd like to do now is to have my sheet appearing like a human. That
means putting the value into the cells immediatly after a change
occurs the application would notice that I'm not human. As that is not
enough the cell-change-and-reaction-delay should not affect the
calculations taking place within the other cells. Hm, I really hope
that's not too strange to understand anyway.

Unfortunately, I am
very familiar with event macros myself; and I am having trouble finding
canonical documentation.   (I find some; but I'm not sure the list is
complete.)  So I cannot provide a specific example.  Perhaps other readers
can.

Are they fired in a multithreaded way? Meaning that one
Worksheet_Change is still running while another get's fired?

michael

 

[JoeU2004]

Unfortunately, I am very familiar with event macros myself

[....]
Are they fired in a multithreaded way? Meaning that
one Worksheet_Change is still running while another
get's fired?

Apparently, Excel 2007 can multithread. I know nothing about that. Google
"Excel multithreaded", without quotes, for more information.

What I was alluding to, the SheetCalculate event, "is invoked after __all__
calculations are completed for a worksheet", as I wrote before. That is, it
is a one-time event for each worksheet. But....

the cell-change-and-reaction-delay should not affect
the calculations taking place within the other cells.
Hm, I really hope that's not too strange to understand
anyway.

No; I believe I understand. If the Excel MTR feature is implemented
correctly, it might give you what you want. (Ignore my SheetCalculate
suggestion.) But I have not been impressed with Microsoft's multitasking
implementation in WinXP. So forgive me if I am a little suspicious of any
claims about MTR.

Spoken out of school. Again, I have no experience with Excel 2007 or MTR.


----- original message -----

Well, that is what you asked for. I must admit: I was mystified by your
request. But ass-u-me-d you knew what you were doing.

Sometimes I do, but I'm not really sure at the moment.

No. However, you might be able to use event macros to accomplish whatever
it is you want. (I'm still not clear on what that is.)

Well you're right, I really should describe more precisely what I'd
like to do:
I've got an application that feeds data into an excel sheet. Within
the excel-sheet there are some cells I can put values into and the
application will interprete them and start acting in some way. What
I'd like to do now is to have my sheet appearing like a human. That
means putting the value into the cells immediatly after a change
occurs the application would notice that I'm not human. As that is not
enough the cell-change-and-reaction-delay should not affect the
calculations taking place within the other cells. Hm, I really hope
that's not too strange to understand anyway.

Unfortunately, I am
very familiar with event macros myself; and I am having trouble finding
canonical documentation. (I find some; but I'm not sure the list is
complete.) So I cannot provide a specific example. Perhaps other readers
can.

Are they fired in a multithreaded way? Meaning that one
Worksheet_Change is still running while another get's fired?

michael