excel-vba => VBA bästa praxis

Anmärkningar

Vi känner dem alla, men dessa metoder är mycket mindre uppenbara för någon som börjar programmera i VBA.

Använd ALLTID "Explicit alternativ"

I VBA Editor-fönstret väljer du "Alternativ" från verktygsmenyn:

Kontrollera sedan att "Kräva variabel deklaration" i fliken "Editor":

Om du väljer detta alternativ kommer automatiskt Option Explicit att vara högst upp i varje VBA-modul.

Liten anmärkning: Detta gäller för moduler, klassmoduler etc. som inte har öppnats hittills. Så om du redan tittat på t.ex. koden för Sheet1 innan du aktiverar alternativet "Kräv variabel deklaration" kommer Option Explicit inte att läggas till!

Option Explicit kräver att alla variabler måste definieras före användning, t.ex. med en Dim sats. Utan Option Explicit aktiverat antas alla okända ord av VBA-kompilatorn vara en ny variabel av typen Variant , vilket orsakar extremt svåra att upptäcka fel relaterade till typografiska fel. Med Option Explicit aktiverat orsakar alla okända orden ett sammanställningsfel som kastas, vilket indikerar den kränkande linjen.

Exempel:

Om du kör följande kod:

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

Du får följande meddelande:

Du har gjort ett fel genom att skriva myvariable istället för my_variable , då visas meddelanderutan en tom variabel. Om du använder Option Explicit är detta fel inte möjligt eftersom du får ett kompileringsfelmeddelande som anger problemet.

Om du nu lägger till rätt deklaration:

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

Du får ett felmeddelande som anger exakt felet med myvariable :

Anmärkning om Explicit Alternativ och Arrays ( Declaring a Dynamic Array ):
Du kan använda ReDim-uttalandet för att deklarera en matris implicit inom en procedur.
Var försiktig så att du inte stavar namnet på matrisen när du använder ReDim-uttalandet
Även om uttalandet Option Explicit ingår i modulen skapas en ny matris
Dim arr() as Long

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

Arbeta med matriser, inte med intervall

Office Blog - Excel VBA Performance Coding Best Practices

Ofta uppnås bästa prestanda genom att undvika användningen av Range så mycket som möjligt. I det här exemplet läser vi i ett helt Range objekt i en matris, kvadrerar varje nummer i arrayen och återför sedan matrisen tillbaka till Range . Detta får endast tillgång till Range två gånger, medan en slinga får åtkomst till det 20 gånger för läs / skriv.

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

Fler tips och info med tidsbestämda exempel kan hittas i Charles Williams skrivande effektiva VBA UDF: er (del 1) och andra artiklar i serien .

Använd VB-konstanter när de är tillgängliga

If MsgBox("Click OK") = vbOK Then

kan användas istället för

If MsgBox("Click OK") = 1 Then

för att förbättra läsbarheten.

Använd Object Browser för att hitta tillgängliga VB-konstanter. Visa → Object Browser eller F2 från VB Editor.

Ange klass för att söka

Visa tillgängliga medlemmar

Använd beskrivande variabelnamn

Beskrivande namn och struktur i din kod hjälper till att göra kommentarer onödiga

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

ductArea = ductWidth * ductHeight

är bättre än

Dim a, w, h

a = w * h

Detta är särskilt användbart när du kopierar data från en plats till en annan, oavsett om det är en cell, intervall, kalkylblad eller arbetsbok. Hjälp dig själv genom att använda namn som dessa:

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

Om du deklarerar flera variabler på en rad måste du ange en typ för varje variabel som:

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

Följande kommer bara att förklara den sista variabeln och de första kommer att förbli Variant :

Dim ductWidth, ductHeight, ductArea As Double

Felhantering

Bra felhantering förhindrar slutanvändare att se VBA-runtime-fel och hjälper utvecklaren enkelt att diagnostisera och korrigera fel.

Det finns tre huvudmetoder för felhantering i VBA, varav två bör undvikas för distribuerade program om inte specifikt krävs i koden.

On Error GoTo 0 'Avoid using

eller

On Error Resume Next 'Avoid using

Föredrar att använda:

On Error GoTo <line> 'Prefer using

Vid fel GoTo 0

