excel-vba
Najlepsze praktyki VBA

Uwagi

ZAWSZE używaj „opcji jawnej”

W oknie Edytora VBA z menu Narzędzia wybierz „Opcje”:

Następnie na karcie „Edytor” upewnij się, że zaznaczona jest opcja „Wymagaj deklaracji zmiennej”:

Wybranie tej opcji spowoduje automatyczne umieszczenie Option Explicit na górze każdego modułu VBA.

Mała uwaga: dotyczy to modułów, modułów klasy itp., Które nie zostały jeszcze otwarte. Jeśli więc już Sheet1 np. Kod Sheet1 przed aktywacją opcji „Wymagaj deklaracji zmiennej”, Option Explicit nie zostanie dodana!

Option Explicit wymaga, aby przed użyciem zdefiniować każdą zmienną, np. Za pomocą instrukcji Dim . Bez włączonej Option Explicit każde nierozpoznane słowo zostanie uznane przez kompilator VBA za nową zmienną typu Variant , powodując niezwykle trudne do wykrycia błędy związane z błędami typograficznymi. Po włączeniu Option Explicit wszelkie nierozpoznane słowa spowodują zgłoszenie błędu kompilacji, wskazując na linię obrażającą.

Przykład:

Jeśli uruchomisz następujący kod:

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

Otrzymasz następujący komunikat:

Popełniłeś błąd, pisząc myvariable zamiast my_variable , wtedy w oknie komunikatu wyświetla się pusta zmienna. Jeśli użyjesz Option Explicit , ten błąd nie jest możliwy, ponieważ pojawi się komunikat o błędzie kompilacji wskazujący problem.

Teraz, jeśli dodasz poprawną deklarację:

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

Otrzymasz komunikat o błędzie dokładnie wskazujący błąd z myvariable :

Uwaga na temat opcji Jawne i tablic ( Deklarowanie tablicy dynamicznej ):

Za pomocą instrukcji ReDim można niejawnie zadeklarować tablicę w ramach procedury.

• Uważaj, aby nie wpisać błędnie nazwy tablicy podczas korzystania z instrukcji ReDim

• Nawet jeśli w module znajduje się instrukcja Option Explicit, zostanie utworzona nowa tablica

  Dim arr() as Long

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

Pracuj z tablicami, a nie z zakresami

Office Blog - najlepsze praktyki kodowania wydajności VBA Excel

Często najlepszą wydajność osiąga się, unikając maksymalnego wykorzystania Range . W tym przykładzie wczytujemy cały obiekt Range do tablicy, kwadratujemy każdą liczbę w tablicy, a następnie zwracamy tablicę z powrotem do Range . To uzyskuje dostęp do Range tylko dwa razy, podczas gdy pętla miałaby do niego dostęp 20 razy w celu odczytu / zapisu.

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

Więcej wskazówek i informacji z przykładami czasowymi można znaleźć w wydajnych VBA UDFs Charlesa Williamsa (część 1) i innych artykułach z serii .

Użyj stałych VB, jeśli są dostępne

If MsgBox("Click OK") = vbOK Then

można użyć zamiast

If MsgBox("Click OK") = 1 Then

w celu poprawy czytelności.

Użyj przeglądarki obiektów, aby znaleźć dostępne stałe VB. Widok → Przeglądarka obiektów lub F2 z edytora VB.

Wprowadź klasę do przeszukania

Wyświetl członków dostępnych

Użyj opisowego nazewnictwa zmiennych

Opisowe nazwy i struktura w kodzie sprawiają, że komentarze stają się niepotrzebne

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

ductArea = ductWidth * ductHeight

jest lepszy niż

Dim a, w, h

a = w * h

Jest to szczególnie przydatne, gdy kopiujesz dane z jednego miejsca do drugiego, niezależnie od tego, czy jest to komórka, zakres, arkusz roboczy czy skoroszyt. Pomóż sobie, używając takich nazw:

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

Jeśli deklarujesz wiele zmiennych w jednym wierszu, upewnij się, że podałeś typ dla każdej zmiennej, np .:

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

Poniższe deklaruje tylko ostatnią zmienną, a pierwsze pozostaną Variant :

