excel-vba => VBA Best Practices

Opmerkingen

We kennen ze allemaal, maar deze praktijken zijn veel minder duidelijk voor iemand die begint te programmeren in VBA.

Gebruik ALTIJD "Option Explicit"

Selecteer "Opties" in het menu VBA-editor:

Controleer vervolgens op het tabblad "Editor" of "Vereiste variabele verklaring" is aangevinkt:

Als u deze optie selecteert, wordt Option Explicit automatisch bovenaan elke VBA-module geplaatst.

Kleine opmerking: dit geldt voor de modules, klassemodules, enz. Die tot nu toe niet zijn geopend. Dus als u bijvoorbeeld al eens de code van Sheet1 voordat u de optie "Vereiste variabele verklaring" hebt geactiveerd, wordt Option Explicit niet toegevoegd!

Option Explicit vereist dat elke variabele vóór gebruik moet worden gedefinieerd, bijvoorbeeld met een Dim instructie. Zonder Option Explicit ingeschakeld, wordt elk niet-herkend woord door de VBA-compiler verondersteld een nieuwe variabele van het Variant type te zijn, wat extreem moeilijk te vinden bugs veroorzaakt door typografische fouten veroorzaakt. Als Option Explicit ingeschakeld, veroorzaken niet-herkende woorden een compilatiefout die de aanstootgevende regel aangeeft.

Voorbeeld:

Als u de volgende code uitvoert:

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

U krijgt het volgende bericht:

U hebt een fout gemaakt door myvariable schrijven myvariable plaats van my_variable , waarna het berichtvenster een lege variabele weergeeft. Als u Option Explicit , is deze fout niet mogelijk omdat u een compilatiefoutbericht krijgt dat het probleem aangeeft.

Als u nu de juiste aangifte toevoegt:

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

U krijgt een foutmelding die precies de fout met myvariable :

Opmerking over optie Expliciet en arrays ( een dynamische array verklaren ):
U kunt de ReDim-instructie gebruiken om een array impliciet binnen een procedure te declareren.
Let op dat u de naam van de array niet verkeerd spelt wanneer u de ReDim-instructie gebruikt
Zelfs als de optie Option Explicit in de module is opgenomen, wordt er een nieuwe array gemaakt
Dim arr() as Long

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

Werk met arrays, niet met bereiken

Office-blog - Praktische codering van Excel VBA-prestatiecodering

Vaak worden de beste prestaties bereikt door het gebruik van Range zoveel mogelijk te vermijden. In dit voorbeeld lezen we een heel Range object in een array, kwadrateren elk getal in de array en keren de array terug naar het Range . Dit heeft slechts tweemaal toegang tot Range , terwijl een lus er 20 keer toegang toe zou hebben voor lezen / schrijven.

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

Meer tips en info met getimede voorbeelden zijn te vinden in Charles Williams's Writing efficient VBA UDF's (Part 1) en andere artikelen in de serie .

Gebruik VB-constanten indien beschikbaar

If MsgBox("Click OK") = vbOK Then

kan worden gebruikt in plaats van

If MsgBox("Click OK") = 1 Then

om de leesbaarheid te verbeteren.

Gebruik de Objectbrowser om beschikbare VB-constanten te vinden. Beeld → Objectbrowser of F2 vanuit VB Editor.

Voer klasse in om te zoeken

Bekijk beschikbare leden

Gebruik een beschrijvende naamgeving van variabelen

Beschrijvende namen en structuur in uw code maken opmerkingen overbodig

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

ductArea = ductWidth * ductHeight

is beter dan

Dim a, w, h

a = w * h

Dit is vooral handig wanneer u gegevens van de ene plaats naar de andere kopieert, of het nu een cel, bereik, werkblad of werkmap is. Help uzelf door namen zoals deze te gebruiken:

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

Als u meerdere variabelen op één regel declareert, moet u voor elke variabele een type opgeven, zoals:

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

Het volgende zal alleen de laatste variabele declareren en de eerste blijven Variant :

