Excel a volte impazzisce

[ramset1978]

Ho un file che uso per fare dei test... questo file richiama valori di altri 40 file. Per completare un test ci vogliono circa 2,5 ore. A volte però excel impazzisce ed il test lo fa in 30 secondi..... ed io non riesco a capire cosa succede, sarebbe interessante capirlo in modo da far durare il test pochi minuti. Che ne pensate?

[ramset1978]

Dimenticavo, ovviamente il richiamo agli altri 40 file avviene tramite vba.

[Marius44]

Ciao
a mio avviso quello che dici non "dovrebbe" essere possibile. Una macro dovrebbe impiegare sempre all'incirca lo stesso tempo (qualche secondo o, addirittura, minuto ci può anche stare, ma una differenza di ore mi sembra impossibile).

Prova a postare la macro principale per darci uno sguardo (senza impegno, ovviamente).
Ciao,
Mario

[ramset1978]

Sono completamente d'accordo con te... non dovrebbe essere possibile. Allora fino all'altro ieri utilizzavo excel 2010 e questo succedeva spesso per l'aggiornamento della sezione1 vba che di norma di mette circa 5-6 minuti, spesso ci metteva 20 secondi. Per la sezione2 vba molto più lunga non mi era mai successo. Oggi utilizzando excel 2016 il test era iniziato da circa 10 minuti, io sono andato su internet a fare una ricerca e ci ho messo meno di 1 minuto, chiudo il browser e trovo il test terminato (correttamente). Incredibile ma vero.
Comunque questa è la macro:

Codice: Seleziona tutto

Private Sub AggiornaTutto()
Application.Calculation = xlCalculationAutomatic
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Sheets("Sys").Select

'memorizzo il percorso dove si trovano i file da analizzare
Path1 = Range("Path1").Value
Path2 = Range("Path2").Value


'acquisisco dati
win = Range("window").Value

'conto i titoli presenti nel Settaggi
Sheets("Settaggi").Select
NrTitoliEsame = Application.WorksheetFunction.CountA(Range("TD"))
NrTitoliTrading = Range("MaxTitForTrading")

PredCapitale = Range("PredCapitale").Value
LagPredCapitale = Range("LagPredCapitale").Value

UscitaCval = Range("UscitaCval").Value
LagUscitaCval = Range("LagUscitaCval").Value
UscitaLeva = Range("UscitaLeva").Value
LagUscitaLeva = Range("LagUscitaLeva").Value
UscitaRend = Range("UscitaRend").Value
LagUscitaRend = Range("LagUscitaRend").Value
UscitaRendLeva = Range("UscitaRendLeva").Value
LagUscitaRendLeva = Range("LagUscitaRendLeva").Value
UscitaNomeTitolo = Range("UscitaNomeTitolo").Value
LagUscitaNomeTitolo = Range("LagUscitaNomeTitolo").Value


'memorizzo il numero totale di colonne
UcTot = Worksheets("Sys").Range("XFD1").End(xlToLeft).Column
ColOccIniz = 3
ColDopRis = 14
Sheets("Sys").Select

'righe scritte della data e prime colonne
Rsdata = Application.WorksheetFunction.CountA(Columns(ColOccIniz))

'AGGIORNAMENTO PARTE EXCEL
'aggiorno QtaCapitaleTot
PcSez0Excel = ColOccIniz + 1
UcSez0Excel = ColOccIniz + 1
RsSez0Excel = Application.WorksheetFunction.CountA(Columns(ColOccIniz + 2))
Range(Cells(RsSez0Excel, PcSez0Excel), Cells(RsSez0Excel, UcSez0Excel)).Select
Selection.Copy
Range(Cells(RsSez0Excel, PcSez0Excel), Cells(Rsdata, UcSez0Excel)).Select
ActiveSheet.Paste
Application.CutCopyMode = False



'aggiorno sez1 excel
PcSez1Excel = UcSez0Excel + 1
UcSez1Excel = UcSez0Excel + NrTitoliEsame + NrTitoliTrading * 2
RsSez1Excel = Application.WorksheetFunction.CountA(Columns(PcSez1Excel))
Range(Cells(RsSez1Excel, PcSez1Excel), Cells(RsSez1Excel, UcSez1Excel)).Select
Selection.Copy
Range(Cells(RsSez1Excel, PcSez1Excel), Cells(Rsdata, UcSez1Excel)).Select
ActiveSheet.Paste
Application.CutCopyMode = False