Dim ductWidth, ductHeight, ductArea As Double

Obsługa błędów

Dobra obsługa błędów zapobiega wyświetlaniu przez użytkowników końcowych błędów środowiska wykonawczego VBA i pomaga deweloperowi w łatwym diagnozowaniu i poprawianiu błędów.

Istnieją trzy główne metody obsługi błędów w VBA, z których dwóch należy unikać w przypadku programów rozproszonych, chyba że kod wyraźnie tego wymaga.

On Error GoTo 0 'Avoid using

lub

On Error Resume Next 'Avoid using

Wolę używać:

On Error GoTo <line> 'Prefer using

W przypadku błędu GoTo 0

Jeśli w kodzie nie ustawiono obsługi błędów, w przypadku On Error GoTo 0 jest domyślną procedurą obsługi błędów. W tym trybie wszelkie błędy w czasie wykonywania uruchomią typowy komunikat o błędzie VBA, co pozwala albo zakończyć kod, albo przejść do trybu debug , identyfikując źródło. Podczas pisania kodu ta metoda jest najprostsza i najbardziej użyteczna, ale zawsze należy jej unikać w przypadku kodu rozpowszechnianego wśród użytkowników końcowych, ponieważ metoda ta jest bardzo nieestetyczna i trudna do zrozumienia dla użytkowników końcowych.

Po błędzie Wznów dalej

On Error Resume Next spowoduje, że VBA zignoruje wszelkie błędy, które zostaną wygenerowane w czasie wykonywania dla wszystkich linii następujących po wywołaniu błędu, dopóki program obsługi błędów nie zostanie zmieniony. W bardzo szczególnych przypadkach ta linia może być przydatna, ale należy jej unikać poza tymi przypadkami. Na przykład podczas uruchamiania osobnego programu z makra Excela wywołanie On Error Resume Next może być przydatne, jeśli nie masz pewności, czy program jest już otwarty:

'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

Gdybyśmy nie użyli wywołania On Error Resume Next a aplikacja Powerpoint nie była jeszcze otwarta, metoda GetObject błąd. Tak więc On Error Resume Next było konieczne, aby uniknąć utworzenia dwóch wystąpień aplikacji.

Uwaga: Najlepszym rozwiązaniem jest natychmiastowe zresetowanie modułu obsługi błędów, gdy tylko nie będzie już potrzebne wezwanie On Error Resume Next

W przypadku błędu przejdź do <line>

Ta metoda obsługi błędów jest zalecana dla całego kodu dystrybuowanego do innych użytkowników. Pozwala to programiście dokładnie kontrolować, w jaki sposób VBA obsługuje błąd, wysyłając kod do określonej linii. Znacznik może być wypełniony dowolnym ciągiem (w tym ciągami liczbowymi) i wyśle kod do odpowiedniego ciągu, po którym następuje dwukropek. Można użyć wielu bloków obsługi błędów, wykonując różne wywołania On Error GoTo <line> . Poniższy podprogram pokazuje składnię wywołania On Error GoTo <line> .

Uwaga: Niezbędne jest umieszczenie wiersza Exit Sub pierwszym modułem obsługi błędów i przed każdym kolejnym modułem obsługi błędów, aby zapobiec naturalnemu przejściu kodu do bloku bez wywołania błędu. Dlatego najlepszą praktyką dla funkcji i czytelności jest umieszczenie programów obsługi błędów na końcu bloku kodu.

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

Jeśli zakończysz swoją metodę przy użyciu kodu obsługi błędów, upewnij się, że wyczyściłeś:

• Cofnij wszystko, co jest częściowo ukończone
• Zamknij pliki
• Zresetuj aktualizację ekranu
• Zresetuj tryb obliczeń
• Zresetuj zdarzenia
• Zresetuj wskaźnik myszy
• Wywołaj metodę odciążania instancji obiektów, które utrzymują się po End Sub
• Zresetuj pasek stanu

Dokumentuj swoją pracę

Dobrą praktyką jest dokumentowanie pracy do późniejszego wykorzystania, szczególnie jeśli kodujesz dynamiczne obciążenie pracą. Dobre komentarze powinny wyjaśniać, dlaczego kod coś robi, a nie to, co robi kod.

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

