Here’s the scenario – you’re programming using VBA, but interacting with Excel sheet formulae/other aspects of Excel (like Charts or Pivots), and when the scale becomes large, you see errors in your sheet updation – like data is not represented correctly or charts are not updated uniformly etc.

And this is not as a result of bugs in your programming logic or algorithm, but merely a bug in ‘time’!

Time?? Yes, the time involved for Excel to update while VBA doesn’t wait that long enough to process and hence a ‘time sync error’! This is a common error in many areas of science; occurs when two different engines are working together to produce a result – it could be mechanics, electronics or computers.

And the way this ‘has’ to work is – both the engines be sync-ed to a common speed – necessarily the slower one between the two. It’s like this – In the scenario of computer hardware, the Processor is a very fast engine but the Memory is not as fast. If the Processor were to process information even before the Memory could dispatch, what would result is processing of wrong data!

This is the same that happens with Excel & VBA, and when you’re in the VBA mode, you have  to wait for Excel to finish its calculations and screen updations and the like (unless you’ve turned them off manually on VBA).

So, how do we do this? Simple, just ask VBA to WAIT! Yes, it’s that simple!

The way to do this would be to include a

Application.WAIT (Now() + TimeValue(“00:00:01″))

What the above line does is, ask the VBA processor to wait for a second before moving on with the processing! That simple!

But isn’t 1 sec too long a time  to wait?! MAYBE!

In which case, there are some other functions out there that can help you, but not WAIT. WAIT is defined to delay the process only in multiples of seconds – so, the smallest unit you can use is 1 sec! Sometimes, you don’t need to wait that long, you may only need a few milliseconds – then what?!

You’ve got another function called SLEEP then. It’s a Microsoft API routine, and hence slightly different inherently, but yes, you are calling for help from outside of VBA’s territory now! There are pros and cons for using this, but let’s keep this for later! For now, it’s just WAIT and learn…


Advertisement