Elettracompany.com

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

Переименовать лист в excel vba

Запись макроса, переименовывающего лист

Знакомство с объектами

Чтобы решить проблему, используя программирование на VBA, необходимо сначала понять, с какими объектами будет работать код. Важным средством изучения этих сведений является Справочник по объектной модели Excel, входящий в справочное руководство разработчика по Excel 2007 в библиотеке MSDN.

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

Рисунок 3. Справочник по объектной модели Excel на сайте MSDN

Первый шаг — понять, как управлять конкретными объектами, нужными для решения задачи, такими как листы, имена листов, ячейки и содержимое ячеек. В Excel существует по крайней мере два способа решения этой проблемы:

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

· Запись нескольких автоматизируемых действий, просмотр того, как записанный код работает с объектами, и последующее обращение к справочному руководству по объектной модели за дополнительными сведениями.

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

Использование записи макроса

Иногда все, что требуется — просто записать макрос, в этом случае даже не потребуется просматривать код. Но чаще одной записи недостаточно. Но запись служит отправной точкой последующего процесса.

Использование записи макроса в качестве отправной точки решения

1. Запишите действия, которые нужно кодировать.

2. Просмотрите код и найдите строки, выполняющие действия.

3. Удалите оставшуюся часть кода.

4. Измените записанный код.

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

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

Запись макроса, переименовывающего лист

1. Нажмите кнопку Записать макрос на вкладке Разработчик.

2. Назовите макрос RenameWorksheets, переименуйте Лист1 в Новое имя и нажмите кнопкуОстановить запись.

3. На вкладке Разработчик или Вид нажмите кнопку Макрос и выберите Изменить, чтобы открыть редактор Visual Basic.

Код в редакторе Visual Basic должен быть похож на следующий код.

Sheets(«Sheet1»).Name = «New Name»

Первые четыре строки после строки Sub — это комментарии. Любая строка, начинающаяся с апострофа, является комментарием и не влияет на действия макроса. В основном комментарии используются для следующих целей:

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

· Чтобы временно отключить строку кода (закомментировать).

Четыре строки комментариев в записанном макросе не решают никаких задач, поэтому удалите их.

Следующая строка использует метод Select, чтобы выбрать член Sheet1 (Лист1) объекта коллекции Sheets. В коде VBA обычно не нужно выбирать объекты перед работой с ними, даже если это делается при записи макросов. Другими словами, эта строка кода является избыточной, поэтому ее тоже можно удалить.

Последняя строка записанного макроса изменяет свойство «Name» члена Sheet1 коллекции Sheets. Эту строку нужно сохранить.

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

Sheets(«Sheet1»).Name = «New Name»

Вручную измените имя листа «Новое имя» обратно на «Лист1», затем выполните макрос. Имя должно измениться обратно на «Новое имя».

Изменение записанного кода

Теперь самое время изучить коллекцию Sheets, используемую при записи макроса. Раздел «Листы» в справочном руководстве по объектной модели содержит следующий текст.

«Коллекция Sheets может содержать объекты Chart или Worksheet. Если нужно работать только с листами одного типа, просмотрите раздел об объекте этого типа листа».

Используется только коллекция Worksheets, поэтому изменить код следующим образом.

Worksheets(«Sheet1»).Name = «New Name»

Циклы

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

В VBA есть конструкция, называемая циклом For Each и идеально подходящая для этого случая. Цикл For Each проверяет все элементы в объекте коллекции, таком как Worksheets, и может использоваться для выполнения действия (например, изменения имени) над некоторыми или над всеми этими элементами.

Дополнительные сведения о цикле For Each см. в справочном руководстве по языку VBA. Щелкните «Visual Basic Conceptual Topics» (Концептуальные темы Visual Basic), затем «Using For Each. Next Statements» (Использование инструкций For Each. Next). Кроме того, помните, что справочное руководство по языку VBA, как и справочное руководство по объектной модели, сторицей окупит время, потраченное на его изучение, и является отличным местом для поиска идей при плотной работе над кодом.