Om ingen felhantering är inställd i din kod, är On Error GoTo 0 standardfelhanteraren. I det här läget startar alla runtime-fel det typiska VBA-felmeddelandet, vilket gör att du kan avsluta koden eller gå in i debug och identifiera källan. När man skriver kod är denna metod den enklaste och mest användbara, men den bör alltid undvikas för kod som distribueras till slutanvändare, eftersom denna metod är mycket fula och svår för slutanvändare att förstå.

Vid fel Återuppta nästa

On Error Resume Next kommer att få VBA att ignorera eventuella fel som kastas under körning för alla rader som följer felanropet tills felhanteraren har ändrats. I mycket specifika fall kan den här linjen vara användbar, men den bör undvikas utanför dessa fall. När du till exempel startar ett separat program från en Excel-makro kan samtalet On Error Resume Next vara användbart om du är osäker på om programmet redan är öppet eller inte:

'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

Hade vi inte använt On Error Resume Next samtalet och Powerpoint-applikationen inte redan var öppen, skulle GetObject metoden kasta ett fel. Således var On Error Resume Next nödvändigt för att undvika att skapa två instanser av applikationen.

Obs: Det är också en bra praxis att omedelbart återställa felhanteraren så snart du inte längre behöver On Error Resume Next samtal

Vid fel GoTo <line>

Denna metod för felhantering rekommenderas för all kod som distribueras till andra användare. Detta gör att programmeraren kan kontrollera exakt hur VBA hanterar ett fel genom att skicka koden till den angivna raden. Taggen kan fyllas med valfri sträng (inklusive numeriska strängar) och skickar koden till motsvarande sträng som följs av en kolon. Flera felhanteringsblock kan användas genom att On Error GoTo <line> olika samtal av On Error GoTo <line> . Underrutinen nedan visar syntaxen för ett On Error GoTo <line> -samtal.

Obs: Det är Exit Sub linjen Exit Sub placeras ovanför den första felhanteraren och före varje efterföljande felhanterare för att förhindra att koden naturligtvis går in i blocket utan att ett fel anropas. Således är det bästa praxis för funktion och läsbarhet att placera felhanterare i slutet av ett kodblock.

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

Om du avslutar din metod med din felhanteringskod, se till att du städar:

Ångra allt som är delvis avslutat
Stäng filer
Återställ skärmuppdatering
Återställ beräkningsläget
Återställ händelser
Återställ muspekaren
Anropslastningsmetod på förekomster av objekt som kvarstår efter End Sub
Återställ statusfältet

Dokumentera ditt arbete

Det är bra att dokumentera ditt arbete för senare användning, särskilt om du kodar för en dynamisk arbetsbelastning. Bra kommentarer bör förklara varför koden gör något, inte vad koden gör.

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

Om din kod är så otydlig att den kräver kommentarer för att förklara vad den gör, kan du överväga att skriva om den för att vara mer tydlig i stället för att förklara den genom kommentarer. I stället för:

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

Klargöra koden så att den är lättare att följa, till exempel:

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

Stäng av egenskaper under makroekvertering

Det är bästa praxis på alla programmeringsspråk att undvika för tidig optimering. Om tester emellertid visar att din kod körs för långsamt, kan du få viss hastighet genom att stänga av några av programmets egenskaper medan den körs. Lägg till den här koden i en standardmodul:

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

Mer information om Office Blog - Excel VBA Performance Coding Best Practices

Och bara kalla det i början och slutet av makron:

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

Även om dessa till stor del kan betraktas som "förbättringar" för vanliga Public Sub förfaranden, inaktiverar du händelseshantering med Application.EnableEvents = False bör anses vara obligatoriskt för Worksheet_Change och Workbook_SheetChange privata händelsemakron som ändrar värden på en eller flera kalkylblad. Underlåtenhet att inaktivera händelsestrigrar kommer att göra att händelsemakroet rekursivt körs ovanpå sig själv när ett värde ändras och kan leda till en "frusen" arbetsbok. Kom ihåg att aktivera händelserna igen innan du lämnar händelsemakroen, eventuellt genom en felhanterare av "säker utgång".

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

En varning: Medan inaktivering av dessa inställningar kommer att förbättra körtiden kan de göra felsökning av din applikation mycket svårare. Om din kod inte fungerar korrekt, kommentera SpeedUp True samtalet tills du har kommit fram till problemet.

