Jak uniknąć używania Select w Excel VBA

Question

Słyszałem dużo o zrozumiałej wstrętności używania .Selectw Excel VBA, ale nie jestem pewien, jak go uniknąć. Przekonałem się, że mój kod byłby bardziej przydatny do ponownego wykorzystania, gdybym mógł używać zmiennych zamiast Selectfunkcji. Jednak nie jestem pewien, jak odnieść się do rzeczy (takich jak ActiveCellitp.), Jeśli nie używam Select.

Znalazłem ten artykuł na temat zakresów i ten przykład na temat korzyści wynikających z nieużywania funkcji select , ale nie mogę znaleźć niczego, jak to zrobić .

Answer 1

Kilka przykładów, jak unikać zaznaczania

Użyj Dim'd zmiennych

Dim rng as Range

Setzmienną do wymaganego zakresu. Istnieje wiele sposobów odwoływania się do zakresu pojedynczej komórki:

Set rng = Range("A1")
Set rng = Cells(1, 1)
Set rng = Range("NamedRange")

Lub zakres wielokomórkowy:

Set rng = Range("A1:B10")
Set rng = Range("A1", "B10")
Set rng = Range(Cells(1, 1), Cells(10, 2))
Set rng = Range("AnotherNamedRange")
Set rng = Range("A1").Resize(10, 2)

Państwo może używać skrótu do Evaluatemetody, ale to jest mniej wydajny i generalnie należy unikać w kodzie produkcyjnym.

Set rng = [A1]
Set rng = [A1:B10]

Wszystkie powyższe przykłady odnoszą się do komórek w aktywnym arkuszu . O ile nie chcesz specjalnie pracować tylko z aktywnym arkuszem, lepiej też przyciemnić Worksheetzmienną:

Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
Set rng = ws.Cells(1, 1)
With ws
    Set rng = .Range(.Cells(1, 1), .Cells(2, 10))
End With

Jeśli nie chcą pracować z ActiveSheet, dla jasności, że najlepiej być jawne. Ale uważaj, ponieważ niektóre Worksheetmetody zmieniają aktywny arkusz.

Set rng = ActiveSheet.Range("A1")

Ponownie odnosi się to do aktywnego skoroszytu . Jeśli nie chcesz specjalnie pracować tylko z ActiveWorkbooklub ThisWorkbook, lepiej też przyciemnić Workbookzmienną.

Dim wb As Workbook
Set wb = Application.Workbooks("Book1")
Set rng = wb.Worksheets("Sheet1").Range("A1")

Jeśli nie chcą pracować z ActiveWorkbook, dla jasności, że najlepiej być jawne. Ale uważaj, ponieważ wiele WorkBookmetod zmienia aktywną książkę.

Set rng = ActiveWorkbook.Worksheets("Sheet1").Range("A1")

Możesz także użyć ThisWorkbookobiektu, aby odnieść się do książki zawierającej działający kod.

Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1")

Typowym (złym) fragmentem kodu jest otwarcie książki, pobranie danych i ponowne zamknięcie

To jest złe:

Sub foo()
    Dim v as Variant
    Workbooks("Book1.xlsx").Sheets(1).Range("A1").Clear
    Workbooks.Open("C:\Path\To\SomeClosedBook.xlsx")
    v = ActiveWorkbook.Sheets(1).Range("A1").Value
    Workbooks("SomeAlreadyOpenBook.xlsx").Activate
    ActiveWorkbook.Sheets("SomeSheet").Range("A1").Value = v
    Workbooks(2).Activate
    ActiveWorkbook.Close()
End Sub

A byłoby lepiej:

Sub foo()
    Dim v as Variant
    Dim wb1 as Workbook
    Dim  wb2 as Workbook
    Set wb1 = Workbooks("SomeAlreadyOpenBook.xlsx")
    Set wb2 = Workbooks.Open("C:\Path\To\SomeClosedBook.xlsx")
    v = wb2.Sheets("SomeSheet").Range("A1").Value
    wb1.Sheets("SomeOtherSheet").Range("A1").Value = v
    wb2.Close()
End Sub

Przekaż zakresy do swoich Subs i Functionjako zmienne zakresu:

Sub ClearRange(r as Range)
    r.ClearContents
    '....
End Sub