Jeśli kod jest tak niejasny, że wymaga komentarza w celu wyjaśnienia, co robi, rozważ przepisanie go w sposób bardziej przejrzysty, zamiast wyjaśniać go za pomocą komentarzy. Na przykład zamiast:

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

Wyjaśnij kod, aby był łatwiejszy do naśladowania, na przykład:

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 

Wyłącz właściwości podczas wykonywania makra

Jest to najlepsza praktyka w każdym języku programowania, aby uniknąć przedwczesnej optymalizacji. Jeśli jednak testy wykażą, że kod działa zbyt wolno, możesz przyspieszyć, wyłączając niektóre właściwości aplikacji podczas działania. Dodaj ten kod do standardowego modułu:

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

Więcej informacji na blogu pakietu Office - najważniejsze wskazówki dotyczące Excel VBA Performance Coding

I po prostu nazwij to na początku i na końcu makr:

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

Chociaż można je w dużej mierze uznać za „udoskonalenia” zwykłych Public Sub procedur Public Sub , wyłączenie obsługi zdarzeń za pomocą Application.EnableEvents = False należy uznać za obowiązkowe w przypadku makr zdarzeń prywatnych Worksheet_Change i Workbook_SheetChange które zmieniają wartości w jednym lub większej liczbie arkuszy. Brak wyłączenia wyzwalaczy zdarzeń spowoduje rekurencyjne uruchamianie makra zdarzeń na sobie, gdy zmienia się wartość, i może prowadzić do „zamrożonego” skoroszytu. Pamiętaj, aby ponownie włączyć zdarzenia przed opuszczeniem makra zdarzenia, prawdopodobnie za pomocą procedury obsługi błędów „bezpiecznego wyjścia”.

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

Jedno zastrzeżenie: wyłączenie tych ustawień poprawi czas działania, ale może znacznie utrudnić debugowanie aplikacji. Jeśli kod nie działa poprawnie, skomentuj wywołanie SpeedUp True dopóki nie rozwiążesz problemu.

Jest to szczególnie ważne, jeśli piszesz do komórek w arkuszu, a następnie odczytujesz obliczone wyniki z funkcji arkusza, ponieważ xlCalculationManual uniemożliwia obliczenie skoroszytu. Aby obejść ten problem bez wyłączania SpeedUp , możesz dołączyć Application.Calculate aby uruchomić obliczenia w określonych punktach.

UWAGA: Ponieważ są to właściwości samej Application , musisz upewnić się, że zostały one ponownie włączone przed zamknięciem makra. To sprawia, że szczególnie ważne jest stosowanie programów obsługi błędów i unikanie wielu punktów wyjścia (np. End lub Unload Me ).

Z obsługą błędów:

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

Unikaj używania ActiveCell lub ActiveSheet w Excelu

Korzystanie z ActiveCell lub ActiveSheet może być źródłem błędów, jeśli (z jakiegokolwiek powodu) kod zostanie wykonany w niewłaściwym miejscu.

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"

• Korzystanie z Active* może powodować problemy w długo działających makrach, jeśli użytkownik się nudzi i kliknie inny arkusz lub otworzy inny skoroszyt.
• Może powodować problemy, jeśli Twój kod zostanie otwarty lub utworzy inny skoroszyt.
• Może powodować problemy, jeśli Twój kod korzysta z Sheets("MyOtherSheet").Select i zapomniałeś, na którym arkuszu byłeś, zanim zacząłeś czytać lub pisać na nim.

Nigdy nie zakładaj arkusza roboczego

Nawet jeśli cała praca jest skierowana do jednego arkusza, nadal bardzo dobrą praktyką jest jawne określanie arkusza roboczego w kodzie. Ten nawyk znacznie ułatwia późniejsze rozszerzanie kodu lub podnoszenie części (lub wszystkich) części Sub lub Function celu ponownego użycia w innym miejscu. Wielu programistów ustanawia nawyk (ponownego) używania tej samej lokalnej nazwy zmiennej dla arkusza roboczego w swoim kodzie, dzięki czemu ponowne użycie tego kodu jest jeszcze prostsze.

