Как избежать использования Select в Excel VBA

две основные причины, почему .Select/.Activate/Selection/Activecell/Activesheet/Activeworkbook etc... следует избегать

1. это замедляет ваш код.
2. обычно это основная причина ошибок во время выполнения.

как нам этого избежать?

1) напрямую работать с соответствующими объектами

рассмотрим этот код

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

этот код также может быть записан как

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

2) при необходимости объявите свои переменные. Тот же код выше может быть написан как

Dim ws as worksheet

Set ws = Sheets("Sheet1")

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

один небольшой акцент я добавлю ко всем отличным ответам, приведенным выше:

вероятно, самое большое, что вы можете сделать, чтобы избежать использования Select-это насколько это возможно, используйте именованные диапазоны (в сочетании со значимыми именами переменных) в коде VBA. Этот момент был упомянут выше, но немного затушеван; однако он заслуживает особого внимания.

вот несколько дополнительных причин, чтобы сделать либеральное использование именованных диапазонов хотя я уверен, что мог бы придумать и больше.

именованные диапазоны облегчают чтение и понимание кода.

пример:

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

довольно очевидно, что названные диапазоны Months и MonthlySales содержат, и что процедура делает.

почему это важно? Частично потому, что другим людям легче понять это, но даже если вы единственный человек, который когда-либо увидит или использует ваш код, вы все равно должны использовать именованные диапазоны и хорошие имена переменных, потому что ТЫ ЗАБУДЕШЬ что ты собирался с ним делать через год, и вы будете тратить 30 минут только выяснить, что ваш код делает.

именованные диапазоны убедитесь, что ваши макросы не ломаются, когда (не если!) конфигурация электронной таблицы изменяется.

подумайте, если бы приведенный выше пример был написан так:

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

этот код будет работать нормально сначала - то есть до тех пор, пока вы или будущий пользователь не решите "gee wiz, я думаю, что собираюсь добавить новый столбец с годом в столбце A!", или поместите столбец расходов между столбцами месяцев и продаж, или добавьте заголовок к каждому столбцу. Теперь ваш код сломан. И поскольку вы использовали ужасные имена переменных, вам потребуется гораздо больше времени, чтобы понять, как это исправить, чем это должно занять.

если вы использовали именованные диапазоны для начала, то Months и Sales столбцы можно перемещать все, что вам нравится, и ваш код будет работать просто отлично.

Я собираюсь дать короткий ответ, так как все остальные дали длинный.

вы получите .выбрать и. активируйте каждый раз, когда вы записываете макросы и повторно их используете. Когда вы.выберите ячейку или лист он просто делает его активным. С этого момента всякий раз, когда вы используете неполные ссылки вроде Range.Value Они просто используют активную ячейку и лист. Это также может быть проблематично, если вы не смотрите, где находится ваш код или пользователь нажимает на книгу.

Итак, вы можете устранить эти проблемы напрямую связаны с вашими ячейками. Который идет:

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

вы можете

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

"... и я нахожу, что мой код был бы более пригодным для повторного использования, если бы я мог использовать переменные вместо выбранных функций."

в то время как я не могу думать ни о чем, кроме изолированной горстки ситуаций, где .Select было бы лучшим выбором, чем прямая ссылка на ячейку, Я бы встал на защиту Selection и отметить, что он не должен быть выброшен по тем же причинам, что .Select следует избегать.

бывают случаи, когда наличие коротких, экономящих время подпрограмм макросов, назначенных комбинациям горячих клавиш, доступных с нажатием нескольких клавиш, экономит много времени. Возможность выбора группы ячеек для введения в действие операционного кода творит чудеса при работе с карманными данными, которые не соответствуют формату данных на листе. Точно так же, как вы можете выбрать группу ячеек и применить изменение формата, выбор группы ячеек для запуска специального макрокода может быть основным временем бережливый человек.

примеры суб-фреймворка на основе выбора:

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

фактический код для обработки может быть от одной строки до нескольких модулей. Я использовал этот метод для запуска длительных процедур на неровном выборе ячеек, содержащих имена файлов внешних книг.

короче говоря, не выбрасывайте Selection из-за его тесной связи с .Select и ActiveCell. Как свойство рабочего листа он имеет много других цели.

_{(Да, я знаю, что этот вопрос был о .Select, а не Selection но я хотел удалить любые неправильные представления, которые могут сделать начинающие кодеры VBA.)}

обратите внимание, что в следующем я сравниваю подход Select (тот, который OP хочет избежать), с подходом Range (и это ответ на вопрос). Так что не прекращайте чтение, когда вы видите первый выбор.

Это действительно зависит от того, что вы пытаетесь сделать. В любом случае простой пример может быть полезным. Предположим, что вы хотите установить значение активной ячейки в "foo". Используя ActiveCell вы бы написали что-то вроде это:

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

если вы хотите использовать его для ячейки, которая не является активной, например для "B2", вы должны выбрать его сначала, например:

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

используя диапазоны, вы можете написать более общий макрос, который можно использовать для установки значения любой ячейки, которую вы хотите, чтобы все, что вы хотите:

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

тогда вы можете переписать Macro2 как:

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

и macro1 в качестве:

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

надеюсь, что это поможет немного прояснить ситуацию.

избежать Select и Activate это шаг, который делает вас немного лучше разработчика VBA. В общем, Select и Activate используются при записи макроса, таким образом Parent рабочий лист или диапазон всегда считается активным.

вот как вы можете избежать Select и Activate в следующих случаях:

---

добавление нового листа и копирование ячейки на нем:

From (код, созданный с помощью макроса рекордер):

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

To:

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

