Код vba если в выбранном файле есть лист с именем
Обновлено: 05.07.2024
Я видел посты с ответами, в которых используется код / макросы VBA ( Visual Basic для приложений ), однако впоследствии я также отметил дополнительные комментарии о том, как использовать VBA (как реализовать и выполнить его). Я надеюсь, что это поможет просто выполнить код.
Итак, мой вопрос:
У меня есть некоторый код VBA. Как добавить VBA в проект MS Excel, Word или Outlook?
Я пытаюсь создать канонический вопрос. Не стесняйтесь добавлять любые другие вопросы, на которые я могу сослаться / мы можем закрыть, добавить теги, или отредактировать вопрос или ответ, чтобы улучшить его, или опубликовать ответы.Чтобы открыть редактор VBA в любом продукте Microsoft Office 1 , нажмите Alt + F11 .
Затем вам нужно определить, как вы хотите, чтобы ваш VBA был реализован. Вы хотите, чтобы это было вызвано каким-либо событием в программном обеспечении, таким как сохраняемый файл или изменяемый выбор в электронной таблице? Или вы хотите иметь возможность выполнять код самостоятельно в любое время?
Если первое, взгляните на Project Explorer и найдите проект, связанный с файлом, над которым вы сейчас работаете.
Если вы хотите, чтобы ваш код запускался событием, связанным с конкретным листом (например, код должен запускаться каждый раз, когда значение изменяется на Sheet1), вам нужно дважды щелкнуть этот конкретный объект листа, указанный в Project Explorer. Откроется текстовое поле, в которое вы можете вставить код. Если событие связано со всей книгой, а не с конкретным листом (например, код должен выполняться при сохранении книги), дважды щелкните ThisWorkbook объект в проводнике проекта.
Однако, если вы не хотите, чтобы ваш код вызывался событием, вам нужно поместить код в модуль. Если в документе или рабочей книге есть другой код, например, из записанного макроса, он появится в модуле. Если существует существующий модуль, в большинстве случаев вы захотите вставить свой код в тот же модуль под любым существующим кодом. Существующие модули будут перечислены в Project Explorer.
Если модуль не существует, вам нужно будет вставить новый. Для этого выберите Module из Insert выпадающего меню окно редактора VBA. Это вставит новый модуль и отобразит текстовое окно внутри окна редактора. Вставьте туда свой код.
Если вы хотите выполнить код в модуле, у вас есть несколько вариантов. Одним из них является запуск кода из окна редактора VBA. Вы можете просто поместить курсор где-нибудь в коде, который вы хотите запустить, и нажать F5 или нажать кнопку (Выполнить) на панели инструментов под строкой меню, чтобы выполнить код.
Другой вариант - использовать Macros диалоговое окно. Чтобы открыть его, выполните одно из следующих действий:
- на View вкладке ленты, в Macros панели (справа), нажмите Macros кнопку
- на Developer вкладке, в Code панели (крайний левый), нажмите Macros кнопку
- нажмите Alt + F8
Диалоговое окно выглядит так:
В приведенном выше примере, вы можете запустить с YourCode помощью
- выберите его из списка и затем нажмите Run , или
- выбрав его из списка и дважды щелкнув по нему.
______
1 Как я могу использовать VBA в Excel 2010 Starter Edition? , вы не можете использовать VBA в Excel 2010 Starter Edition.
Для активации или выбора листа в Excel мы вручную переходим на лист и нажимаем на вкладку «Лист». Но когда мы пишем код, в котором нам нужно связать разные листы в разных формах, мы можем использовать функцию VBA Activate Sheet. Это можно сделать с помощью «. Активировать »в VBA. Это автоматически выберет или активирует лист для дальнейшего использования.
Активация листа означает выбор этого листа. И чтобы узнать, активирован или выбран этот лист, после проверки кода в Excel лучше проверить, можем ли мы увидеть текущий лист или нет.
Как активировать лист в Excel VBA?
Ниже приведены различные примеры активации листа в Excel с использованием кода VBA.
Вы можете скачать этот шаблон Excel для активации листа VBA здесь - Шаблон Excel для активации листа VBA
VBA Activate Sheet - Пример № 1
Существуют различные способы активации листа в VBA. Для этого мы рассмотрели 3 листа с именами Первый, Второй и Третий, как показано ниже. Целью присвоения имен каждому листу является различие между номером листа и именем листа, которое будет использоваться в следующих примерах.
Выполните следующие шаги, чтобы активировать лист в Excel VBA.
Шаг 1: Теперь перейдите к VBA и на вкладке меню « Вставка » выберите « Модуль», как показано ниже.
Модуль - это место, где мы напишем код для активации листа. После этого мы откроем новый модуль.
Шаг 2: В этом модуле создайте подкатегорию функции с любым именем. Для лучшего понимания мы создали его от имени VBA Activate Sheet по порядку. Это поможет в отслеживании правильного модуля.
Код:
Шаг 3: Теперь используйте функцию « Листы» для выбора любого листа.
Код:
Шаг 4: Теперь, чтобы фактически выбрать или активировать лист в Excel, используйте команду « Активировать», за которой следует точка (.)
Код:
Таким образом, мы разрешаем выбирать лист с именем «Первый» всякий раз, когда код вызывает его.
Шаг 5: Теперь сделайте то же самое для имени листа Second и Third, как показано ниже.
Код:
Шаг 6: Теперь скомпилируйте код пошагово или за один раз. Небольшой код может быть скомпилирован за один раз, что может не привести к ошибке. После этого запустите код, нажав на кнопку Play, которая находится под строкой меню.
Мы увидим, что третий лист будет выбран так, как он был расположен в конце. Если мы выполним пошаговую компиляцию, то сможем увидеть, кто первый, второй и третий листы активируются нажатием клавиши F8.
Когда компилятор пройдет через первую строку, мы увидим, что лист First активирован, как показано ниже.
Снова нажав клавишу F8, компилятор пройдет через вторую строку кода, и мы увидим, что лист Second активирован.
И когда компилятор достиг End Sub структуры кода, мы активируем третий лист, как показано ниже.
VBA Activate Sheet - Пример № 2
Мы также можем использовать команду «Рабочий лист» для выбора и активации любого листа. Этот процесс очень похож на пример-1. Для этого еще раз рассмотрим имя файла с именем First , Second и Third. Выполните следующие шаги, чтобы активировать лист в Excel VBA.
Шаг 1: В модуле начните писать подкатегорию VBA Activate Sheet в последовательности, как показано ниже.
Код:
Шаг 2: Поскольку мы использовали команду Sheet для активации Sheet, здесь мы будем использовать команду Worksheet .
Код:
Шаг 3: Теперь используйте функцию «Активировать», обозначенную точкой (.), Чтобы активировать лист.
Код:
Шаг 4: Теперь сделайте то же самое для Второго и Третьего Листа.
Код:
Шаг 5: После этого скомпилируйте код. Здесь мы сделаем пошаговую компиляцию, чтобы увидеть изменения. Для прессы F8, которая выполняет пошаговую компиляцию и запуск кода. Когда компилятор пройдет через вторую строку кода, мы увидим, что в листе Excel второй активирован или выбран.
Шаг 6: Снова нажмите клавишу F8, чтобы выбрать следующий лист. Мы увидим, как компилятор проходит через третью строку кода и достигает конца, лист с именем «Третий» активируется или выбирается.
VBA Activate Sheet - Пример № 3
Есть еще один способ активировать любой лист. Для этого мы также рассмотрим тот же набор листов с одинаковыми именами First, Second и Third, которые используются в приведенных выше примерах. В этом примере мы увидим 3 комбинации функций Sheet и Worksheet вместе с Select, которые мы будем использовать вместо Activate . Функция выбора работает так же, как и активировать . Используя комбинацию различных функций для одного и того же использования, мы увидим, как меняется результат.
Выполните следующие шаги, чтобы активировать лист в Excel VBA.
Шаг 1: Для этого откройте модуль и поместите подкатегорию в название выполняемой функции или в любое другое имя по вашему выбору. Мы сохраняем имя, но в последовательности, как показано ниже.
Код:
Шаг 2: Снова таким же образом используйте функцию Sheets с именем листа, как показано ниже.
Код:
Шаг 3: После этого используйте функцию Select, тянущуюся через точку (.)
Код:
Шаг 4: Теперь во второй строке кода мы будем использовать функцию Sheets вместе с Activate для листа с именем Second, как показано ниже.
Код:
Шаг 5: И для последнего листа с именем «Третий» мы будем использовать «Рабочие листы» вместе с функцией «Выбор», чтобы активировать его, как показано ниже.
Код:
Шаг 6: Теперь скомпилируйте полный код и выполните шаг за шагом, чтобы увидеть изменения, нажав клавишу F8. Когда компилятор проходит через первую строку кода, лист First будет активирован.
Когда компиляция проходит через вторую строку кода, лист Second будет активирован.
И наконец, когда компилятор придет в End Sub, активируется третий лист, как показано ниже.
Вот как мы можем использовать комбинацию Sheets и Worksheets вместе с функциями Activate и Select для активации любого рабочего листа.
Плюсы Excel VBA Activate Sheet
- Выбрать любой лист в Excel, а также с VBA довольно просто.
- Эта функция очень полезна, когда наш код связан с данными на разных листах.
То, что нужно запомнить
- Функция выбора и активации выполняет ту же функцию, что и активация любого листа. Хотя название другое.
- Различные комбинации выбора и активации вместе с листами и рабочим листом возможны для активации одного или нескольких рабочих листов.
- Активация любых листов означает выбор этого листа.
- После выполнения кода сохраните файл в Macro Enable Worksheet. Это помогает сохранить код, чтобы мы могли использовать его несколько раз без каких-либо потерь.
Рекомендуемые статьи
Это руководство к листу активации VBA. Здесь мы обсудили, как активировать лист в Excel, используя код VBA, а также некоторые практические примеры и загружаемый шаблон Excel. Вы также можете просмотреть наши другие предлагаемые статьи -
Вы говорите об "индексе" листа. Все листы книги номеруются, в том числе скрытые. Новый лист будет иметь индекс 1, если сделать так (первый лист в книге, например, "Лист1"):
Проверьте его индекс, он будет первым:
В окне Immediate (Ctrl+G):
Вы говорите об "индексе" листа. Все листы книги номеруются, в том числе скрытые. Новый лист будет иметь индекс 1, если сделать так (первый лист в книге, например, "Лист1"):
Проверьте его индекс, он будет первым:
В окне Immediate (Ctrl+G):
Совершенно верно! Хотя при этом в vba у второго листа в скобках Вы бы опять-таки увидели (Лист1). Посмотрите пример во вложении - вроде там все наглядно видно. В книге вообще нет (Лист1) и ряда другихВам-то явно важно, чтобы добавляемый лист имел адрес Sheets(1) и был первым в книге, или не так?
так и в чем вопрос?
Совершенно верно! Хотя при этом в vba у второго листа в скобках Вы бы опять-таки увидели (Лист1). Посмотрите пример во вложении - вроде там все наглядно видно. В книге вообще нет (Лист1) и ряда другихВам-то явно важно, чтобы добавляемый лист имел адрес Sheets(1) и был первым в книге, или не так? В Вашем примере создается лист с именем "СВОД", индекс единица, что не так? Последний раз редактировалось EugeneS; 24.06.2010 в 16:55 . Да не к Вам вопрос, Евгений, к автору темы, я просто в подтверждение Вашей правоты еще и пример бросил -) Да вроде все так, но в Propeties (F4) когда выбираю лист "СВОД", то Name у него - Лист5, Лист6 и т.д., смотря сколько раз он удалялся и создавался.
А если смотреть по коду, то все верно и все работает.
Вот такая странность или я не догоняю чтото.
Спасибо.!
| то Name у него - Лист5, Лист6 и т.д., смотря сколько раз он удалялся и создавался. |
Это не Name (имя листа), а Codename (кодовое имя листа)
Хотя название свойства и отображается как Name, на самом деле это Codename.
Можно, конечно, изменить и кодовое имя листа, но это намного сложнее.
Да и доступ к объектной модели надо включать в настройках Excel.
Если вы в коде не работаете именно с кодовыми именами листов - то просто не обращайте внимание на эти имена.
P.s. Сейчас я пишу вот так:
Dim oExcel As Object
oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.open("Book1.xlsx")
oExcel.Cells(stroka, stolbets).Value = data
oExcel.Workbooks(1).close(True)
_workbook.sheets(2).activate
Вообще очень много тайн откроет, если сделаешь в екселе запись макроса и посмотришь его код позже
Спасибо большое, сработало! Скажу честно, макросы я пока создавать не умею. Уверен, что это не сложно, но как-нибудь потом разберусь. Сейчас мне как можно быстрее нужно решить конкретную задачу. И я уже научился почти всему, что для этого нужно. Осталось совсем немного.
Можно ещё вопрос? Мне нужно найти последнюю заполненную строку. Я написал программу, которая через цикл проверяет одну строку за другой. Она работает, но это довольно медленно, особенно если файл большой, так что мне не нравится. В интернете я нашёл способ, вроде бы лучше, вот такой:
stroka = oExcel.cells("A".Count, 1).End(xlUp).Row
Но у меня он не работает, ругается на xlUp, говорит не знает такого. То ли я что-то неправильно понял и сделал, то ли в последней Студии это попросту не заложено. Можешь подсказать?
Джони Пруф Просветленный (33926) Тоже такого не знаю к сожалению
Открыть определенный лист в файле
oExcel.ActiveWorkbook.Sheets("ИМЯ_ЛИСТА").Select
Спасибо большое! Можно ещё вопрос? Мне нужно найти последнюю заполненную строку. Я написал для этого программу, которая через цикл проверяет строку за строкой. Она работает, но это довольно медленно, особенно если файл большой, так что мне не нравится. В интернете я нашёл вот такой способ:
stroka = oExcel.cells("A".Count, 1).End(xlUp).Row
Но у меня он не работает, ругается на xlUp, говорит не знает такого. То ли я что-то неправильно понял и сделал, то ли в последней Студии это попросту не заложено. Можете подсказать?
Folko Мастер (2017) К сожалению не знаю другого способа как цикл, и обрывать его (то есть как нашел пустую конец цикла) после обнаружения пустой строки. Единственно если у Вас данных много в таблице рекомендовал бы Вам перейти на хранение и работу с ними а Access, а не в Exсel. Намного проще Вам будет. Тем более что SQL совсем не сложный)))
К именам листов рабочей книги предъявляется несколько ограничений:
1) длина введенного имени не должна превышать 31-го знака;
3) имя не должно быть пустым.
Процесс переименования
Процедура переименования листов в Экселе в целом интуитивно понятна. Тем не менее, у некоторых пользователей, которые только начинают освоение программы, возникают определенные трудности.
Прежде, чем перейти непосредственно к описанию способов переименования, выясним, какие названия давать можно, а присвоение каких будет некорректным. Имя может быть присвоено на любом языке. При его написании можно использовать пробелы. Что же касается основных ограничений, то следует выделить следующие:
- В наименовании не должны присутствовать такие символы: «?», «/», «», «:», «*», «[]»;
- Название не может быть пустым;
- Общая длина наименования не должна превышать 31 знак.
При составлении имени листа нужно учитывать вышеуказанные правила. В обратном случае программа не даст завершить данную процедуру.
контекстное меню ярлыка
Наиболее интуитивно понятный способ переименования – это воспользоваться возможностями, которые предоставляет контекстное меню ярлыков листов, расположенных в левой нижней части окна приложения сразу над строкой состояния.
- Кликаем правой кнопкой по ярлыку, над которым хотим произвести манипуляцию. В контекстном меню выбираем пункт «Переименовать».
двойной щелчок по ярлыку
Существует и более простой способ переименования. Нужно просто кликнуть двойным щелчком по нужному ярлыку, правда, в отличие от предыдущего варианта, не правой кнопкой мыши, а левой. При использовании данного способа никакого меню вызывать не нужно. Наименование ярлыка станет активным и готовым к переименованию. Вам останется только набрать нужное название с клавиатуры.
Как переименовать лист рабочей книги при помощи мыши?
Для переименования листа необходимо:
1) в окне открытой книги дважды щелкнуть левой кнопкой мыши на ярлычке нужного листа;
2) набрать нужное имя листа, соблюдая требования к листам, изложенные выше;
3) нажать клавишу Enter на клавиатуре для закрепления введенного имени.
Вставка нового листа в Excel
Для того чтобы изменить количество листов, открываемых по умолчанию в новой книге Excel, перейдите к представлению Backstage, нажмите Параметры, затем выберите количество листов, которые необходимо включать в каждую новую книгу.
Обращение к рабочим листам
Рабочий лист (Worksheet) принадлежит коллекции всех рабочих листов (Worksheets) книги Excel. Обратиться к листу можно как к элементу коллекции и, напрямую, по его уникальному имени.
Откройте редактор VBA и обратите внимание на вашу книгу в проводнике, где уникальные имена листов указаны без скобок, а в скобках – имена листов, отображаемые на ярлычках в открытой книге Excel. Уникальные имена листов отсортированы по алфавиту и их расположение по порядку не будет соответствовать их индексам (номерам), если листы перемещались по отношению друг к другу. Индексы листов смотрите по порядку расположения ярлычков в открытой книге. Переместили листы – изменились их индексы.
Обращение к рабочему листу в коде VBA Excel:
- УникИмяЛиста – уникальное имя листа, отображаемое в проводнике редактора VBA без скобок, с помощью кода VBA изменить его невозможно.
- N – индекс листа от 1 до количества всех листов в книге, соответствует порядковому номеру ярлычка этого листа в открытой книге Excel.
- Имя листа – имя листа, отображаемое в проводнике редактора VBA в скобках, с помощью кода VBA изменить его можно.
Количество листов в рабочей книге Excel определяется так:
Скрытие и отображение листов
Для скрытия и отображения рабочих листов в VBA Excel используется свойство Worksheet.Visible со следующим синтаксисом:
expression.Visible
где expression – переменная, представляющая собой объект Worksheet. Свойству Worksheet.Visible могут присваиваться следующие значения:
- False – лист становится невидимым, но он будет присутствовать в списке скрытых листов, и пользователь сможет его отобразить с помощью инструментов рабочей книги Excel.
- xlVeryHidden – лист становится супер невидимым и его не будет в списке скрытых листов, пользователь не сможет его отобразить. Актуально для Excel 2003-2016.
- True – лист становится видимым.
Аналоги присваиваемых значений:
- False = xlHidden = xlSheetHidden = 1
- xlVeryHidden = xlSheetVeryHidden = 2
- True = xlSheetVisible = -1 (константа xlVisible вызывает ошибку)
Как создать, скопировать, переместить или удалить рабочий лист с помощью кода VBA Excel, смотрите в этой статье .
Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.Описание задачи
Как правило, когда мы долго работаем с большим количеством листов, возникает потребность в их переименовании. Переименовать листы поштучно достаточно просто дважды кликнув по вкладке и введя новое название. Однако, если листов десятки, то это утомительно и отнимает время. Гораздо удобнее использовать надстройку VBA-Excel.
C помощью нее переименовать все листы в книге можно следующим образом:
Как копировать и переименовать лист Excel макросом
Представьте ситуацию: Вы готовите планы работ для сотрудников определенного отдела фирмы. Пример таблицы плана выглядит так:
Руководитель этого отдела пожелал, чтобы названия листов планов работ для каждого сотрудника содержали их имена и фамилии.
Многократное копирование листов шаблона для составления и заполнения планов работ сотрудников с переименованием, потребует много времени и сил если делать все это вручную. Поэтому напишем свой макрос, который упростит данную задачу.
Техническое Задание (ТЗ) для макроса заключается в следующем. После выделения ячеек, которые содержат названия для листов нужно скопировать лист с шаблоном для планов работ ровно столько сколько будет выделенных ячеек и ввести соответствующие названия в ярлычки.
Для этого сначала откройте редактор кода макроса: «РАЗРАБОТЧИК»-«Код»-«Visual Basic» (ALT+F11):
В редакторе создайте новый модуль выбрав инструмент «Insert»-«Module» и введите в него следующий код макроса:
Sub PlanRabot()
Dim diapaz As Range
Dim i As Long
Dim list As Worksheet
On Error Resume Next
Set diapaz = Application.InputBox( "Пожалуйста, выделите диапазон ячеек, который содержит названия для новых листов!" , Type:=8)
On Error GoTo 0
If diapaz Is Nothing Then Exit Sub
Set list = ActiveSheet
For i = 1 To diapaz.Count
list.Copy after:=ActiveSheet
ActiveSheet.Name = Left(diapaz(i), 31)
Next
End Sub
Теперь если вы хотите скопировать лист шаблона для заполнения плана работ и создать копии плана для каждого сотрудника имена с фамилиями которых будут присвоены названиям листа, то перейдите на исходный лист с шаблоном плана работ и выберите инструмент: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«PlanRabot»-«Выполнить». Сразу же после запуска макроса появиться диалоговое окно:
Теперь перейдите на лист «Имена и Фамилии» и выделите в нем диапазон ячеек, которые содержат имена и фамилии сотрудников. И нажмите на кнопку ОК.
В результате чего макрос сам автоматически скопирует планы работ для каждого сотрудника и присвоит новым листам соответственные имена и фамилии.
Перемещение/копирование листов в Excel
Перемещать листы в Excel с помощью мышки очень просто: возьмите ярлычок листа и, двигаясь по горизонтали, перетащите его на новое место.
Если во время этой операции вы нажмете и будете удерживать клавишу [Ctrl], то лист… правильно, скопируется! (Вспомните, как мы копировали файлы в WINDOWS ). То же самое можно делать с группой листов.
Эту же операцию можно выполнить в Excel с помощью команды контекстного меню листа Переместить/скопировать или команды Правка/Переместить/скопировать лист . Ими особенно удобно пользоваться, если вы хотите скопировать или переместить лист в другую книгу.
Перед копированием или перемещением листа в другую книгу необходимо открыть книгу-получатель. Кроме того, в случае копирования не забудьте установить флажок в поле Создать копию . В противном случае лист просто переместится в указанное вами место.
Задание для самостоятельной работы:
В книге Мои таблицы. xls переместите лист Население Земли в конец книги, верните его на прежнее место, сделайте копию листа в этой же книге, скопируйте его в новую книгу.
Создайте пустой файл Мои таблицы-копии.xls ( для этого новую рабочую книгу сохраните под этим названием в каталоге C:ST). Скопируйте в него лист Население Земли.
Как сменить название листа
При создании новой книги Excel рабочие листы по умолчанию носят имя Лист1, Лист2 и т.д.
Как правило, листы переименовывают в более подходящие имена, в зависимости от его содержания. Это может быть и «Отчет» и «График» и т.д. Чтобы переименовать название листа, дважды кликните на ярлыке данного листа.
Программа выделит этот ярлык и включит название в режим редактирования, так что вы сможете прописать любое название какое захотите.
1. Название листа может содержать максимум до 31 символа, причем пробелы допускаются.
2. В названии листа нельзя прописывать следующие символы:
* (звездочка).
: (двоеточие);
/ (косая черта);
[ ] (квадратные скобки);
< > угловые скобки);
? (знак вопроса);
(обратная косая черта);
Учтите, что именно на ярлыке листа будет отображаться название, поэтому для длинного названия необходимо будет более длинный ярлык. Таким образом, если вы хотите использовать более длинные имена листов, то без прокручивания полосы вы сможете увидеть только несколько ярлыков.
Как изменить имя листа при помощи контекстного меню?
Чтобы переименовать лист при помощи контекстного меню, нужно:
1) в окне открытой книги один раз кликнуть правой кнопкой мыши на ярлыке нужного листа;
3) набрать новое имя листа в соответствии с требованиями к именам листов;
4) нажать клавишу Enter на клавиатуре, чтобы закрепить новое имя.
Удаление листа в Excel
Удаление листа из рабочей книги нельзя отменить. Это одно из немногих необратимых действий в Excel. Хорошенько подумайте, прежде чем удалять их.
Если требуется ограничить изменения, которые разрешается вносить в текущий лист, Вы можете его защитить. Для этого щелкните правой кнопкой мыши по ярлычку листа и из контекстного меню выберите пункт Защитить лист.
Читайте также: