Foreversoft.ru

IT Справочник
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Excel vba сделать активным лист

Select и Activate — зачем нужны и нужны ли?

Все начинающие изучать VBA сталкиваются с тем, что записанные через макрорекордер коды пестрят методами Select и Activate.
Если не знакомы с работой макрорекордера — Что такое макрос и где его искать?
Это значительно ухудшает читабельность кода и, как ни странно — быстродействие. Но есть недостатки и куда более критичные. Если код выполняется достаточно долго и он постоянно что-то выделяет — пользователь может заскучать и забыться и начнет тыкать мышкой по листам и ячейкам, выделяя не то, что выделил ранее код. Что повлечет ошибки логики. Т.е. код может и выполнится, но совершенно не так, как ожидалось. Поэтому избавляться от Select и Activate необходимо везде, где это возможно.

Для начала рассмотрим два кода, выполняющие одни те же действия — запись в ячейку А3 листа Лист2 слова «Привет». При этом сам код запускается с Лист1 и после выполнения код Лист1 должен остаться активным. Чтобы сделать эти действия вручную потребуется сначала перейти на Лист2, выделить ячейку А3, записать в неё слово «Привет» и вернуться на Лист1. Поэтому запись макрорекордером этих действий приведет к такому коду:

Sub Макрос1() Sheets(«Лист2»).Select ‘выделяем Лист2 Range(«A3»).Select ‘выделяем ячейку А3 ActiveCell.FormulaR1C1 = «Привет» ‘записываем слово Привет Range(«A4»).Select ‘после нажатия Enter автоматически выделяется ячейка А4 Sheets(«Лист1»).Select ‘возвращаемся на Лист1 End Sub

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

Sub Макрос1() Sheets(«Лист2»).Range(«A3»).FormulaR1C1 = «Привет» End Sub

Как видно, вместо 5-ти строк кода получилась одна строка. Которая выполняет ту же задачу, что и код из 5-ти строк.
Прежде чем понять как правильно избавляться от лишнего давайте разберемся зачем же тогда VBA записывает эти Select и Activate? Как ни странно, но здесь все очень просто. VBA просто не знает, что Вы будете делать после того, как выделили Лист2. И когда Вы переходите на Лист2 — VBA записывает именно переход(его активацию, выделение). Когда выделяете ячейку — так же именно это действие записывает VBA. Захотите ли Вы затем выделить еще что-то, или закрасить ячейку, или записать в неё формулу/значение — VBA не знает. Поэтому в дальнейшем VBA работает именно с выделенным объектом Selection на активном листе.
Но при написании кода вручную или при правке записанного рекордером мы уже вольны в выборе и знаем, чего хотели добиться и какие действия нам точно не нужны.
Итак, чтобы записать в ячейку слово «Привет» рекордер предложит нам такой код:

Sub Макрос1() Range(«A3»).Select ‘выделяем ячейку А3 ActiveCell.FormulaR1C1 = «Привет» ‘записываем слово Привет Range(«A4»).Select ‘после нажатия Enter автоматически выделяется ячейка А4 End Sub

однако выделять ячейку( Range(«A3»).Select ) совершенно необязательно. Значит один Select уже лишний. После этого идет обращение к активной ячейке — ActiveCell . .FormulaR1C1 = «Привет» означает запись значения «Привет» в эту ячейку.
Пусть не смущает FormulaR1C1 — VBA всегда так указывает запись и значения и формулы. Т.к. перед словом «Привет» нет знака равно — то это значение.
Т.к. ActiveCell является обращением к выделенной ячейке, а выделили мы до этого А3, значит их можно просто «сократить»:

Sub Макрос1() Range(«A3»).FormulaR1C1 = «Привет» Range(«A4»).Select ‘после нажатия Enter автоматически выделяется ячейка А4 End Sub

Теперь у нас код получился короче и понятнее. Однако остался один Select: Range(«A4»).Select . Если нет необходимости выделять ячейку А4 после записи в А3 значения, то надо просто удалить эту строку и после выполнения кода активной будет та ячейка, которая была выделена до выполнения(т.е. выделенная ячейка просто не изменится). Таким образом мы с трех строк сократим код до 1-ой:

Sub Макрос1() Range(«A3»).FormulaR1C1 = «Привет» End Sub

Теперь несложно догадаться, что с листами все в точности так же. Sheets(«Лист2»).Select — Select хоть и не нужен, но и ActiveSheet после него нет. Здесь необходимо знать некоторую иерархию в Excel. Сначала идет сам Excel — Application, потом книга — Workbook. В книгу входят рабочие листы(Worksheets), а уже в листах — ячейки и диапазоны — Range и Cells(Application ->Workbook ->Worksheet ->Range). Если перед Range или Cells не указывать явно лист: Range(«A3»).FormulaR1C1 = «Привет» , то значение будет записано на активный лист. Подробнее можно прочесть в статье: Как обратиться к диапазону из VBA

