Is there a way to slow VBA down??

[450nick]

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!

 

[450nick]

anyone?

 

[thorpyuk]

Hiya,

You could use Application.Wait (Now() + TimeValue("0:00:1"))

This will pause VBA for a second, but i'm unsure if it'll also pause Excel or not!

OR

You could add a pointless loop:

Do
For N = 1 to 300000
Next N
Loop until N = 300000

 

[450nick]

Thought about this, but it seems to fall over in lots of places, so I'd have to put in an awful lot of these, and then it would slow it down for people running it on proper computers as well for no reason! This case is very much the minority! I really need a way of altering the code speed, much like you do with picaxe chips. That would be really handy, though I don't think it can be done with Excel...

 

[thorpyuk]

Do you mean it's falling over because of calculation? EG, excel isn't calculating quick enough before the VBA runs?

Is it possible to turn off calculation?

Application.Calculation = xlCalculationManual

 

[450nick]

Well one example of a bit which falls over, is when it tries to (with a large selection) set all text to Arial font. This works fine normally, but on the slow computer, the VBA tells Excel to do the font, and then carries on. By the time it comes back to tell Excel to do something else, Excel is still busy doing the font, so tells VBA to F**K off (I would too!), VBA then doesn't like this and chucks up an error. While the error box pops up, Excel finishes doing the font, and so if you press F5 in the debugger, it just carries on from where it stopped and the macro continues running as normal. So really I just need VBA to hold up a little bit on every single step, as going through it to put in hang commands would take forever! (And slow down the program un-necessarily on faster machines).

 

[Norie]

Any chance you could post the offending code?

I think it's practically impossible to give help without seeing it, especially since I've lost my crystal ball.

 

[450nick]

Not really... I have around 2000 lines of code and the problems are pretty much throughout them on the slow computer. It sounds like there isn't any method of slowing down VBA so I'll just have to tell the guy to get a new computer!

 

[Norie]

So you can't post the code?

If there are 2000 lines of code that already sets the alarm bells ringing.

 

[drsarao]

This thread rings a bell. I had a similar problem:

Sub UpDateMF()
Dim sht As Variant
Dim dt(1 To 1, 1 To 3) 'holds date, invested=null and nav
With Sheets("MFM")
    mfr = 2
    mlr = 10
    dt(1, 1) = .Range("E1") - 1 'date
    dt(1, 2) = ""           'null for "Invest" column
    sht = .Range("A" & mfr & ":A" & mlr)  'get sheet names
    nav = .Range("E" & mfr & ":E" & mlr)  'get navs
    For r = 1 To mlr - mfr + 1
        If sht(r, 1) <> "" And nav(r, 1) <> "" Then
            With Sheets(sht(r, 1))
                .Activate    'not strictly required but otherwise throws error sometimes
                nr = .Range("O2") + 1  'new row number
                If nr > 34 Then ActiveWindow.ScrollRow = nr - 20
                If .Cells(nr, 5).Formula = "" Then       'fill formulas if reqd
                    .Range(.Cells(nr - 1, 5), .Cells(nr - 1, 11)).AutoFill .Range(.Cells(nr - 1, 5), .Cells(nr + 5, 11))
                End If
                dt(1, 3) = nav(r, 1)   'nav
                .Range(.Cells(nr, 1), .Cells(nr, 3)) = dt
                .Cells(nr + 1, 1).Select
            End With
        End If
    Next r
End With
End Sub

There were about 15 sheets but the For loop got stuck after 1 or 2 throwing some error.
Once I put an .activate for each sheet, the macro ran smoothly. That is also a kind of delay.

So Nick can try using .activate or .select to slow down the code just enough to avoid errors.