excel-vba
Typowe błędy

Kwalifikujące się referencje

Odnosząc się do worksheet range , range lub pojedynczych cells , ważne jest pełne zakwalifikowanie odwołania.

Na przykład:

ThisWorkbook.Worksheets("Sheet1").Range(Cells(1, 2), Cells(2, 3)).Copy

Nie jest w pełni kwalifikowany: Odwołania do Cells nie mają powiązanego skoroszytu i arkusza roboczego. Bez wyraźnego odwołania Komórki domyślnie odnoszą się do ActiveSheet . Tak więc ten kod się nie powiedzie (wygeneruje niepoprawne wyniki), jeśli arkusz roboczy inny niż Sheet1 jest bieżącym arkuszem ActiveSheet .

Najprostszym sposobem na poprawienie tego jest użycie instrukcji With w następujący sposób:

With ThisWorkbook.Worksheets("Sheet1")
    .Range(.Cells(1, 2), .Cells(2, 3)).Copy
End With

Alternatywnie możesz użyć zmiennej Arkusz. (Najprawdopodobniej będzie to preferowana metoda, jeśli kod musi odwoływać się do wielu arkuszy, np. Kopiować dane z jednego arkusza na inny).

Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("Sheet1")
ws1.Range(ws1.Cells(1, 2), ws1.Cells(2, 3)).Copy

Innym częstym problemem jest odwoływanie się do kolekcji arkuszy roboczych bez kwalifikowania skoroszytu. Na przykład:

Worksheets("Sheet1").Copy

Arkusz Sheet1 nie jest w pełni kwalifikowany i nie ma skoroszytu. Może to się nie powieść, jeśli w skrypcie odwołuje się wiele skoroszytów. Zamiast tego użyj jednej z następujących opcji:

ThisWorkbook.Worksheets("Sheet1")       '<--ThisWorkbook refers to the workbook containing 
                                        'the running VBA code
Workbooks("Book1").Worksheets("Sheet1") '<--Where Book1 is the workbook containing Sheet1

Unikaj jednak używania następujących opcji:

ActiveWorkbook.Worksheets("Sheet1")     '<--Valid, but if another workbook is activated
                                        'the reference will be changed

Podobnie w przypadku obiektów range , jeśli nie zostanie jawnie zakwalifikowany, range będzie odnosił się do aktualnie aktywnego arkusza:

Range("a1")

Jest taki sam jak:

ActiveSheet.Range("a1")

Usuwanie wierszy lub kolumn w pętli

Jeśli chcesz usunąć wiersze (lub kolumny) w pętli, zawsze powinieneś zapętlić zaczynając od końca zakresu i cofać się na każdym kroku. W przypadku użycia kodu:

Dim i As Long
With Workbooks("Book1").Worksheets("Sheet1")
    For i = 1 To 4
        If IsEmpty(.Cells(i, 1)) Then .Rows(i).Delete
    Next i
End With

Tęsknisz za niektórymi rzędami. Na przykład, jeśli kod usuwa wiersz 3, wówczas wiersz 4 staje się wierszem 3. Jednak zmienna i zmieni się na 4. Tak więc w tym przypadku kod pominie jeden wiersz i sprawdzi inny, który nie był wcześniej w zakresie.

Właściwy kod to

Dim i As Long
With Workbooks("Book1").Worksheets("Sheet1")
    For i = 4 To 1 Step -1
        If IsEmpty(.Cells(i, 1)) Then .Rows(i).Delete
    Next i
End With

ActiveWorkbook vs. ThisWorkbook

ActiveWorkbook i ThisWorkbook czasami używane zamiennie przez nowych użytkowników języka VBA bez pełnego zrozumienia, do którego obiektu się odnosi, może to powodować niepożądane zachowanie w czasie wykonywania. Oba te obiekty należą do obiektu aplikacji

Obiekt ActiveWorkbook odnosi się do skoroszytu, który jest obecnie w najwyższym widoku obiektu aplikacji Excel w czasie wykonywania. (np. skoroszyt, który można wyświetlić i z którym można korzystać w momencie odwołania do tego obiektu)

Sub ActiveWorkbookExample()

'// Let's assume that 'Other Workbook.xlsx' has "Bar" written in A1.

    ActiveWorkbook.ActiveSheet.Range("A1").Value = "Foo"
    Debug.Print ActiveWorkbook.ActiveSheet.Range("A1").Value '// Prints "Foo"

    Workbooks.Open("C:\Users\BloggsJ\Other Workbook.xlsx")
    Debug.Print ActiveWorkbook.ActiveSheet.Range("A1").Value '// Prints "Bar"

    Workbooks.Add 1
    Debug.Print ActiveWorkbook.ActiveSheet.Range("A1").Value '// Prints nothing

End Sub

Obiekt ThisWorkbook odnosi się do skoroszytu, do którego należy kod w momencie jego wykonywania.

Sub ThisWorkbookExample()

'// Let's assume to begin that this code is in the same workbook that is currently active

    ActiveWorkbook.Sheet1.Range("A1").Value = "Foo"
    Workbooks.Add 1
    ActiveWorkbook.ActiveSheet.Range("A1").Value = "Bar"

    Debug.Print ActiveWorkbook.ActiveSheet.Range("A1").Value '// Prints "Bar"
    Debug.Print ThisWorkbook.Sheet1.Range("A1").Value '// Prints "Foo"

End Sub

Interfejs jednego dokumentu a wiele interfejsów dokumentów

Należy pamiętać, że program Microsoft Excel 2013 (i nowsze wersje) korzysta z interfejsu pojedynczego dokumentu (SDI), a program Excel 2010 (i poniżej) korzysta z wielu interfejsów dokumentów (MDI).

Oznacza to, że dla programu Excel 2013 (SDI) każdy skoroszyt w jednym wystąpieniu programu Excel zawiera własny wstążkowy interfejs użytkownika:

Z kolei w przypadku programu Excel 2010 każdy skoroszyt w jednym wystąpieniu programu Excel korzystał ze wspólnego interfejsu użytkownika wstążki (MDI):

Rodzi to kilka ważnych problemów, jeśli chcesz przeprowadzić migrację kodu VBA (2010 <-> 2013), które współdziałają ze Wstążką.

Należy utworzyć procedurę aktualizującą formanty interfejsu użytkownika wstążki w tym samym stanie we wszystkich skoroszytach dla programu Excel 2013 i nowszych wersji.

Uwaga:

1. Wszystkie metody, zdarzenia i właściwości okien programu Excel na poziomie aplikacji pozostają niezmienione. ( Application.ActiveWindow , Application.Windows ...)
2. W programie Excel 2013 i nowszych (SDI) wszystkie metody, zdarzenia i właściwości okna na poziomie skoroszytu działają teraz w oknie najwyższego poziomu. Możliwe jest pobranie uchwytu tego okna najwyższego poziomu za pomocą Application.Hwnd

Aby uzyskać więcej informacji, zobacz źródło tego przykładu: MSDN .

Powoduje to również pewne problemy z modelowymi formularzami użytkownika. Zobacz tutaj rozwiązanie.