Jak uniknąć używania Select w Excelu VBA

Dwa główne powody, dla których .Select/.Activate/Selection/Activecell/Activesheet/Activeworkbook itd... należy unikać

Spowalnia twój kod.
1. jest zwykle główną przyczyną błędów w uruchomieniu.

Jak tego uniknąć?

1) bezpośrednio pracować 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

Jeden mały punkt podkreślenia dodam do wszystkich doskonałych odpowiedzi podanych powyżej:

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

Oto kilka dodatkowych powodów, aby liberalnie używać nazwanych zakresów, chociaż jestem pewien, że mógłbym myśleć więcej.

Nazwane zakresy ułatwiają odczyt 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 dość oczywiste, co zawierają nazwane zakresy Months i MonthlySales i co robi procedura.

Nazwane zakresy zapewniają, że makra nie pękają kiedy(nie jeśli!) zmienia się konfiguracja arkusza kalkulacyjnego.

Rozważ, jeśli 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 będzie działał dobrze na początku - czyli dopóki ty lub przyszły użytkownik nie zdecyduje " gee wiz, myślę, że dodam nową kolumnę z rokiem w kolumnie A!", lub umieścić kolumnę wydatków między kolumnami miesięcy i sprzedaży, lub dodać nagłówek do każdej kolumny. Twój kod jest złamany. A ponieważ użyłeś strasznych nazw zmiennych, zajmie ci to o wiele więcej czasu, aby dowiedzieć się, jak to naprawić, niż powinno to zająć.

Jeśli na początku użyłeś nazwanych zakresów, Kolumny Months i Sales mogą być przesuwane do woli, a Twój kod będzie działał poprawnie.

Dam krótką odpowiedź, ponieważ wszyscy inni Dali długą.

Więc możesz wyeliminować te problemy poprzez bezpośrednio odsyła do twoich komórek. Co 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)

Or you could

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

Istnieją różne kombinacje tych metod, ale to byłaby ogólna idea wyrażona tak szybko, jak to możliwe dla niecierpliwych ludzi, takich jak ja.

"... i odkrywam, że mój kod byłby bardziej nadający się do ponownego użycia, gdybym mógł używać zmiennych zamiast wybranych funkcji."

Chociaż nie mogę myśleć o niczym więcej niż o odosobnionej garści sytuacji, w których .Select byłoby lepszym Wyborem niż bezpośrednie odwoływanie się do komórek, chciałbym stanąć do obrony {[2] } i zwrócić uwagę, że nie powinno się go wyrzucać z tych samych powodów, których należy unikać.

Są chwile, kiedy mamy krótkie, oszczędzające czas makro sub procedury przypisane do kombinacji skrótów klawiszowych dostępnych za dotknięciem kilku klawiszy oszczędzają dużo czasu. Jest w stanie wybrać grupę komórek do wprowadzenia kodu operacyjnego na działa cuda, gdy mamy do czynienia z kieszonkowymi danymi, które nie są zgodne z formatem danych w całym arkuszu roboczym. W ten sam sposób, w jaki można wybrać grupę komórek i zastosować zmianę formatu, wybranie grupy komórek do uruchomienia specjalnego kodu makra może być główną oszczędnością czasu.

Przykłady podprogramów opartych na selekcji framework:

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 pojedynczej linii do wielu modułów. Użyłem tej metody do zainicjowania długotrwałych procedur na poszarpanym zaznaczeniu komórek zawierających nazwy plików zewnętrznych skoroszytów.

Krótko mówiąc, nie odrzucaj Selection ze względu na jego ścisłe powiązanie z .Select i ActiveCell. Jako właściwość arkusza roboczego ma wiele innych celów.

_{(tak, Wiem, że to pytanie dotyczyło .Select, nie Selection ale chciałem usunąć wszelkie błędne przekonania, które początkujący Programiści VBA mogą wywnioskować.)}

Proszę zauważyć, ż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 pierwsze zaznaczenie.

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

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

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

Używając zakresów możesz napisać bardziej ogólne makro, które może być użyte do ustawienia wartości dowolnej komórki na dowolną:

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

Następnie możesz przepisać Makro2 jako:

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

Oraz Macro1 jako:

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

Unikanie Select i Activate to ruch, który sprawia, że jesteś nieco lepszym programistą VBA. Ogólnie rzecz biorąc, Select i Activate są używane, gdy makro jest rejestrowane, więc arkusz roboczy lub zakres Parent jest zawsze uważany za aktywny.

W ten sposób można uniknąć Select i Activate w następujących przypadkach:

Dodawanie nowego arkusza i kopiowanie komórki na nim:

From (kod wygenerowany z 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

Kiedy chcesz aby skopiować zakres między arkuszami:

From:

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życie fantazyjnych nazw zakresów

Możesz uzyskać do nich dostęp za pomocą []. Co jest naprawdę piękne, w porównaniu do innego sposobu. 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

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

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

Staraj się zawsze odwoływać się również do arkusza]}