Na przykład poniższy kod jest niejednoznaczny - ale działa! - dopóki programista nie aktywuje się ani nie zmienia na inny arkusz:

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

Jeśli Sheet1 jest aktywny, wówczas komórka A1 na arkuszu 1 zostanie wypełniona bieżącą datą i godziną. Ale jeśli użytkownik zmieni arkusze z jakiegokolwiek powodu, kod zaktualizuje wszystko, co arkusz jest aktualnie aktywny. Docelowy arkusz roboczy jest niejednoznaczny.

Najlepszą praktyką jest zawsze identyfikowanie arkusza, do którego odnosi się Twój kod:

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

Powyższy kod wyraźnie identyfikuje zarówno skoroszyt, jak i arkusz roboczy. Chociaż może się to wydawać przesadą, stworzenie dobrego nawyku dotyczącego referencji docelowych uchroni cię przed przyszłymi problemami.

Unikaj używania WYBIERZ lub AKTYWUJ

Bardzo rzadko zdarza się, że będziesz chciał użyć opcji Select lub Activate w kodzie, ale niektóre metody Excela wymagają aktywacji arkusza roboczego lub skoroszytu, zanim zaczną działać zgodnie z oczekiwaniami.

Jeśli dopiero zaczynasz się uczyć VBA, często sugerowane jest nagrywanie swoich działań za pomocą rejestratora makr, a następnie spójrz na kod. Na przykład zarejestrowałem działania podjęte w celu wprowadzenia wartości do komórki D3 na arkuszu 2, a kod makra wygląda następująco:

Option Explicit
Sub Macro1()
'
' Macro1 Macro
'

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

Pamiętaj jednak, że rejestrator makr tworzy wiersz kodu dla KAŻDEGO działania (użytkownika). Obejmuje to kliknięcie karty arkusza roboczego, aby wybrać Arkusz2 ( Sheets("Sheet2").Select ), kliknięcie komórki D3 przed wprowadzeniem wartości ( Range("D3").Select ) i użycie klawisza Enter (który jest efektywny ” wybierając „komórkę poniżej aktualnie wybranej komórki: Range("D4").Select ).

Istnieje wiele problemów z używaniem. .Select tutaj:

• Arkusz nie zawsze jest określony. Dzieje się tak, jeśli nie przełączasz arkuszy podczas nagrywania, i oznacza to, że kod da różne wyniki dla różnych aktywnych arkuszy.
• .Select() działa wolno. Nawet jeśli Application.ScreenUpdating ma wartość False , jest to niepotrzebna operacja do przetworzenia.
• .Select() jest niesforny. Jeśli wartość Application.ScreenUpdating zostanie pozostawiona na wartość True , program Excel faktycznie wybierze komórki, arkusz roboczy, formularz ... cokolwiek to jest, z czym pracujesz. Jest to stresujące dla oczu i naprawdę nieprzyjemne do oglądania.
• .Select() uruchomi detektory. Jest to już trochę zaawansowane, ale jeśli nie Worksheet_SelectionChange() tego, zostaną uruchomione funkcje takie jak Worksheet_SelectionChange() .

Kiedy piszesz w VBA, wszystkie działania związane z pisaniem na klawiaturze (tj. Instrukcje Select ) nie są już konieczne. Twój kod może zostać zredukowany do jednej instrukcji, aby umieścić wartość w komórce:

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

(Powyższy przykład LEPIEJ pokazuje użycie zmiennych pośrednich do oddzielenia różnych części odwołania do komórki. Przykład DOBRY zawsze będzie działał dobrze, ale może być bardzo kłopotliwy w znacznie dłuższych modułach kodu i trudniejszy do debugowania, jeśli jedno z odniesień zostanie źle wpisane. )

** UWAGA: rejestrator makr przyjmuje wiele założeń dotyczących rodzaju wprowadzanych danych, w tym przypadku wpisując wartość ciągu jako formułę do utworzenia wartości. Twój kod nie musi tego robić i może po prostu przypisać wartość liczbową bezpośrednio do komórki, jak pokazano powyżej.

