Excel zeer traag bij gebruik macro

[StevenV.K.]

Na het instellen van onderstaande macro is het excel bestand zeer traag geworden.
Iemand enig idee hoe dit komt.
Ik ken zelf weinig van excel. Online heb ik een tutorial gevolgd om deze macro in te stellen. Liefst een niet te technische uitleg graag.

Function SOMZELFDEKLEUR(Gebied As Range, Cel As Range) As Double
Dim Kleur As Integer
Application.Volatile
Kleur = Cel.Interior.ColorIndex
For Each Cel In Gebied.Cells
If Cel.Interior.ColorIndex = Kleur And IsNumeric(Cel.Value) Then
SOMZELFDEKLEUR = SOMZELFDEKLEUR + Cel.Value
End If
Next Cel
End Function

[g0tanks]

Welkom op Tweakers.

We verwachten bij het openen van een topic dat je aangeeft wat je al zelf hebt geprobeerd of gevonden. Zie ook: Het algemeen beleid #quickstart Voor de volgende keer daar graag op letten. Daarnaast heb ik je titel aangepast naar iets dat meer zegt: 'Excel macro' -> 'Excel zeer traag bij gebruik macro'.

In ieder geval denk ik dat het ligt aan dit stukje code:

code:

Application.Volatile

Die zegt dat de macrofunctie opnieuw moet worden uitgevoerd als een cel in de Excel verandert. Het kost natuurlijk veel rekenkracht als dat bij elke kleine verandering gebeurt.

[dix-neuf]

StevenV.K. schreef op dinsdag 6 oktober 2020 @ 08:51:
For Each Cel In Gebied.Cells

Die .cells kun je best weglaten,
'For each Cel in Gebied' is voldoende.

[g0tanks]

dix-neuf schreef op dinsdag 6 oktober 2020 @ 17:37:
[...]
Die .cells kun je best weglaten,
'For each Cel in Gebied' is voldoende.

Heeft dat enige impact op performance? Of zeg je het alleen omdat de code er netter van wordt?

[dix-neuf]

Ik heb dat maar voor een zeer klein bereik getest en daarbij merk ik in snelheid geen verschil. 'Cells' wordt doorgaans voor alle cellen van een werkblad gebruikt (bv. Sheets("Blad1").Cells.....) en dat zijn miljoenen cellen. Daar zat ik aan te denken toen ik mijn reactie schreef, maar het is hier niet van toepassing omdat met 'For Each Cel In Gebied.Cells'' toch ook alleen de cellen in het bereik 'Gebied' worden benaderd. Kortom, mijn reactie was enigszins overbodig (en misschien wilde je dat ook zeggen; wel, dan heb je gelijk). En dat de code er netter van wordt als je dat ene woord weglaat: ach, als ik daarvoor had gereageerd (voor dat ene woord), dat zou wat al te pietluttig zijn mijnerzijds.

[ Voor 3% gewijzigd door dix-neuf op 06-10-2020 23:05 ]

[Lustucru]

g0tanks schreef op dinsdag 6 oktober 2020 @ 10:20:
In ieder geval denk ik dat het ligt aan dit stukje code:

code:

Application.Volatile

Die zegt dat de macrofunctie opnieuw moet worden uitgevoerd als een cel in de Excel verandert. Het kost natuurlijk veel rekenkracht als dat bij elke kleine verandering gebeurt.

Kleine correctie: volatile zorgt ervoor dat de functie wordt doorlopen bij elke berekening.

Strikt genomen is het ook geen macro maar een user defined function. De vraag aan @StevenV.K. is wmb dan ook:
- hoe groot is het bereik waarover hij gekleurde cellen moet optellen? Als dat iets is als "A:C", ja dan zal het wel traag worden. . Maw hoe gebruik je de functie?
- Zitten er veel andere berekeningen in?

Wat gebeurt er als je in opties automatisch berekenen uitzet en hoe lang duurt het dan als je een keer op F9 drukt?

