excel-vba => Best practice VBA

Osservazioni

Li conosciamo tutti, ma queste pratiche sono molto meno ovvie a chi inizia a programmare in VBA.

Utilizzare SEMPRE "Option Explicit"

Nella finestra Editor VBA, dal menu Strumenti seleziona "Opzioni":

Quindi nella scheda "Editor", assicurati che "Richiedi dichiarazione di variabili" sia spuntato:

Selezionando questa opzione verrà automaticamente aggiunta l' Option Explicit nella parte superiore di ogni modulo VBA.

Piccola nota: questo è vero per i moduli, i moduli di classe, ecc. Che non sono stati aperti finora. Quindi, se hai già dato un'occhiata ad esempio al codice di Sheet1 prima di attivare l'opzione "Require Variable Declaration", Option Explicit non verrà aggiunta!

Option Explicit richiede che ogni variabile debba essere definita prima dell'uso, ad esempio con un'istruzione Dim . Senza Option Explicit abilitato, il compilatore VBA assumerà qualsiasi parola non riconosciuta come una nuova variabile del tipo Variant , causando bug estremamente difficili da individuare in relazione agli errori tipografici. Con Option Explicit abilitata, qualsiasi parola non riconosciuta causerà un errore di compilazione, che indica la linea offendente.

Esempio :

Se si esegue il seguente codice:

Sub Test()
  my_variable = 12
  MsgBox "My Variable is : " & myvariable
End Sub

Riceverai il seguente messaggio:

Hai fatto un errore scrivendo myvariable anziché my_variable , quindi la finestra del messaggio mostra una variabile vuota. Se si utilizza Option Explicit , questo errore non è possibile perché si otterrà un messaggio di errore di compilazione che indica il problema.

Ora se aggiungi la dichiarazione corretta:

Sub Test()
  Dim my_variable As Integer
  my_variable = 12
  MsgBox "My Variable is : " & myvariable
End Sub

myvariable un messaggio di errore che indica precisamente l'errore con myvariable :

Nota su Option Explicit e Arrays ( Dichiarazione di una matrice dinamica ):
È possibile utilizzare l'istruzione ReDim per dichiarare implicitamente un array all'interno di una procedura.
Fare attenzione a non digitare errori ortografici nel nome dell'array quando si utilizza l'istruzione ReDim
Anche se l'istruzione Option Explicit è inclusa nel modulo, verrà creato un nuovo array
Dim arr() as Long

ReDim ar() 'creates new array "ar" - "ReDim ar()" acts like "Dim ar()"

Lavora con le matrici, non con le gamme

Blog di Office: best practice per la codifica delle prestazioni di Excel VBA

Spesso, le migliori prestazioni si ottengono evitando l'uso di Range il più possibile. In questo esempio si legge in un intero Range oggetto in una matrice quadrata ciascun numero nella matrice, e quindi restituire l'array alla Range . Questo accede a Range solo due volte, mentre un loop accederà 20 volte per la lettura / scrittura.

Option Explicit
Sub WorkWithArrayExample()
    
Dim DataRange As Variant
Dim Irow As Long
Dim Icol As Integer
DataRange = ActiveSheet.Range("A1:A10").Value ' read all the values at once from the Excel grid, put into an array

For Irow = LBound(DataRange,1) To UBound(DataRange, 1) ' Get the number of rows.
  For Icol = LBound(DataRange,2) To UBound(DataRange, 2) ' Get the number of columns.
    DataRange(Irow, Icol) = DataRange(Irow, Icol) * DataRange(Irow, Icol) ' cell.value^2
  Next Icol
Next Irow
ActiveSheet.Range("A1:A10").Value = DataRange ' writes all the results back to the range at once
    
End Sub

Ulteriori suggerimenti e informazioni con esempi temporizzati possono essere trovati negli UDF di VBA efficienti di Charles Williams (parte 1) e in altri articoli della serie .

Utilizzare le costanti VB quando disponibili

If MsgBox("Click OK") = vbOK Then

può essere usato al posto di

If MsgBox("Click OK") = 1 Then

al fine di migliorare la leggibilità.

Utilizzare il Visualizzatore oggetti per trovare le costanti VB disponibili. Visualizza → Browser oggetti o F2 da VB Editor.