'aggiorno sez2 excel
PcSez2Excel = UcSez1Excel + NrTitoliTrading + 1
UcSez2Excel = UcSez1Excel + NrTitoliTrading * 2
RsSez2Excel = Application.WorksheetFunction.CountA(Columns(PcSez2Excel))
Range(Cells(RsSez2Excel, PcSez2Excel), Cells(RsSez2Excel, UcSez2Excel)).Select
Selection.Copy
Range(Cells(RsSez2Excel, PcSez2Excel), Cells(Rsdata, UcSez2Excel)).Select
ActiveSheet.Paste
Application.CutCopyMode = False



'aggiorno sez3 excel
PcSez3Excel = UcSez2Excel + NrTitoliTrading * 5 + 1
UcSez3Excel = UcSez2Excel + NrTitoliTrading * 5 + ColDopRis
RsSez3Excel = Application.WorksheetFunction.CountA(Columns(PcSez3Excel))
Range(Cells(RsSez3Excel, PcSez3Excel), Cells(RsSez3Excel, UcSez3Excel)).Select
Selection.Copy

Range(Cells(RsSez3Excel, PcSez3Excel), Cells(Rsdata, UcSez3Excel)).Select
ActiveSheet.Paste
Application.CutCopyMode = False



'abilito e disabilito il calcolo automatico
Application.Calculation = xlCalculationAutomatic
Application.Calculation = xlCalculationManual

'autoadatto larghezza colonne
Cells.Select
Selection.Columns.AutoFit

'abilito e disabilito il calcolo automatico
Application.Calculation = xlCalculationAutomatic
Application.Calculation = xlCalculationManual
Beep
'FINE AGGIORNAMENTO PARTE EXCEL


   'AVVISO CON LABEL IL TERMINE AGGIORNAMENTO SEZIONI EXCEL
    UserForm1.Controls("Label1").BackColor = RGB(210, 210, 210)
    UserForm1.Controls("Label1").ForeColor = RGB(0, 0, 0)
    UserForm1.Label1.Caption = "Aggiornamento sezioni EXCEL completato." & vbCrLf & vbCrLf & "Procedo ad aggiornare la sezione 1 di 2 VBA."
    Application.Run "AttivaUserform"
   


'INIZIO AGGIORNAMENTO PARTE VBA
'aggiorno sez1 vba
PcSez1Vba = UcSez1Excel + 1
UcSez1Vba = UcSez1Excel + NrTitoliTrading
RsSez1Vba = Application.WorksheetFunction.CountA(Columns(PcSez1Vba)) + win

'cancelle le ultime 2 righe
Range(Cells(RsSez1Vba - 1, PcSez1Vba), Cells(RsSez1Vba, UcSez1Vba)).Select
Selection.ClearContents
'riconto le righe
RsSez1Vba = Application.WorksheetFunction.CountA(Columns(PcSez1Vba)) + win


'righe scritte
xy = Application.WorksheetFunction.Max(1, RsSez1Vba + 1)

For y = xy To Rsdata
For i = 0 To NrTitoliTrading - 1

'acquisisco il nome del file
If Cells(y, PcSez1Vba - NrTitoliTrading * 2 + i) = "" Then GoTo Saltacella1
NomeFile = Cells(y, PcSez1Vba - NrTitoliTrading * 2 + i).Value

'formatto le celle e scrivo PredXQtaCap
Cells(y, PcSez1Vba + i).Select
Selection.NumberFormat = "#,##0_ ;[Red]-#,##0 "

Cells(y, PcSez1Vba + i).FormulaLocal = "=SE(TitForTrading>=" & i + 1 & ";ASS(INDICE('" & Path1 & NomeFile & ".xlsm'! " & PredCapitale & ";NrDay+LagPredCapitale-INDICE(PrimaRiga;CONFRONTA(""" & NomeFile & """;TD;0))));"""")"

Ricomincia1:
Next i
Next y
If y <= Rsdata And i <= NrTitoliTrading Then
Saltacella1:
Cells(y, PcSez1Vba + i).FormulaLocal = "no" ' necessario per il conteggio delle righe già scritte
If y <= Rsdata Then GoTo Ricomincia1:
End If


'abilito e disabilito il calcolo automatico
Application.Calculation = xlCalculationAutomatic
Application.Calculation = xlCalculationManual
'autoadatto larghezza colonne
Cells.Select
Selection.Columns.AutoFit
Beep
'Termine aggiornamento SEZIONE 1 VBA

   'AVVISO CON LABEL IL TERMINE AGGIORNAMENTO SEZIONE 1 VBA
    UserForm1.Controls("Label1").BackColor = RGB(210, 210, 210)
    UserForm1.Controls("Label1").ForeColor = RGB(0, 0, 0)
    UserForm1.Label1.Caption = "Aggiornamento sezione 1 di 2 VBA completato." & vbCrLf & vbCrLf & "Procedo ad aggiornare la sezione 2 di 2 VBA."
    Application.Run "AttivaUserform"



