macro's plots veel trager uitgevoerd ...

[peterpn]

Ben bezig een programmaatje aan 't schrijven in Excel met macro's. Na nog wat gesleuteld te hebben aan de formules en macro's blijkt plots dat de macro's veel trager worden uitgevoerd.
Het berekenen van de formules in het excel-blad gebeurd niet automatisch maar doe ik met Calculate.
Zelfs onderstaande code waar in een regel 15 cellen worden gevuld, wordt nu wel 5 tot 10 keer trager uitgevoerd dan voorheen.

Het programma doet er een kleine seconde over om de regel te vullen.

ActiveCell.Offset(r - 1, 0).Value = Range("b27").Value
ActiveCell.Offset(r - 1, 1).Value = Range("c26").Value
ActiveCell.Offset(r - 1, 2).Value = Range("bm27").Value
ActiveCell.Offset(r - 1, 3).Value = Range("f26").Value
ActiveCell.Offset(r - 1, 4).Value = Range("j27").Value
ActiveCell.Offset(r - 1, 5).Value = Range("t26").Value
ActiveCell.Offset(r - 1, 6).Value = Range("n26").Value
ActiveCell.Offset(r - 1, 7).Value = Range("v26").Value
ActiveCell.Offset(r - 1, 8).Value = Range("af27").Value * 100
ActiveCell.Offset(r - 1, 9).Value = Range("ah27").Value
ActiveCell.Offset(r - 1, 10).Value = Range("ak27").Value
ActiveCell.Offset(r - 1, 11).Value = Range("an26").Value
ActiveCell.Offset(r - 1, 12).Value = Range("ay26").Value
ActiveCell.Offset(r - 1, 13).Value = Range("bi27").Value * 100
ActiveCell.Offset(r - 1, 14).Value = Range("bk27").Value

Het ligt niet aan mijn pc want in vorige versies (zonder de wijzigingen) worden de macro's en dezelfde code wél vlot uitgevoerd.

Iemand een idee wat de oorzaak zou kunnen zijn van dit snelheidsverlies ?

 

[ExcelExpert]

Heb je Application.Screenupdating = false?

Of:
Excel compileerd alle vba code onderwater, en ook al wis je je originele vb code, deze gecompileerde code blijft bestaan, daardoor wordt je bestand ook een stuk groter.
Misschien ligt de vertraging ook hier aan. Je zou kunnen proberen om alle code als tekst te kopieren (en werkbladen) naar een nieuw excel bestand.

 

[peterpn]

Bedankt voor de snelle reactie maar ik ben spijtig genoeg nog niet verder.

als ik de code 300x in een lus uitvoer en ik copieer de tijd in 2 verschillende cellen voor en na de lus merk ik het volgende tijdverschil:

in de vorige versie : 19 sec.
in de laatste versie : 2' 26"

Application.Screenupdating = false of true maakt nagenoeg geen verschil uit

de wijzigingen tussen de 2 versies zijn eerder miniem, ook de bestandsgroottes van de opgeslagen werkbladen zijn ongeveer dezelfde zodat ik betwijfel dat ook de 2e mogelijkheid de oorzaak zou kunnen zijn.

 

[ExcelExpert]

Hoe ziet de code die je in de lus hebt staan er in de beide versies uit?

 

[peterpn]

snelle versie :

For i = x To y
Calculate
r = r + 1

z = ActiveCell.Offset(-3, 67).Value
If z > 5488 Then Let z = 5488
ActiveCell.Offset(-3, 67).Value = z + ActiveCell.Offset(-3, 68).Value

ActiveCell.Offset(r - 1, 15).Value = ActiveCell.Offset(-3, 62).Value
ActiveCell.Offset(-20, 3).Value = r

If Range("v21") = "OnWaar" Then
ActiveCell.Offset(r - 1, 0).Value = "x"
GoTo 20
End If

