Much faster VBA Macro for Excel

Question

I've written a short VBA Macro to run several macros on different sheets. But it takes about 1 1/2 minute because it calls the macros in a row. It would be great if it could call them at once.:

Public Sub Gesamt_Rechner()
     Application.ScreenUpdating = False
     Application.Calculation = xlCalculationManual
     Application.EnableEvents = False

 On Error Resume Next

Sheets("Ra1").Select
Call Berechnen1
Sheets("Ra2").Select
Call Berechnen1
Sheets("Ra3").Select
Call Berechnen1
Sheets("Ra4").Select
Call Berechnen1
Sheets("Ra5").Select
Call Berechnen1
Sheets("Ra6").Select

...... and so on.

Is there a way to call them much faster? Or every at once :)

Berechnen1.:

Sub Berechnen1()

  Range("P26").ClearContents
  Range("I38").GoalSeek Goal:=32, ChangingCell:=Range("P26")

If Range("P26") < 0 Then
Range("P26") = Application.RoundUp(Range("P26"), 0)
Else
Range("P26") = Application.RoundDown(Range("P26"), 0)
End If

'Hier wird das Delta für den Reiter "E1" OHNE RUNDEN berechnet'
  Range("P91").ClearContents
  Range("I103").GoalSeek Goal:=32, ChangingCell:=Range("P91")

End Sub

1) [How to avoid using Select/Active statements](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) 2) [Why I should use On Error Resume Next judiciously](http://stackoverflow.com/questions/21673529/if-not-function-proceeds-when-value-is-the-one-specified/21673617#21673617) — Dmitry Pavliv, May 06 '14 at 11:08
I use On Error Resume Next because the Sheets doesn't exist sometimes. — user3414272, May 06 '14 at 11:12
maybe this would be a good question on code review but here its off-topic as is too broad to answer and possibly primarily opinion based — , May 06 '14 at 11:13
Unless you check `Err` after every statement, `On Error Resume Next` means "ignore all errors" which is not a good idea. — Tony Dallimore, May 06 '14 at 11:22
Have you done any timings to discover which statements are taking an excessive amount of time? `Select` is slow but I doubt it is that slow. How many worksheets might not exist? In my experience, the VBA error handler is slow; is that where the time is going. Which worksheet is being processed by Berechnen1 when the `Select` fails? Is that where the time is going? — Tony Dallimore, May 06 '14 at 12:11

S Meaden · Accepted Answer · 2014-05-06T16:11:44.057

1

I'm inferring an awful lot from hidden code base so I might way off. Initially, I thought perhaps your code could well be tightened in terms of syntax so use

Sub Gesamt_Rechner()

    Dim l As Long
    For l = 1 To 6
        Sheets("Ra" + Chr$(48+l)).Select
        Call Berechnen1
    Next l

End Sub

instead of repeating lines. Having said that, even after tightening your code I don't actually think that the problem is in the Excel VBA. I should imagine that the time is taken in your goal-seeking and I am inferring that you have some financial model going on here, "Delta" is a mathematical sensitivity term frequently used in Investment Banks. Your next step is to tighten the worksheet formulae. After that if still slow you'll have to write the equivalent model in C++.

edited May 06 '14 at 16:11

answered May 06 '14 at 12:09

S Meaden

8,050
3
34
65

I think you mean `chr$(48+l)`. However, I do not believe this loop would make any difference. I believe your point about goal-seeking is much more relevant. – Tony Dallimore May 06 '14 at 12:23
Yes, `chr$(48+l)`, quite right. Thanks for catching. – S Meaden May 06 '14 at 16:10

score 0 · Answer 2 · answered May 06 '14 at 11:43

0

Sorry, I dont't think that's possible, VBA executes everything line by line. If your execution is slow, just try to improve the code itself by increasing its efficiency if it is possible.

answered May 06 '14 at 11:43

handris

1,999
8
27
41

Specifically, vba is single threaded. – RubberDuck May 06 '14 at 19:27

Much faster VBA Macro for Excel

2 Answers2