'aggiorno sez2 vba
PcSez2Vba = UcSez2Excel + 1
UcSez2Vba = UcSez2Excel + NrTitoliTrading * 5
RsSez2Vba = Application.WorksheetFunction.CountA(Columns(PcSez2Vba)) + win
'cancelle le ultime 2 righe
Range(Cells(RsSez2Vba - 1, PcSez2Vba), Cells(RsSez2Vba, UcSez2Vba)).Select
Selection.ClearContents

'riconto le righe
RsSez2Vba = Application.WorksheetFunction.CountA(Columns(PcSez2Vba)) + win

'righe scritte sui risultati
xy = Application.WorksheetFunction.Max(1, RsSez2Vba + 1)

'tiro fuori leva trade e risultati
For y = xy To Rsdata
For i = 0 To NrTitoliTrading - 1

'acquisisco il nome del file
If Cells(y, PcSez2Vba - NrTitoliTrading * 3 + i) = "" Then GoTo Saltacella2
NomeFile = Cells(y, PcSez2Vba - NrTitoliTrading * 3 + i).Value

'formatto le celle e scrivo il nome del file tradato
Cells(y, PcSez2Vba + i).Select
Selection.NumberFormat = "#,##0"
Cells(y, PcSez2Vba + i).FormulaLocal = "=SE(TitForTrading>=" & i + 1 & ";INDICE('" & Path2 & NomeFile & ".xlsm'! " & UscitaNomeTitolo & ";NrDay+LagUscitaNomeTitolo-INDICE(PrimaRiga;CONFRONTA(""" & NomeFile & """;TI;0)));"""")"

'formatto le celle e scrivo il controvalore
Cells(y, PcSez2Vba + NrTitoliTrading + i).Select
Selection.NumberFormat = "#,##0"
Cells(y, PcSez2Vba + NrTitoliTrading + i).FormulaLocal = "=SE(TitForTrading>=" & i + 1 & ";SE(INDICE('" & Path2 & NomeFile & ".xlsm'! " & UscitaRend & ";NrDay+LagUscitaRend-INDICE(PrimaRiga;CONFRONTA(""" & NomeFile & """;TI;0)))="""";"""";INDICE('" & Path2 & NomeFile & ".xlsm'! " & UscitaCval & ";NrDay+LagUscitaCval-INDICE(PrimaRiga;CONFRONTA(""" & NomeFile & """;TI;0))));"""")"

'formatto le celle e scrivo la leva
Cells(y, PcSez2Vba + NrTitoliTrading * 2 + i).Select
Selection.NumberFormat = "0.00_ ;[Red]-0.00 "
Cells(y, PcSez2Vba + NrTitoliTrading * 2 + i).FormulaLocal = "=SE(TitForTrading>=" & i + 1 & ";SE(INDICE('" & Path2 & NomeFile & ".xlsm'! " & UscitaLeva & ";NrDay+LagUscitaLeva-INDICE(PrimaRiga;CONFRONTA(""" & NomeFile & """;TI;0)))="""";"""";INDICE('" & Path2 & NomeFile & ".xlsm'! " & UscitaLeva & ";NrDay+LagUscitaLeva-INDICE(PrimaRiga;CONFRONTA(""" & NomeFile & """;TI;0))));"""")"

'formatto le celle e scrivo il rendimento
Cells(y, PcSez2Vba + NrTitoliTrading * 3 + i).Select
Selection.NumberFormat = "0.00_ ;[Red]-0.00 "
With Selection.Font
        .Color = -10477568
        .TintAndShade = 0
End With
Cells(y, PcSez2Vba + NrTitoliTrading * 3 + i).FormulaLocal = "=SE(TitForTrading>=" & i + 1 & ";SE(INDICE('" & Path2 & NomeFile & ".xlsm'! " & UscitaRend & ";NrDay+LagUscitaRend-INDICE(PrimaRiga;CONFRONTA(""" & NomeFile & """;TI;0)))="""";"""";INDICE('" & Path2 & NomeFile & ".xlsm'! " & UscitaRend & ";NrDay+LagUscitaRend-INDICE(PrimaRiga;CONFRONTA(""" & NomeFile & """;TI;0)))*RC[-" & NrTitoliTrading * 4 & "]/(1/TitForTrading));"""")"

'formatto le celle e scrivo il rendimento con capitale levato
Cells(y, PcSez2Vba + NrTitoliTrading * 4 + i).Select
Selection.NumberFormat = "0.00_ ;[Red]-0.00 "
With Selection.Font
        .Color = -10477568
        .TintAndShade = 0
