Risultati da 1 a 13 di 13

Discussione: vba con o senza "formule" ... vantaggi, velocità di esecuzione



  1. #1

    L'avatar di ges
    Clicca e Apri
    Data Registrazione
    Jun 2015
    Località
    Como
    Età
    53
    Messaggi
    7149
    Versione Office
    2011MAC 2016WIN
    Likes ricevuti
    2064
    Likes dati
    1300

    vba con o senza "formule" ... vantaggi, velocità di esecuzione

    Ciao a tutti,
    premetto che sono tra quelli che scrive il codice senza ricorrere a funzioni (es. ActiveCell.FormulaR1C1 = "=VLOOKUP(RC..,R.C...:R...,..,FALSE", Application.CountIf(....,....) noto invece che alcuni invece lo fanno e mi chiedevo se fosse solo un questione di scelte personali oppure vi fossero dei vantaggi o svantaggi nella scelta.

    A titolo di esempio, ho scritto due routine che hanno la funzione "lato formule" di INDICE ... CONFRONTA che devono lavorare su un range di 10.000 righe ... come tempo entrambi impiegano circa 7 secondi... e così non vedo vantaggi o svantaggi.

    Che ne pensate?

    Codice con formule:
    Codice: 
    Sub ordina()
    'esegui con formula
    Application.ScreenUpdating = False
    inizio = Timer
    Dim uRiga As Long, y As Long
    uRiga = Range("A" & Rows.Count).End(xlUp).Row
    For y = 2 To uRiga
        Sheets(2).Cells(y, 2) = Application.WorksheetFunction.Index(Sheets(1).Range("B2" & ":B" & uRiga), Application.WorksheetFunction.Match(Sheets(2).Cells(y, 1), Sheets(1).Range("A2" & ":A" & uRiga), 0))
       Sheets(2).Cells(y, 3) = Application.WorksheetFunction.Index(Sheets(1).Range("C2" & ":C" & uRiga), Application.WorksheetFunction.Match(Sheets(2).Cells(y, 1), Sheets(1).Range("A2" & ":A" & uRiga), 0))
            Next
    fine = Timer
    Application.ScreenUpdating = True
    MsgBox "Tempo di esecuzione: " & fine - inizio
    End Sub
    Codice senza formula
    Codice: 
    Sub confronta()
    'esegui senza formula
    Application.ScreenUpdating = False
    Application.Calculation = False
    inizio = Timer
    Dim area1 As Object, area2 As Object, X As Object, y As Object
    uRiga1 = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
    uRiga2 = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row
    Set area1 = Sheets(1).Range("A2:A" & uRiga1)
    Set area2 = Sheets(2).Range("A2:A" & uRiga2)
        For Each X In area1
            For Each y In area2
                If X = y Then
                    y.Offset(0, 1) = Format(X.Offset(0, 1), "mm/dd/yy")
                     y.Offset(0, 2) = X.Offset(0, 2)
                End If
            Next
        Next
        Set area1 = Nothing
        Set area2 = Nothing
        fine = Timer
        Application.ScreenUpdating = True
        Application.Calculation = True
        MsgBox "Tempo di esecuzione: " & fine - inizio
    End Sub

    FILE DA SCARICARE
    Quando si scartano tutte le ipotesi possibili, quella che resta, anche se può sembrare improbabile, non può che essere quella giusta!

  2. #2
    L'avatar di GiuseppeMN
    Clicca e Apri
    Data Registrazione
    Oct 2015
    Località
    Mantova
    Messaggi
    864
    Versione Office
    2000 - 2013
    Likes ricevuti
    285
    Likes dati
    160

    Re: vba con o senza "formule" ... vantaggi, velocità di esecuzione

    Buona giornata, Ges;
    quando è possibile preferisco utilizzare Funzioni del tipo:
    Codice: 
    Application.WorksheetFunction.
    Questo tipo di gestione, a mio modesto parere presenta diversi vantaggi:
    - Dimensione del File più contenuta
    - Tempi di ricalcolo praticamente nulli
    Ovviamente questo tipo di scelta andrà ponderata molto attentamente ogni volta che si debba decidere quale soluzione adottare.
    Voglio dire, se le Formule sono in quantità ragionevole e il risultato varia in funzione del variare dei Valori di alcune celle (ad esempio la Funzione CERCA.VERT legata ai Valori variabili delle Celle di riferimento), sono propenso ad utilizzare la Funzione VBA:
    Codice: 
    =VLOOKUP
    Questo evita di dover lanciare continuamente il Codice VBA per aggiornare i Valori assoluti resi da una soluzione senza Formule.

    Il Tuo Esempio praticamente non utilizza Formule, ma, Prova pensare di dover scegliere una soluzione del Tipo:
    in Cella "B2":
    Codice: 
    Range("B2") = "=VLOOKUP(RC1,Foglio1!RC1:R[9999]C3,2,0)"
    che corrisponde a:
    Codice: 
    =CERCA.VERT($A2;Foglio1!$A2:$C10001;2;0)
    in Cella "C2":
    Codice: 
    Range("C2") = "=VLOOKUP(RC1,Foglio1!RC1:R[9999]C3,3,0)"
    che corrisponde a:
    Codice: 
    =CERCA.VERT($A2;Foglio1!$A2:$C10001;3;0)
    Formule da Copiare fino alla Riga 10001
    Le dimensioni del Tuo File 485 KB a 601 KB; inoltre i tempi di ricalcolo ovviamente si allungherebbero.

    Queste, sono solo le considerazioni di un neofita di VBA, ma, tant'è


    A disposizione.

    Buon Lavoro e buona serata.

    Giuseppe
    Windows XP Excel 2000 - Windows 10 Excel 2013

  3. #3

    L'avatar di scossa
    Clicca e Apri
    Data Registrazione
    Jul 2015
    Località
    Verona Provincia
    Età
    57
    Messaggi
    1017
    Versione Office
    .
    Likes ricevuti
    366
    Likes dati
    0

    Re: vba con o senza "formule" ... vantaggi, velocità di esecuzione

    @ges: potresti allegare il file anche sul forum oltre che su un sito esterno, grazie.

    ----------Post unito in automatico----------

    @ges: potresti allegare il file anche sul forum oltre che su un sito esterno, grazie.
    Bye!
    scossa
    scossa's web site
    ___
    Se tu hai una mela, e io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno. Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)

  4. #4
    L'avatar di Gerardo Zuccalà
    Clicca e Apri
    Data Registrazione
    May 2015
    Località
    Milano, Italy
    Età
    49
    Messaggi
    4916
    Versione Office
    2013
    Likes ricevuti
    1117
    Likes dati
    1126

    Re: vba con o senza "formule" ... vantaggi, velocità di esecuzione

    Ciao Scossa
    Rispondo io per ges, credo che abbia messo un link esterno perche è un file con dimensioni di 518 kb al di sopra del limite massimo consentito per questo forum cioè 400 kb
    Lo so, Lo so dovrei aumentare la dimensione ma lo spazio nei server è sempre più risicato
    Un Saluto

  5. #5

    L'avatar di ges
    Clicca e Apri
    Data Registrazione
    Jun 2015
    Località
    Como
    Età
    53
    Messaggi
    7149
    Versione Office
    2011MAC 2016WIN
    Likes ricevuti
    2064
    Likes dati
    1300

    Re: vba con o senza "formule" ... vantaggi, velocità di esecuzione

    Citazione Originariamente Scritto da scossa Visualizza Messaggio
    @ges: potresti allegare il file anche sul forum oltre che su un sito esterno, grazie.
    Ciao scossa, l'avevo caricato su un sito esterno poichè era superiore a 400kb, adesso ho ridotto i dati da 10000 righe a 5000 righe e lo posso allegare qui.

    ----------Post unito in automatico----------

    Ciao Gerardo, come ho scritto sopra ho ridotto i dati a 5000 righe, il tempo di esecuzione ora è di circa 2,4 secondi.
    Ma l'argomento resta sempre valido.
    File Allegati File Allegati
    Quando si scartano tutte le ipotesi possibili, quella che resta, anche se può sembrare improbabile, non può che essere quella giusta!

  6. #6

    L'avatar di scossa
    Clicca e Apri
    Data Registrazione
    Jul 2015
    Località
    Verona Provincia
    Età
    57
    Messaggi
    1017
    Versione Office
    .
    Likes ricevuti
    366
    Likes dati
    0

    Re: vba con o senza "formule" ... vantaggi, velocità di esecuzione

    Citazione Originariamente Scritto da ges Visualizza Messaggio
    A titolo di esempio, ho scritto due routine che hanno la funzione "lato formule" di INDICE ... CONFRONTA che devono lavorare su un range di 10.000 righe ... come tempo entrambi impiegano circa 7 secondi... e così non vedo vantaggi o svantaggi.
    Guarda che hai assegnato ad entrambi i pulsanti la stessa sub Ordina(), ci credo che ti sono sembrate uguali!
    Bye!
    scossa
    scossa's web site
    ___
    Se tu hai una mela, e io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno. Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)

  7. #7

    L'avatar di Rubik72
    Clicca e Apri
    Data Registrazione
    Dec 2015
    Località
    Cosenza
    Età
    45
    Messaggi
    2809
    Versione Office
    Excel 2013
    Likes ricevuti
    1020
    Likes dati
    979

    Re: vba con o senza "formule" ... vantaggi, velocità di esecuzione

    Citazione Originariamente Scritto da scossa Visualizza Messaggio
    Guarda che hai assegnato ad entrambi i pulsanti la stessa sub Ordina(), ci credo che ti sono sembrate uguali!
    Infatti la macro confronta() con i 2 cicli For Each...Next impiega mooolto di più (circa 3 minuti) anche intervenendo con l'uscita dal ciclo anticipata:
    Codice: 
    [...]
        For Each X In area1
            For Each y In area2
                If X = y Then
                    y.Offset(0, 1) = Format(X.Offset(0, 1), "mm/dd/yy")
                    y.Offset(0, 2) = X.Offset(0, 2)
                    Exit For
                End If
            Next
        Next
    [...]

  8. #8

    L'avatar di ges
    Clicca e Apri
    Data Registrazione
    Jun 2015
    Località
    Como
    Età
    53
    Messaggi
    7149
    Versione Office
    2011MAC 2016WIN
    Likes ricevuti
    2064
    Likes dati
    1300

    Re: vba con o senza "formule" ... vantaggi, velocità di esecuzione

    Citazione Originariamente Scritto da scossa Visualizza Messaggio
    Guarda che hai assegnato ad entrambi i pulsanti la stessa sub Ordina(), ci credo che ti sono sembrate uguali!
    Citazione Originariamente Scritto da Rubik72 Visualizza Messaggio
    Infatti la macro confronta() con i 2 cicli For Each...Next impiega mooolto di più (circa 3 minuti) anche intervenendo con l'uscita dal ciclo anticipata:
    ..
    Vuol dire che mi sono rimbambito del tutto!!!

    Quindi le cosa sono due: o le formule sono più veloci oppure ho usato un metodo di ciclazione lento.

    ----------Post unito in automatico----------

    Continuando le mie prove di oggi, pare che il CERCA.VERTICALE sia più veloce dell'INDICE...CONFRONTA

    7,4 secondi INDICE..CONFRONTA
    2,4 secondi CERCA.VERTICALE

    Codice: 
    Sub CERCA_VERTICALE()
    Dim uRiga As Long, y As Long, inizio As Double, fine As Double
    Application.ScreenUpdating = False
    inizio = Timer
    On Error Resume Next
    uRiga = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
    For y = 2 To uRiga
        Sheets(2).Cells(y, 2) = Application.WorksheetFunction.VLookup(Sheets(2).Cells(y, 1), Sheets(1).Range("A2" & ":C" & uRiga), 2)
        Sheets(2).Cells(y, 3) = Application.WorksheetFunction.VLookup(Sheets(2).Cells(y, 1), Sheets(1).Range("A2" & ":C" & uRiga), 3)
            Next
    fine = Timer
    Application.ScreenUpdating = True
    MsgBox "Tempo di esecuzione: " & fine - inizio
    End Sub
    Quando si scartano tutte le ipotesi possibili, quella che resta, anche se può sembrare improbabile, non può che essere quella giusta!

  9. I seguenti utenti hanno dato un "Like"


  10. #9

    L'avatar di cromagno
    Clicca e Apri
    Data Registrazione
    Aug 2015
    Località
    Sardegna
    Età
    37
    Messaggi
    4020
    Versione Office
    2013
    Likes ricevuti
    1228
    Likes dati
    923

    Re: vba con o senza "formule" ... vantaggi, velocità di esecuzione

    Ciao a tutti,

    @ges
    io raramente uso le formule nei codici (a meno che non le trovi particolarmente pratiche), però, in caso di molti dati, le operazioni principali le faccio con delle matrici (direttamente nel codice quindi) e alla fine copio i valori della matrice ottenuta nel foglio.

    Codice: 
    Sub confronta_matrix()
    'esegui senza formula ma con matrici
    
    
    Application.ScreenUpdating = False
    Application.Calculation = False
    inizio = Timer
    Dim Matr1(), Matr2(), i As Long, j As Long
    uriga1 = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
    uriga2 = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row
    ReDim Matr1(1 To uriga1, 1 To 3)
    ReDim Matr2(1 To uriga2, 1 To 3)
    
    
    With Sheets(1)
        For i = 2 To uriga1
                Matr1(i - 1, 1) = .Cells(i, 1)
                Matr1(i - 1, 2) = .Cells(i, 2)
                Matr1(i - 1, 3) = .Cells(i, 3)
        Next i
    End With
    
    
    With Sheets(2)
        For i = 2 To uriga1
                Matr2(i - 1, 1) = .Cells(i, 1)
        Next i
    End With
    
    
    For i = 1 To uriga2 - 1
        For j = 1 To uriga1 - 1
            If Matr2(i, 1) = Matr1(j, 1) Then
                Matr2(i, 2) = Matr1(j, 2)
                Matr2(i, 3) = Matr1(j, 3)
                GoTo prossimo
            End If
        Next j
    prossimo:
    Next i
    
    
    With Sheets(2)
        For i = 2 To uriga2
            .Cells(i, 2) = Matr2(i - 1, 2)
            .Cells(i, 3) = Matr2(i - 1, 3)
        Next i
    End With
        
        
        Set area1 = Nothing
        Set area2 = Nothing
        fine = Timer
        Application.ScreenUpdating = True
        Application.Calculation = True
        MsgBox "Tempo di esecuzione: " & fine - inizio
    End Sub
    Ho aggiunto un pulsante nel tuo file per eseguire questo codice...
    File Allegati File Allegati

    "Sono le persone che nessuno immagina che possano fare certe cose, quelle che fanno cose che nessuno può immaginare."

  11. I seguenti 2 utenti hanno dato un "Like" a cromagno per questo post:


  12. #10
    L'avatar di ggratis
    Clicca e Apri
    Data Registrazione
    Sep 2015
    Località
    Lecce - Pisa
    Età
    45
    Messaggi
    737
    Versione Office
    Excel 2010
    Likes ricevuti
    257
    Likes dati
    215

    Re: vba con o senza "formule" ... vantaggi, velocità di esecuzione

    ...io mi aspetto che l'uso delle formule sia più veloce, perché mi aspetto che i guru della Microsoft che le creano utilizzino gli algoritmi più efficienti per ottenere il risultato che si propongono. Basta pensare per esempio al classico algoritmo di ordinamento, io mi aspetto che tra i tanti disponibili https://it.m.wikipedia.org/wiki/Algo...di_ordinamento loro abbiano scelto quello più efficiente ma mi aspetto anche altre ottimizzazioni nella gestione della macchina che un curioso come me non sarebbe in grado di riproporre.
    Quindi a mio avviso, sapendolo fare bisognerebbe tendere all'uso delle funzioni complesse messe a disposizione...
    Poi è anche vero che con la potenza di calcolo disponibile e le dimensioni degli "oggetti" con cui spesso si lavora, le differenze possano risultare irrilevanti...
    Saluti
    GG

    Inviato dal mio GT-I9105P utilizzando Tapatalk

  13. #11

    L'avatar di ges
    Clicca e Apri
    Data Registrazione
    Jun 2015
    Località
    Como
    Età
    53
    Messaggi
    7149
    Versione Office
    2011MAC 2016WIN
    Likes ricevuti
    2064
    Likes dati
    1300

    Re: vba con o senza "formule" ... vantaggi, velocità di esecuzione

    Citazione Originariamente Scritto da cromagno Visualizza Messaggio
    ..

    @ges
    io raramente uso le formule nei codici (a meno che non le trovi particolarmente pratiche), però, in caso di molti dati, le operazioni principali le faccio con delle matrici (direttamente nel codice quindi) e alla fine copio i valori della matrice ottenuta nel foglio.

    ....
    Ciao Cromagno,
    ottima soluzione anche la tua - però salvo miei errori - l'ho provata nel file con 10.000 records ed è certamente più veloce del mio ciclo ma tra le altre risulta la più lunga come tempo di esecuzione circa 8 secondi contro i 7 dell'Indice/confronta e i 2,5 del Cerca Verticale.
    Quando si scartano tutte le ipotesi possibili, quella che resta, anche se può sembrare improbabile, non può che essere quella giusta!

  14. #12

    L'avatar di Rubik72
    Clicca e Apri
    Data Registrazione
    Dec 2015
    Località
    Cosenza
    Età
    45
    Messaggi
    2809
    Versione Office
    Excel 2013
    Likes ricevuti
    1020
    Likes dati
    979

    Re: vba con o senza "formule" ... vantaggi, velocità di esecuzione

    Dopo vari tentativi sono arrivato alla conclusione che la routine di @ges con le formule VBA è la più veloce, segue la routine di @Cromagno con le matrici e due cicli.
    Ma seguendo una delle tante lezioni di @Scossa pensavo che la mia fosse pari o più veloce di quella di @Cromagno, invece impiega il triplo del tempo. Ecco la routine:
    Codice: 
    Sub Confronta_Array()
    'by Rubik
    Dim aNome1 As Variant
    Dim aData1 As Variant
    Dim aValore1 As Variant
    Dim aNome2 As Variant
    Dim aData2 As Variant
    Dim aValore2 As Variant
    Dim sA As String
    Dim sB As String
    Dim inizio As Single
    Dim fine As Single
    Dim uRiga As Long
    Dim rngA As Range
    Dim rngB As Range
    Dim rngC As Range
    Dim rngD As Range
    Dim i As Long
    Dim j As Long
    
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    inizio = Timer
    
    
    Set rngA = Foglio1.Range(Foglio1.Cells(2, 1), Foglio1.Cells(Rows.Count, 1).End(xlUp))
    Set rngB = Foglio1.Range(Foglio1.Cells(2, 2), Foglio1.Cells(Rows.Count, 2).End(xlUp))
    Set rngC = Foglio1.Range(Foglio1.Cells(2, 3), Foglio1.Cells(Rows.Count, 3).End(xlUp))
    
    
    Set rngD = Foglio2.Range(Foglio2.Cells(2, 1), Foglio2.Cells(Rows.Count, 1).End(xlUp))
    
    
    aNome1 = Application.Transpose(rngA)
    aData1 = Application.Transpose(rngB)
    aValore1 = Application.Transpose(rngC)
    
    
    aNome2 = Application.Transpose(rngD)
    
    
    uRiga = Foglio2.Cells(Rows.Count, 1).End(xlUp).Row
    
    
    For i = 1 To UBound(aNome1)
        For j = 1 To UBound(aNome2)
            If aNome2(i) = aNome1(j) Then
                sA = sA & aData1(j) & "#"
                sB = sB & aValore1(j) & "#"
                GoTo prossimo
            End If
        Next j
    prossimo:
    Next i
    
    
    With Foglio2
        aData2 = Split(sA, "#")
        .Range("B2:B" & uRiga) = Application.Transpose(aData2)
        aValore2 = Split(sB, "#")
        .Range("c2:c" & uRiga) = Application.Transpose(aValore2)
    End With
        
        fine = Timer
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
        MsgBox "Tempo di esecuzione: " & fine - inizio
    End Sub
    Attendo con ansia l'intervento di @Scossa


    P.S. Mi sostituisco un attimo a @Scossa.
    @Cromagno e @ges le volete dichiarare o no queste variabili?
    Ritorno subito in me

  15. #13

    L'avatar di ges
    Clicca e Apri
    Data Registrazione
    Jun 2015
    Località
    Como
    Età
    53
    Messaggi
    7149
    Versione Office
    2011MAC 2016WIN
    Likes ricevuti
    2064
    Likes dati
    1300

    Re: vba con o senza "formule" ... vantaggi, velocità di esecuzione

    Citazione Originariamente Scritto da Rubik72 Visualizza Messaggio
    ......
    @Cromagno e @ges le volete dichiarare o no queste variabili?
    ...
    Al post 8 le ho dichiarate!!! (dopo che ho visto che c'era scossa )
    Quando si scartano tutte le ipotesi possibili, quella che resta, anche se può sembrare improbabile, non può che essere quella giusta!

Discussioni Simili

  1. "codice" non si può leggere senza perder la vista.
    Di dracoscrigno nel forum Domande su Excel VBA e MACRO
    Risposte: 50
    Ultimo Messaggio: 26/03/17, 10:35
  2. [Risolto] Ordinare con la funzione "piccolo"..ma "portandosi dietro" anche altri dati affiancati
    Di mauri73 nel forum Domande su Excel in generale
    Risposte: 13
    Ultimo Messaggio: 05/02/17, 14:57
  3. [Risolto] Come leggere la seguente formula =SE.ERRORE(INDIRETTO("'"&A1&"'!K1";1);""))
    Di sitolofi nel forum Domande su Excel in generale
    Risposte: 5
    Ultimo Messaggio: 20/12/16, 14:54
  4. [Risolto] Formule "Rimpiazza" e "Sostituisci" per i numeri
    Di giteti58 nel forum Domande su Excel in generale
    Risposte: 4
    Ultimo Messaggio: 16/10/16, 17:45
  5. "Run Time Error" durante l'esecuzione di una macro
    Di Giuseppe1810 nel forum Domande su Excel VBA e MACRO
    Risposte: 2
    Ultimo Messaggio: 20/03/16, 12:47

Permessi di Scrittura

  • Tu non puoi inviare nuove discussioni
  • Tu non puoi inviare risposte
  • Tu non puoi inviare allegati
  • Tu non puoi modificare i tuoi messaggi
  •