Inserisci la classe per la ricerca

Visualizza i membri disponibili

Usa la denominazione delle variabili descrittive

I nomi e la struttura descrittivi nel codice aiutano a rendere inutili i commenti

Dim ductWidth  As Double
Dim ductHeight As Double
Dim ductArea   As Double

ductArea = ductWidth * ductHeight

è meglio di

Dim a, w, h

a = w * h

Ciò è particolarmente utile quando si copiano dati da un luogo a un altro, che si tratti di una cella, un intervallo, un foglio di lavoro o una cartella di lavoro. Aiutati usando nomi come questi:

Dim myWB As Workbook
Dim srcWS As Worksheet
Dim destWS As Worksheet
Dim srcData As Range
Dim destData As Range

Set myWB = ActiveWorkbook
Set srcWS = myWB.Sheets("Sheet1")
Set destWS = myWB.Sheets("Sheet2")
Set srcData = srcWS.Range("A1:A10")
Set destData = destWS.Range("B11:B20")
destData = srcData

Se dichiari più variabili in una riga, assicurati di specificare un tipo per ogni variabile come:

Dim ductWidth As Double, ductHeight As Double, ductArea As Double

Quanto segue dichiarerà solo l'ultima variabile e le prime rimarranno Variant :

Dim ductWidth, ductHeight, ductArea As Double

Gestione degli errori

Una buona gestione degli errori impedisce agli utenti finali di visualizzare errori di runtime di VBA e aiuta lo sviluppatore a diagnosticare e correggere facilmente gli errori.

Esistono tre metodi principali di gestione degli errori in VBA, due dei quali dovrebbero essere evitati per i programmi distribuiti, a meno che non siano specificatamente richiesti nel codice.

On Error GoTo 0 'Avoid using

On Error Resume Next 'Avoid using

Preferisci usare:

On Error GoTo <line> 'Prefer using

On Error GoTo 0

Se nel tuo codice non è impostata la gestione degli errori, On Error GoTo 0 è il gestore degli errori predefinito. In questa modalità, qualsiasi errore di runtime avvierà il tipico messaggio di errore VBA, consentendo di terminare il codice o accedere alla modalità di debug , identificando la sorgente. Durante la scrittura del codice, questo metodo è il più semplice e utile, ma dovrebbe essere sempre evitato per il codice distribuito agli utenti finali, poiché questo metodo è molto sgradevole e difficile da comprendere per gli utenti finali.

In caso di errore, riprendi

On Error Resume Next farà in modo che VBA ignori gli eventuali errori generati in fase di esecuzione per tutte le righe successive alla chiamata di errore fino a quando il gestore degli errori non è stato modificato. In casi molto specifici, questa linea può essere utile, ma dovrebbe essere evitata al di fuori di questi casi. Ad esempio, quando si avvia un programma separato da una macro di Excel, la chiamata On Error Resume Next può essere utile se non si è certi che il programma sia già aperto o meno:

'In this example, we open an instance of Powerpoint using the On Error Resume Next call
Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide

'Open PPT if not running, otherwise select active instance
On Error Resume Next
Set PPApp = GetObject(, "PowerPoint.Application")
On Error GoTo ErrHandler
If PPApp Is Nothing Then
    'Open PowerPoint
    Set PPApp = CreateObject("PowerPoint.Application")
    PPApp.Visible = True
End If

Se non avessimo utilizzato la chiamata On Error Resume Next e l'applicazione Powerpoint non fosse già aperta, il metodo GetObject genererebbe un errore. Pertanto, On Error Resume Next stato necessario per evitare di creare due istanze dell'applicazione.

Nota: è anche consigliabile ripristinare immediatamente il gestore degli errori non appena non è più necessaria la chiamata On Error Resume Next

On Error GoTo <line>

Questo metodo di gestione degli errori è consigliato per tutto il codice che viene distribuito ad altri utenti. Ciò consente al programmatore di controllare esattamente come VBA gestisce un errore inviando il codice alla linea specificata. Il tag può essere riempito con qualsiasi stringa (comprese le stringhe numeriche) e invierà il codice alla stringa corrispondente seguita da due punti. È possibile utilizzare più blocchi di gestione degli errori effettuando chiamate diverse di On Error GoTo <line> . La subroutine di seguito mostra la sintassi di una chiamata On Error GoTo <line> .