Sub MyMacro()
    Dim rng as Range
    Set rng = ThisWorkbook.Worksheets("SomeSheet").Range("A1:B10")
    ClearRange rng
End Sub

Powinieneś także zastosować metody (takie jak Findi Copy) do zmiennych:

Dim rng1 As Range
Dim rng2 As Range
Set rng1 = ThisWorkbook.Worksheets("SomeSheet").Range("A1:A10")
Set rng2 = ThisWorkbook.Worksheets("SomeSheet").Range("B1:B10")
rng1.Copy rng2

Jeśli pętla obejmuje zakres komórek, często lepiej (szybciej) skopiować wartości zakresu do tablicy wariantu i wykonać pętlę nad tym:

Dim dat As Variant
Dim rng As Range
Dim i As Long

Set rng = ThisWorkbook.Worksheets("SomeSheet").Range("A1:A10000")
dat = rng.Value  ' dat is now array (1 to 10000, 1 to 1)
for i = LBound(dat, 1) to UBound(dat, 1)
    dat(i,1) = dat(i, 1) * 10 ' Or whatever operation you need to perform
next
rng.Value = dat ' put new values back on sheet

To mały przedsmak tego, co jest możliwe.

Answer 2

Dwa główne powody .Select, .Activate, Selection, Activecell, Activesheet, Activeworkbook, itd. Należy unikać

1. Spowalnia twój kod.
2. Jest to zwykle główna przyczyna błędów w czasie wykonywania.

Jak tego uniknąć?

1) Pracuj bezpośrednio z odpowiednimi obiektami

Rozważ ten kod

Sheets("Sheet1").Activate
Range("A1").Select
Selection.Value = "Blah"
Selection.NumberFormat = "@"

Ten kod można również zapisać jako

With Sheets("Sheet1").Range("A1")
    .Value = "Blah"
    .NumberFormat = "@"
End With

2) W razie potrzeby zadeklaruj swoje zmienne. Ten sam kod powyżej można zapisać jako

Dim ws as worksheet

Set ws = Sheets("Sheet1")

With ws.Range("A1")
    .Value = "Blah"
    .NumberFormat = "@"
End With

Answer 3

Jeden mały punkt podkreślenia dodam do wszystkich doskonałych odpowiedzi udzielonych wcześniej:

Prawdopodobnie największą rzeczą, jaką możesz zrobić, aby uniknąć używania Select, jest jak najwięcej, używanie nazwanych zakresów (w połączeniu ze znaczącymi nazwami zmiennych) w kodzie VBA . Ten punkt został wspomniany powyżej, ale został nieco zamazany; zasługuje jednak na szczególną uwagę.

Oto kilka dodatkowych powodów, dla których warto liberalnie używać nazwanych zakresów, chociaż jestem pewien, że mógłbym wymyślić więcej.

Nazwane zakresy ułatwiają odczytywanie i zrozumienie kodu.

Przykład:

Dim Months As Range
Dim MonthlySales As Range

Set Months = Range("Months")
' E.g, "Months" might be a named range referring to A1:A12

Set MonthlySales = Range("MonthlySales")
' E.g, "Monthly Sales" might be a named range referring to B1:B12

Dim Month As Range
For Each Month in Months
    Debug.Print MonthlySales(Month.Row)
Next Month

Jest całkiem oczywiste, co zawiera nazwane zakresy Monthsi co MonthlySaleszawiera procedura.

Dlaczego to jest ważne? Częściowo dlatego, że innym osobom łatwiej to zrozumieć, ale nawet jeśli jesteś jedyną osobą, która kiedykolwiek zobaczy lub użyje twojego kodu, nadal powinieneś używać nazwanych zakresów i dobrych nazw zmiennych, ponieważ zapomnisz, co zamierzałeś z tym zrobić rok później, a stracisz 30 minut na zastanawianie się, co robi Twój kod.

Nazwane zakresy zapewniają, że makra nie ulegną uszkodzeniu, gdy (nie jeśli!) Zmieni się konfiguracja arkusza kalkulacyjnego.

Zastanów się, czy powyższy przykład został napisany w ten sposób:

Dim rng1 As Range
Dim rng2 As Range

Set rng1 = Range("A1:A12")
Set rng2 = Range("B1:B12")