Маленький нюанс: если сокращаем обращение к объектам, то Select-ов быть не должно вообще. Иначе есть шанс получить ошибку «Subscript out of range»:

буквально это означает, что указанный индекс вне досягаемости. А появляется эта ошибка потому, что нельзя выделить ячейку НЕактивного листа или лист НЕактивной книги. Легко эту ошибку получить например в таком коде:

Читать еще:  Как вставить числа в excel

Sub Макрос2() Windows(«Книга3»).Activate ‘здесь появится ошибка, т.к. пытаемся выделить лист в Книга2 ‘а на данный момент активной является Книга3 Windows(«Книга2»).Sheets(«Лист3»).Select End Sub

Ошибка обязательно появится, т.к. сначала мы активировали кодом книгу «Книга3», а потом пытаемся активировать лист НЕактивной на этот момент книги «Книга2». А это сделать невозможно без активации той книги, в которой активируемый лист. Т.е. активация должна происходить именно последовательно: Книга ->Лист ->Ячейка. И никак иначе, если мы хотим активировать именно конкретную ячейку конкретного листа в конкретной книге.
И пример с ячейками:

Sub Макрос2() Sheets(«Лист3»).Select ‘здесь появится ошибка, т.к. пытаемся выделить ячейку на листе «Лист1» ‘а на данный момент активным является Лист3 Sheets(«Лист1»).Range(«C7»).Select End Sub

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

Еще небольшой пример оптимизации:

Sub Макрос2() Windows(«Книга3»).Activate Sheets(«Лист3»).Select Range(«C7»).Select ActiveCell.FormulaR1C1 = «Привет» Range(«C7»).Select Selection.Font.Bold = True With Selection.Interior .Pattern = xlSol >

Этот код записывает в ячейку С7 Лист3 книги «Книга3» слово «Привет», потом делает жирным шрифт и назначает желтый цвет заливке. Убираем активацию книги, листа и ячейки, заменив их прямым обращением:

далее делаем для ячейки жирный шрифт:

With Workbooks(«Книга3»).Sheets(«Лист3»).Range(«C7»).Interior .Pattern = xlSol >

Тут есть нюанс. Windows необходимо всегда заменять на Workbooks — в кодах я сделал именно так. Если этого не сделать, то получите ошибку 438 — объект не поддерживает данное свойство или метод(object dos’t support this property or metod), т.к. коллекция Windows не содержит определения для Sheets.

Важный момент: лучше всегда указать имя книги вместе с расширением(.xlsx, xlsm, .xls и т.д.). Если в настройках ОС Windows(Панель управленияПараметры папок -вкладка ВидСкрывать расширения для зарегистрированных типов файлов) указано скрывать расширения — то указывать расширение не обязательно — Workbooks(«Книга2»). Но и ошибки не будет, если его указать. Однако, если пункт «Скрывать расширения для зарегистрированных типов файлов» отключен, то указание Workbooks(«Книга2») обязательно приведет к ошибке.

Вместо Workbooks(«Книга3.xlsx») можно использовать обращение к активной книге или книге, в которой расположен код. Обращение к Лист3 активной книги, когда активен Лист2 или другой:

Но бывают случаи, когда необходимо производить действия исключительно в той книге, в которой сам код. И не зависеть при этом от того, какая книга активна в данный момент и как она называется. Ведь в процессе книга может быть переименована. За это отвечает ThisWorkbook:

ActiveWorkbook — действия с активной на момент выполнения кода книгой
ThisWorkbook — действия с книгой, в которой записан код

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

Sub NewBook() ‘объявляем переменную для дальнейшего обращения Dim wbNewBook As Workbook ‘создаем книгу Set wbNewBook = Workbooks.Add ‘теперь можно обращаться к wbNewBook как к любой другой книге ‘но уже не указывая её имя wbNewBook.Sheets(1).Range(«A1»).Value = «Привет» ‘Sheets(1) — обращение к листу по его порядковому номеру ‘(отсчет с начинается с 1 слева) End Sub Sub NewSheet() ‘объявляем переменную для дальнейшего обращения Dim wsNewSheet As Worksheet ‘добавляем новый лист в активную книгу Set wsNewSheet = ActiveWorkbook.Sheets.Add ‘теперь можно обращаться к wsNewSheet как к любому другому листу ‘но уже не указывая его имя или индекс wsNewSheet.Range(«A1»).Value = «Привет» End Sub