Nota: è essenziale che la riga Exit Sub sia posizionata sopra il primo gestore di errori e prima di ogni gestore di errori successivo per impedire al codice di avanzare naturalmente nel blocco senza che venga chiamato un errore. Pertanto, è buona pratica per la funzione e la leggibilità posizionare i gestori degli errori alla fine di un blocco di codice.

Sub YourMethodName()
    On Error GoTo errorHandler
    ' Insert code here
    On Error GoTo secondErrorHandler

    Exit Sub 'The exit sub line is essential, as the code will otherwise
             'continue running into the error handling block, likely causing an error

errorHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
        VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error"
    Exit Sub

secondErrorHandler:
    If Err.Number = 424 Then 'Object not found error (purely for illustration)
        Application.ScreenUpdating = True
        Application.EnableEvents = True
        Exit Sub
    Else
        MsgBox "Error " & Err.Number & ": " & Err.Desctription
        Application.ScreenUpdating = True
        Application.EnableEvents = True   
        Exit Sub
    End If      
    Exit Sub

End Sub

Se esci dal metodo con il codice di gestione degli errori, assicurati di pulire:

Annulla tutto ciò che è parzialmente completato
Chiudi i file
Ripristina l'aggiornamento della schermata
Ripristina la modalità di calcolo
Reimposta eventi
Ripristina il puntatore del mouse
Chiama il metodo di scaricamento su istanze di oggetti che persistono dopo End Sub
Reimposta la barra di stato

Documenta il tuo lavoro

È buona norma documentare il proprio lavoro per un uso successivo, specialmente se si sta codificando per un carico di lavoro dinamico. Buoni commenti dovrebbero spiegare perché il codice sta facendo qualcosa, non quello che sta facendo il codice.

Function Bonus(EmployeeTitle as String) as Double
    If EmployeeTitle = "Sales" Then
        Bonus = 0    'Sales representatives receive commission instead of a bonus
    Else
        Bonus = .10
    End If
End Function

Se il tuo codice è così oscuro da richiedere commenti per spiegare cosa sta facendo, considera di riscriverlo per essere più chiaro invece di spiegarlo attraverso i commenti. Ad esempio, invece di:

Sub CopySalesNumbers
    Dim IncludeWeekends as Boolean
    
    'Boolean values can be evaluated as an integer, -1 for True, 0 for False.
    'This is used here to adjust the range from 5 to 7 rows if including weekends.
    Range("A1:A" & 5 - (IncludeWeekends * 2)).Copy
    Range("B1").PasteSpecial
End Sub

Chiarire il codice per essere più facile da seguire, come ad esempio:

Sub CopySalesNumbers
    Dim IncludeWeekends as Boolean
    Dim DaysinWeek as Integer
    
    If IncludeWeekends Then
        DaysinWeek = 7
    Else
        DaysinWeek = 5
    End If
    Range("A1:A" & DaysinWeek).Copy
    Range("B1").PasteSpecial
End Sub

Disattiva le proprietà durante l'esecuzione della macro

È una buona pratica in qualsiasi linguaggio di programmazione per evitare l'ottimizzazione prematura. Tuttavia, se i test rivelano che il tuo codice è in esecuzione troppo lentamente, potresti guadagnare un po 'di velocità disattivando alcune delle proprietà dell'applicazione mentre è in esecuzione. Aggiungi questo codice a un modulo standard:

Public Sub SpeedUp( _
    SpeedUpOn As Boolean, _
    Optional xlCalc as XlCalculation = xlCalculationAutomatic _
)
    With Application
        If SpeedUpOn Then
            .ScreenUpdating = False
            .Calculation = xlCalculationManual
            .EnableEvents = False
            .DisplayStatusBar = False 'in case you are not showing any messages
            ActiveSheet.DisplayPageBreaks = False 'note this is a sheet-level setting
        Else
            .ScreenUpdating = True
            .Calculation = xlCalc
            .EnableEvents = True
            .DisplayStatusBar = True
            ActiveSheet.DisplayPageBreaks = True
        End If
    End With
End Sub

Ulteriori informazioni sul blog di Office - Best practice per la codifica delle prestazioni di Excel VBA