Dim rng3 As Range
For Each rng3 in rng1
    Debug.Print rng2(rng3.Row)
Next rng3

Ten kod na początku będzie działał dobrze - to znaczy do czasu, gdy ty lub przyszły użytkownik zdecydujesz „o rany, myślę, że dodam nową kolumnę z rokiem w kolumnie A!” Lub wstawię kolumnę wydatków między miesiącami a sprzedaży lub dodaj nagłówek do każdej kolumny. Twój kod jest uszkodzony. A ponieważ użyłeś okropnych nazw zmiennych, zajmie ci to dużo więcej czasu, niż powinno to zająć, aby dowiedzieć się, jak to naprawić.

Gdybyś na początku używał nazwanych zakresów, kolumny Monthsi Salesmożna by przesuwać w dowolne miejsce, a kod nadal działałby dobrze.

Answer 4

Dam krótką odpowiedź, ponieważ wszyscy inni udzielili długiej.

Otrzymasz .select i .activate za każdym razem, gdy zarejestrujesz makra i użyjesz ich ponownie. Kiedy wybierzesz komórkę lub arkusz, po prostu je aktywujesz. Od tego momentu za każdym razem, gdy używasz niekwalifikowanych odwołań, tak jak Range.Valueużywają one tylko aktywnej komórki i arkusza. Może to również być problematyczne, jeśli nie obserwujesz, gdzie znajduje się Twój kod lub użytkownik klika skoroszyt.

Możesz więc wyeliminować te problemy, bezpośrednio odwołując się do komórek. Który idzie:

'create and set a range
Dim Rng As Excel.Range
Set Rng = Workbooks("Book1").Worksheets("Sheet1").Range("A1")
'OR
Set Rng = Workbooks(1).Worksheets(1).Cells(1, 1)

Albo mógłbyś

'Just deal with the cell directly rather than creating a range
'I want to put the string "Hello" in Range A1 of sheet 1
Workbooks("Book1").Worksheets("Sheet1").Range("A1").value = "Hello"
'OR
Workbooks(1).Worksheets(1).Cells(1, 1).value = "Hello"

Istnieje wiele kombinacji tych metod, ale taka byłaby ogólna idea wyrażona możliwie najkrócej dla niecierpliwych ludzi, takich jak ja.

Answer 5

„... i stwierdzam, że mój kod byłby łatwiejszy do ponownego wykorzystania, gdybym mógł używać zmiennych zamiast funkcji Select”.

Chociaż nie przychodzi mi do głowy nic więcej niż odosobniona garstka sytuacji, w których .Selectbyłby lepszy wybór niż bezpośrednie odniesienie do komórki, stanąłbym w obronie Selectioni wskazał, że nie powinno się tego wyrzucać z tych samych powodów, których .Selectnależy unikać.

Są chwile, kiedy krótkie, oszczędzające czas podprogramy makr przypisane do kombinacji klawiszy skrótu, dostępne po naciśnięciu kilku klawiszy, oszczędzają dużo czasu. Możliwość wybrania grupy komórek w celu wprowadzenia kodu operacyjnego działa cuda w przypadku danych kieszonkowych, które nie są zgodne z formatem danych obejmującym cały arkusz. Podobnie jak w przypadku wybrania grupy komórek i zastosowania zmiany formatu, wybranie grupy komórek, na których mają być uruchamiane specjalne makra, może znacznie zaoszczędzić czas.

Przykłady ram podrzędnych opartych na wyborze:

Public Sub Run_on_Selected()
    Dim rng As Range, rSEL As Range
    Set rSEL = Selection    'store the current selection in case it changes
    For Each rng In rSEL
        Debug.Print rng.Address(0, 0)
        'cell-by-cell operational code here
    Next rng
    Set rSEL = Nothing
End Sub

Public Sub Run_on_Selected_Visible()
    'this is better for selected ranges on filtered data or containing hidden rows/columns
    Dim rng As Range, rSEL As Range
    Set rSEL = Selection    'store the current selection in case it changes
    For Each rng In rSEL.SpecialCells(xlCellTypeVisible)
        Debug.Print rng.Address(0, 0)
        'cell-by-cell operational code here
    Next rng
    Set rSEL = Nothing
End Sub

Public Sub Run_on_Discontiguous_Area()
    'this is better for selected ranges of discontiguous areas
    Dim ara As Range, rng As Range, rSEL As Range
    Set rSEL = Selection    'store the current selection in case it changes
    For Each ara In rSEL.Areas
        Debug.Print ara.Address(0, 0)
        'cell group operational code here
        For Each rng In ara.Areas
            Debug.Print rng.Address(0, 0)
            'cell-by-cell operational code here
        Next rng
    Next ara
    Set rSEL = Nothing
End Sub

Rzeczywisty kod do przetworzenia może być dowolny, od jednej linii do wielu modułów. Użyłem tej metody do zainicjowania długotrwałych procedur na poszarpanej selekcji komórek zawierających nazwy plików zewnętrznych skoroszytów.

Krótko mówiąc, nie wyrzucaj z Selectionpowodu bliskiego związku z .Selecti ActiveCell. Jako właściwość arkusza ma wiele innych celów.

_{(Tak, wiem, że to pytanie dotyczyło .Select, Selectionale chciałem usunąć wszelkie nieporozumienia, które mogliby wywnioskować początkujący programiści VBA).}

Answer 6

Unikanie Selecti Activatejest to ruch, który sprawia, że ​​jesteś nieco lepszym programistą VBA. Ogólnie Selecti Activatesą używane podczas rejestrowania makra, dlatego Parentarkusz lub zakres jest zawsze uważany za aktywny.

Oto, jak możesz tego uniknąć Selecti Activatew następujących przypadkach:

---

Dodanie nowego arkusza roboczego i skopiowanie w nim komórki:

Od (kod wygenerowany za pomocą rejestratora makr):

Sub Makro2()
    Range("B2").Select
    Sheets.Add After:=ActiveSheet
    Sheets("Tabelle1").Select
    Sheets("Tabelle1").Name = "NewName"
    ActiveCell.FormulaR1C1 = "12"
    Range("B2").Select
    Selection.Copy
    Range("B3").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

Do:

Sub TestMe()
    Dim ws As Worksheet
    Set ws = Worksheets.Add
    With ws
        .Name = "NewName"
        .Range("B2") = 12
        .Range("B2").Copy Destination:=.Range("B3")
    End With
End Sub

Jeśli chcesz skopiować zakres między arkuszami:

Od:

Sheets("Source").Select
Columns("A:D").Select
Selection.Copy
Sheets("Target").Select
Columns("A:D").Select
ActiveSheet.Paste

Do:

Worksheets("Source").Columns("A:D").Copy Destination:=Worksheets("Target").Range("a1")

---

Używanie fantazyjnych nazwanych zakresów

Możesz uzyskać do nich dostęp [], co jest naprawdę piękne w porównaniu z drugą drogą. Sprawdź się:

Dim Months As Range
Dim MonthlySales As Range

Set Months = Range("Months")    
Set MonthlySales = Range("MonthlySales")

Set Months =[Months]
Set MonthlySales = [MonthlySales]

Przykład z góry wyglądałby tak:

Worksheets("Source").Columns("A:D").Copy Destination:=Worksheets("Target").[A1]

---

Nie kopiowanie wartości, ale ich przyjmowanie

Zwykle, jeśli chcesz select, najprawdopodobniej coś kopiujesz. Jeśli interesują Cię tylko wartości, jest to dobra opcja, aby uniknąć zaznaczania:

Range("B1:B6").Value = Range("A1:A6").Value

---

Zawsze staraj się odwoływać również do arkusza roboczego

Jest to prawdopodobnie najczęstszy błąd w VBA . Za każdym razem, gdy kopiujesz zakresy, czasami arkusz roboczy nie jest przywoływany i dlatego VBA traktuje niewłaściwy arkusz jako ActiveWorksheet.

'This will work only if the 2. Worksheet is selected!
Public Sub TestMe()
    Dim rng As Range
    Set rng = Worksheets(2).Range(Cells(1, 1), Cells(2, 2)).Copy
End Sub

'This works always!
Public Sub TestMe2()
    Dim rng As Range
    With Worksheets(2)
        .Range(.Cells(1, 1), .Cells(2, 2)).Copy
    End With
End Sub

---

Czy naprawdę mogę nigdy nie używać .Selectani .Activatedo niczego?

• Dobrym przykładem tego, kiedy możesz być usprawiedliwiony w używaniu .Activatei .Selectkiedy chcesz, jest upewnienie się, że określony arkusz roboczy jest wybrany ze względów wizualnych. Np. Program Excel zawsze otwierał się z arkuszem okładki wybranym jako pierwszym, niezależnie od tego, który arkusz był ActiveSheet, gdy plik był zamykany.

Tak więc coś takiego jak poniższy kod jest absolutnie w porządku:

Private Sub Workbook_Open()
    Worksheets("Cover").Activate
End Sub

• Innym dobrym przykładem jest sytuacja, gdy musisz wyeksportować wszystkie arkusze do jednego pliku PDF, jak wspomniano w tym przypadku - Jak uniknąć zaznaczenia / aktywnych instrukcji w VBA w tym przykładzie?

• Gdy polecenie działa tylko ActiveWindowjak ActiveWindow.Zoom lub ActiveWindow.FreezePanes

Answer 7

Zwróć uwagę, że poniżej porównuję podejście Select (to, którego OP chce uniknąć) z podejściem Range (i to jest odpowiedź na pytanie). Więc nie przestawaj czytać, gdy zobaczysz pierwszy Select.

To naprawdę zależy od tego, co próbujesz zrobić. W każdym razie prosty przykład mógłby się przydać. Załóżmy, że chcesz ustawić wartość aktywnej komórki na „foo”. Używając ActiveCell, napisałbyś coś takiego:

Sub Macro1()
    ActiveCell.Value = "foo"
End Sub

Jeśli chcesz go użyć dla komórki, która nie jest aktywna, na przykład dla "B2", powinieneś ją najpierw wybrać, na przykład:

Sub Macro2()
    Range("B2").Select
    Macro1
End Sub

Korzystając z zakresów, możesz napisać bardziej ogólne makro, którego można użyć do ustawienia wartości dowolnej komórki na cokolwiek chcesz:

Sub SetValue(cellAddress As String, aVal As Variant)
    Range(cellAddress).Value = aVal
End Sub

Następnie możesz przepisać Macro2 jako:

Sub Macro2()
    SetCellValue "B2", "foo"
End Sub

I Macro1 jako:

Sub Macro1()
    SetValue ActiveCell.Address, "foo"
End Sub

Answer 8

Zawsze określaj skoroszyt, arkusz i komórkę / zakres.

Na przykład:

Thisworkbook.Worksheets("fred").cells(1,1)
Workbooks("bob").Worksheets("fred").cells(1,1)

Ponieważ użytkownicy końcowi zawsze będą po prostu klikać przyciski, a gdy tylko fokus zostanie przeniesiony ze skoroszytu, z którym kod chce pracować, wszystko idzie całkowicie nie tak.

I nigdy nie używaj indeksu skoroszytu.

Workbooks(1).Worksheets("fred").cells(1,1)

Nie wiesz, jakie inne skoroszyty zostaną otwarte, gdy użytkownik uruchomi Twój kod.

Answer 9

Metody te są raczej piętnowane, więc przejęcie przywództwa Vityaty i Jeeped w celu narysowania linii na piasku:

Wezwanie .Activate, .Select, Selection, ActiveSomethingmetody / właściwości

Zasadniczo dlatego, że są wywoływane głównie w celu obsługi danych wejściowych użytkownika za pośrednictwem interfejsu użytkownika aplikacji. Ponieważ są to metody wywoływane, gdy użytkownik obsługuje obiekty za pośrednictwem interfejsu użytkownika, są one tymi, które są rejestrowane przez rejestrator makr i dlatego wywoływanie ich jest w większości sytuacji albo kruche, albo zbędne: nie trzeba wybierać obiekt tak, aby wykonać akcję Selectionzaraz potem.

Jednak definicja ta reguluje sytuacje, w których są wymagane:

Kiedy zadzwonić .Activate, .Select, .Selection, .ActiveSomethingmetody / właściwości

Zasadniczo, gdy oczekujesz, że użytkownik końcowy odegra rolę w wykonaniu.

Jeśli programujesz i oczekujesz, że użytkownik wybierze wystąpienia obiektów do obsługi przez kod, wtedy .Selectionlub .ActiveObjectsą odpowiednie.