Jest to prawdopodobnie najczęstszy błąd w vba . Za każdym razem, gdy kopiujesz zakresy, czasami arkusz roboczy nie jest odsyłany, a zatem VBA rozważa arkusz 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ć .Select lub .Activate do niczego?

Jedyny czas, kiedy możesz być uzasadniony do użycia .Activate i .Select jest wtedy, gdy chcesz upewnij się, że ze względów wizualnych wybrano konkretny arkusz roboczy. Na przykład, że Twój Excel zawsze otwierałby się z wybranym najpierw arkuszem okładki, pomijając, który był arkuszem aktywnym, gdy plik został zamknięty. Tak więc coś takiego jest absolutnie ok: {]}

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

Zawsze podawaj skoroszyt, arkusz roboczy 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ą klikać przyciski i jak tylko fokus przesunie się ze skoroszytu, z którym KOD chce pracować, wtedy wszystko pójdzie zupełnie nie tak.

I nigdy nie używaj indeksu skoroszytu.

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

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

IMHO użycie .select pochodzi od ludzi, którzy tak jak ja zaczęli uczyć się VBA z konieczności poprzez nagrywanie makr, a następnie modyfikowanie kodu, nie zdając sobie sprawy, że .select i kolejne selection to tylko niepotrzebny średni-men.

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

W Przeciwnym Razie, nie ma nic domyślnie źle z samym .select i możesz łatwo znaleźć do tego zastosowania, 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 jednak wymaga ostatecznych ręcznych (nieprzewidywalnych) wejść do sąsiedniej komórki. Więc nadchodzi moment .select, który oszczędza mi dodatkowy ruch myszy i kliknięcie.

Szybka Odpowiedź:

Aby uniknąć użycia metody .Select, możesz ustawić zmienną równą żądanej właściwości.

► na przykład, jeśli chcesz mieć wartość w Cell A1 moż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 mieć nazwę kodową ' Sheet3`, możesz ustawić zmienną równą właściwości codename tego arkusza roboczego.

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

Mam nadzieję, że pomaga. Daj znać, jeśli masz jakieś pytania.

W 1999 roku, w wyniku połączenia się z firmą, została ona założona w 1999 roku, a w 1999 roku została założona w 1999 roku.]}

Dlaczego nie zadzwonić .Activate, .Select, Selection, ActiveSomething metody / właściwości

W zasadzie dlatego, że są wywoływane przede wszystkim do obsługi wprowadzania danych przez interfejs aplikacji. Ponieważ są to metody wywoływane, gdy użytkownik obsługuje obiekty za pośrednictwem interfejsu użytkownika, są to metody zarejestrowane przez makro-rejestrator, dlatego wywołanie ich jest kruche lub zbędne w większości sytuacji: nie musisz wybierać obiektu, aby wykonać akcję z Selection zaraz po tym.

Definicja ta określa jednak sytuacje, do których są powoływane:

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

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

Jeśli rozwijasz i oczekujesz, że użytkownik wybierze instancje obiektów, dla których kod ma obsługiwać, to .Selection lub .ActiveObject są właściwe.

Z drugiej strony, .Select i .Activate są przydatne, gdy możesz wywnioskować następną akcję użytkownika i chcesz, aby Twój kod prowadził użytkownika, prawdopodobnie oszczędzając mu trochę czasu i kliknięć myszką. Na przykład, jeśli twój kod stworzył zupełnie nową instancję wykresu lub zaktualizowaną, użytkownik może chcieć to sprawdzić i możesz wywołać .Activate na nim lub jego arkuszu, aby zaoszczędzić użytkownikowi czas na szukanie go; lub jeśli wiesz, że użytkownik będzie musiał zaktualizować niektóre wartości zakresu, możesz programowo wybierz ten zakres.

Jest to przykład, który wyczyści zawartość komórki " A1 " (lub więcej, jeśli typem wyboru jest xllastcell, itd.). Wszystko odbywa się bez konieczności wybierania komórek.

Application.GoTo Reference:=Workbook(WorkbookName).Worksheets(WorksheetName).Range("A1")
Range(Selection,selection(selectiontype)).clearcontents 

Zauważyłem, że żadna z tych odpowiedzi nie wspomina .Właściwość Offset . Można to również wykorzystać, aby uniknąć użycia działania Select podczas manipulowania niektórymi komórkami, szczególnie w odniesieniu do wybranej komórki(jak wspomina op z ActiveCell).

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

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

• to zmieni komórkę J6 na wartość 12
• minus -2 wskazywałby na J2

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

• to skopiuje komórkę z k4 do L4.
• zauważ, że "0" nie jest potrzebne w parametrze offset, jeśli nie jest potrzebne (, 2)
• podobnie jak w poprzednim przykładzie A minus 1 będzie i4

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

• spowoduje wyczyszczenie wartości we wszystkich komórkach w kolumnie K.

To nie znaczy, że są "lepsze" niż powyższe opcje, ale po prostu wymieniając alternatywy.