Dim ductWidth, ductHeight, ductArea As Double

Foutafhandeling

Goede foutafhandeling voorkomt dat eindgebruikers VBA-runtime-fouten zien en helpt de ontwikkelaar om eenvoudig fouten te diagnosticeren en te corrigeren.

Er zijn drie belangrijke methoden voor foutafhandeling in VBA, waarvan er twee moeten worden vermeden voor gedistribueerde programma's, tenzij specifiek vereist in de code.

On Error GoTo 0 'Avoid using

On Error Resume Next 'Avoid using

Gebruik bij voorkeur:

On Error GoTo <line> 'Prefer using

Bij fout Ga naar 0

Als er geen foutafhandeling is ingesteld in uw code, is On Error GoTo 0 de standaardfoutafhandelaar. In deze modus wordt bij runtime-fouten het typische VBA-foutbericht gestart, zodat u de code kunt beëindigen of de debug kunt openen, waarbij de bron wordt geïdentificeerd. Tijdens het schrijven van code is deze methode de eenvoudigste en meest bruikbare, maar het moet altijd worden vermeden voor code die aan eindgebruikers wordt gedistribueerd, omdat deze methode erg lelijk en moeilijk te begrijpen is voor eindgebruikers.

On Error Resume Next

On Error Resume Next zal VBA fouten die tijdens runtime worden gegooid voor alle regels na de foutoproep negeren totdat de foutafhandelaar is gewijzigd. In zeer specifieke gevallen kan deze regel nuttig zijn, maar deze moet buiten deze gevallen worden vermeden. Wanneer u bijvoorbeeld een afzonderlijk programma start vanuit een Excel Macro, kan de aanroep On Error Resume Next nuttig zijn als u niet zeker weet of het programma al is geopend:

'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

Als we de aanroep On Error Resume Next niet hadden gebruikt en de Powerpoint-toepassing nog niet was geopend, zou de GetObject methode een foutmelding geven. Daarom was On Error Resume Next nodig om te voorkomen dat er twee exemplaren van de toepassing werden gemaakt.

Opmerking: het is ook een goede gewoonte om de foutafhandelaar onmiddellijk opnieuw in te stellen zodra u de oproep On Error Resume Next niet langer nodig hebt

Bij fout Ga naar <lijn>

Deze methode voor foutafhandeling wordt aanbevolen voor alle code die wordt gedistribueerd naar andere gebruikers. Hiermee kan de programmeur precies bepalen hoe VBA een fout verwerkt door de code naar de opgegeven regel te verzenden. De tag kan worden gevuld met elke tekenreeks (inclusief numerieke tekenreeksen) en verzendt de code naar de overeenkomstige tekenreeks die wordt gevolgd door een dubbele punt. Meerdere foutafhandelingsblokken kunnen worden gebruikt door verschillende aanroepen van On Error GoTo <line> . De onderstaande subroutine toont de syntaxis van een On Error GoTo <line> -oproep.

Opmerking: Het is van essentieel belang dat de Exit Sub boven de eerste foutafhandelaar en vóór elke volgende foutafhandelaar wordt geplaatst om te voorkomen dat de code op natuurlijke wijze het blok binnenkomt zonder dat er een fout wordt aangeroepen. Het is daarom een goede praktijk voor functie en leesbaarheid om foutafhandelaars aan het einde van een codeblok te plaatsen.

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

Als u uw methode verlaat met uw foutafhandelingscode, zorg er dan voor dat u opschoont:

Maak iets dat gedeeltelijk is voltooid ongedaan
Sluit bestanden
Schermupdate resetten
Reset berekeningsmodus
Gebeurtenissen resetten
Reset muisaanwijzer
Roep de verwijderingsmethode aan op instanties van objecten die blijven bestaan na de End Sub
Reset statusbalk

Documenteer je werk

Het is een goede gewoonte om uw werk te documenteren voor later gebruik, vooral als u codeert voor een dynamische werklast. Goede opmerkingen zouden moeten verklaren waarom de code iets doet, niet wat de code doet.

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