E basta chiamarlo all'inizio e alla fine dei macro:

Public Sub SomeMacro
    'store the initial "calculation" state
    Dim xlCalc As XlCalculation
    xlCalc = Application.Calculation

    SpeedUp True

    'code here ...

    'by giving the second argument the initial "calculation" state is restored
    'otherwise it is set to 'xlCalculationAutomatic'
    SpeedUp False, xlCalc
End Sub

Mentre questi possono essere in gran parte considerati "miglioramenti" per le normali procedure Public Sub , la disabilitazione della gestione degli eventi con Application.EnableEvents = False deve essere considerata obbligatoria per le macro di eventi privati Worksheet_Change e Workbook_SheetChange che modificano i valori su uno o più fogli di lavoro. La mancata disabilitazione dei trigger di evento farà sì che la macro dell'evento ricorra in modo ricorsivo su se stessa quando un valore cambia e può portare a una cartella di lavoro "congelata". Ricordarsi di riattivare gli eventi prima di lasciare la macro dell'evento, possibilmente tramite un gestore degli errori di "uscita sicura".

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        On Error GoTo bm_Safe_Exit
        Application.EnableEvents = False
        
        'code that may change a value on the worksheet goes here
        
    End If
bm_Safe_Exit:
    Application.EnableEvents = True
End Sub

Un avvertimento: se disabilitare queste impostazioni migliorerà il tempo di esecuzione, potrebbe rendere molto più difficile il debug dell'applicazione. Se il tuo codice non funziona correttamente, commenta la chiamata SpeedUp True finché non indovina il problema.

Ciò è particolarmente importante se si scrivono le celle in un foglio di lavoro e quindi si leggono i risultati calcolati dalle funzioni del foglio di lavoro poiché xlCalculationManual impedisce il calcolo della cartella di lavoro. Per aggirare questo problema senza disabilitare SpeedUp , è possibile includere Application.Calculate per eseguire un calcolo in punti specifici.

NOTA: poiché queste sono proprietà Application stessa, è necessario assicurarsi che siano nuovamente abilitate prima che la macro esca. Ciò rende particolarmente importante l'utilizzo di gestori di errori e per evitare più punti di uscita (ad es. End o Unload Me ).

Con la gestione degli errori:

Public Sub SomeMacro()
    'store the initial "calculation" state
    Dim xlCalc As XlCalculation
    xlCalc = Application.Calculation

    On Error GoTo Handler
    SpeedUp True
    
    'code here ...
    i = 1 / 0
CleanExit:
    SpeedUp False, xlCalc
    Exit Sub
Handler:
    'handle error
    Resume CleanExit
End Sub

Evitare l'uso di ActiveCell o ActiveSheet in Excel

L'utilizzo di ActiveCell o ActiveSheet può essere fonte di errori se (per qualsiasi motivo) il codice viene eseguito nel posto sbagliato.

ActiveCell.Value = "Hello" 
'will place "Hello" in the cell that is currently selected
Cells(1, 1).Value = "Hello" 
'will always place "Hello" in A1 of the currently selected sheet

ActiveSheet.Cells(1, 1).Value = "Hello" 
'will place "Hello" in A1 of the currently selected sheet
Sheets("MySheetName").Cells(1, 1).Value = "Hello" 
'will always place "Hello" in A1 of the sheet named "MySheetName"

L'uso di Active* può creare problemi nelle macro di lunga durata se l'utente si annoia e fa clic su un altro foglio di lavoro o apre un'altra cartella di lavoro.
Può creare problemi se il codice si apre o crea un'altra cartella di lavoro.
Può creare problemi se il tuo codice utilizza Sheets("MyOtherSheet").Select e hai dimenticato il foglio in cui ti Sheets("MyOtherSheet").Select prima di iniziare a leggere o scrivere su di esso.

Non assumere mai il foglio di lavoro

Anche quando tutto il tuo lavoro è diretto a un singolo foglio di lavoro, è comunque molto utile specificare esplicitamente il foglio di lavoro nel tuo codice. Questa abitudine rende molto più facile espandere il codice in un secondo momento o sollevare parti (o tutto) di un Sub o di una Function da riutilizzare altrove. Molti sviluppatori stabiliscono l'abitudine di (ri) utilizzare lo stesso nome di variabile locale per un foglio di lavoro nel loro codice, rendendo il riutilizzo di quel codice ancora più semplice.