Detta är särskilt viktigt om du skriver till celler i ett kalkylblad och sedan läser tillbaka beräknade resultat från kalkylarkfunktioner eftersom xlCalculationManual hindrar arbetsboken från att beräkna. För att komma runt detta utan att inaktivera SpeedUp , kanske du vill inkludera Application.Calculate att köra en beräkning på specifika punkter.

OBS: Eftersom det här är egenskaperna för själva Application måste du se till att de är aktiverade igen innan ditt makro avslutas. Detta gör det särskilt viktigt att använda felhanterare och att undvika flera utgångspunkter (dvs. End eller Unload Me ).

Med felhantering:

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

Undvik att använda ActiveCell eller ActiveSheet i Excel

Att använda ActiveCell eller ActiveSheet kan vara källa till misstag om (av någon anledning) koden körs på fel plats.

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"

Användning av Active* kan skapa problem i långa makron om din användare blir uttråkad och klickar på ett annat kalkylblad eller öppnar en annan arbetsbok.
Det kan skapa problem om din kod öppnas eller skapar en annan arbetsbok.
Det kan skapa problem om din kod använder Sheets("MyOtherSheet").Select och du har glömt vilket ark du var på innan du började läsa från eller skriva till det.

Anta aldrig kalkylbladet

Även när allt ditt arbete riktas mot ett enda kalkylblad är det fortfarande en mycket bra praxis att uttryckligen specificera kalkylbladet i din kod. Denna vana gör det mycket lättare att utöka din kod senare, eller att lyfta delar (eller alla) av en Sub eller Function som ska återanvändas någon annanstans. Många utvecklare skapar en vana att (använda) samma lokala variabla namn för ett kalkylblad i sin kod, vilket gör återanvändning av koden ännu mer enkel.

Som exempel är följande kod tvetydig - men fungerar! - så länge utvecklaren inte aktiverar eller ändrar till ett annat kalkylblad:

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

Om Sheet1 är aktiv Sheet1 cell A1 på Blad1 med aktuellt datum och tid. Men om användaren ändrar kalkylblad av någon anledning, kommer koden att uppdatera vad kalkylbladet för närvarande är aktivt. Destinationens kalkylblad är tvetydigt.

Den bästa praxis är att alltid identifiera vilket kalkylblad som din kod hänvisar till:

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

Koden ovan är tydlig när man identifierar både arbetsboken och kalkylbladet. Även om det kan verka som överdöd, skapar du en god vana när det gäller målreferenser kommer du att rädda dig från framtida problem.

Undvik att använda VÄLJ eller AKTIVERA

Det är mycket sällsynt att du någonsin vill använda Select eller Activate i din kod, men vissa Excel-metoder kräver att ett kalkylblad eller en arbetsbok ska aktiveras innan de fungerar som förväntat.

Om du precis börjar lära dig VBA, föreslås ofta att du spelar in dina handlingar med makroinspelaren, och titta sedan på koden. Till exempel spelade jag in åtgärder som gjorts för att ange ett värde i cell D3 på ark2, och makrokoden ser ut så här:

Option Explicit
Sub Macro1()
'
' Macro1 Macro
'

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

Kom dock ihåg att makroinspelaren skapar en kodrad för VARJE av dina (användar) åtgärder. Detta inkluderar att klicka på kalkylbladet för att välja Sheet2 ( Sheets("Sheet2").Select ), klicka på cell D3 innan du anger värdet ( Range("D3").Select ) och använda Enter-tangenten (som är effektivt " att välja "cellen under den för närvarande valda cellen: Range("D4").Select ).

Det finns flera problem med att använda .Select här:

Kalkylarket är inte alltid specificerat. Detta händer om du inte byter kalkylblad under inspelning och innebär att koden ger olika resultat för olika aktiva kalkylblad.
.Select() är långsam. Även om Application.ScreenUpdating är inställt på False , är detta en onödig process som ska behandlas.
.Select() är orubblig. Om Application.ScreenUpdating lämnas till True , kommer Excel faktiskt att välja cellerna, kalkylbladet, formuläret ... vad det än är du arbetar med. Detta är stressande för ögonen och verkligen obehagligt att titta på.
.Select() utlöser lyssnare. Detta är redan lite avancerat, men om inte fungerat, kommer funktioner som Worksheet_SelectionChange() att triggas.

När du kodar i VBA är alla "typ" -åtgärder (dvs Select uttalanden) inte längre nödvändiga. Din kod kan reduceras till ett enda uttalande för att sätta värdet i cellen:

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