Als uw code zo onduidelijk is dat er commentaar nodig is om uit te leggen wat het doet, overweeg dan om het te herschrijven om het duidelijker te maken in plaats van het door commentaar te verklaren. Bijvoorbeeld in plaats van:

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

Verduidelijk de code zodat deze gemakkelijker te volgen is, zoals:

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

Schakel eigenschappen uit tijdens macro-uitvoering

Het is de beste praktijk in elke programmeertaal om voortijdige optimalisatie te voorkomen. Als uit het testen echter blijkt dat uw code te langzaam wordt uitgevoerd, kunt u enige snelheid behalen door enkele eigenschappen van de toepassing uit te schakelen terwijl deze wordt uitgevoerd. Voeg deze code toe aan een standaardmodule:

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

Meer info op Office Blog - Best Practices voor Excel VBA-prestatiecodering

En noem het gewoon aan het begin en einde van macro's:

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

Hoewel deze grotendeels kunnen worden beschouwd als "verbeteringen" voor normale Public Sub , moet het uitschakelen van gebeurtenissen met Application.EnableEvents = False als verplicht worden beschouwd voor macro's van Worksheet_Change en Workbook_SheetChange die waarden op een of meer werkbladen wijzigen. Als u event-triggers niet uitschakelt, wordt de event-macro recursief op zichzelf uitgevoerd wanneer een waarde verandert en kan dit leiden tot een "bevroren" werkmap. Vergeet niet om gebeurtenissen weer in te schakelen voordat u de gebeurtenismacro verlaat, mogelijk via een foutafhandelingsprogramma 'veilig afsluiten'.

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

Eén waarschuwing: hoewel het uitschakelen van deze instellingen de runtime verbetert, kan het debuggen van uw toepassing veel moeilijker worden. Als uw code niet correct functioneert, maakt u commentaar op de SpeedUp True aanroep totdat u het probleem hebt ontdekt.

Dit is vooral belangrijk als u naar cellen in een werkblad schrijft en vervolgens de berekende resultaten van de werkbladfuncties xlCalculationManual omdat de xlCalculationManual voorkomt dat de werkmap kan worden berekend. Om dit te omzeilen zonder SpeedUp te schakelen, kunt u Application.Calculate om een berekening op specifieke punten uit te voeren.

OPMERKING: Aangezien dit eigenschappen van de Application zelf zijn, moet u ervoor zorgen dat ze opnieuw worden ingeschakeld voordat uw macro wordt afgesloten. Dit maakt het bijzonder belangrijk om foutafhandelaars te gebruiken en om meerdere exitpunten (bijv. End of Unload Me ) te vermijden.

Met foutafhandeling:

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

Vermijd het gebruik van ActiveCell of ActiveSheet in Excel

Het gebruik van ActiveCell of ActiveSheet kan een bron van fouten zijn als (om welke reden dan ook) de code op de verkeerde plaats wordt uitgevoerd.

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"

Het gebruik van Active* kan problemen veroorzaken bij langlopende macro's als uw gebruiker zich verveelt en op een ander werkblad klikt of een andere werkmap opent.
Het kan problemen veroorzaken als uw code wordt geopend of een andere werkmap maakt.
Het kan problemen veroorzaken als uw code Sheets("MyOtherSheet").Select gebruikt Sheets("MyOtherSheet").Select en u bent vergeten welk blad u had voordat u begon met lezen of schrijven.

Ga nooit uit van het werkblad

Zelfs als al uw werk op één werkblad is gericht, is het nog steeds een goede gewoonte om het werkblad expliciet in uw code op te geven. Deze gewoonte maakt het veel gemakkelijker om je code later uit te breiden, of delen (of alle) van een Sub of Function op te tillen om ergens anders opnieuw te gebruiken. Veel ontwikkelaars hebben de gewoonte om dezelfde lokale variabelenaam voor een werkblad in hun code te (her) gebruiken, waardoor het hergebruik van die code nog eenvoudiger wordt.