10
ActiveCell.Offset(r - 1, 0).Value = Range("b25").Value
ActiveCell.Offset(r - 1, 1).Value = Range("c24").Value
ActiveCell.Offset(r - 1, 2).Value = Range("bm25").Value
ActiveCell.Offset(r - 1, 3).Value = Range("f24").Value
ActiveCell.Offset(r - 1, 4).Value = Range("j25").Value
ActiveCell.Offset(r - 1, 5).Value = Range("t24").Value
ActiveCell.Offset(r - 1, 6).Value = Range("n24").Value
ActiveCell.Offset(r - 1, 7).Value = Range("v24").Value
ActiveCell.Offset(r - 1, 8).Value = Range("af25").Value * 100
ActiveCell.Offset(r - 1, 9).Value = Range("ah25").Value
ActiveCell.Offset(r - 1, 10).Value = Range("ak25").Value
ActiveCell.Offset(r - 1, 11).Value = Range("an24").Value
ActiveCell.Offset(r - 1, 12).Value = Range("ay24").Value
ActiveCell.Offset(r - 1, 13).Value = Range("bi25").Value * 100
ActiveCell.Offset(r - 1, 14).Value = Range("bk25").Value
20 Next i



trage versie :


For r = x To ey

u = Range("a28").Value
If Range("a32").Value = 0 Then Range("a28").Value = u + 1
Calculate


z = ActiveCell.Offset(-3, 67).Value
ActiveCell.Offset(-3, 67).Value = z + ActiveCell.Offset(-3, 68).Value




ActiveCell.Offset(r - 1, 15).Value = ActiveCell.Offset(-3, 62).Value
ActiveCell.Offset(-22, 3).Value = r

If l = 0 Then
ActiveCell.Offset(r - 1, 0).Value = "x"
GoTo 20
End If


ActiveCell.Offset(r - 1, 0).Value = Range("b27").Value
ActiveCell.Offset(r - 1, 1).Value = Range("c26").Value
ActiveCell.Offset(r - 1, 2).Value = Range("bm27").Value
ActiveCell.Offset(r - 1, 3).Value = Range("f26").Value
ActiveCell.Offset(r - 1, 4).Value = Range("j27").Value
ActiveCell.Offset(r - 1, 5).Value = Range("t26").Value
ActiveCell.Offset(r - 1, 6).Value = Range("n26").Value
ActiveCell.Offset(r - 1, 7).Value = Range("v26").Value
ActiveCell.Offset(r - 1, 8).Value = Range("af27").Value * 100
ActiveCell.Offset(r - 1, 9).Value = Range("ah27").Value
ActiveCell.Offset(r - 1, 10).Value = Range("ak27").Value
ActiveCell.Offset(r - 1, 11).Value = Range("an26").Value
ActiveCell.Offset(r - 1, 12).Value = Range("ay26").Value
ActiveCell.Offset(r - 1, 13).Value = Range("bi27").Value * 100
ActiveCell.Offset(r - 1, 14).Value = Range("bk27").Value
20 Next r

 

[ExcelExpert]

Er zit inderdaad niet zo heel veel verschil tussen de twee. Ik heb niet uitgebreid gekeken, maar het valt me op dat in het trage deel de de loop tot ey gaat, is ey niet toevallig heel groot?

Daarnaast zou ik Calculate buiten de loop plaatsen, als dat verder geen problemen levert.

Misschien is het zo dat je i wel gedimensioneerd hebt en r niet, vba werkt sneller met een Integer dan met een Variant, maar dat zou niet teveel moeten uitmaken.

Als dit niet werkt zou je misschien het bestand kunnen uploaden / mailen, dan kan ik er goed naar kijken en zien of de loops bij mij ook verschillende doorloop snelheden hebben.

 

[peterpn]

ey en y zijn in beide gevallen 300; x = 1
calculate kan niet uit de loop want het zijn telkens andere waarden die worden geschreven op de volgende lijn ,die bij iedere loop opnieuw berekend worden (met randomgetallen)
noch i noch r zijn gedefinieerd

Is er misschien ergens een Errorchecking die in- of uitgeschakeld is bij de uitvoering ? De opties bij Extra-Opties-Algemeen voor foutonderschepping en compileren hab ik reeds allen geprobeerd zonder verschil.
Heeft het met gewijzigde formules te maken maar waarom vertraagd dan de uitvoering als enkel maar bij 'calculate' wordt berekend? Ook de code van het inleidende bericht wordt zichtbaar trager uitgevoerd, je ziet als het ware het programma de cellen 1 voor 1 invullen.

Zodra ik wat meer tijd heb en ik er niet uitgeraak zal ik de werkmap doorsturen.