Z drugiej strony, .Selecti .Activatesą przydatne, kiedy można wywnioskować użytkownika następną akcję i chcesz, aby Twój kod, aby poprowadzić użytkownika, ewentualnie oszczędzając mu / jej trochę czasu i kliknięć myszą. Na przykład, jeśli twój kod właśnie utworzył zupełnie nową instancję wykresu lub zaktualizowaną, użytkownik może chcieć to sprawdzić, a Ty możesz wywołać .Activateto lub jego arkusz, aby zaoszczędzić czas na szukaniu go; lub jeśli wiesz, że użytkownik będzie musiał zaktualizować niektóre wartości zakresu, możesz programowo wybrać ten zakres.

Answer 10

Używanie IMHO .selectpochodzi od ludzi, którzy tak jak ja zaczęli uczyć się VBA z konieczności poprzez nagrywanie makr, a następnie modyfikowanie kodu bez zdawania sobie z tego sprawy, .selecta późniejsze selectionto tylko niepotrzebne pośrednicy.

.select można uniknąć, jak wielu już opublikowanych, poprzez bezpośrednią pracę z już istniejącymi obiektami, co umożliwia różne pośrednie odniesienia, takie jak obliczanie i i j w złożony sposób, a następnie edytowanie komórki (i, j) itp.

W przeciwnym razie nie ma w sobie nic niejawnego .selecti możesz łatwo znaleźć zastosowanie do tego, np. Mam arkusz kalkulacyjny, który wypełniam datą, aktywuję makro, które robi z nim magię i eksportuje je w akceptowalnym formacie na oddzielnym arkuszu, który wymaga jednak ostatecznych ręcznych (nieprzewidywalnych) danych wejściowych do sąsiedniej komórki. A więc nadeszła chwila, .selectaby zaoszczędzić mi dodatkowego ruchu myszy i kliknięcia.

Answer 11

Aby uniknąć korzystania z tej .Selectmetody, możesz ustawić zmienną równą żądanej właściwości.

► Na przykład, jeśli chcesz mieć wartość w, Cell A1możesz ustawić zmienną równą właściwości value tej komórki.

• Przykład valOne = Range("A1").Value

► Na przykład, jeśli chcesz uzyskać nazwę kodową właściwości „ you could set a variable equal to theNazwa kodowa Arkusza3” tego arkusza.

• Przykład valTwo = Sheets("Sheet3").Codename

Answer 12

Zauważyłem, że żadna z tych odpowiedzi nie wspomina o właściwości .Offset . Można to również wykorzystać, aby uniknąć używania Selectakcji podczas manipulowania pewnymi komórkami, szczególnie w odniesieniu do wybranej komórki (jak wspomina OP ActiveCell).

Oto kilka przykładów.

Zakładam również, że „ActiveCell” to J4 .

ActiveCell.Offset(2, 0).Value = 12

• Spowoduje to zmianę komórki J6na wartość 12
• Minus -2 odnosiłby się do J2

ActiveCell.Offset(0,1).Copy ActiveCell.Offset(,2)

• Spowoduje to skopiowanie komórki k4do L4.
• Zauważ, że "0" nie jest potrzebne w parametrze przesunięcia, jeśli nie jest potrzebne (, 2)
• Podobnie jak w poprzednim przykładzie byłoby minus 1 i4

ActiveCell.Offset(, -1).EntireColumn.ClearContents

• Spowoduje to wyczyszczenie wartości we wszystkich komórkach w kolumnie k.

Nie oznacza to, że są one „lepsze” niż powyższe opcje, ale po prostu wymieniają alternatywy.

Answer 13

Jak uniknąć kopiowania i wklejania?

Spójrzmy prawdzie w oczy: ten pojawia się często podczas nagrywania makr:

Range("X1").Select
Selection.Copy
Range("Y9).Select
Selection.Paste

Chociaż jedyne, czego osoba chce, to:

Range("Y9").Value = Range("X1").Value

Dlatego zamiast używać kopiowania i wklejania w makrach VBA, radziłbym następujące proste podejście:

Destination_Range.Value = Source_Range.Value

Answer 14

Ten przykład, pracując z funkcją .Parent , pokazuje, jak ustawienie tylko jednego odwołania myRng umożliwia dynamiczny dostęp do całego środowiska bez żadnych elementów .Select, .Activate, .Activecell, .ActiveWorkbook, .ActiveSheet i tak dalej. (Nie ma żadnej ogólnej funkcji .Dziecko ).

Sub ShowParents()
    Dim myRng As Range
    Set myRng = ActiveCell
    Debug.Print myRng.Address                    ' An address of the selected cell
    Debug.Print myRng.Parent.name                ' The name of sheet, where MyRng is in
    Debug.Print myRng.Parent.Parent.name         ' The name of workbook, where MyRng is in
    Debug.Print myRng.Parent.Parent.Parent.name  ' The name of application, where MyRng is in

    ' You may use this feature to set reference to these objects
    Dim mySh  As Worksheet
    Dim myWbk As Workbook
    Dim myApp As Application

    Set mySh = myRng.Parent
    Set myWbk = myRng.Parent.Parent
    Set myApp = myRng.Parent.Parent.Parent
    Debug.Print mySh.name, mySh.Cells(10, 1).Value
    Debug.Print myWbk.name, myWbk.Sheets.Count
    Debug.Print myApp.name, myApp.Workbooks.Count

    ' You may use dynamically addressing
    With myRng
        .Copy

        ' Pastes in D1 on sheet 2 in the same workbook, where the copied cell is
        .Parent.Parent.Sheets(2).Range("D1").PasteSpecial xlValues

        ' Or myWbk.Sheets(2).Range("D1").PasteSpecial xlValues

        ' We may dynamically call active application too
        .Parent.Parent.Parent.CutCopyMode = False

        ' Or myApp.CutCopyMode = False
    End With
End Sub

Answer 15

Głównym powodem, dla którego nigdy nie należy używać arkusza Select lub Activesheet, jest to, że większość ludzi będzie mieć otwartych co najmniej kilka kolejnych skoroszytów (czasami dziesiątki) po uruchomieniu makra i jeśli klikną poza arkuszem, gdy makro jest uruchomione i klikną inne książkę, którą otworzyli, a następnie zmieni się „Arkusz aktywny”, a skoroszyt docelowy dla niekwalifikowanego polecenia „Wybierz” również się zmieni.

W najlepszym przypadku makro ulegnie awarii, w najgorszym możesz zapisać wartości lub zmienić komórki w niewłaściwym skoroszycie bez możliwości ich „cofnięcia”.

Mam prostą złotą zasadę, której się trzymam: dodaj zmienne o nazwach „wb” i „ws” dla obiektu skoroszytu i arkusza i zawsze używaj ich do odwoływania się do mojej książki makr. Jeśli chcę odwołać się do więcej niż jednej książki lub więcej niż jednego arkusza, dodaję więcej zmiennych.

Na przykład,

Dim wb as Workbook
Dim ws as Worksheet
Set wb = ThisWorkBook
Set ws = wb.sheets("Output")

Polecenie „Set wb = ThisWorkbook” jest absolutnie kluczowe. „ThisWorkbook” to specjalna wartość w programie Excel i oznacza skoroszyt, z którego aktualnie jest uruchamiany kod VBA . Bardzo pomocny skrót do ustawiania zmiennej skoroszytu za pomocą.

Gdy już to zrobisz u góry swojego Sub, używanie ich nie może być prostsze, po prostu używaj ich wszędzie tam, gdzie używałbyś „Selection”:

Aby więc zmienić wartość komórki „A1” w polu „Output” na „Hello”, zamiast:

Sheets("Output").Activate
ActiveSheet.Range("A1").Select
Selection.Value = "Hello"

Teraz możemy to zrobić:

ws.Range("A1").Value = "Hello"

Który jest nie tylko znacznie bardziej niezawodny i mniej podatny na awarię, jeśli użytkownik pracuje z wieloma arkuszami kalkulacyjnymi; jest też dużo krótszy, szybszy i łatwiejszy do napisania.

Dodatkową korzyścią jest to, że jeśli zawsze nazywasz swoje zmienne „wb” i „ws”, możesz kopiować i wklejać kod z jednej książki do drugiej, co zwykle będzie działać przy minimalnych potrzebnych zmianach, jeśli w ogóle.