I have discovered a strange problem with my VBA code. After testing it on several machines, I've found no problems with my workbook until now. After being run on a rather slow Windows Vista laptop, I've found that VBA appears to be running faster than Excel, and consequently it is creating errors as VBA asks Excel to do things before it has finished a previous action. This is causing macros to fail several times in each run, though can be solved simply by pressing 'debug' and then F5 to continue the code. Is there a way to get VBA to run slower to solve this? As I can't think of any other way of doing it? I've certainly not seen it happen on any XP or Win 7 computer!
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Is there a way to slow VBA down??
- Thread starter 450nick
- Start date
So you can't post the code?
If there are 2000 lines of code that already sets the alarm bells ringing.
Yup, I'm basically using Excel like Access and have turned it into a database. The additional code though comes from automatic reporting in Word, stats, print functions and another output to chuck out certain data into another spreadsheet within its self etc. Taken a fair while to get right, and i've spend a fair while testing it on various computers, but this morning someone borrowed it and found it to have all sorts of errors.
I traced it to this issue of his computer running increadibly slow (you can see the window breaking up even when stepping through the code one point at a time.) but with the number of things which cause it to fail on his computer, I suspect that there's not much I can do about it. He has said he is wiping said laptop at the weekend and will be installing Windows 7 so perhaps this will speed things up enough to allow it all to run correctly.
I've been told that a similar problem can occur on another Excel based program run at work (though that is a HUGE program) used to generate cost/weight estimates for entire oil & gas projects and has many more thousand lines of code than my pokey little workbook!
Thanks for the advice though, I'll try and find the areas on which it is stumbling most often and will put some hesitating commands in. Trouble is he's gone now and I can't find anything slow enough to even begin to replicate the problem!
Upvote
0
Excel Facts
Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
I have some code snippets to disable some visual feedback or auto calculating during VBA process. Maybe you have applied already.
HTH
Code:
' Place before the main codes. Store some current status and then disable them.
screenUpdateState = Application.ScreenUpdating
statusBarState = Application.DisplayStatusBar
calcState = Application.Calculation
eventsState = Application.EnableEvents
displayPageBreakState = ActiveSheet.DisplayPageBreaks 'this is for sheet only.
'turn off some features
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False 'this is for sheet only.
'place your code here
'......
'you code end
'restore the status before exit your function/sub
Application.ScreenUpdating = screenUpdateState
Application.DisplayStatusBar = statusBarState
Application.Calculation = calcState
Application.EnableEvents = eventsState
ActiveSheet.DisplayPageBreaks = displayPageBreaksState 'this is for sheet onlyHTH
Upvote
0
Its 2007 (latest version).
Ok well evidence is as follows:
- Macros are falling over which have been tried and tested on many different machines running xp, vista and win 7 and found to not cause any problems in normal circumstances.
- Macros are falling over generally at the point at which Excel has to do something to the data, eg change the style, copy/paste lots of values at once, insert/remove rows etc.
- The subject machine did feel very slow to use, and as I said, displays some graphical glitches when using the VBA editor just to step through simple macros (due to high cpu load/limited memory or whatever it might be).
- The errors are not repeatable, rather they display a pattern of similar circumstances (i.e. the same bits of code are not the problem), it seems to relate to how busy the computer was at the time (slight improvement seen when closing non-required programs to free up memory/cpu
- A copy was taken of the offending workbook and run on another fairly modest spec xp machine - this ran without fault, even when the amount of data in the workbook was manually trippled.
- Some speed measures are already in place to try to streamline the workbook (e.g use of option explicit, screen & event updating disabled during macros, only 3 modules present, shortened code where possible.
- After asking collegues about the problem this was recognised and confirmed as similar fault to another system. In the case of the other program, the only method of solution discovered was to introduce delay commands as has been mentioned in this thread. I could do this in my workbook, but it would be a lot of work to ensure compatibility with one machine! Hopefully when he's come back with a clean win 7 installation next week I can see if the problem is cured or whether it needs further work.
- If I step through the code one bit at a time, no faults are seen and the macro completes no problem.
I'm fairly sure what the cause is as a result; it must be the speed at which the functions are being performed surely?!
Ok well evidence is as follows:
- Macros are falling over which have been tried and tested on many different machines running xp, vista and win 7 and found to not cause any problems in normal circumstances.
- Macros are falling over generally at the point at which Excel has to do something to the data, eg change the style, copy/paste lots of values at once, insert/remove rows etc.
- The subject machine did feel very slow to use, and as I said, displays some graphical glitches when using the VBA editor just to step through simple macros (due to high cpu load/limited memory or whatever it might be).
- The errors are not repeatable, rather they display a pattern of similar circumstances (i.e. the same bits of code are not the problem), it seems to relate to how busy the computer was at the time (slight improvement seen when closing non-required programs to free up memory/cpu
- A copy was taken of the offending workbook and run on another fairly modest spec xp machine - this ran without fault, even when the amount of data in the workbook was manually trippled.
- Some speed measures are already in place to try to streamline the workbook (e.g use of option explicit, screen & event updating disabled during macros, only 3 modules present, shortened code where possible.
- After asking collegues about the problem this was recognised and confirmed as similar fault to another system. In the case of the other program, the only method of solution discovered was to introduce delay commands as has been mentioned in this thread. I could do this in my workbook, but it would be a lot of work to ensure compatibility with one machine! Hopefully when he's come back with a clean win 7 installation next week I can see if the problem is cured or whether it needs further work.
- If I step through the code one bit at a time, no faults are seen and the macro completes no problem.
I'm fairly sure what the cause is as a result; it must be the speed at which the functions are being performed surely?!
Upvote
0
RoryA
MrExcel MVP, Moderator
- Joined
- May 2, 2008
- Messages
- 43,100
- Office Version
- 365
- Platform
- Windows
- MacOS
It's in the Excel options. It allows Excel to use multiple core processors to speed things up. Using multiple threads of course could mean that they run at different times, in theory (I think!). If it's on, you might try switching it off.
Upvote
0
Richard Schollar
MrExcel MVP
- Joined
- Apr 19, 2005
- Messages
- 23,707
Office Button>Excel Options>Formulas section is where the option resides. It means Excel will use both processor cores (if you have a dual core system) during certain calculations.
Upvote
0
Hmm good idea, that sounds plausible... When he comes in on Monday i'll ask him to try this and see what we get... I'm running a dual core machine here and thats works fine, though it is quite quick and his isn't. I'll give it a go anyway. Thanks guys.
Upvote
0
Similar threads
- Question
