Вопрос по excel, vba – Как избежать использования Select в Excel VBA

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

& # X25BA; Например, если вы хотите, чтобы значение вCell A1 Вы можете установить переменную, равную значению свойства этой ячейки.

Example valOne = Range("A1").Value

& # X25BA; Например, если вам нужно кодовое имя «Sheet3», вы можете установить переменную, равную свойству кодового имени этого листа.

Example valTwo = Sheets("Sheet3").Codename

Надеюсь, это поможет. Дайте знать, если у вас появятся вопросы.

Two Main reasons why .Select/.Activate/Selection/Activecell/Activesheet/Activeworkbook etc... should be avoided

It slows down your code. It is usually the main cause of runtime errors.

How do we avoid it?

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

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

Why not call .Activate, .Select, Selection, ActiveSomething methods/properties

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

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

When to call .Activate, .Select, .Selection, .ActiveSomething methods/properties

В основном, когда вы ожидаетеfinal user играть роль в исполнении.

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

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

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

Вероятно, самое большое, что вы можете сделать, чтобы избежать использования Select, этоas much as possible, use named ranges (combined with meaningful variable names) in your VBA code, Этот момент был упомянут выше, но немного затенен; Однако это заслуживает особого внимания.

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

Named ranges make your code easier to read and understand.

Пример:

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 содержать, и что делает процедура.

Почему это важно? Частично потому, что другим людям легче понять это, но даже если вы единственный человек, который когда-либо увидит или использует ваш код, вы все равно должны использовать именованные диапазоны и хорошие имена переменных, потому чтоYOU WILL FORGET что вы хотели сделать с ним через год, иyou will waste 30 минут, чтобы понять, что делает ваш код.

Named ranges ensure that your macros do not break when (not if!) the configuration of the spreadsheet changes.

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

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! & quot; или поместите столбец расходов между месяцами и столбцами продаж или добавьте заголовок к каждому столбцу. Теперь ваш код не работает. А поскольку вы использовали ужасные имена переменных, вам понадобится гораздо больше времени, чтобы понять, как исправить это, чем нужно.

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

... and am finding that my code would be more re-usable if I were able to use variables instead of Select functions.;

Хотя я не могу думать о чем-то большем, чем об отдельных ситуациях.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, В качестве свойства листа он имеет много других целей.

(Yes, I know this question was about .Select, not Selection but I wanted to remove any misconceptions that novice VBA coders might infer.)

так как все остальные дали длинный ответ.

Вы будете получать .select и .activate всякий раз, когда будете записывать макросы и использовать их повторно. Когда вы выбираете ячейку или лист, она просто становится активной. С этого момента, когда вы используете неквалифицированные ссылки, такие как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"

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

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

Это действительно зависит от того, что вы пытаетесь сделать. В любом случае, простой пример может быть полезен. Предположим, что вы хотите установить значение активной ячейки на «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

И Макро1 как:

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

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

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

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

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

Например:

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

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

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

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

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

Свойство .Offset, Это также может быть использовано, чтобы избежать использованияSelect действие при манипулировании определенными ячейками, особенно в отношении выбранной ячейки (как упоминается в OPActiveCell).

Вот пара примеров.

Я также приму «ActiveCell» являетсяJ4.

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

This will change the cell J6 to be a value of 12 A minus -2 would have referenced J2

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

This will copy the cell in k4 to L4. Note that "0" is not needed in the offset parameter if not needed (,2) Similar to the previous example a minus 1 would be i4

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

This will clear values in all cells in the column k.

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

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

This is how you may avoid Select and Activate in the following cases:

Adding a new Worksheet and copying a cell on it:

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

Для того, чтобы:

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

When you want to copy range between worksheets:

От:

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

Для того, чтобы:

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

Using fancy named ranges

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

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]

Not copying values, but taking them

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

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

Try always to refer the Worksheet as well

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

Can I really never use .Select or .Activate for anything?

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

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

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

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

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

использованиеDimпеременные

Dim rng as Range

Set переменная до необходимого диапазона. Есть много способов обратиться к диапазону одной ячейки

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

или многосотовый диапазон

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)

Выcan используйте ярлык кEvaluate метод, но это менее эффективно и, как правило, его следует избегать в производственном коде.

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

Все приведенные выше примеры относятся к ячейкам наactive sheet, Если вы специально не хотите работать только с активным листом, лучше затемнитьWorksheet переменная тоже

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

если тыdo хочу работать сActiveSheetдля ясности лучше всего быть явным. Но будьте осторожны, так как некоторыеWorksheet методы меняют активный лист.

Set rng = ActiveSheet.Range("A1")

Опять же, это относится кactive workbook, Если вы специально не хотите работать только сActiveWorkbook или жеThisWorkbookДим, лучшеWorkbook переменная тоже.

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

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

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

Вы также можете использоватьThisWorkbook объект для ссылки на книгу, содержащую исполняемый код.

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

Обычный (плохой) кусок кода - открыть книгу, получить некоторые данные и снова закрыть

Это плохо:

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

И было бы лучше, как:

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

Передайте диапазоны к вашемуSubиFunctionв качестве переменных диапазона

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

Вы также должны применять методы (такие какFind а такжеCopy) к переменным

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

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

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

Это небольшая дегустация того, что возможно.