Ad esempio, il codice seguente è ambiguo, ma funziona! - finché lo sviluppatore non si attiva o non passa a un altro foglio di lavoro:

Option Explicit
Sub ShowTheTime()
    '--- displays the current time and date in cell A1 on the worksheet
    Cells(1, 1).Value = Now()  ' don't refer to Cells without a sheet reference!
End Sub

Se Sheet1 è attivo, la cella A1 su Sheet1 verrà riempita con la data e l'ora correnti. Ma se l'utente cambia i fogli di lavoro per qualsiasi motivo, il codice aggiornerà qualunque sia il foglio di lavoro attualmente attivo. Il foglio di lavoro di destinazione è ambiguo.

La migliore pratica è identificare sempre il foglio di lavoro a cui si riferisce il codice:

Option Explicit
Sub ShowTheTime()
    '--- displays the current time and date in cell A1 on the worksheet
    Dim myWB As Workbook
    Set myWB = ThisWorkbook
    Dim timestampSH As Worksheet
    Set timestampSH = myWB.Sheets("Sheet1")
    timestampSH.Cells(1, 1).Value = Now()
End Sub

Il codice sopra riportato è chiaro nell'identificare sia la cartella di lavoro che il foglio di lavoro. Anche se può sembrare eccessivo, la creazione di una buona abitudine riguardante i riferimenti target ti farà risparmiare problemi futuri.

Evitare l'uso di SELECT o ACTIVATE

E 'molto raro che tu abbia mai desidera utilizzare Select o Activate nel codice, ma alcuni metodi di Excel richiedono un foglio di lavoro o cartella di lavoro da attivare prima che funzioneranno come previsto.

Se stai appena iniziando a imparare VBA, ti verrà spesso suggerito di registrare le tue azioni usando il registratore di macro, quindi vai a guardare il codice. Ad esempio, ho registrato le azioni eseguite per immettere un valore nella cella D3 su Sheet2 e il codice macro ha il seguente aspetto:

Option Explicit
Sub Macro1()
'
' Macro1 Macro
'

'
    Sheets("Sheet2").Select
    Range("D3").Select
    ActiveCell.FormulaR1C1 = "3.1415"   '(see **note below)
    Range("D4").Select
End Sub

Ricorda però che il registratore di macro crea una linea di codice per OGNI delle tue azioni (utente). Ciò include il fare clic sulla scheda del foglio di lavoro per selezionare Foglio2 ( Sheets("Sheet2").Select ), facendo clic sulla cella D3 prima di immettere il valore ( Range("D3").Select ) e utilizzando il tasto Invio (che è effettivamente " selezionando "la cella sotto la cella attualmente selezionata: Range("D4").Select ).

Ci sono più problemi nell'uso. .Select qui:

Il foglio di lavoro non è sempre specificato. Ciò accade se non si cambiano i fogli di lavoro durante la registrazione e ciò significa che il codice produrrà risultati diversi per diversi fogli di lavoro attivi.
.Select() è lento. Anche se Application.ScreenUpdating è impostato su False , si tratta di un'operazione non necessaria da elaborare.
.Select() è indisciplinato. Se Application.ScreenUpdating viene lasciato su True , Excel selezionerà effettivamente le celle, il foglio di lavoro, il modulo ... con qualunque cosa stai lavorando. Questo è stressante per gli occhi e davvero sgradevole da guardare.
.Select() attiverà gli ascoltatori. Questo è già un po 'avanzato, ma a meno che non funzionino, verranno attivate funzioni come Worksheet_SelectionChange() .

Quando si codifica in VBA, tutte le azioni di "digitazione" (cioè le istruzioni Select ) non sono più necessarie. Il tuo codice può essere ridotto a una singola istruzione per inserire il valore nella cella:

'--- GOOD
ActiveWorkbook.Sheets("Sheet2").Range("D3").Value = 3.1415

'--- BETTER
Dim myWB      As Workbook
Dim myWS      As Worksheet
Dim myCell    As Range

Set myWB = ThisWorkbook             '*** see NOTE2
Set myWS = myWB.Sheets("Sheet2")
Set myCell = myWS.Range("D3")

