Excel Marco versnellen (het selecteren van sliceritems)

[Verwijderd]

Hallo,

Ik ben al een poosje aan het puzzelen hoe ik het onderstaande kan versnellen.
Het doel is om bepaalde project nummers te selecteren die tot een bepaalde categorie behoren.
De geselecteerde project nummers bepalen uiteindelijk de waardes die in een Pivottable komen te staan.

De code die ik momenteel gebruik werkt wel, maar is erg traag.
Heeft iemand een goede tip hoe ik dit kan herschrijven?

Sub DevAll()
' DevAll Macro

ActiveWorkbook.SlicerCaches("Slicer_Project_Number").ClearManualFilter

Dim oSlicerItem As SlicerItem
Dim vItems As Variant
Dim vMatchVal As Variant

Application.ScreenUpdating = False

' Project Number Selection
vItems = Array("951", "1031", "1091", "10123", "1211")

With ActiveWorkbook.SlicerCaches("Slicer_Project_Number")
.ClearManualFilter
For Each oSlicerItem In .SlicerItems
vMatchVal = Application.Match(oSlicerItem.Name, vItems, 0)
If IsError(vMatchVal) Then
oSlicerItem.Selected = False
End If
Next oSlicerItem
End With

' Enables screen refreshing.
Application.ScreenUpdating = True

End Sub

[Atanamir]

In eerste instantie zie ik niet iets heel geks in de code.
Ik kan me wel voorstellen dat als de dataset heel groot is het .ClearManualFilter lang duurt omdat de achterliggende pivottable dan weer ververst wordt. Staan er wellicht nog veel of complexe formules in het werkboek? Als die allemaal opnieuw berekend worden kan dat ook voor de vertraging zorgen.

[Verwijderd]

Bedankt voor je reactie.

De dataset is inderdaad erg groot (en wordt alleen maar groter).
Weet je toevallig of er een snellere code is dat in de plaats van .ClearManualFilter of dat op een andere manier hetzelfde bereikt kan worden?

Op deze manier worden andere gegevens die uiteindelijk in de pivot-table komt berekend (deze codes worden meerdere keren gebruikt (voor verschillende stukken data)).
Wellicht zie je hier zaken in die de traagheid veroorzaken.
Echter is de enorme vertraging ontstaan bij het toevoegen van de eerdere genoemde Macro.


'FORECAST AMOUNT LABOR
Sheets("Forecast").Select
Range("N2:N" & Range("A" & Rows.Count).End(xlUp).Row).Copy
Sheets("Consolidated").Select
Range("H" & Range("A" & Rows.Count).End(xlUp).Row + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

'FORECAST DATE
Sheets("Forecast").Select
Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Copy
Sheets("Consolidated").Select
Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


Range("A1").Select
Sheets("Consolidated analysis").Select
Range("D1").Select
End Sub

[Atanamir]

Nee niets geks in de stukken hierboven.
Wel is het steeds selecteren van een werkblad niet nodig. Je kunt gewoon direct kopiëren en plakken zonder steeds naar de betreffende sheets te gaan. Bijvoorbeeld:

with Sheets("Forecast")
.Range("N2:N" & .Range("A" & Rows.Count).End(xlUp).Row).Copy
.Range("H" & .Range("A" & Rows.Count).End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
end with

Je kan nog wel eens proberen dit bovenaan je code te zetten:
application.calculation = xlManual

En dit onderaan:
application.calculation = xlAutomatic

Als de vertraging komt doordat bij iedere verversing van de pivot table allemaal formules herberekend moeten worden dan zal dit het oplossen.

[Verwijderd]

Bedankt voor je suggesties.
Helaas maken beide oplossingen de macro niet sneller.

Zou het kunnen dat het aan het onderstaande stukje ligt?

For Each oSlicerItem In .SlicerItems
vMatchVal = Application.Match(oSlicerItem.Name, vItems, 0)

[Lustucru]

Meten is weten. Prop in je code de volgende code:

Visual Basic:

t = Timer
' doe je ding hier
Debug.Print "Ding gedaan in " & Timer - t & " s"
t = Timer
'doe een ander ding hier
Debug.Print "Ander ding gedaan in " & Timer - t & " s"

En je weet welk stukje code zo veel tijd nodig heeft.

[ Voor 19% gewijzigd door Lustucru op 11-05-2017 21:20 ]

[Verwijderd]

Bedankt voor de tip!

Het zit inderdaad in het laatste stuk.
Het is een loop die steeds opnieuw gaat.
Het onderstaande duurt in totaal 28 seconde.

For Each oSlicerItem In .SlicerItems
vMatchVal = Application.Match(oSlicerItem.Name, vItems, 0)
If IsError(vMatchVal) Then
oSlicerItem.Selected = False
End If
Next oSlicerItem
End With

[Reptile209]

Misschien helpt dit nog om je code sneller te maken:

Set Application.Calculation = xlCalculationManual at the beginning and Application.Calculation = xlCalculationAutomatic at the end of your code

En anders geeft deze pagina nog wat handige pointers om te checken, met name voor je Match: MSDN: Excel 2010 Performance: Tips for Optimizing Performance Obstructions

Beiden gevonden met [google=excel application.match slow].

Je zou nog even binnen je loop kunnen kijken waar de tijd nu precies in zit: de Match of het False maken van je item. Ik gok het eerste, maar meten is weten.

Edit: en ze je stukjes code voortaan tussen

[code=vba] en [/code]

tags, dat leest wat makkelijker

Nog een nuttige aanvulling met tips: http://stackoverflow.com/...licer-selection-using-vba bij een slice moetje blijkbaar nog iets op manual zetten. Zie ook [google=excel vba slicercaches slow].

[ Voor 20% gewijzigd door Reptile209 op 16-05-2017 23:41 ]