Set Up a Pause Between Calls

[YellowLabPro]

I have a large amount of data that I am processing. I believe that one of the issues I am seeing is directly related to the processor not being able to keep up.
Is there a method to pause for a split second between calls? And, w/out user intervention, continue on its own?

Here is a string of calls I perform. If possible, I would like to place the pause here in the main code to better enable me to track than to place it inside the individual modules.


Call removeDupes
Call Dec2FracII
Call CorrectDateSizing
Call Dates2Sizes
Call AttribSize
Call ImportAttribs
Call JoinAttribs

thanks

[rory]

If it really is a timing issue (bearingin ind the linear nature of VBA), you can use Application.Wait

[YellowLabPro]

Rory,
I just found that method, thanks.
What is your thought when you say it is really a timing issue? Is this explicit to the answer/method or are you eluding to the string of calls and this perhaps is causing the problem?

[TonyJollans]

I don't think you'll have a timing issue in VBA itself - it'll be some asynchronous process kicked off by one of your called routines and my first guess would be automatic calculation. If you don't need it, switch it off; if you do need it you might find a DoEvents call helps.

[rory]

What Tony said.

[YellowLabPro]

Ok- can you guys explain a little bit more regarding these items you brought up. I seemed to have the initial issue resolved, but interested in some of the other core items you broached....

Tony can you shed some light on this item: some asynchronous process What is meant by asynchronous pertaining to what I am doing?
One thing is that I don't have a clear definition to asynchronous and particularly to here.

Tony- auto calculation- are you referring to the native function in Excel of Auto calculation, turning this off?

I can't post my code right yet, Excel has locked up again. I am going to reboot and then post it.

[TonyJollans]

Asynchronous --- you might explicitly start a process or, more likely, implicitly start one, which runs outside of VBA and therefore does not wait for VBA code and VBA code does not wait for it but it could cause a problem. In Excel (and remember I'm a Word MVP ) the most likely such process is, yes, (native) Excel automatic calculation and, if you don't need the results of it, turning it off could help.

[YellowLabPro]

Good morning Tony,
Great explanation, thank you. In fact, I do have auto-calc turned off on these workbooks, they are so large now that that is the only way to manage them.
I have no imperical evidence, but in watching this for a long period yesterday, I am of the belief it is directly related to the memory not being able to keep up.
Here is the code I have inserted, I still have to test extensively to ensure its success.

[VBA]
Call removeDupes
Call Dec2FracII
Call CorrectDateSizing
Call Dates2Sizes

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

Call AttribPriceSize

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

Call ImportAttribs

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

Call JoinAttribs

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

Call ClearAttribs
'Call CloseTGSProductsAttribPrep
ActiveSheet.[D2].Activate
Call SaveTextFile 'Previous method used to upload entire database to ecboardco.com site
Call Update_ecboardco
Call UpdatedSavedTextFile
[/VBA]

[unmarkedhelicopter]

Looking at your code you have written too much :-[vba]newHour = Hour(Now())
newMinute = Minute(Now())
newSec + 2
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime[/vba]should be something like :-[vba]Application.Wait Now() + TimeSerial(0, 0, 2)[/vba]If your procedure works with the 2 second delays then it is highly unlikely it is a memory issue as by definition the previous line ends before the next begins, so it's not still working on the previous procedure whilst starting the next. I'm intregued to know what you were watching to reach this conclusion (probably the task manager performance tab). Someone once said "Just because I see something, doesn't mean that what's actually happening is what I see." I don't know who that was but ...

I think Tony and Rory were barking up the right tree. If it's not auto calculation it may be some other similar event though I would rule out hardware as disk and memory access delays are a fact of nature on PC's and generally aplications don't have built-in timers to check for problems (some do but generally it's left to the OS) if you can't find it and you've stopped every extraneous activity you can then I'd just live with the timers.

[TonyJollans]

What do your called routines do?

For that matter, what are the symptoms that you are seeing?

[YellowLabPro]

Heli and Tony-
Thanks for staying w/ me on this. I will try and answer both of you collectively. If I fail to miss a pertinent point, feel free to readdress it.

Here is what is occurring.
I am processing 16,000 rows of data.
There are four significant items taking place.

Call Dates2Sizes
This sub converts strings that are formatted as dates by Excel, (which I hate, we have no control to turn this off), to text.

Call AttribPriceSize
This is a Select Case routine that extracts the Prices and Sizes of the item and places it into a defined range, i.e. if a snowboard is 152cm, then it sets up a range of 152cm-155cm. It creates a group basically.

Call ImportAttribs
This opens another worksheet and grabs data and places it in the activesheet.

Call JoinAttribs
This takes all the attributes we just built and joins them and copies them into the appropriate column.

Where the problems arise are:
1)Inconsistency in results in this collection of procedures. W/out any data or code being altered, rerunning the code over and over I receive different results.
2)One common thread is if one of the items in the list is missing a piece of data, like the item is missing a department or category listing this item shows up w/out the JoinAttrib value. There is no reason for this, the department/Cat listing has no bearing on the Attrib value or result. What I believe is happening is as the procedure is running when it hits a blank cell the memory is failing to keep up. Getting confused.
3)If I step through this line by line then I do not experience the results of missing data. It returns the expected values.
4)I am also experiencing very bad things in other programs. Maybe memory leak, maybe a power supply that is not powerful enough. I replaced my graphics card several months ago. I was warned it might cause problems due to an under rated power supply. Unfortunately I purchased a more powerful one, only to find out that Dell does not use ATX compatible parts. It does not fit. I am stuck.
5)All of this had been running properly until recently. Things that have been cemented for months are now failing.

This is my reasoning that it is hardware related.

Heli,
Until I put the wait method in there it was failing. I took this directly from a help file, so I will look at yours for better code. Before placing the wait method, this is where I was getting all the bad stuff happening.

[Paul_Hossler]

The easest way to 'pause' is to call the Sleep API. AFAIK this pauses the running process. BTW, have you tried putting DoEvents in your code to see if that helps? If it does, then you might not need to pause

[VBA]
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

'pass the amount of time (in milliseconds)
Sub TakeNap(timelength As Long)
Sleep (timelength)
End Sub

[/VBA]

[YellowLabPro]

Hi Paul,
I saw something very similar to what you write about today, yesterday when I was searching the web. But since I do not have any experience w/ API, I shyed away from that approach. The answer I found yesterday was not directed to Excel/VBA directly so I could not figure out how to re-write it for my needs. I will definitely try this.

No to the DoEvents. Again, I am not familiar w/ this. Would you provide me what I need to try this? Do you need more of my code to give you what you need to help me?
"Help me Help you!" LOL!!!

[TonyJollans]

Dell does not use ATX compatible parts

Just to go OT for a moment. I got caught out by this and, for that single reason, will not touch Dell again.

[TonyJollans]

I have noticed what appear to be similar symptoms in some Word processes and have my suspicions, which I can't prove.

Do you have a multi-core processor on your machine?

[TonyJollans]

DoEvents -- pauses your running code to process anything waiting on the Windows stack to run. It is useful if you are waiting for external devices or similar but doesn't sound like it would help with what you are doing - certainly worth a try though if you can find the right place to put it.