[Lustucru]

Daarnaast, qua netheid etc:

'Gebied.Cells' heeft imho de voorkeur boven alleen 'Gebied'. 'Cells' is idd de default property, zodat de interpreter automatisch op de achtergrond 'Gebied' omzet in 'Gebied.Cells'. Ronduit lelijk is het hergebruik van de variabele 'Cel', die eerst wordt gebruikt als referentie en later wordt hergebruikt in de for-each lus.

Grappig is hoe een stukje code van 14 jaar oud blijft opduiken in allerlei kleine variaties:
Lustucru in "excel cellen op kleur sorteren/optellen"

[dix-neuf]

Lustucru schreef op dinsdag 6 oktober 2020 @ 20:01:
'Gebied.Cells' heeft imho de voorkeur boven alleen 'Gebied'. 'Cells' is idd de default property, zodat de interpreter automatisch op de achtergrond 'Gebied' omzet in 'Gebied.Cells'.

Maar dat zie je toch (bijna) nooit? Als in een macro een bereik een naam wordt gedefinieerd en vervolgens wordt gebruikt, gaat dat meestal zo (voorbeeld):

code:

Dim cl as Range
Set myrange = Range("A1:E100")
for each cl in myrange
....

code:

For each cl in myrange.Cells

ben ik nog nooit tegengekomen.

[Lustucru]

Volledig offtopic uiteraard, maar toch:


Dat je het zelden ziet maakt het geen slechtere code. DE VBA interpreter is gewoon erg vergevingsgezind.

Visual Basic:

Dim a as string, b as long
a = "2"
b = 3
debug.print a+1 '?!!
Debug.Print a * b '??!!

compileert gewoon en geeft als antwoorden 3 en 6. Als je daar aan gewend raakt wordt de overstap naar een striktere taal erg wennen en het helpt ook niet echt bij een beter begrip van wat je aan het doen bent...

code:

Dim cl as Range
Set myrange = Range("A1:E100")
for each cl in myrange
....

Is op de keeper beschouwd onzin. Een range is een object en is niet enumereerbaar. Maar de interpreter snapt (dankzij default members) dat je met range("A1:E100") Activesheet.Range("A1:E100") bedoelt en daarvan graag alle items wilt hebben om te enumereren. Het werkt, het is korter te schrijven, maar mooi? Nah. Hetzelfde zie je met het rare:

code:

Dim s as string
s=range("A1")

Huh: een rangeobject wordt aan een string toegewezen? Dat kan natuurlijk niet. De interpreter pakt ook hier het defaultproperty .value, zet die gratis voor je om naar tekst (ook al staat er een datumwaarde of een double in) en kent die toe aan de string. Soms gaat het wel mis en dan is het lastig troubleshooten of je krijgt performanceproblemen. Neem bv het scenario waarin je een rangeobject van columns gebruikt:

Visual Basic:

   Dim c As Excel.Range
   Dim columnRange As Excel.Range
   Set columnsRange = ThisWorkBook.Worksheets("exampleSheet").Range("B2:C3").Columns
   

   For Each c In columnsRange
      Debug.Print c.Address   'Prints "$B$2:$B$3", "$C$2:$C$3"
   Next

   For Each c In columnsRange.Cells
      Debug.Print c.Address          'Prints "$B$2", "$C$2", "$B$3", "$C$3"
   Next  

Je kunt maar beter schrijven wat je wilt.

code:

dim a
set a= range("A1"). 'a bevat het rangeobject A1
a= range("A1") 'a bevat een of andere waarde
a=range("A1").Text 'a bevat een string

Het is net zoiets als het weglaten van option explicit. Zonder dat compileert het net zo goed, met option explicit dwing je jezelf nauwkeurig te werken en te denken, en dat resulteert meestal in betere code. Default properties? Lijkt handig, maar geef mij maar een strikte taal.

[ Voor 20% gewijzigd door Lustucru op 07-10-2020 00:24 ]