| | Post: 39 | Registrato il: 09/09/2017
| Città: SASSARI | Età: 64 | Utente Junior | 2007 | | OFFLINE | |
|
29/11/2019 16:55 | |
Buonasera,
abitualmente eseguo la macro di aggiornamento sotto riportata con Office 2007 (pc vecchio con processore dual core). Oggi ho provato ad eseguirla con Office 365 in un pc decisamente più moderno (i5). Deve aggiornare circa 25.000 righe. In Office 365 gira con una lentezza esasperante (circa 5 min con Office 2007, circa 40 min con Office 365). La lentezza si manifesta in particolare quando esegue i cicli. In Office 2007 avevo risolto e velocizzato con questa istruzione:
Application.Calculation = xlCalculationManual
C'è modo di velocizzare l'esecuzione anche in Office 365?
Sub AggiornamentoDati()
Dim wb As Workbook
Dim wks As Worksheet
Dim wbagg As Workbook
Dim wksagg As Worksheet
Dim RwRng As Integer
Dim RwRng1 As Integer
Dim x As Integer, y As Integer, z As Integer, w As Integer
Dim a As Integer, b As Integer, c As Integer
Workbooks.Open Filename:="C:\StatistichePS\aggiornamento.xlsx"
Set wb = Workbooks("2019_Statistiche.xlsm")
Set wks = wb.Worksheets("ORIGINE")
Set wbagg = Workbooks("aggiornamento.xlsx")
Set wksagg = wbagg.Worksheets("Foglio1")
Application.Calculation = xlCalculationManual
wks.Activate
RwRng = wks.Cells(Rows.Count, 5).End(xlUp).Row
wks.Range("E2:BP" & RwRng).ClearContents
wksagg.Activate
RwRng1 = wksagg.Cells(Rows.Count, 5).End(xlUp).Row
wksagg.Range("C2:BN" & RwRng1).Copy
wks.Activate
wks.Range("E2:BP" & RwRng1).PasteSpecial xlPasteValues
wks.Range("A3:D25000").ClearContents
wks.Range("A2").Copy
For x = 3 To RwRng1
Cells(x, 1).PasteSpecial xlPasteFormulas
Next x
wks.Range("B2").Copy
For y = 3 To RwRng1
Cells(y, 2).PasteSpecial xlPasteFormulas
Next y
wks.Range("C2").Copy
For z = 3 To RwRng1
Cells(z, 3).PasteSpecial xlPasteFormulas
Next z
wks.Range("D2").Copy
For w = 3 To RwRng1
Cells(w, 4).PasteSpecial xlPasteFormulas
Next w
Sheets("DURATA").Activate
Range("N3:P" & RwRng1).ClearContents
Range("N2").Copy
For a = 2 To RwRng1
Cells(a, 14).PasteSpecial xlPasteFormulas
Next a
Range("O2").Copy
For b = 2 To RwRng1
Cells(b, 15).PasteSpecial xlPasteFormulas
Next b
Range("P2").Copy
For c = 2 To RwRng1
Cells(c, 16).PasteSpecial xlPasteFormulas
Next c
Range("H4").Select
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = False
Workbooks("aggiornamento.xlsx").Close
Application.DisplayAlerts = True
Application.CutCopyMode = False
Application.CutCopyMode = True
MsgBox ("Aggiornamento completato."), vbInformation
End Sub
|
|
| | Post: 3.561 | Registrato il: 03/04/2013
| Utente Master | Excel 2000 - 2013 | | OFFLINE | |
|
29/11/2019 17:37 | |
Buona sera, @eltorpe;
si potrebbe velocizare con l'Istruzione:
Sub AggiornamentoDati()
Application.ScreenUpdating = False
...
...
...
Application.ScreenUpdating = True
End Sub
probabilmente non risolve ma aiuta.
Potrebbero esserci altri modi per velocizzare l'esecuzione ma, senza un File di riferimento, non sono in grado di garantire un apprezzabile miglioramento del tempo di esecuzione.
A disposizione.
Buona serata.
Giuseppe
Windows XP - Excel 2000
Windows 10 - Excel 2013 |
| | Post: 39 | Registrato il: 09/09/2017
| Città: SASSARI | Età: 64 | Utente Junior | 2007 | | OFFLINE | |
|
29/11/2019 18:10 | |
Grazie Giuseppe,
l'istruzione evita di vedere a video quanto sta avvenendo, ma temo che ai fini della velocità di esecuzione nulla cambi.
|
| | Post: 3.562 | Registrato il: 03/04/2013
| Utente Master | Excel 2000 - 2013 | | OFFLINE | |
|
29/11/2019 19:25 | |
Buona sera, @eltorpe; grazie del riscontro. Personalmente un controllino lo farei, male non fa e potrebbe riservarti un certo miglioramento. Se lo ritieni utile e opportuno, potresti allegare un File (senza Dati sensibili) con alcuni Record significativi già inseriti con le relative Formule; domani potrei condurre alcuni test comparativi e magari trovare qualche soluzione interessante. A disposizione. Buona serata. Giuseppe
Windows XP - Excel 2000
Windows 10 - Excel 2013 |
| | Post: 3.564 | Registrato il: 03/04/2013
| Utente Master | Excel 2000 - 2013 | | OFFLINE | |
|
30/11/2019 13:01 | |
Buona giornata.
Come anticipato in Risposta #4, ho dedicato qualche minuto per analizzare il Codice VBA proposto in #1.
Preliminarmente ho:
- Attivato la Directory (Cartella) StatistichePS in C:\
- Salvato il File aggiornamento.xlsx (con un solo Foglio di lavoro "Foglio1") nella Directory sopra indicata
- Strutturato il File 2019_Statistiche.xlsm con due Fogli di lavoro; ORIGINE e DURATA
- Modificato il Codice VBA nel seguente modo:
Option Explicit
Sub AggiornamentoDati()
Application.ScreenUpdating = False
Dim wb As Workbook, wbagg As Workbook
Dim wks As Worksheet, wksagg As Worksheet
Dim RwRng As Integer, RwRng1 As Integer, x As Integer
Workbooks.Open Filename:="C:\StatistichePS\aggiornamento.xlsx"
Set wb = Workbooks("2019_Statistiche.xlsm")
Set wks = wb.Worksheets("ORIGINE")
Set wbagg = Workbooks("aggiornamento.xlsx")
Set wksagg = wbagg.Worksheets("Foglio1")
Application.Calculation = xlCalculationManual
wks.Activate
RwRng = wks.Cells(Rows.Count, 5).End(xlUp).Row
wks.Range("E2:BP" & RwRng).ClearContents
wksagg.Activate
RwRng1 = wksagg.Cells(Rows.Count, 5).End(xlUp).Row
wksagg.Range("C2:BN" & RwRng1).Copy
wks.Activate
wks.Range("E2:BP" & RwRng1).PasteSpecial xlPasteValues
Application.DisplayAlerts = False
Workbooks("aggiornamento.xlsx").Close
Application.DisplayAlerts = True
wks.Range("A3:D25000").ClearContents
wks.Range("A2:D2").Copy
wks.Range("A2").Copy
For x = 3 To RwRng1
Cells(x, 1).PasteSpecial xlPasteFormulas
Next x
Sheets("DURATA").Activate
Range("N3:P" & RwRng1).ClearContents
Range("N2:P2").Copy
For x = 2 To RwRng1
Cells(x, 14).PasteSpecial xlPasteFormulas
Next x
Range("H4").Select
Application.Calculation = xlCalculationAutomatic
Application.CutCopyMode = False
Set wb = Nothing
Set wks = Nothing
Set wbagg = Nothing
Set wksagg = Nothing
MsgBox ("Aggiornamento completato."), vbInformation
Application.ScreenUpdating = True
End Sub
Ovviamente non conoscendo le Formule proposte nei due File non sono in grado di valutare il tempo di esecuzione; in ogni caso direi che il tempo di esecuzione dovrebbe migliorare in modo apprezzabile.
Giuseppe
Windows XP - Excel 2000
Windows 10 - Excel 2013 |
| | Post: 40 | Registrato il: 09/09/2017
| Città: SASSARI | Età: 64 | Utente Junior | 2007 | | OFFLINE | |
|
28/12/2019 15:54 | |
Buon pomeriggio Giuseppe,
sono stato assente per motivi di salute e solo ora ho potuto esaminare il codice che hai proposto; ti ringrazio.
Hai ridotto i cicli for a uno per foglio; così però mi calcola una sola colonna per foglio, mentre ne deve calcolare 4 nel foglio ORIGINE e 3 nel foglio DURATA. Che poi sono la causa del rallentamento in office 365 mentre vanno molto più speditamente in office 2007.
Le formule sono banali:
Foglio ORIGINE: col A =DATA.VALORE(L2)
col B =L2
col C =AL2
col D =SE.ERRORE(C2-B2;"")
In effetti posso risparmiare due cicli for, che sono i responsabili del rallentamento, compilando le colonne B e C con Range("L:L").Copy
Range("B:B").PasteSpecial xlPasteValues; idem per la colonna C.
Foglio DURATA: col N =ORIGINE!AL2-ORIGINE!L2
col O =ORIGINE!AV2-ORIGINE!AL2
col P =ORARIO.VALORE(ORIGINE!L2)
Continuo a non capire il perchè Office 365 installato su PC molto più performante sia nettamente più lento di Office 2007 installato su PC vecchio di 10 anni ad eseguire cicli di calcolo su formule così semplici.
Considerando anche che, avendo attivato Application.Calculation = xlCalculationManual mi scrive i seriali (utili comunque nei calcoli impostati su altri fogli) e non date e orari formattati che rallenterebbero ulteriormente il processo.
Application.ScreenUpdating = False mi evita il flickering (che nella fattispecie può tornarmi utile in quanto mi mostra l'avanzamento del processo), ma non incide sulla velocità di calcolo.
Grazie ancora.
Saluti e Auguri
|
| | Post: 5.801 | Registrato il: 14/11/2004
| Utente Master | Office 2019 | | OFFLINE |
|
28/12/2019 19:17 | |
Ciao a parte che concordo con Giuseppe che saluto, riguardo ad un applicativo dedicato visto la mole dei dati e del file.
ma un ulteriore snellimento potresti averlo con 2-3 accorgimenti
eliminando dove è possibile le formule nelle celle creando delle macro che facciano i calcoli in memoria e poi scrivano il risultato nelle celle.
spezzettare il file, non credo che quando fai un operazione vengono usati tutti i fogli, ma solo quelli che riguardano quell'operazione, oltre a diminuire il peso del singolo file aumenta pure la velocità di esecuzione.
crei il file principale e poi i satelliti per singoli fornitori.
Ciao ed Auguri By Sal
se ti piace la soluzione sostienici con una DONAZIONE a piacere. Grazie clicca qui |
|
|