Используя третий пример в разделе «Using For Each. Next Statements» (Использование инструкций For Each. Next), измените макрос так, чтобы он стал похож на следующий код.

For Each myWorksheet In Worksheets

myWorksheet.Name = «New Name»

myWorksheet является переменной, то есть ее значение меняется. В этом случае переменная myWorksheet последовательно представляет каждый лист в коллекции Worksheets. Необязательно использовать myWorksheet, можно использовать «x», «ws», «WorksheetToRenameAfterTheContentsOfCellB1» или (с небольшими ограничениями) практически любое желательное имя. Хорошей практикой является использование имен переменных, достаточно длинных, чтобы напомнить о ее смысле, но не настолько длинных, чтобы загромождать код.

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

myWorksheet.Name = «New Name»

Чтобы исправить строку так, чтобы можно было проверить работу цикла For Each, измените строку следующим образом.

myWorksheet.Name = myWorksheet.Name & «-changed»

Вместо попытки присвоить каждому листу одно и то же имя, эта строка изменяет текущее имя каждого листа (myWorksheet.Name) на текущее имя с добавлением суффикса «-changed».

Читать еще:  Эксцесс в excel

Полезное переименование

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

В этот раз вместо использования записи макроса для изучения ссылки на ячейку, сделаем предположение и посмотрим, сработает ли использование объекта Cell (ячейка). Это хорошее предположение, но если открыть справочное руководство по объектной модели и попытаться найти объект Cell, окажется, что там никакого объекта Cell нет! Но есть объект CellFormat (Возможно, на английском языке).

Раздел объекта CellFormat содержит следующий код в первом примере.

‘ Set the interior of cell A1 to yellow.

Предполагается, что объект Range (диапазон) используется, чтобы задать диапазон ячеек или только одну отдельную ячейку. И снова, часть .Selectне понадобится, но понадобится найти, как ссылаться на содержимое объекта Range, а не на сам объект Range. Если перейти к разделу Range, можно прочитать, что у объекта Range есть и Methods, и Properties. Содержимым объекта Range является сущность, не действие, поэтому это, скорее всего, будет Property. Если просмотреть список, можно найти свойство Value. Поэтому попробуйте следующий код.

For Each myWorksheet In Worksheets

Выполнение этого кода для книги, содержащей листы с пустой ячейкой B1, приведет к ошибке, так как значение свойства Value пустого диапазонаRange равно «» (пустая текстовая строка), которое не является допустимым именем листа. Самое время создать какие-нибудь данные для примера. Сделайте три листа книги похожими на показанные на следующем рисунке, а затем выполните макрос.

Рисунок 4. Пример данных для макроса RenameWorksheets

Имена листов изменятся соответствующим образом.

Проверка пустых ячеек

Как отмечалось ранее, макрос приводит к ошибке, если любая из ячеек B1 книги оказывается пустой. Вместо ручной проверки каждого листа можно запрограммировать макрос так, чтобы эти действия выполнялись в нем. Перед строкой myWorksheet.Name добавьте следующую строку кода.

If myWorksheet.Range(«B1»).Value <> «» Then

А после строки myWorksheet.Name добавьте следующий текст.

Такой код называется инструкцией If…Then. Инструкция If…Then указывает Excel выполнять весь код между строкой If и строкой End If, но только при выполнении условия, приведенного в строке If. В примере проверяемое условие задается следующей строкой.

Знаки <> означают «не равно», а знаки кавычек, между которыми ничего нет, обозначают пустую текстовую строку, то есть полное отсутствие текста. Следовательно, все строки кода между If и End If будут выполнены только если значение в ячейке B1 не пусто, то есть, когда ячейка B1 содержит текст.

Дополнительные сведения об инструкции If…Then см. в справочном руководстве по языку VBA. (Полное название раздела — «If…Then…Else statement» (Инструкция If…Then…Else), где Else — это необязательный компонент.)

Объявления переменных

Другим улучшением, которое нужно внести в макрос, является помещение в начало макроса объявления переменной myWorksheet.

Dim myWorksheet As Worksheet

