run a macro every second

[earp_]

Hi if I want to run a macro every second do I need to do something like that?

Sub Autpen()
Application.OnTime Now, "myMacro"
End Sub

Sub myMacro
...
mdNextTime1 = Now + TimeValue("00:00:01")
Application.OnTime mdNextTime1, "myMacro"
End Sub

but this creates a kind of blinking in excel because it copies things from a Sheet to another one.
Is this the best way or do you know something else?

 

[xld]

Don't do any selecting, and turn screenupdating off.

 

[Joe4]

If you put the code:

Application.ScreenUpdating=False

at the beginning of your code and then put

Application.ScreenUpdating=True

at the end of it, it should suppress the blinking.

You piqued my curiosity though. Why do you want to run a macro every second, especially upon opening it? How/when are you stopping it?

You are also going to limit your ability to do anything else in that file while the macro is running. You could have a lot of interference (like I think you are inferring from your other post).

 

[earp_]

To be honest I don't know.
I have on Sheet1 some datas which are dynamically updated.
I run my macro so every second I copy some values in Sheet1 into Sheet2
So every second I copy a value into a column of Sheet2
After 5 minutes I make some calculation for the current Column and I continue to copy the value into the next column in Sheet2, so after other 5 minutes I do the same things, like to calculate the max and the min of the current column...and so on.

I don't know if I need to run my macro every second with

mdNextTime1 = Now + TimeValue("00:00:01")
Application.OnTime mdNextTime1, "myMacro"

or if, once I started my macro, it continue to do things until i stop it without the above code.

You are also going to limit your ability to do anything else in that file while the macro is running. You could have a lot of interference (like I think you are inferring from your other post).

Any suggest?

 

[Joe4]

I have on Sheet1 some datas which are dynamically updated.

How are they dynamically updated?
Is there other VBA code running or is it linked to something that is constantly changing?

 

[earp_]

It's linked to something that changes.

 

[Joe4]

I was hoping that there was some event in Excel that was triggering the data changes so you would be able to use an Event Procedure instead of running a macro every second. I am not sure, but you may be able to do something like that with the Worksheet_Calculate event (I have written a lot of Event Procedures, but never using Worksheet_Calculate).

In any event, your original question about the flickering screen should be resolved by the first two responses to this thread.

 

[earp_]

I received the data through a DDE.
Do you know how to avoid the copy and paste problem?
I can't copy and paste anything with windows but the value in excel

 

[Joe4]

Since you asked that question in another thread, you should keep it over there. But I am not sure what what you mean exactly (you can only copy the value in Excel)?

I would recommend posting a clarification in your other thread that explains what your macro is doing, what you are trying to do, what your expected result is, and what is actually happening.

The more detailed explanation you give, the better the chances you will receive a helpful response.

 

[earp_]

If you put the code:

Application.ScreenUpdating=False

at the beginning of your code and then put

Application.ScreenUpdating=True

at the end of it, it should suppress the blinking.

You piqued my curiosity though. Why do you want to run a macro every second, especially upon opening it? How/when are you stopping it?

You are also going to limit your ability to do anything else in that file while the macro is running. You could have a lot of interference (like I think you are inferring from your other post).

if I'm using this i don't see anymore values in the column of sheet2 and how can I make some operations afterwards?