myCell.Value = 3.1415

(L'esempio BETTER in alto mostra l'utilizzo di variabili intermedie per separare parti diverse del riferimento di cella. L'esempio GOOD funzionerà sempre bene, ma può essere molto ingombrante in moduli di codice molto più lunghi e più difficile da eseguire il debug se uno dei riferimenti è stato digitato erroneamente. )

** NOTA: il registratore di macro fa molte ipotesi sul tipo di dati che stai inserendo, in questo caso inserendo un valore di stringa come formula per creare il valore. Il tuo codice non deve farlo e può semplicemente assegnare un valore numerico direttamente alla cella come mostrato sopra.

** NOTA2: la pratica raccomandata è di impostare la variabile della cartella di lavoro locale su ThisWorkbook invece di ActiveWorkbook (a meno che non ne abbiate esplicitamente bisogno). Il motivo è che la macro in genere richiede / utilizza risorse in qualsiasi cartella di lavoro che il codice VBA ha origine e NON guarderà al di fuori di tale cartella di lavoro, sempre, a meno che non si diriga esplicitamente il proprio codice per lavorare con un'altra cartella di lavoro. Quando in Excel sono aperte più cartelle di lavoro, ActiveWorkbook è quello con lo stato attivo che potrebbe essere diverso dalla cartella di lavoro visualizzata nell'editor VBA . Quindi pensi di essere eseguito in una cartella di lavoro quando stai davvero citando un altro. ThisWorkbook di lavoro si riferisce alla cartella di lavoro contenente il codice in esecuzione.

Definisci sempre e imposta i riferimenti a tutte le cartelle di lavoro e fogli

Quando si lavora con più cartelle di lavoro aperte, ognuna delle quali può avere più fogli, è più sicuro definire e impostare il riferimento a tutte le cartelle di lavoro e fogli di lavoro.

Non fare affidamento su ActiveWorkbook o ActiveSheet in quanto potrebbero essere modificati dall'utente.

Il seguente illustra come copiare un range da foglio “RAW_DATA” nella cartella di lavoro “Data.xlsx” alla scheda “Refined_Data” nella cartella di lavoro “Results.xlsx”.

La procedura mostra anche come copiare e incollare senza utilizzare il metodo Select .

Option Explicit

Sub CopyRanges_BetweenShts()

    
    Dim wbSrc                           As Workbook
    Dim wbDest                          As Workbook
    Dim shtCopy                         As Worksheet
    Dim shtPaste                        As Worksheet
    
    ' set reference to all workbooks by name, don't rely on ActiveWorkbook
    Set wbSrc = Workbooks("Data.xlsx")
    Set wbDest = Workbooks("Results.xlsx")
    
    ' set reference to all sheets by name, don't rely on ActiveSheet
    Set shtCopy = wbSrc.Sheet1 '// "Raw_Data" sheet
    Set shtPaste = wbDest.Sheet2 '// "Refined_Data") sheet
    
    ' copy range from "Data" workbook to "Results" workbook without using Select
    shtCopy.Range("A1:C10").Copy _
    Destination:=shtPaste.Range("A1")

End Sub

L'oggetto Worksheet Function viene eseguito più velocemente di un equivalente UDF

VBA è compilato in fase di esecuzione, il che ha un enorme impatto negativo sulle sue prestazioni, tutto il built-in sarà più veloce, provare a usarli.

Come esempio sto confrontando le funzioni SUM e CONTA.SE, ma puoi usare se per qualcosa che puoi risolvere con WorkSheetFunctions.

Un primo tentativo per quelli sarebbe quello di scorrere l'intervallo e processarlo cella per cella (utilizzando un intervallo):

Sub UseRange()
    Dim rng as Range
    Dim Total As Double
    Dim CountLessThan01 As Long
    
    Total = 0
    CountLessThan01 = 0
    For Each rng in Sheets(1).Range("A1:A100")
        Total = Total + rng.Value2
        If rng.Value < 0.1 Then
            CountLessThan01 = CountLessThan01 + 1
        End If
    Next rng
    Debug.Print Total & ", " & CountLessThan01
End Sub

Un miglioramento può essere quello di memorizzare i valori dell'intervallo in una matrice e elaborare ciò:

Sub UseArray()
    Dim DataToSummarize As Variant
    Dim i As Long
    Dim Total As Double
    Dim CountLessThan01 As Long
    
    DataToSummarize = Sheets(1).Range("A1:A100").Value2 'faster than .Value
    Total = 0
    CountLessThan01 = 0
    For i = 1 To 100
        Total = Total + DataToSummarize(i, 1)
        If DataToSummarize(i, 1) < 0.1 Then
            CountLessThan01 = CountLessThan01 + 1
        End If
    Next i
    Debug.Print Total & ", " & CountLessThan01
End Sub

Ma invece di scrivere qualsiasi loop puoi usare Application.Worksheetfunction che è molto utile per l'esecuzione di formule semplici:

Sub UseWorksheetFunction()
    Dim Total As Double
    Dim CountLessThan01 As Long
    
    With Application.WorksheetFunction
        Total = .Sum(Sheets(1).Range("A1:A100"))
        CountLessThan01 = .CountIf(Sheets(1).Range("A1:A100"), "<0.1")
    End With
    
    Debug.Print Total & ", " & CountLessThan01
End Sub

Oppure, per calcoli più complessi puoi persino usare Application.Evaluate :

Sub UseEvaluate()
    Dim Total As Double
    Dim CountLessThan01 As Long
    
    With Application
        Total = .Evaluate("SUM(" & Sheet1.Range("A1:A100").Address( _
            external:=True) & ")")
        CountLessThan01 = .Evaluate("COUNTIF('Sheet1'!A1:A100,""<0.1"")")
    End With
    
    Debug.Print Total & ", " & CountLessThan01
End Sub

Infine, passando sopra i Subs a 25.000 volte ciascuno, ecco il tempo medio (5 test) in millisecondi (ovviamente sarà diverso per ogni pc, ma confrontati tra loro si comportano allo stesso modo):

Funzione UseWorksheet: 2156 ms
UseArray: 2219 ms (+ 3%)
Usa valutazione: 4693 ms (+ 118%)
UseRange: 6530 ms (+ 203%)

Evita di riproporre i nomi di Proprietà o Metodi come variabili

Generalmente non è considerata la "migliore pratica" per riutilizzare i nomi riservati di Proprietà o Metodi come nome / i delle proprie procedure e variabili.

Bad Form - Mentre il seguente è (strettamente parlando) codice legale, funzionante, la ri-proposizione del metodo Find così come le proprietà Row , Column e Address possono causare problemi / conflitti con l'ambiguità del nome e sono semplicemente confuse in generale.

Option Explicit

Sub find()
    Dim row As Long, column As Long
    Dim find As String, address As Range
    
    find = "something"
    
    With ThisWorkbook.Worksheets("Sheet1").Cells
        Set address = .SpecialCells(xlCellTypeLastCell)
        row = .find(what:=find, after:=address).row        '< note .row not capitalized
        column = .find(what:=find, after:=address).column  '< note .column not capitalized
        
        Debug.Print "The first 'something' is in " & .Cells(row, column).address(0, 0)
    End With
End Sub

Forma buona - Con tutte le parole riservate rinominate in approssimazioni ravvicinate ma univoche degli originali, sono stati evitati potenziali conflitti di denominazione.

Option Explicit

Sub myFind()
    Dim rw As Long, col As Long
    Dim wht As String, lastCell As Range
    
    wht = "something"
    
    With ThisWorkbook.Worksheets("Sheet1").Cells
        Set lastCell = .SpecialCells(xlCellTypeLastCell)
        rw = .Find(What:=wht, After:=lastCell).Row         '◄ note .Find and .Row
        col = .Find(What:=wht, After:=lastCell).Column     '◄ .Find and .Column
        
        Debug.Print "The first 'something' is in " & .Cells(rw, col).Address(0, 0)
    End With
End Sub

Mentre può arrivare un momento in cui vuoi riscrivere intenzionalmente un metodo o una proprietà standard secondo le tue specifiche, quelle situazioni sono poche e lontane tra loro. Per la maggior parte, evita di riutilizzare i nomi riservati per i tuoi costrutti.

Modified text is an extract of the original Stack Overflow Documentation

Autorizzato sotto CC BY-SA 3.0

Non affiliato con Stack Overflow

excel-vba
Best practice VBA

Ricerca…