End With
Cells(y, PcSez2Vba + NrTitoliTrading * 4 + i).FormulaLocal = "=SE(TitForTrading>=" & i + 1 & ";SE(INDICE('" & Path2 & NomeFile & ".xlsm'! " & UscitaRendLeva & ";NrDay+LagUscitaRendLeva-INDICE(PrimaRiga;CONFRONTA(""" & NomeFile & """;TI;0)))="""";"""";INDICE('" & Path2 & NomeFile & ".xlsm'! " & UscitaRendLeva & ";NrDay+LagUscitaRendLeva-INDICE(PrimaRiga;CONFRONTA(""" & NomeFile & """;TI;0)))*RC[-" & NrTitoliTrading * 5 & "]/(1/TitForTrading));"""")"


Ricomincia2:
Next i
Next y
If y <= Rsdata And i <= NrTitoliTrading Then
Saltacella2:
Cells(y, PcSez2Vba + i).FormulaLocal = "no" ' necessario per il conteggio delle righe già scritte
Cells(y, PcSez2Vba + NrTitoliTrading + i).FormulaLocal = "no" ' necessario per il conteggio delle righe già scritte
Cells(y, PcSez2Vba + NrTitoliTrading * 2 + i).FormulaLocal = "no" ' necessario per il conteggio delle righe già scritte
Cells(y, PcSez2Vba + NrTitoliTrading * 3 + i).FormulaLocal = "no" ' necessario per il conteggio delle righe già scritte
Cells(y, PcSez2Vba + NrTitoliTrading * 4 + i).FormulaLocal = "no" ' necessario per il conteggio delle righe già scritte

If y <= Rsdata Then GoTo Ricomincia2:
End If

'TERMINA
' autoadatto la larghezzza colonne
Columns("A:XFD").Select
Selection.Columns.AutoFit

'abilito il calcolo automatico in caso excel lo abbia disabilitato
Application.Calculation = xlCalculationAutomatic
Application.Calculation = xlCalculationManual

'riposiziono il cursore nell'ultima riga
Sheets("Sys").Select

Worksheets("Sys").Range("A1").Cells(Rsdata, PcSez2Vba + 1).Select

'cambio foglio
Sheets("Report").Select
Beep

'AVVISO CON LABEL IL TERMINE AGGIORNAMENTO
    UserForm1.Controls("Label1").BackColor = RGB(210, 210, 210)
    UserForm1.Controls("Label1").ForeColor = RGB(0, 0, 0)
    UserForm1.Label1.Caption = vbCrLf & vbCrLf & "           Aggiornamento completato."
    Application.Run "AttivaUserform"
   

End Sub






[Marius44]

Ciao
non mi permetto entrare nel merito del codice ma non mi sembra il massimo dell'efficienza.

Ciò detto, ti faccio notare due cose che mi son saltate all'occhio.

Codice: Seleziona tutto

'abilito il calcolo automatico in caso excel lo abbia disabilitato
Application.Calculation = xlCalculationAutomatic
Application.Calculation = xlCalculationManual