Не везде Activate лишний
Но есть и такие свойства и методы, которые требуют обязательной активации книги/листа. Одним из таких свойств является свойство окна FreezePanes(Закрепление областей):

Sub Freeze_Panes() ThisWorkbook.Activate Sheets(2).Activate Range(«B2»).Select ActiveWindow.FreezePanes = True End Sub

В этом коде нельзя убирать Select и Activate, т.к. свойство FreezePanes применяется исключительно к активному листу и активной ячейке, потому что является оно именно методом окна, а не листа или ячейки.
Так же сюда можно отнести свойства: Split, SplitColumn, SplitHorizontal и им подобные. Иными словами все свойства, которые работают исключительно с активным окном приложения, а не с объектами напрямую.

Статья помогла? Поделись ссылкой с друзьями!

Excel vba сделать активным лист

На этом шаге мы приведем основные методы этого объекта .

Перечислим методы объекта Worksheet и семейства Worksheets .

Таблица 1. Методы объекта Worksheet и семейства Worksheets
МетодыВыполняемые действия
ActivateАктивизирует указанный рабочий лист. Например:

AddСоздает новый рабочий лист. Синтаксис:

  • Before — указывает лист, перед которым будет размещен новый рабочий лист;
  • After — указывает лист, после которого будет размещен новый рабочий. Если аргументы Before и After опущены, то новый лист размещается перед активным листом;
  • Count — число добавляемых листов, по умолчанию имеет значение 1;
  • Туре — указывает тип добавляемого листа. Допустимые значения: xlWorksheet (по умолчанию), xlExce14MacroSheet и хlЕхсеl4IntlMacroSheet .

Например:
— вставляется новый лист перед активным листом активной рабочей книги
DeleteУдаляет рабочий лист. Например:
— удаляется первый рабочий лист из активной рабочей книги
ProtectЗащищает рабочий лист от внесения в него изменений. Синтаксис:

  • Password — строка, используемая в качестве пароля для защиты листа;
  • DrawingObjects —допустимые значения: True (графические объекты защищены) и False (графические объекты не защищены). По умолчанию используется значение False ;
  • Contents — допустимые значения: True (ячейки защищены) и False (ячейки не защищены). По умолчанию используется значение True ;
  • Scenarios — допустимые значения: True (сценарии защищены) и False (сценарии не защищены). По умолчанию используется значение True ;
  • UserInterfaceOnly — допустимые значения: True (лист защищен от изменений со стороны пользователя, но не подпрограммы VBA ) и False (лист защищен от изменений со стороны как пользователя, так и подпрограммы VBA ). По умолчанию используется значение False .

В следующем примере установлена полная защита активного рабочего листа от любых изменений со стороны пользователя:
UnprotectСнятие защиты с рабочего листа. Синтаксис:
CopyКопирование рабочего листа в другое место рабочей книги. Синтаксис:

  • Before — рабочий лист книги, перед которым вставляется данный;
  • After — рабочий лист, после которого вставляется данный.

Одновременно допустимо использование только одного из аргументов . В следующем примере Лист1 активной рабочей книги копируется после Лист3 той же рабочей книги:
MoveПеремещение рабочего листа в другое место рабочей книги. Синтаксис:

  • Before — рабочий лист книги, перед которым вставляется данный;
  • After — рабочий лист, после которого вставляется данный.

Одновременно допустимо использование только одного из аргументов . В примере Лист1 активной рабочей книги перемещается перед Лист3 той же рабочей книги:
EvaluateПреобразует выражение в объект или значение. Используется при вводе формул и ячеек из диалоговых окон. В следующем примере в поле ввода первого появившегося диалогового окна вводится ссылка на ячейку, например A1 , процедура считывает значение из этой ячейки и отображает его в диалоговом окне. После этого в поле ввода следующего диалогового окна надо ввести какое-нибудь арифметическое выражение, например sin(1)^2 . Процедура вычислит значение этого выражения и отобразит в диалоговом окне:

На следующем шаге мы рассмотрим события этого объекта .

Проблема с активацией листа в VBA Эксель

Здравствуйте, форумчане! Есть экселевский файл с двумя вкладками. При двойном клике на ячейке в первой вкладке появляется форма, отображающая некие данные. На форме две кнопки. При нажатии на одну форма просто закрывается и здесь проблем нет. При нажатии на вторую запускается макрос поиска ячейки на втором листе. Ячейка находится, выводится на середину экрана, второй лист с найденной ячейкой активируется, форма закрывается. И здесь начинается самое интересное и непонятное. При попытке прокрутить лист колесом прокрутки, ничего не происходит. Ячейки не перемещаются ни вверх, ни вниз. Причем полоса прокрутки на вращение колеса реагирует и перемещение ячеек происходит на первом листе, который в данный момент не показан на экране. Как только щелкаю мышью на первом листе, а потом возвращаюсь на второй, работа восстанавливается. Файл здесь:https://cloud.mail.ru/public/7Tms/2wH2Wznrb