De volgende code is bijvoorbeeld dubbelzinnig - maar werkt! - zolang de ontwikkelaar niet activeert of naar een ander werkblad overschakelt:

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

Als Sheet1 actief is, wordt cel A1 op Blad1 gevuld met de huidige datum en tijd. Maar als de gebruiker om welke reden dan ook werkbladen wijzigt, wordt de code bijgewerkt, ongeacht het actieve werkblad. Het doelwerkblad is dubbelzinnig.

De beste praktijk is om altijd te identificeren naar welk werkblad uw code verwijst:

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

De bovenstaande code is duidelijk bij het identificeren van zowel de werkmap als het werkblad. Hoewel het misschien overdreven lijkt, zal het maken van een goede gewoonte met betrekking tot doelreferenties je redden van toekomstige problemen.

Vermijd het gebruik van SELECT of ACTIVATE

Het is zeer zeldzaam dat u ooit Select of Activate in uw code wilt gebruiken, maar sommige Excel-methoden vereisen dat een werkblad of werkmap wordt geactiveerd voordat ze werken zoals verwacht.

Als je net begint met het leren van VBA, wordt je vaak voorgesteld om je acties op te nemen met de macrorecorder en kijk je vervolgens naar de code. Ik heb bijvoorbeeld acties vastgelegd die zijn genomen om een waarde in cel D3 op Sheet2 in te voeren, en de macrocode ziet er als volgt uit:

Option Explicit
Sub Macro1()
'
' Macro1 Macro
'

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

Vergeet echter niet dat de macrorecorder een regel code creëert voor ELK van uw (gebruikers) acties. Dit omvat het klikken op het werkbladtabblad om Blad2 ( Sheets("Sheet2").Select ), op cel D3 klikken voordat de waarde wordt ingevoerd ( Range("D3").Select ) en de Enter-toets gebruiken (wat effectief is " selecteer "de cel onder de momenteel geselecteerde cel: Range("D4").Select ).

Er zijn meerdere problemen met het gebruik. .Select hier:

Het werkblad wordt niet altijd opgegeven. Dit gebeurt als u tijdens het opnemen niet van werkblad wisselt en dit betekent dat de code verschillende resultaten voor verschillende actieve werkbladen oplevert.
.Select() is langzaam. Zelfs als Application.ScreenUpdating is ingesteld op False , is dit een onnodige bewerking die moet worden verwerkt.
.Select() is onhandelbaar. Als Application.ScreenUpdating op True wordt gelaten, selecteert Excel daadwerkelijk de cellen, het werkblad, de vorm ... waar u ook mee werkt. Dit is stressvol voor de ogen en echt onaangenaam om te zien.
.Select() activeert luisteraars. Dit is al een beetje geavanceerd, maar tenzij er omheen wordt gewerkt, worden functies zoals Worksheet_SelectionChange() geactiveerd.

Wanneer u codeert in VBA, zijn alle "typ" -acties (dwz Select instructies) niet langer nodig. Uw code kan worden gereduceerd tot een enkele instructie om de waarde in de cel te plaatsen:

'--- 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

(Het BETERE voorbeeld hierboven toont het gebruik van tussenliggende variabelen om verschillende delen van de celverwijzing te scheiden. Het GOEDE voorbeeld werkt altijd prima, maar kan in veel langere codemodules erg omslachtig zijn en moeilijker te debuggen als een van de referenties verkeerd is getypt. )

** OPMERKING: de macrorecorder maakt veel veronderstellingen over het type gegevens dat u invoert, in dit geval voert u een tekenreekswaarde in als een formule om de waarde te maken. Uw code hoeft dit niet te doen en kan eenvoudig een numerieke waarde rechtstreeks aan de cel toewijzen, zoals hierboven weergegeven.