In questo modo stai mettendo il calcolo in "Manuale" e non in Automatico. Fra l'altro all'inizio della macro (ma anche nel corpo del codice usi spesso
Application.Calculation = xlCalculationAutomatic
Application.Calculation = xlCalculationManual
che è un errore. Normalmente, per velocizzare l'esecuzione di un determinato codice, si imposta all'inizio il Calcolo in Manuale (si disabilita quello che in genere è in Automatico) per riabilitarlo alla fine del processo.

Altra cosa: utilizzi troppo spesso .Select; ad esempio

Codice: Seleziona tutto

Range(Cells(RsSez0Excel, PcSez0Excel), Cells(RsSez0Excel, UcSez0Excel)).Select
Selection.Copy
Range(Cells(RsSez0Excel, PcSez0Excel), Cells(Rsdata, UcSez0Excel)).Select
ActiveSheet.Paste

Quella "doppia" selezione assorbe tempo. Perchè non provi così:

Codice: Seleziona tutto

Range(Cells(RsSez0Excel, PcSez0Excel), Cells(RsSez0Excel, UcSez0Excel)).Copy
Range(Cells(RsSez0Excel, PcSez0Excel), Cells(Rsdata, UcSez0Excel)).Paste

Tieni presente che, forse, vuole il PasteSpecial per i soli valori.

Ancora: l'autofit delle colonne, il format delle celle, ecc. perchè ogni volta e non solo una volta per tutte le colonne/celle interessate.

Ciao,
Mario

[ramset1978]

Ciao
non mi permetto entrare nel merito del codice ma non mi sembra il massimo dell'efficienza.

Ciò detto, ti faccio notare due cose che mi son saltate all'occhio.

Codice: Seleziona tutto

'abilito il calcolo automatico in caso excel lo abbia disabilitato
Application.Calculation = xlCalculationAutomatic
Application.Calculation = xlCalculationManual

In questo modo stai mettendo il calcolo in "Manuale" e non in Automatico. Fra l'altro all'inizio della macro (ma anche nel corpo del codice usi spesso
Application.Calculation = xlCalculationAutomatic
Application.Calculation = xlCalculationManual
che è un errore. Normalmente, per velocizzare l'esecuzione di un determinato codice, si imposta all'inizio il Calcolo in Manuale (si disabilita quello che in genere è in Automatico) per riabilitarlo alla fine del processo.

Altra cosa: utilizzi troppo spesso .Select; ad esempio

Codice: Seleziona tutto

Range(Cells(RsSez0Excel, PcSez0Excel), Cells(RsSez0Excel, UcSez0Excel)).Select
Selection.Copy
Range(Cells(RsSez0Excel, PcSez0Excel), Cells(Rsdata, UcSez0Excel)).Select
ActiveSheet.Paste

Quella "doppia" selezione assorbe tempo. Perchè non provi così:

Codice: Seleziona tutto

Range(Cells(RsSez0Excel, PcSez0Excel), Cells(RsSez0Excel, UcSez0Excel)).Copy
Range(Cells(RsSez0Excel, PcSez0Excel), Cells(Rsdata, UcSez0Excel)).Paste

Tieni presente che, forse, vuole il PasteSpecial per i soli valori.

Ancora: l'autofit delle colonne, il format delle celle, ecc. perchè ogni volta e non solo una volta per tutte le colonne/celle interessate.

Ciao,
Mario

Grazie per i consigli... l'abilitazione e la disabilitazione del calcolo automatico è necessario perchè altrimenti la parte in excel non si aggiorna e quindi i nomi restano in bianco. Il secondo punto è un ottimo consiglio che seguirò. L'autoflit è un secondo e lo fa solo alla fine del codice, quindi non è un problema. il format delle celle non posso farlo per tutte le colonne perchè non sono sempre uguali. Ciò detto, non sono certo questi i motivi per cui ogni tanto anzichè 2,5 ore ci mette 30 secondi nell'eseguire il codice, non credi?

[ramset1978]

Tra l'altro il secondo punto riguarda la parte excel, che si aggiorna in 10 secondi.... se seguo il tuo consiglio si aggiornerà in 9 secondi.... risparmio 1 secondo su 2,5 ore. Io vorrei capire altro Mario... Comunque io sono un autodidatta, ho realizzato questo file leggendo qua e la e chiedendo spesso consigli su questo forum. Per me averlo realizzato è stato una specie di miracolo, per te che sei un esperto invece il codice non ti sembrerà granché e lo capisco.

[Anthony47]

In aggiunta ai suggerimenti di Mario...
Vedo che inserisci formule che attingono ai famosi 40 file di cui parli; in prima battuta prova ad aprire i file subito prima di inserire le formule e chiudili subito dopo
Ad esempio nel primo loop:

Codice: Seleziona tutto

For y = xy To Rsdata
    For i = 0 To NrTitoliTrading - 1
        'acquisisco il nome del file
        If Cells(y, PcSez1Vba - NrTitoliTrading * 2 + i) = "" Then GoTo Saltacella1
        NomeFile = Cells(y, PcSez1Vba - NrTitoliTrading * 2 + i).Value
        Workbooks.Open Path1 & NomeFile & ".xlsm"           '**** Apri il file
        ThisWorkbook.Activate                               '**** Torna al TUO workbook
        'formatto le celle e scrivo PredXQtaCap
        Cells(y, PcSez1Vba + i).Select
        Selection.NumberFormat = "#,##0_ ;[Red]-#,##0 "
        Cells(y, PcSez1Vba + i).FormulaLocal = "=SE(TitForTrading>=" & i + 1 & ";ASS(INDICE('" & Path1 & NomeFile & ".xlsm'! " & PredCapitale & ";NrDay+LagPredCapitale-INDICE(PrimaRiga;CONFRONTA(""" & NomeFile & """;TD;0))));"""")"
        Workbooks(NomeFile & ".xlsm").Close False           '**** CHIUDILO
Ricomincia1:
    Next i
Next y


Le istruzioni aggiunte sono quelle marcate ****

Contemporaneamente inserisci dei Debug.Print "La Fase Corrente", Timer per avere un'idea della durata delle varie fasi.
Ad esempio:

Codice: Seleziona tutto

'abilito e disabilito il calcolo automatico
Debug.Print "AA_1", Timer
Application.Calculation = xlCalculationAutomatic
Application.Calculation = xlCalculationManual

'autoadatto larghezza colonne
Cells.Select
Selection.Columns.AutoFit

'abilito e disabilito il calcolo automatico
Application.Calculation = xlCalculationAutomatic
Application.Calculation = xlCalculationManual
Beep
Debug.Print "AA_2", Timer
'FINE AGGIORNAMENTO PARTE EXCEL


Questo ci da l'idea di quanto duri ad esempio "quella fase" di Ricalcolo.
Ovviamente maggiori sono i punti di misurazione e maggiore e' la possibilita' di cercare a posteriori i colli di bottiglia

Debug.Print scrive nella finestra Immediata del vba; per visualizzarla, dalla finestra del vba premere Contr-g

Ciao

[ramset1978]

Grazie Anthony per i consigli, tuttavia voi non sapete alcune cose e quindi non avete il quadro completo. Il file di test ha x colonne di cui parte sono colonne che contengono formule e che vengono aggiornate in una volta sola (tempo 10 secondi circa) ed altre che vengono aggiornate da vba. Il calcolo automatico è inserito nelle sezione vba ed è assolutamente necessario (viceversa dopo aver aggiornato le colonne non vengono fuori i nomi dei file da aprire e conseguenza non possono essere richiamati da vba). Di aprire i file non se ne parla, sono file pesanti (ed è questo il motivo per cui ci metto 2,5 ore a fare il test). Se li apro il tempo per eseguire il test diventa molto ma molto maggiore (almeno x10). Ciò premesso i consigli per ridurre eventualmente il tempo dei test sono bene accetti ma la domanda iniziale era un'altra.... perchè a volte anzichè 2,5 ore ci mette meno di 1 minuto? Che diavolo succede? Considera che ogni file che viene richiamato pesa 45 mb circa. In particolare spesso succede che la sezione 1vba che di norma di mette 5-6 minuti ad aggiornarsi, si aggiorna in pochissimi secondi. Da quando ho installato excel 2016 è successo che tutto il test si è aggiornato (correttamente tra l'altro) in meno di 1 minuto. E' questo che non mi spiego....

[Anthony47]

Rimane il suggerimento di inserire le istruzione per misurare i tempi di esecuzioni di singole fasi.
Poi dal confronto tra la metrica di un caso e quella dell'altro caso si puo' fare qualche deduzione.
Ne approfitto per affinare la tecnica di misura:

Codice: Seleziona tutto

myTim = Timer         'All'inzio della macro


Codice: Seleziona tutto

Debug.Print "LaFase", Format(Timer - myTim, "0.00")             'Nei punti dove vuoi misurare il tempo

myTim definiscila "as Single".
Questo ti consente di leggere meglio i tempi di esecuzione

Altro suggerimento (da provare su una copia del file): prima degli aggiornamenti cancella tutte le formule che vai ad aggiornare col vba (serve ad avere la certezza che la fase e' stata eseguita)
Tra l'altro: ma le formule perche' le devi riscrivere, cambiano i riferimenti?

Ne approfitto per chiedere se stai lavorando sullo stesso PC dove eseguivi XL2010 o e' cambiato anche quello.
Ciao

[ramset1978]

Anthony, io credo sia del tutto inutile misurare il tempo, la domanda iniziale era che da 2,5 ore si passa a 1 minuto. Ciò è totalmente fuori da ogni buon senso considerato che bisognare richiamare centinaia di volte file che pesano 50mb l'uno. La parte di excel dura infatti pochi secondi. Si devo richiamare i file in vba perchè ad ogni test cambiano i riferimenti (i nomi dei titoli da richiamare). Si il pc è lo stesso con cui lavoravo su excel 2010. Ciao

[Anthony47]

Anche a me sembra strano che si passi da ore a minuti solo col cambio di versione; la richiesta di cancellare le formule prima di riscriverle con vba serviva proprio ad avere la certezza che i valori fossero stati ricalcolati.

Quanto al metodo, beh sono approcci personali: io tendo a guardare i numeri e poi cercare di capire cosa c'e' dietro. Capisco che ci sia poco interesse a misurarli considerando che la versione lenta e' quella che stai abbandonando (XL2010) a favore di quella insperatamente piu' veloce (XL2016).

Ciao

[ramset1978]

Anche a me sembra strano che si passi da ore a minuti solo col cambio di versione; la richiesta di cancellare le formule prima di riscriverle con vba serviva proprio ad avere la certezza che i valori fossero stati ricalcolati.

Quanto al metodo, beh sono approcci personali: io tendo a guardare i numeri e poi cercare di capire cosa c'e' dietro. Capisco che ci sia poco interesse a misurarli considerando che la versione lenta e' quella che stai abbandonando (XL2010) a favore di quella insperatamente piu' veloce (XL2016).

Ciao

No Anthony, il tempo del test è esattamente uguale tra 2010 e 2016. La differenza è che mentre per la prima parte di vba succedeva anche con il 2010 che a volte ci mettesse pochi secondi anzichè 5-6 minuti, con il 2016 è successo 1 sola volta che abbia aggiornato tutto il test in 1 minuto circa. Stranezze impossibili da capire, è corretto che ci metta oltre 2 ore a fare un test. Ne ho fatti a decine ed il tempo medio è quello e considerato che i file che richiama sono pesanti io credo sia assolutamente corretto. La stranezza è quando ci mette 1 minuto, ma capire perchè succede è pressochè impossibile. Ciao grande!

[Anthony47]

Ah... Rimane il dubbio se impazzisce quando impiega 2.5 ore o quando impiega pochi minuti...

Pero' senza misurazioni dei tempi a me non viene nessuna idea brillante.

Ma tu sei sicuro che quando impiega poco i risultati sono calcolati correttamente? Hai provato a guardare se le formule contengono tutto l'intervallo dei dati, o (chissa' perche') in qualche caso includono solo una porzione? E puoi copiarci un paio di formule compilate nella sez 2 vba?

(Sempre se la curiosita' non e' sopita)

[ramset1978]

Ah... Rimane il dubbio se impazzisce quando impiega 2.5 ore o quando impiega pochi minuti...

Pero' senza misurazioni dei tempi a me non viene nessuna idea brillante.

Ma tu sei sicuro che quando impiega poco i risultati sono calcolati correttamente? Hai provato a guardare se le formule contengono tutto l'intervallo dei dati, o (chissa' perche') in qualche caso includono solo una porzione? E puoi copiarci un paio di formule compilate nella sez 2 vba?

(Sempre se la curiosita' non e' sopita)

Su quando impazzisce non ho dubbi... quando impiega qualche minuto. Sono anni che uso questo file. Inoltre come ho già detto i tempi lunghi sono dovuti al fatto che i file da richiamare sono pesanti. La sez 2vb è interamente contenuta nella macro che ho pubblicato cosi come la sez1 vba. Quello che manca è la macro iniziale che inserisce le formule (parte excel) nel primo rigo. Il resto del codice è tutto pubblicato. La sez 1 vba utilizza lo stesso procedimento, ci mette molto meno (5-6) minuti circa quando non impazzisce perchè mentre la sez 2 vba compila 20 colonne e richiama file pesanti, la sez 1vb richiama file più leggeri (15 mb circa) e compila solo 4 colonne. In ultimo, si quando impazzisce i richiami ai file sono corretti.
Ciao

[ramset1978]

Aggiungo, per capire perchè ci mette tanto tempo, che le righe da compilare sono oltre 1600 in continuo aumento.

[ramset1978]

Stamattina, nuovo test.... sez 2 vba aggiornata in poco più di 1 minuto. Verificato alcuni valori, sembrano tutti corretti. Chi ci capisce qualcosa è un genio. E' la seconda volta che succede da quando ho installato la versione 2016 (mai successo con la 2010), mentre per la sez 1 vba succedeva e succede spesso.

[Anthony47]

Perche' non vuoi provare a cancellare quelle 1600 righe da compilare all'inizio della macro, cosi' siamo certi che sono state compilate dalla macro?
Se lo fai, subito dopo fai anche un File Save (per cancellare anche le copie cache dei collegamenti esterni, salvate internamente al file xlsm), prima di continuare con l'esecuzione della macro.
Per le formule, si lo so che nel vba c'e' il codice che le genera e quindi posso con calma provare a ricostruirmele; ma tu le hai davanti, impiegheresti molto meno a fare un paio di copia e incolla e mi daresti le formule esatte, non il mio best guess... Farebbero comodo le prime tre formule inserite nel ciclo intitolato " 'tiro fuori leva trade e risultati".
Se vuoi mascherare informazioni sensibili puoi sostituire "singoli caratteri" con una X (senza alterare i caratteri che fanno parte della sintassi)

Quanto al discorso delle copie dei collegamenti a file esterni memorizzati all'interno dei file xlsx/m, accennato sopra, questo significa che il tuo file Riepilogone dovrebbe avere una dimensione sproporzionata rispetto alle 1600 righe di formule che contiene; ad esempio, avendo io inserito un cerca.vert a una tabella esterna di 4000 righe X 10 colonne al mio file e' stato aggiunto un externalLink1.xml che occupa (zippato) circa 250KB.

Oltre alle menzionate 1600 circa celle di formule, il tuo Riepilogone contiene altre elaborazioni significative (es tabelle pivot, grafici, elaborazioni complesse), e quale e' la dimensione finale del file?
Cancellando le 1600 celle con collegamenti ai file esterni e salvando, il file di quanto si riduce?

Cosa mi frulla per la mente? In ordine:
1) buio
2) magari non tutte le 1600 formule sono aggiornate quando la macro si completa rapidamente (il perche' sarebbe poi da accertare); lo vediamo se all'inizio cancelliamo le 1600 formule e subito salviamo il file (senza le copie di externalLinkXYZ.xml).
3) magari uno stesso file rimane ancora disponibile in windows per un certo tempo dopo aver inserito una formula, quindi alla formula successiva non deve essere ricaricato nuovamente (era lo stesso motivo per cui suggerivo di aprire il file prima di inserire nel Riepilogone le formule a lui facente capo; il vantaggio a farlo si potrebbe determinare solo dopo aver misurato i secondi necessari a fare una operazione senza il file in memoria confrontandolo col tempo necessario ad aprire il file)

Ciao

[ramset1978]

Perche' non vuoi provare a cancellare quelle 1600 righe da compilare all'inizio della macro, cosi' siamo certi che sono state compilate dalla macro?
Se lo fai, subito dopo fai anche un File Save (per cancellare anche le copie cache dei collegamenti esterni, salvate internamente al file xlsm), prima di continuare con l'esecuzione della macro.
Per le formule, si lo so che nel vba c'e' il codice che le genera e quindi posso con calma provare a ricostruirmele; ma tu le hai davanti, impiegheresti molto meno a fare un paio di copia e incolla e mi daresti le formule esatte, non il mio best guess... Farebbero comodo le prime tre formule inserite nel ciclo intitolato " 'tiro fuori leva trade e risultati".
Se vuoi mascherare informazioni sensibili puoi sostituire "singoli caratteri" con una X (senza alterare i caratteri che fanno parte della sintassi)

Quanto al discorso delle copie dei collegamenti a file esterni memorizzati all'interno dei file xlsx/m, accennato sopra, questo significa che il tuo file Riepilogone dovrebbe avere una dimensione sproporzionata rispetto alle 1600 righe di formule che contiene; ad esempio, avendo io inserito un cerca.vert a una tabella esterna di 4000 righe X 10 colonne al mio file e' stato aggiunto un externalLink1.xml che occupa (zippato) circa 250KB.

