VBA help

[andy2001]

I just started playing around with VBA. I can get info from excel cells in to VBA and I can send info back to excel cells, but the problem is when I have VBA running it hogs the cpu and locks out betting assistant from updating excel.

I need to get VBA to pause for a split second . I’ve tried
Application.OnTime Now + TimeValue("00:00:01"), "aaa"
And this waits without using the cpu for whatever time is listed but then it gets an error “the macro c:\book1.xls.aaa canot be found”, despite it being the next sub in the same file.

What’s the best way to wait with out using CPU in excel?

[Mitch]

Everything I've written uses the Worksheet_Calculate event handler to trigger the main macro. That way every time BA writes to Excel the macro will be triggered. It also means that the macro runs each time the info changes regardless of how long it's been since the last time.

2 things to note if you choose this route......

1. Before your main macro runs you want to include Application.EnableEvents = False to prevent further changes to the sheet triggering the macro again, and then Application.EnableEvents = True when your code has run so that it can be triggered again.

2. When BA writes to Excel it doesn't do it all in one go. Instead it writes all the runner info first, then bet info about each runner line by line. If there are 8 runners in a horse race your macro would get triggered 9 times which would hog the processor. To get round this, the first line in my worksheet_calculate macro is always If target.Columns.Count <> 16 Then Exit Sub. That way the macro will only fire when the runner info is written to the sheet.

I hope that makes sense