Dim является сокращением от «Dimension» (размерность), а Worksheet — это тип этой конкретной переменной. Эта инструкция сообщает VBA, какой тип сущности представляет переменная myWorksheet. Обратите внимание, что после введения As, редактор Visual Basic выводит всплывающую подсказку, содержащую перечень всех доступных типов переменных. Это пример технологии IntelliSense, то есть редактор Visual Basic реагирует на то, что, как он считает, пытается сделать пользователь, и предлагает список соответствующих вариантов. Можно выбрать вариант из списка или продолжить ввод.

Хотя объявления переменных в VBA не являются обязательными, их использование настоятельно рекомендуется! Объявление переменных резко упрощает отслеживание переменных и обнаружение ошибок в коде. Кроме того, помните, что при объявлении переменной с типом объекта (например, Worksheet) IntelliSense выводит соответствующий список свойств и методов, связанных с объектом, при последующем использовании этой переменной объекта в макросе.

Комментарии

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

Dim myWorksheet As Worksheet

For Each myWorksheet In Worksheets

‘make sure that cell B1 is not empty

If myWorksheet.Range(«B1»).Value <> «» Then

‘rename the worksheet to the contents of cell B1

Чтобы проверить макрос, переименуйте листы обратно в Лист1, Лист2 и Лист3 и удалите содержимое ячейки B1 на одном или нескольких листах. Выполните макрос, чтобы проверить, что он переименовывает листы с текстом в ячейке B1 и оставляет нетронутыми остальные листы. Макрос работает для любого числа листов, с любой комбинацией заполненных и пустых ячеек B1.

Как переименовать листы в Excel? Переименование листов рабочей книги

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

Требования к именам листов

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

1) длина введенного имени не должна превышать 31-го знака;

2) имя листа не должно содержать ни одного из следующих знаков: двоеточи» ( : ), косая черта ( / ), вопросительный знак ( ? ), звездочка ( * ) и квадратные скобки ( [ ] );

3) имя не должно быть пустым.

Как переименовать лист рабочей книги при помощи мыши?

Для переименования листа необходимо:

1) в окне открытой книги дважды щелкнуть левой кнопкой мыши на ярлычке нужного листа;

2) набрать нужное имя листа, соблюдая требования к листам, изложенные выше;

3) нажать клавишу Enter на клавиатуре для закрепления введенного имени.

Как изменить имя листа при помощи контекстного меню?

Чтобы переименовать лист при помощи контекстного меню, нужно:

Читать еще:  Как убрать умную таблицу excel

1) в окне открытой книги один раз кликнуть правой кнопкой мыши на ярлыке нужного листа;

2) в контекстном меню выбрать пункт «Переименовать»;

3) набрать новое имя листа в соответствии с требованиями к именам листов;

4) нажать клавишу Enter на клавиатуре, чтобы закрепить новое имя.

Переименование листа при помощи стандартного меню

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

1) в окне открытой книги перейти на лист, который необходимо переименовать;

2) перейти на вкладку «Главная», в группе кнопок «Ячейки» зайти в меню кнопки «Формат» и выбрать пункт «Переименовать лист»;

3) ввести новое имя листа, соблюдая требования к именам листов;

4) нажать клавишу Enter для закрепления нового имени.

Как быстро переименовать все листы рабочей книги Excel?

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

1) быстро вызывать диалоговое окно надстройки из панели инструментов;

2) присваивать листам имена, соответствующие значениям ячеек в выделенном диапазоне (весь диапазон ячеек находится на одном листе);

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

4) формировать список, состоящий из имен листов рабочей книги в ячейках заданного столбца (столбец можно задавать как номером, так и именем).

Переименовать лист в excel vba

Поговорим про то, как в VBA обращаться к листам книги Excel.

Вариант 1

