how to slow down this flow

jjk2 · January 24th, 2008, 04:35 AM

a formula linked to DDE fetches some data from a server.

in a split second the following happens in the cell

#N/A turns into 0

the problem with this is, each request requires that the previous cell turned into 0. In my case, all requests are sent out @ once, causing problems.

I've tried putting in sleep(500) but what happens is the cells will freeze #N/A for 0.5 sec. Quite contrary to what I expected which the 0.5 sec should be sufficient for #N/A to turn into 0.

I tried doing

submit_request1

if Range("A15").Value = "0"
submit_request2
end if

but as you can see, this is completely ignored since #N/A is displayed for the first request and a split second after turns into 0.

im kinda out of ideas, please help!

Thank you for reading.

Shasur · January 24th, 2008, 05:22 AM

If you want to hold the calculation you can try the following

Code:

Sub Hold_Calc()

' stop auto calculation
Application.Calculation = xlCalculationManual

' code that populates excel

' your code here

' start auto calculation
Application.Calculation = xlCalculationAutomatic

End Sub

Cheers
Shasur

http://www.dotnetdud.blogspot.com

VBA Tips & Tricks (http://www.vbadud.blogspot.com)

jjk2 · January 25th, 2008, 02:27 AM

no, that does not work....

BrianWren · January 25th, 2008, 04:36 PM

Your explanation is very hard to follow. You appear to be in a hurry.

Take a little time, think of what that which yo type will cause to form in the mind of the reader, and fill in the details.

For instance, you said

Quote:

quote:I tried doing

Code:

submit_request1

if Range("A15").Value = "0"
submit_request2
end if

But where do you put that? (See, itâs clear to youâyouâre looking at it on your monitor. But you have to lead us to an understanding.)

jjk2 · January 25th, 2008, 09:21 PM

yea i admit i was in a hurry that day.

i'll be more clear.

through DDE, a formula that is inserted into Range("A15") will turn from #N/A to 0, in split second.

Because of this delay, the if condition is completely ignored as the codes are being executed.

I sought to delay the if condition from executing by placing a sleep() function. However, this just froze the formula in the #N/A state, still causing the if statement to not work.

so what i am resorting to is, placing MsgBox in between each requests so taht I have to manually click, which buys time for the formulas to be calculated into "0" state from #N/A

BrianWren · January 28th, 2008, 02:14 PM

[T]hrough DDE, a formula that is inserted into Range("A15") will turn from #N/A to 0 [I presume you mean âwill turn that [u]cell</u> from #N/A to 0â], in split second.

Because of this ["this delay?" What "delay?" You imply that what takes place takes place in an instant] delay, the [I]f condition [you are referring to an âIfâ condition that you have not identified] is completely ignored as the codes [âcode isâ (stuff), not âcodes areâ (items)] are being executed.

I sought to delay the if condition from executing by placing a sleep() function. [Sought to delay the âIfâ from executing, until what happens?] However, this just froze the formula in the #N/A state, still causing the if statement to not work.

[S]o what [I] am resorting to is placing [a] MsgBox in between each request so that I have to manually click, which buys time for the formulas to be calculated into "0" state from #N/A.

jjk2 · January 28th, 2008, 02:34 PM

so is there no solution? anyway to give time for excel to calculate something.

something like

when cell_turns_into_0 then

request_stuff

end

jjk2 · January 28th, 2008, 02:35 PM

please dont be so condescending. english is not my first language. its my fifth. :)

BrianWren · January 28th, 2008, 07:12 PM

I am not being condescending. I am targeting for you where it is that what you have to say fails to get the point across.

I don't understand what you are asking (and English is my first language), and so I cannot help until you clarify. Do not shirk using language as an excuse. Every language has syntax. Every language has concepts like the recipient of the action (subjective), the object/person taking the action (objective), the action being taken (verb), and when it happened (past/present/future tense).

Reread your posts, and you will see that your sentences mismatch the action and the recipient of that action, they are incomplete, and so on.

I'm trying to help you; quit being so sensitive, and try to give me the minimum required to help you.

You say, "so ther's no solution?" But I have not said anything like that. I have not hinted at such a thing. You are jumping to that conclusion; jumping to conclusions is going to make this harderâplease try to avoid doing that.