3 ответа 3

Ошибка не воспроизводится. Похоже на недостаток видеопамяти, да и памяти вообще.

Грубая ошибка:

переменная в процедуре

Найдите различия. Переменные уровня процедуры могут иметь одинаковые имена в разных процедурах, уровня модуля — в разных модулях. Но нельзя давать переменным одинаковое имя, если эти переменные пересекаются!

Кушаем память.

Много лишних присвоений, без которых можно спокойно обойтись.

Эта строка выделяет область памяти для хранения объекта. Если после использования память принудительно не освобождать, область может остаться занятой. Чем больше присвоений, тем больше памяти отрезается. Поэтому после отработки основной части кода память обязательно нужно освобождать

Переменные уровня процедуры. Занимают память до завершения работы процедуры.

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

Например, передача номера искомого вагона в процедуру поиска. Публичная переменная не нужна, значение можно передать в процедуру:

Сам поиск можно облегчить, по минимуму занимая память (в Вашем варианте активирование листа происходит два раза; в переменные загоняются три объекта; для номеров последней и искомой строки можно использовать одну переменную):

Поиск происходит быстро, зачем Вам ProgressBar? Для большего подтормаживания? )

Попутно.

Не обязательно для каждого макроса создавать отдельные модули. Так можно больше запутаться.

Некоторые процедуры можно объединить в одну. Например, PredpriyatiyePoslednego*. 3 или 4 кода одинаковы, меняются только переменные.

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

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

Нужно не очищать строки, а удалять их. Где удаление строк недопустимо, очищать диапазоны. На листе со справками более 35 000 строк пользовательского диапазона (строк с данными — 1221). А это лишний вес файла, лишний диапазон при обработке массивов.

В общем, писать можно много. Автора вопроса это должно радовать — есть куда расти )

Создание и именование рабочего листа в Excel VBA

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

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

Код, который я использую, находится здесь:

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

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

3 Ответа

Вам не нужно знать, где он находится или как его зовут, вы просто называете его WS.
Если вы все еще хотите сделать это «old fashioned» способом, попробуйте это:

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

для списка недопустимых символов,которые вы можете проверить.

Обновление

Другие вещи, чтобы попробовать: полностью квалифицированные ссылки, бросание в Doevents, очистка кода. Этот код квалифицирует вашу ссылку на листы до ThisWorkbook (вы можете изменить ее на ActiveWorkbook, если это подходит). Он также добавляет тысячу DoEvents (глупый перебор, но если что — то займет некоторое время, чтобы сделать это, это позволит ему-Вам может понадобиться только один DoEvents, если это действительно что-то исправит).

Наконец, всякий раз, когда у меня возникает тупая проблема VBA, которая просто не имеет смысла, я использую CodeCleaner Роба Бови. Это надстройка, которая экспортирует все ваши модули в текстовые файлы, а затем повторно импортирует их. Вы можете сделать это и вручную. Этот процесс очищает любой поврежденный p-код, который висит вокруг.

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

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

Похожие вопросы:

Я пытаюсь использовать событие изменения рабочего листа в Excel VBA, но оно, похоже, не работает. Из того, что я понял, достаточно просто определить функцию обработки Worksheet_Change, как я это.

Я пытаюсь автоформировать, как отображается нижняя часть страницы Excel, чтобы автоматически просмотреть все имя листа. По умолчанию половина имени листа обрезается, так как горизонтальная полоса.

У меня есть набор пользовательских функций vba, которые находятся в модуле excel, а затем вызываются из электронной таблицы excel. на данный момент все работает нормально. Меня попросили.

У меня есть рабочий лист с несколькими событиями изменения рабочего листа. К сожалению, я получаю некоторые ошибки памяти из-за них. Мне было интересно, может ли кто-нибудь посмотреть на этот.

Я пытаюсь улучшить производительность проекта C# Winform. Ранее создание файла Excel выполнялось в течение 100000 МС (с использованием Office.Interop.Excel). Используя ClosedXml это около 5000 мс.

Встроенные функции Excel имеют стиль UPPERCASE символов. Я немного OCD и как мои пользовательские функции рабочего листа, чтобы быть UPPERCASE тоже, как и встроенные функции Excel. Однако Excel 2013.

В Microsoft Excel я написал несколько кодов для события изменения рабочего листа. Теперь я хочу создать sub и поместить его в файл personal.xlsb , чтобы всякий раз, когда пользователь запускает его.

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

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

Ссылка на основную публикацию
Adblock
detector