У глобального объекта Excel Application есть 2 коллекции листов: Sheets и Worksheets . Отличаются они тем, что вторая коллекция не включает в себя специальные листы макросов, которые поддерживала Excel 4.0 (на минуточку — 1992 год) во времена, когда VBA ещё не было в природе (появился в Excel 5.0). Тяжело найти эстетов, которые всё ещё пользуются такими листами с макросами, поэтому можно считать, что эти коллекции идентичны. Лично я использую коллекцию Sheets просто потому, что 5 букв лучше, чем 9.

Sheets(1).Cells(1,1)

Sheets(1) — это не первый лист, созданный в книге, а лист, который на панели ярлыков листов занимает КРАЙНЕЕ ЛЕВОЕ положение. То есть Sheets(2) будет указывать на второй слева лист на панели ярлыков. Если вам нужен конкретный лист, то ТАК ссылаться на него, как вы понимаете, плохая идея, ибо пользователь, изменив порядок следования листов, нарушит работоспособность вашей программы.

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

Вариант 2

Sheets(«First»).Cells(1,1)

Пожалуй, такое обращение к листу наиболее популярно в народе. В скобках и кавычках мы видим пользовательское имя листа. Плохо только то, что имя листа, что называется «hard coded» (жёстко запрограммировано), что является моветоном в программировании. Ибо, если у вас в модуле конструкция Sheets(«First») встречается 100 раз, то при смене имени листа придётся делать поиск с заменой 100 констант в коде. Поэтому обычно делают иначе.

Вариант 3

Гораздо разумней добавить ещё один уровень абстракции и объявить константу, ссылающуюся на имя листа.

Public Const wsFirst = «First»

Sheets(wsFirst).Cells(1,1)

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

Вариант 4

У листа есть свойство Name , которое мы видим на ярлыке листа и которое можем менять (если не включена защита структуры книги, но сейчас не об этом), но ещё есть свойство CodeName , которое присваивается листу в момент создания, и которое пользователь сменить не может. В русской редакции офиса эти CodeName -ы формируются по принципу: Лист1 , Лист2 , Лист3 и т.д. В англоязычной версии это: Sheet1 , Sheet2 , Sheet3 , etc.

Если вы удалите лист, то его CodeName повторно не будет использовано. Так вот — VBA разпознаёт имена совпадающие с CodeName листа, как объекты типа Worksheet .

Лист1.Cells(1,1) или [Лист1].Cells(1,1)

Это отличная защита от того, что пользователь перенесёт или переименует лист. Но подобные имена ни о чём не говорят нам в процессе программирования в плане того, что располагается на листе, поэтому вы будете частенько ошибаться, путая листы. А те, кто будут потом разбираться в вашем коде, тоже спасибо вам не скажут. Поэтому я предлагаю следующую модификацию этого способа:

Вариант 5

Public wsData As Worksheet

Set wsData = Лист1

wsData.Cells(1,1)

Строку 1 размещаем в объявлениях модуля. Строку 2 размещаем в обработчике события Workbook_Open . И после этого в любом месте книги мы можем использовать объявленные переменные ( wsData , строка 3). На мой взгляд, это самый удобный и продвинутый вариант из рассмотренных.

Вариант 6

Не забываем про свойство ActiveSheet объекта Application , указывающее на активный лист книги. Если вы работаете с текущим листом, то использовать его, я считаю, оптимально.

ActiveSheet.Cells(1,1)

Вариант 7

Ну и последнее, если вам передан в подпрограмму объект типа Range, то не забываем, что ссылку на лист возвращает его свойство Worksheet .

Это всё, что я имел сказать по поводу ссылок на листы в Excel VBA 🙂

Переименовать лист в excel vba

3257 просмотра

5 ответа

1 Репутация автора

Я знаю, как сделать марко, но в школе он никогда не учил меня всему, что с ними делать, в основном с Димом. Мои вопросы: как сделать марку, которая переименует все мои листы, ожидая первых четырех.

Работает для каждого листа, но я не хочу переименовывать каждый лист. Мои первые четыре: Documentation, Summarry, RONATemplate, KaycanTemplate. Который я хочу уйти, есть. Я не могу на самом деле просто поставить эти имена в ячейку D5, чтобы заставить его работать там, где находится его шаблон, и это испортит мой другой маркос.