** OPMERKING2: de aanbevolen methode is om uw lokale werkmapvariabele in te stellen op ThisWorkbook plaats van ActiveWorkbook (tenzij u dit expliciet nodig hebt). De reden is dat uw macro over het algemeen bronnen nodig heeft / gebruikt in de werkmap waar de VBA-code vandaan komt en NIET buiten die werkmap zal kijken - nogmaals, tenzij u uw code expliciet laat werken met een andere werkmap. Wanneer u meerdere werkmappen in Excel hebt geopend, is het ActiveWorkbook degene met de focus die kan verschillen van de werkmap die wordt bekeken in uw VBA-editor . Dus u denkt dat u in één werkmap uitvoert terwijl u echt naar een andere verwijst. ThisWorkbook verwijst naar de werkmap met de code die wordt uitgevoerd.

Definieer en stel verwijzingen naar alle werkmappen en spreadsheets altijd in

Wanneer u met meerdere open werkmappen werkt, die elk meerdere bladen kunnen hebben, is het het veiligst om een verwijzing naar alle werkmappen en bladen te definiëren en in te stellen.

Vertrouw niet op ActiveWorkbook of ActiveSheet omdat deze door de gebruiker kunnen worden gewijzigd.

In het volgende voorbeeld laat zien hoe u een bereik van “Raw_Data” blad in de “Data.xlsx” werkboek “Refined_Data” vel kopiëren in de “Results.xlsx” werkmap.

De procedure laat ook zien hoe u kunt kopiëren en plakken zonder de Select methode.

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

WorksheetFunction-object wordt sneller uitgevoerd dan een UDF-equivalent

VBA is in runtime gecompileerd, wat een enorme negatieve invloed heeft op de prestaties, alles ingebouwd zal sneller zijn, probeer ze te gebruiken.

Als een voorbeeld vergelijk ik de functies SOM en AANTAL.ALS, maar je kunt het gebruiken voor alles wat je kunt oplossen met WorkSheetFunctions.

Een eerste poging hiervoor zou zijn om het bereik te doorlopen en het cel voor cel te verwerken (met behulp van een bereik):

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

Een verbetering kan zijn om de bereikwaarden in een array op te slaan en te verwerken die:

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

Maar in plaats van een lus te schrijven, kunt u Application.Worksheetfunction wat erg handig is voor het uitvoeren van eenvoudige formules:

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

Of, voor complexere berekeningen kunt u zelfs 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

En ten slotte, met elk 25.000 keer Subs, is hier de gemiddelde (5 testen) tijd in milliseconden (natuurlijk zal het op elke pc anders zijn, maar in vergelijking met elkaar gedragen ze zich hetzelfde):

UseWorksheetFunction: 2156 ms
UseArray: 2219 ms (+ 3%)
Gebruik Evalueren: 4693 ms (+ 118%)
Gebruiksbereik: 6530 ms (+ 203%)

Vermijd het opnieuw gebruiken van de namen van Eigenschappen of Methoden als uw variabelen

Over het algemeen wordt het niet als 'best practice' beschouwd om de gereserveerde namen van Eigenschappen of Methoden om te zetten in de naam (namen) van uw eigen procedures en variabelen.

Bad Form - Terwijl de volgende is (strikt genomen) juridische, werkende code de re-purposing van de Find werkwijze alsmede de rij , kolom en Address eigenschappen kunnen problemen / conflicten met de naam van ambiguïteit veroorzaken en is gewoon verwarrend in het algemeen.

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

Goede vorm - Nu alle gereserveerde woorden zijn omgedoopt tot nauwe maar unieke benaderingen van de originelen, zijn mogelijke naamgevingsconflicten vermeden.

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

Hoewel er een tijd kan komen dat u opzettelijk een standaardmethode of eigenschap wilt herschrijven volgens uw eigen specificaties, zijn die situaties maar weinig. Voor het grootste deel, blijf weg van het hergebruiken van gereserveerde namen voor uw eigen constructies.

Modified text is an extract of the original Stack Overflow Documentation

Licentie onder CC BY-SA 3.0

Niet aangesloten bij Stack Overflow

excel-vba
VBA Best Practices

Zoeken…