если вы хотите скопировать диапазон между листами:

от:

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

To:

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

---

используя причудливые именованные диапазоны

вы можете получить к ним доступ с []. Что действительно красиво, по сравнению с другим способом. Проверьте сами:

Dim Months As Range
Dim MonthlySales As Range

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

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

пример выше будет выглядеть так:

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

---

не копирование ценности, но принимая их

обычно, если вы готовы select, скорее всего, вы что-то скопировали. Если вас интересуют только значения, это хороший вариант, чтобы избежать выбора:

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

---

старайтесь всегда ссылаться на рабочий лист, а также

это, пожалуй, самая распространенная ошибка в vba. Всякий раз, когда вы копируете диапазоны, иногда рабочий лист не упоминается, и поэтому VBA рассматривает 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

---

могу ли я действительно никогда не использовать .Select или .Activate для чего?

единственный раз, когда вы могли бы оправданно использовать .Activate и .Select это когда вы хотите убедиться, что конкретный лист выбран по визуальным причинам. Например, что ваш Excel всегда будет открываться с первым выбранным листом обложки, не обращая внимания на то, что было activesheet, когда файл был закрыт. Таким образом, что-то вроде этого абсолютно ОК:

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

всегда указывайте рабочую книгу, рабочий лист и ячейку/диапазон.

например:

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

потому что конечные пользователи всегда будут просто нажимать кнопки, и как только фокус перемещается с книги, Код хочет работать, тогда все идет совершенно неправильно.

и никогда не используйте индекс рабочей книги.

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

вы не знаете, какие другие книги будут открыты, когда пользователь запускает ваш код.

ИМХО использование .select исходит от людей, которые, как и я, начали изучать VBA по необходимости путем записи макросов, а затем изменения кода, не понимая этого .select и в последующем selection - это просто ненужные посредники.

.select можно избежать, так как многие уже размещены, непосредственно работая с уже существующими объектами, что позволяет использовать различные косвенные ссылки, такие как вычисление i и j сложным образом, а затем редактирование ячейки(i, j), так далее.

в противном случае, нет ничего неявно неправильно с .select и вы можете легко найти применение для этого, например, у меня есть электронная таблица, которую я заполняю датой, активирую макрос, который делает с ней некоторую магию и экспортирует ее в приемлемом формате на отдельном листе, что, однако, требует некоторых окончательных ручных (непредсказуемых) входов в соседнюю ячейку. Так вот наступает момент для .select это спасает меня от дополнительного движения мыши и щелчка.

Короткий Ответ:

чтобы избежать использования .Select метод вы можете установить переменную, равную свойству, которое вы хотите.

► например, если вы хотите, чтобы значение Cell A1 вы можете установить переменную, равную свойству value этой ячейки.

• пример valOne = Range("A1").Value

► например, если вы хотите кодовое имя 'Sheet3', вы можете установить переменную, равную свойству codename этого рабочий лист.

• пример valTwo = Sheets("Sheet3").Codename

Я надеюсь, что помогает. Дайте мне знать если вы имеете любые вопросы.

эти методы довольно стигматизированы, поэтому взяв на себя инициативу @Vityata и @Jeeped ради рисования линии на песке:

почему бы не назвать .Activate,.Select,Selection,ActiveSomething методы/свойства

в основном потому, что они вызываются в первую очередь для обработки пользовательского ввода через пользовательский интерфейс приложения. Поскольку они являются методами, вызываемыми, когда пользователь обрабатывает объекты через пользовательский интерфейс, они записываются макро-рекордером, и именно поэтому их вызов хрупкий или избыточный для большинства ситуаций: вам не нужно выбирать объект, чтобы выполнить действие с Selection сразу после этого.

однако это определение разрешает ситуации, в которых они требуются:

когда звонить .Activate,.Select,.Selection,.ActiveSomething методы/свойства

в основном, когда вы ожидаете финал чтобы сыграть свою роль в исполнении.

если вы разрабатываете и ожидаете пользователя чтобы выбрать экземпляры объектов для обработки кода, затем .Selection или .ActiveObject являются подходящей.

С другой стороны, .Select и .Activate используются, когда вы можете сделать вывод о следующем действии пользователя, и вы хотите, чтобы ваш код направлял пользователя, возможно, экономя ему некоторое время и щелчки мыши. Например, если ваш код только что создал новый экземпляр диаграммы или обновил его, пользователь может захотеть проверить его, и вы можете вызвать .Activate на нем или его листе, чтобы сохранить пользователя время поиска; или если вы знаете, что пользователю нужно будет обновить некоторые значения диапазона, вы можете программно выбрать этот диапазон.

Это пример, который очистит содержимое ячейки " A1 " (или больше, если тип выбора-xllastcell и т. д.). Все сделано без необходимости выбора ячеек.

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

Я надеюсь, что это кому-то поможет.

я заметил, что ни один из этих ответов не говоря уже о .Свойство Смещения. Это также может быть использовано, чтобы избежать использования Select действие при манипулировании определенными ячейками, особенно в отношении выбранной ячейки (как упоминает OP с ActiveCell).

вот несколько примеров.

Я также предполагаю, что "ActiveCell" является J4.

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

• это изменит ячейку J6 значение 12
• минус -2 будет ссылаться на J2

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

• это скопирует ячейку в k4 до L4.
• обратите внимание, что "0" не требуется в параметре смещения,если не требуется (, 2)
• как и в предыдущем примере, минус 1 будет i4

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

• это очистит значения во всех ячейках столбца k.

это не чтобы сказать, что они "лучше", чем приведенные выше варианты, но просто перечисляя альтернативы.