(BÄTTRE exemplet ovan visar användning av mellanvariabler för att separera olika delar av cellreferensen. GOOD-exemplet fungerar alltid bra, men kan vara mycket besvärligt i mycket längre kodmoduler och svårare att felsöka om en av referenserna är felskrivna. )

** OBS: makroinspelaren gör många antaganden om vilken typ av data du anger, i detta fall skriver du in ett strängvärde som en formel för att skapa värdet. Din kod behöver inte göra detta och kan helt enkelt tilldela ett numeriskt värde direkt till cellen som visas ovan.

** OBS2: rekommenderad praxis är att ställa in din lokala arbetsbokvariabel till ThisWorkbook istället för ActiveWorkbook (såvida du inte uttryckligen behöver det). Anledningen är att ditt makro vanligtvis behöver / använda resurser i vilken arbetsbok VBA-koden kommer från och kommer INTE att se utanför den arbetsboken - igen, såvida du inte uttryckligen riktar din kod att arbeta med en annan arbetsbok. När du har flera arbetsböcker öppna i Excel är ActiveWorkbook den med fokus som kan skilja sig från arbetsboken som visas i din VBA Editor . Så du tror att du kör i en arbetsbok när du verkligen hänvisar till en annan. ThisWorkbook hänvisar till arbetsboken som innehåller koden som körs.

Definiera och ställ alltid in referenser till alla arbetsböcker och ark

När du arbetar med flera öppna arbetsböcker, som alla kan ha flera ark, är det säkrast att definiera och ställa in referens till alla arbetsböcker och ark.

ActiveWorkbook inte på ActiveWorkbook eller ActiveSheet eftersom de kan ändras av användaren.

Följande kodexempel visar hur man kopierar ett intervall från " Raw_Data " -arket i " Data.xlsx " -arbetsboken till " Refined_Data " -arket i arbetsresultaten " Results.xlsx ".

Proceduren visar också hur man kopierar och klistrar in utan att använda metoden 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

WorksheetFunction-objekt körs snabbare än en UDF-ekvivalent

VBA sammanställs i körtid, vilket har en enorm negativ inverkan på dess prestanda, allt inbyggt kommer att bli snabbare, försök att använda dem.

Som exempel jämför jag SUM- och COUNTIF-funktioner, men du kan använda if för något du kan lösa med WorkSheetFunctions.

Ett första försök för dem skulle vara att gå igenom intervallet och bearbeta det cell för cell (med hjälp av ett intervall):

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

En förbättring kan vara att lagra intervallvärdena i en matris och process som:

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

Men istället för att skriva någon slinga kan du använda Application.Worksheetfunction som är mycket praktiskt för att utföra enkla formler:

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

Eller för mer komplexa beräkningar kan du till och med använda 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

Och slutligen, kör över Subs 25 000 gånger vardera, här är den genomsnittliga (5 tester) tiden i millisekunder (naturligtvis kommer det att vara annorlunda på varje dator, men jämfört med varandra kommer de att bete sig på liknande sätt):

UseWorksheetFunction: 2156 ms
UseArray: 2219 ms (+ 3%)
UseEvaluate: 4693 ms (+ 118%)
UseRange: 6530 ms (+ 203%)

Undvik att ändra namnen på Egenskaper eller Metoder som dina variabler

Det anses i allmänhet inte som "bästa praxis" att ändra de reserverade namnen på Egenskaper eller Metoder som namn på dina egna procedurer och variabler.

Dålig form - Medan följande är (strikt taget) lagligt, kan arbetskod omförändra Find- metoden samt rad- , kolumn- och adressegenskaper orsaka problem / konflikter med tvetydighet i namnet och är helt enkelt förvirrande i allmänhet.

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

God form - Med alla de reserverade orden döpta om till nära men unika tillnärmningar av originalen har alla potentiella namnkonflikter undvikits.

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

Det kan komma en tid då du avsiktligt vill skriva om en standardmetod eller egenskap till dina egna specifikationer, men dessa situationer är få och långt mellan. För det mesta, håll dig borta från att återanvända reserverade namn för dina egna konstruktioner.

Modified text is an extract of the original Stack Overflow Documentation

Licensierat under CC BY-SA 3.0

Inte anslutet till Stack Overflow

excel-vba
VBA bästa praxis

Sök…