** UWAGA 2: zalecaną praktyką jest ustawienie lokalnej zmiennej skoroszytu na ThisWorkbook zamiast ActiveWorkbook (chyba że jest to wyraźnie potrzebne). Powodem jest to, że twoje makro będzie na ogół potrzebowało / używało zasobów w jakimkolwiek skoroszycie, z którego pochodzi kod VBA i NIE będzie wyglądało poza tym skoroszytem - ponownie, chyba że wyraźnie zalecisz kodowi pracę z innym skoroszytem. Gdy w programie Excel jest otwartych wiele skoroszytów, ActiveWorkbook jest tym, którego fokus może się różnić od skoroszytu wyświetlanego w edytorze VBA . Więc myślisz, że wykonujesz w jednym skoroszycie, gdy naprawdę odwołujesz się do innego. ThisWorkbook skoroszyt odnosi się do skoroszytu zawierającego wykonywany kod.

Zawsze definiuj i ustawiaj odniesienia do wszystkich skoroszytów i arkuszy

Podczas pracy z wieloma otwartymi skoroszytami, z których każdy może mieć wiele Arkuszy, najbezpieczniej jest zdefiniować i ustawić odniesienia do wszystkich skoroszytów i Arkuszy.

Nie polegaj na ActiveWorkbook lub ActiveSheet ponieważ mogą one zostać zmienione przez użytkownika.

Poniższy przykład kodu pokazuje, jak skopiować zakres od „Raw_Data” arkusz w skoroszycie „Data.xlsx” do „Refined_Data” blachy w „Results.xlsx” skoroszycie.

Procedura pokazuje również, jak kopiować i wklejać bez użycia metody 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

Obiekt WorksheetFunction działa szybciej niż odpowiednik UDF

VBA jest kompilowany w czasie wykonywania, co ma ogromny negatywny wpływ na jego wydajność, wszystko wbudowane będzie szybsze, spróbuj ich użyć.

Jako przykład porównuję funkcje SUMA i LICZ.JEŻ, ale możesz użyć, jeśli w ogóle możesz rozwiązać za pomocą WorkSheetFunctions.

Pierwszą próbą dla nich byłoby przejście przez zakres i przetworzenie go komórka po komórce (przy użyciu zakresu):

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

Jednym z ulepszeń może być przechowywanie wartości zakresu w tablicy i proces, który:

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

Ale zamiast pisać dowolną pętlę, możesz użyć funkcji Application.Worksheetfunction która jest bardzo przydatna do wykonywania prostych formuł:

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

Lub, dla bardziej skomplikowanych obliczeń, możesz nawet użyć 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

I na koniec, działając powyżej Subskrybentów po 25 000 razy, oto średni (5 testów) czas w milisekundach (oczywiście będzie różny na każdym komputerze, ale w porównaniu do siebie zachowają się podobnie):

1. UseWorksheetFunction: 2156 ms
2. UseArray: 2219 ms (+ 3%)
3. Użyj oszacowania: 4693 ms (+ 118%)
4. Zakres użycia: 6530 ms (+ 203%)

Unikaj zmiany przeznaczenia nazw właściwości lub metod jako zmiennych

Zasadniczo nie uważa się za „najlepszą praktykę”, aby zmienić przeznaczenie zastrzeżonych nazw Właściwości lub Metod jako nazwy własnych procedur i zmiennych.

Zła forma - Chociaż poniższe (legalnie rzecz biorąc) jest legalne, działający kod przekierowujący metodę Find , a także właściwości Row , Column i Address , może powodować problemy / konflikty z niejednoznacznością nazw i ogólnie jest po prostu mylący.

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

Dobra forma - dzięki zmianie nazwy wszystkich zastrzeżonych słów na ścisłe, ale unikalne przybliżenia oryginałów, uniknięto potencjalnych konfliktów nazw.

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

Chociaż może nadejść czas, kiedy chcesz celowo przepisać standardową metodę lub właściwość według własnych specyfikacji, sytuacje te są nieliczne i dalekie. W większości unikaj ponownego wykorzystywania zastrzeżonych nazw dla własnych konstrukcji.