Oltre alle menzionate 1600 circa celle di formule, il tuo Riepilogone contiene altre elaborazioni significative (es tabelle pivot, grafici, elaborazioni complesse), e quale e' la dimensione finale del file?
Cancellando le 1600 celle con collegamenti ai file esterni e salvando, il file di quanto si riduce?

Cosa mi frulla per la mente? In ordine:
1) buio
2) magari non tutte le 1600 formule sono aggiornate quando la macro si completa rapidamente (il perche' sarebbe poi da accertare); lo vediamo se all'inizio cancelliamo le 1600 formule e subito salviamo il file (senza le copie di externalLinkXYZ.xml).
3) magari uno stesso file rimane ancora disponibile in windows per un certo tempo dopo aver inserito una formula, quindi alla formula successiva non deve essere ricaricato nuovamente (era lo stesso motivo per cui suggerivo di aprire il file prima di inserire nel Riepilogone le formule a lui facente capo; il vantaggio a farlo si potrebbe determinare solo dopo aver misurato i secondi necessari a fare una operazione senza il file in memoria confrontandolo col tempo necessario ad aprire il file)

Ciao

Ciao Anthony, non ho ben capito cosa vuoi che faccia.... rispondo comunque alle tue domande.
1) il file senza le formule vba ovvero dopo averlo predisposto ed aver lanciato la macro1 (non pubblicata) che inserisce le formule nelle colonne excel pesa 2mb. Non ci sono grafici o formule particolari solo un richiamo ad un indicatore dei titoli.
2) il file completo ad oggi 1700 righe di cui circa 450-500 contengono richiamo per la sez 2 vba (le altre sono semplici no), pesa 15 mb circa
3) tutte le 1700 righe vengono cancellate ad ogni test, perchè ad ogni test cambiano ovviamente i nome dei file da richiamare.
4) non ho capito che formule vorresti e come dovrei dartele?
5) Salvo sempre il file quando rifaccio un nuovo test
6) quanto al fatto che il file possa rimanere memorizzato in windows è possibile, ma tieni presente che ad ogni test cambiano i nome dei file da richiamare quindi non vedo come excel possa eventualmente utilizzare quel file.
Infine se mi spieghi meglio cosa vorresti te lo pubblico volentieri, ovviamente cancellando i dati sensibili.
Saluti.

[Zer0Kelvin]

Ciao a tutti.
Solo una domanda: i files si trovano da elaborare si trovano fisicamente sul PC o sono in una rete?