excel-vba
Ottimizzazione Excel-VBA
Ricerca…
introduzione
L'ottimizzazione VBA di Excel si riferisce anche alla codifica della migliore gestione degli errori mediante documentazione e dettagli aggiuntivi. Questo è mostrato qui.
Osservazioni
*) I numeri di riga rappresentano numeri interi, ovvero un tipo di dati a 16 bit con segno compreso nell'intervallo compreso tra -32.768 e 32.767, altrimenti viene generato un overflow. Di solito i numeri di riga vengono inseriti in passaggi di 10 su una parte del codice o tutte le procedure di un modulo nel suo insieme.
Disabilitare l'aggiornamento del foglio di lavoro
Disabilitare il calcolo del foglio di lavoro può ridurre significativamente il tempo di esecuzione della macro. Inoltre, la disattivazione di eventi, l'aggiornamento dello schermo e le interruzioni di pagina sarebbero utili. A seguito di Sub
può essere utilizzato in qualsiasi macro per questo scopo.
Sub OptimizeVBA(isOn As Boolean)
Application.Calculation = IIf(isOn, xlCalculationManual, xlCalculationAutomatic)
Application.EnableEvents = Not(isOn)
Application.ScreenUpdating = Not(isOn)
ActiveSheet.DisplayPageBreaks = Not(isOn)
End Sub
Per l'ottimizzazione segui il seguente pseudo-codice:
Sub MyCode()
OptimizeVBA True
'Your code goes here
OptimizeVBA False
End Sub
Controllo del tempo di esecuzione
Procedure diverse possono dare lo stesso risultato, ma utilizzerebbero tempi di elaborazione diversi. Per verificare quale è più veloce, è possibile utilizzare un codice come questo:
time1 = Timer
For Each iCell In MyRange
iCell = "text"
Next iCell
time2 = Timer
For i = 1 To 30
MyRange.Cells(i) = "text"
Next i
time3 = Timer
debug.print "Proc1 time: " & cStr(time2-time1)
debug.print "Proc2 time: " & cStr(time3-time2)
Private Declare PtrSafe Function getFrequency Lib "Kernel32" Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare PtrSafe Function getTickCount Lib "Kernel32" Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long
Function MicroTimer() As Double
Dim cyTicks1 As Currency
Static cyFrequency As Currency
MicroTimer = 0
If cyFrequency = 0 Then getFrequency cyFrequency 'Get frequency
getTickCount cyTicks1 'Get ticks
If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency 'Returns Seconds
End Function
Utilizzo dei blocchi
L'utilizzo con i blocchi può accelerare il processo di esecuzione di una macro. Invece di scrivere un intervallo, un nome di grafico, un foglio di lavoro, ecc. Puoi usare con blocchi come sotto;
With ActiveChart
.Parent.Width = 400
.Parent.Height = 145
.Parent.Top = 77.5 + 165 * step - replacer * 15
.Parent.Left = 5
End With
Quale è più veloce di questo:
ActiveChart.Parent.Width = 400
ActiveChart.Parent.Height = 145
ActiveChart.Parent.Top = 77.5 + 165 * step - replacer * 15
ActiveChart.Parent.Left = 5
Gli appunti:
Una volta inserito un blocco With, l'oggetto non può essere modificato. Di conseguenza, non è possibile utilizzare una singola istruzione With per influire su un numero di oggetti diversi
Non saltare dentro o fuori con i blocchi . Se vengono eseguite istruzioni in un blocco With, ma l'istruzione With o End With non viene eseguita, una variabile temporanea contenente un riferimento all'oggetto rimane in memoria finché non si esce dalla procedura
Non eseguire il ciclo all'interno di istruzioni With, in particolare se l'oggetto memorizzato nella cache viene utilizzato come iteratore
È possibile annidare le affermazioni inserendo un blocco With in un altro. Tuttavia, poiché i membri dei blocchi esterni With sono mascherati all'interno dei blocchi With interni, è necessario fornire un riferimento oggetto completo in un blocco With interno a qualsiasi membro di un oggetto in un blocco With esterno.
Esempio di annidamento:
Questo esempio utilizza l'istruzione With per eseguire una serie di istruzioni su un singolo oggetto.
L'oggetto e le sue proprietà sono nomi generici usati solo a scopo illustrativo.
With MyObject
.Height = 100 'Same as MyObject.Height = 100.
.Caption = "Hello World" 'Same as MyObject.Caption = "Hello World".
With .Font
.Color = Red 'Same as MyObject.Font.Color = Red.
.Bold = True 'Same as MyObject.Font.Bold = True.
MyObject.Height = 200 'Inner-most With refers to MyObject.Font (must be qualified
End With
End With
Ulteriori informazioni su MSDN
Cancellazione riga - Prestazioni
L'eliminazione delle righe è lenta, specialmente quando si esegue il looping delle celle e si eliminano le righe, una alla volta
Un approccio diverso utilizza un filtro automatico per nascondere le righe da eliminare
Copia l'intervallo visibile e incollalo in un nuovo foglio di lavoro
Rimuovere completamente il foglio iniziale
Con questo metodo, più file verranno eliminate, più velocemente sarà
Esempio:
Option Explicit
'Deleted rows: 775,153, Total Rows: 1,000,009, Duration: 1.87 sec
Public Sub DeleteRows()
Dim oldWs As Worksheet, newWs As Worksheet, wsName As String, ur As Range
Set oldWs = ThisWorkbook.ActiveSheet
wsName = oldWs.Name
Set ur = oldWs.Range("F2", oldWs.Cells(oldWs.Rows.Count, "F").End(xlUp))
Application.ScreenUpdating = False
Set newWs = Sheets.Add(After:=oldWs) 'Create a new WorkSheet
With ur 'Copy visible range after Autofilter (modify Criteria1 and 2 accordingly)
.AutoFilter Field:=1, Criteria1:="<>0", Operator:=xlAnd, Criteria2:="<>"
oldWs.UsedRange.Copy
End With
'Paste all visible data into the new WorkSheet (values and formats)
With newWs.Range(oldWs.UsedRange.Cells(1).Address)
.PasteSpecial xlPasteColumnWidths
.PasteSpecial xlPasteAll
newWs.Cells(1, 1).Select: newWs.Cells(1, 1).Copy
End With
With Application
.CutCopyMode = False
.DisplayAlerts = False
oldWs.Delete
.DisplayAlerts = True
.ScreenUpdating = True
End With
newWs.Name = wsName
End Sub
Disabilitazione di tutte le funzionalità di Excel Prima di eseguire macro di grandi dimensioni
Le procedure qui sotto disabiliteranno temporaneamente tutte le funzionalità di Excel a livello di WorkBook e di WorkSheet
FastWB () è un interruttore che accetta i flag On o Off
FastWS () accetta un oggetto WorkSheet facoltativo o nessuno
Se manca il parametro ws, attiva e disattiva tutte le funzionalità per tutti i fogli di lavoro nella raccolta
- È possibile utilizzare un tipo personalizzato per acquisire tutte le impostazioni prima di spegnerle
- Alla fine del processo, le impostazioni iniziali possono essere ripristinate
Public Sub FastWB(Optional ByVal opt As Boolean = True)
With Application
.Calculation = IIf(opt, xlCalculationManual, xlCalculationAutomatic)
If .DisplayAlerts <> Not opt Then .DisplayAlerts = Not opt
If .DisplayStatusBar <> Not opt Then .DisplayStatusBar = Not opt
If .EnableAnimations <> Not opt Then .EnableAnimations = Not opt
If .EnableEvents <> Not opt Then .EnableEvents = Not opt
If .ScreenUpdating <> Not opt Then .ScreenUpdating = Not opt
End With
FastWS , opt
End Sub
Public Sub FastWS(Optional ByVal ws As Worksheet, Optional ByVal opt As Boolean = True)
If ws Is Nothing Then
For Each ws In Application.ThisWorkbook.Sheets
OptimiseWS ws, opt
Next
Else
OptimiseWS ws, opt
End If
End Sub
Private Sub OptimiseWS(ByVal ws As Worksheet, ByVal opt As Boolean)
With ws
.DisplayPageBreaks = False
.EnableCalculation = Not opt
.EnableFormatConditionsCalculation = Not opt
.EnablePivotTable = Not opt
End With
End Sub
Ripristina tutte le impostazioni di Excel sui valori predefiniti
Public Sub XlResetSettings() 'default Excel settings
With Application
.Calculation = xlCalculationAutomatic
.DisplayAlerts = True
.DisplayStatusBar = True
.EnableAnimations = False
.EnableEvents = True
.ScreenUpdating = True
Dim sh As Worksheet
For Each sh In Application.ThisWorkbook.Sheets
With sh
.DisplayPageBreaks = False
.EnableCalculation = True
.EnableFormatConditionsCalculation = True
.EnablePivotTable = True
End With
Next
End With
End Sub
Ottimizzazione della ricerca errori tramite debug esteso
Usare i numeri di riga ... e documentarli in caso di errore ("L'importanza di vedere Erl")
Rilevare quale linea genera un errore è una parte sostanziale di qualsiasi debug e restringe la ricerca della causa. Per documentare le righe di errore identificate con una breve descrizione si completa un corretto rilevamento degli errori, al meglio insieme ai nomi del modulo e della procedura. L'esempio seguente salva questi dati in un file di registro.
Sfondo
L'oggetto error restituisce il numero di errore (Err.Number) e la descrizione dell'errore (Err.Description), ma non risponde esplicitamente alla domanda su dove individuare l'errore. La funzione di Erl , tuttavia, lo fa, ma a condizione di aggiungere * numeri di linea ) al codice (una delle numerose altre concessioni alle precedenti ore di base).
Se non ci sono righe di errore, la funzione Erl restituisce 0, se la numerazione è incompleta si otterrà l'ultimo numero di riga precedente della procedura.
Option Explicit
Public Sub MyProc1()
Dim i As Integer
Dim j As Integer
On Error GoTo LogErr
10 j = 1 / 0 ' raises an error
okay:
Debug.Print "i=" & i
Exit Sub
LogErr:
MsgBox LogErrors("MyModule", "MyProc1", Err), vbExclamation, "Error " & Err.Number
Stop
Resume Next
End Sub
Public Function LogErrors( _
ByVal sModule As String, _
ByVal sProc As String, _
Err As ErrObject) As String
' Purpose: write error number, description and Erl to log file and return error text
Dim sLogFile As String: sLogFile = ThisWorkbook.Path & Application.PathSeparator & "LogErrors.txt"
Dim sLogTxt As String
Dim lFile As Long
' Create error text
sLogTxt = sModule & "|" & sProc & "|Erl " & Erl & "|Err " & Err.Number & "|" & Err.Description
On Error Resume Next
lFile = FreeFile
Open sLogFile For Append As lFile
Print #lFile, Format$(Now(), "yy.mm.dd hh:mm:ss "); sLogTxt
Print #lFile,
Close lFile
' Return error text
LogErrors = sLogTxt
End Function
' Codice aggiuntivo per mostrare il file di registro
Sub ShowLogFile()
Dim sLogFile As String: sLogFile = ThisWorkbook.Path & Application.PathSeparator & "LogErrors.txt"
On Error GoTo LogErr
Shell "notepad.exe " & sLogFile, vbNormalFocus
okay:
On Error Resume Next
Exit Sub
LogErr:
MsgBox LogErrors("MyModule", "ShowLogFile", Err), vbExclamation, "Error No " & Err.Number
Resume okay
End Sub