Читать еще:  Как написать парсер vba excel

Ответы (5)

плюса

16913 Репутация автора

Первый вариант — использовать различный тип цикла, который выполняет итерацию на основе индекса / числа листа. Вот код, который работает только для Worksheets Collection :

Ваша петля начинается с 5-го листа и запускается до последнего.

Другой вариант — исключить все листы с именами, которые вы упомянули в своем вопросе. В этой ситуации вы можете запустить этот макрос:

Однако имейте в виду, что все условные проверки, например rs.Name <> «Summary» , чувствительны к регистру, поэтому вам нужно помещать соответствующие имена в код, включая верхний и нижний регистр. Или вы можете использовать UCase функцию для сравнения с заглавными именами, например:

Я бы предложил использовать второй тип улучшенной процедуры. Если вы измените порядок своих листов (например, перенесите первый лист на 6-ю позицию), вы получите неожиданные результаты, запускающие первый For i=1 цикл. Нет такой проблемы при запуске второго типа цикла / подпрограммы.

плюса

1 Репутация автора

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

плюса

429 Репутация автора

Чтобы изменить каждый лист после четвертого, используйте свойство индекса листов в выражении if:

Автор: Ryan E Размещён: 25.05.2013 01:57

плюса

1 Репутация автора

<> <> <> Посмотрите мой комментарий внизу.

Автор: Steve Размещён: 23.11.2018 04:20

плюса

1 Репутация автора

Проблема с формулой, которая говорит только о том, чтобы идти на n-й лист, — это то, что если вы хотите добавить больше листов в свой файл excel, а первый горячий лист для применения вашего макроса больше не будет вашим 4-м или 5-м? Потому что тогда вам нужно вернуться и обновить каждый макрос.

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

Dim rs В качестве рабочего листа

Для каждого rs в листах Если x01st.select <> И x02nd.select <> И x03rd.select <> И x04th.select <> И x05th.select <> И x06th.select <> И x07th.select <> И x08th. выберите <> И x09th.select <> И x10th.select <> И x11th.select <> И x12th.select <> И x13th.select <> И x14th.select <> И x15th.select <> И x16th.select И x17th.select <> И x18th.select <> И x19th.select <> И x20th.select <> И x21st.select <> И x22nd.select <> И x23rd.select <> И x24th.select <> И x25th.select <> И x26th.select <> И x27th.select <> И x28th.select <> И x29th.select <> И x30th.select <> И x31st.select <> Затем rs.Name = rs.Range ( «h100 «) Далее rs

Единственное, что я могу придумать, — это просто ввести имя листа, который вы хотите сохранить в D5 этого листа. Может ли кто-нибудь помочь?

Формула номера листа и страницы в Excel

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

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

Рассмотрим варианты, которые позволят нам узнать номера листов и страниц в автоматическом режиме.
Однако прежде всего поймем в чем именно разница между листом и страницей в Excel.
Лист Excel — это таблица, состоящая из ячеек структурированная в виде данных в строках и столбцах, а страница — это представление листа при печати. Другими словами, в зависимости от настроек печати, один лист может содержать несколько страниц, поэтому задачу поиска номеров листов и страниц будем решать по отдельности.

Формула номера листа в Excel

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

Функция ЛИСТ (начиная с Excel 2013)

Синтаксис и описание функции:

ЛИСТ(значение)
Возвращает номер указанного листа.

  • Значение(необязательный аргумент) — название листа или ссылка, для которой нужно узнать номер листа.

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


При работе с функцией обратите внимание, что формулы =ЛИСТ(«Лист2») и =ЛИСТ(A1) (при этом ячейка A1, например, содержит текст «Лист2») могут вернуть разные результаты, так как в первом случае аргумент представлен в виде текста (поиск листа по текстовому названию), а во втором — в виде ссылки (поиск листа по ссылающейся ячейке).

Номер листа на VBA

Для создания пользовательской функции перейдем в редактор Visual Basic (комбинация клавиш Alt + F11), далее создаем новый модуль и добавляем в него следующий код:

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