Formattazione condizionale vs velocità / memory di formattazione VBA

Sto sviluppando un foglio di calcolo model che ridimensiona arrays di dati quando viene aggiornato. Attualmente sto utilizzando la formattazione condizionale per aggiungere le frontiere mentre le matrici crescono e viceversa.

È più pulito utilizzare VBA per formattare le celle quando vengono aggiornate piuttosto che avere la maggior parte del foglio sobject alle regole di formattazione condizionale? Quali sono i vantaggi di uno contro l'altro?

Grazie!

Si consideri il seguente codice:

Option Explicit Private Sub fill() Dim i As Long Dim num As Long Randomize For i = 1 To 500000 num = Int(50 * Rnd) + 1 Sheet1.Cells(i, 1).Value = num Sheet1.Cells(i, 2).Value = num Next End Sub Sub format() Dim i As Long Debug.Print Now With Sheet1 For i = 1 To .UsedRange.Rows.Count If .Cells(i, 1).Value > 20 Then .Cells(i, 1).Interior.ColorIndex = 20 Next End With Debug.Print Now End Sub Sub conditionally_format() Debug.Print Now With Sheet1.Columns(2) .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=40" .FormatConditions(1).Font.Color = -16383844 .FormatConditions(1).Font.TintAndShade = 0 .FormatConditions(1).Interior.PatternColorIndex = xlAutomatic .FormatConditions(1).Interior.Color = 13551615 .FormatConditions(1).Interior.TintAndShade = 0 End With Debug.Print Now End Sub 

Sul mio HP asaurus, ottengo i seguenti risultati

 format: 7/22/2016 1:10:34 PM 7/22/2016 1:10:49 PM conditionally_format: 7/22/2016 1:25:06 PM 7/22/2016 1:25:07 PM 

Tuttavia, va notato che i risultati per il sub conditionally_format rappresentano il tempo necessario per eseguire quel codice, per non formattare in modo effettivo le celle. Non so come misurare che, tranne cambiando un valore sul foglio di calcolo e vedendo quanto tempo ci vuole. Anche facendo questo, è imansible perché succede così veloce che non posso misurarla. Ciò significa che il codice da percorrere attraverso ciascuna cella funziona molto più lentamente che impostare un formato condizionale. Questo è veramente da aspettarsi da quando Excel carica each object Cell , quindi controlla gli oggetti di cella. .Value e quindi imposta la properties; Interior.ColorIndex sulla base di quella valutazione. Lo stesso vale per la formattazione condizionale tranne che VBA rimane fuori dall'image. È il codice compilato sottostante (e ottimizzato) di Excel che esegue le azioni. La differenza di velocità è enorme? Non proprio. Il mio computer porttile lento ha fatto mezzo milione di confronti in 15 secondi rispetto alla formattazione essenzialmente istantanea da regole di formattazione condizionale.

Benefici … questa è una chiamata più dura. Penserei che più utenti finali avrebbero capito le regole di formattazione condizionale, il che significa anche che più utenti finali possono scimmia con loro. Questo potrebbe non essere desiderabile. È più pulito? Ancora una dura chiamata. Se si imposta le regole di formattazione dall'interface utente, uno sviluppatore potrebbe non pensare a cercare regole di formattazione condizionale e poiché non viene visualizzato nell'IDE circa la formattazione condizionale, questo potrebbe portre a conseguenze indesiderate. Nel passato ci sono stati errori con formattazione condizionale , ma non so se questo è ancora un problema. Penserei che il metodo che in ultima istanza sceglierai si abbasserà a quanto controllo vuoi dare a un utente finale rispetto a quanto codice desideri creare.