Tooprogram.ru

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

Vba excel имя текущего листа

VBA Excel. Рабочий лист (обращение, переименование, скрытие)

Обращение к рабочим листам Excel из кода VBA. Переименование листов, скрытие и отображение с помощью кода VBA Excel. Свойства Worksheets.Name и Worksheets.Visible.

Обращение к рабочим листам

Рабочий лист (Worksheet) принадлежит коллекции всех рабочих листов (Worksheets) книги Excel. Обратиться к листу можно как к элементу коллекции и, напрямую, по его уникальному имени.

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

Обращение к рабочему листу в коде VBA Excel:

  • УникИмяЛиста – уникальное имя листа, отображаемое в проводнике редактора VBA без скобок, с помощью кода VBA изменить его невозможно.
  • N – индекс листа от 1 до количества всех листов в книге, соответствует порядковому номеру ярлычка этого листа в открытой книге Excel.
  • Имя листа – имя листа, отображаемое в проводнике редактора VBA в скобках, с помощью кода VBA изменить его можно.

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

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

В VBA Excel есть некоторые особенности в наименовании листов, так как у рабочего листа есть два свойства, связанных с именем: (Name) и Name. Откройте окно «Properties» в редакторе VBA, нажав клавишу «F4», и выделите любой лист в проводнике. Вы увидите, что в окне «Properties» свойству (Name) в скобках соответствует в проводнике уникальное имя листа без скобок, а свойству Name без скобок соответствует изменяемое имя листа в скобках. Оба имени в окне «Properties» можно редактировать.

С помощью кода VBA Excel можно редактировать только имя листа Name, отображаемое на ярлычке листа и в проводнике без скобок. Для этого используется свойство рабочего листа Worksheets.Name со следующим синтаксисом:

expression.Name

где expression – переменная, представляющая собой объект Worksheet. Смена имени осуществляется путем присвоения нового значения свойству Worksheets.Name.

Допустим, у нас есть лист с уникальным именем (Name) – Лист1, индексом – 1 и именем Name – МойЛист, которое необходимо заменить на имя – Реестр.

Скрытие и отображение листов

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

expression.Visible

где expression – переменная, представляющая собой объект Worksheet. Свойству Worksheet.Visible могут присваиваться следующие значения:

  • False – лист становится невидимым, но он будет присутствовать в списке скрытых листов, и пользователь сможет его отобразить с помощью инструментов рабочей книги Excel.
  • xlVeryHidden – лист становится супер невидимым и его не будет в списке скрытых листов, пользователь не сможет его отобразить. Актуально для Excel 2003-2016.
  • True – лист становится видимым.

Аналоги присваиваемых значений:

    False = xlH >Примеры:

Как создать, скопировать, переместить или удалить рабочий лист с помощью кода VBA Excel, смотрите в этой статье.

1 комментарий для “VBA Excel. Рабочий лист (обращение, переименование, скрытие)”

Уникальное имя листа, отображаемое в проводнике редактора VBA без скобок, с помощью кода VBA изменить невозможно. В статье “Кодовое имя листа – что это и зачем нужно? Какие плюсы и минусы?” приведён код, позволяющий это сделать.

Читать еще:  Выбор листа excel vba

Как получить имя листа формулой

Задача: записать в отдельной ячейке или внутри формулы имя текущего листа(т.е. того, в котором сама функция).

В принципе это очень легко сделать простейшей функцией пользователя:

‘————————————————————————————— ‘ Procedure : GetShName ‘ DateTime : 04.03.2015 10:44 ‘ Author : The_Prist(Щербаков Дмитрий) ‘ http://www.excel-vba.ru ‘ Purpose : Функция возвращает в ячейку имя листа ‘ rCell — Необязательный аргумент. ‘ Если указан — функция вернет имя листа, на котором расположена эта ячейка ‘ Если не указан — функция вернет имя листа, в котором записана функция ‘————————————————————————————— Function GetShName(Optional rCell As Range) If Not rCell Is Nothing Then GetShName = rCell.Parent.Name Else GetShName = Application.Caller.Parent.Name End If End Function

Синтаксис:
получение имени листа, в котором записана функция:
=GetShName()
получение имени листа, в котором расположена указанная ячейка
=GetShName( A1 ) — данная запись равнозначна записи без ячейки, т.к. ячейка все равно в пределах листа с самой функцией
=GetShName( Лист2!A1 )

Но бывают случаи, когда использование макросов весьма нежелательно. Тогда можно воспользоваться чуть более громоздкой и менее понятной формулой:
=ПСТР(ЯЧЕЙКА(«filename»; A2 );ПОИСК(«]»;ЯЧЕЙКА(«filename»; A2 ))+1;31)
=MID(CELL(«filename»,A2),SEARCH(«]»,CELL(«filename»,A2))+1,31)
Однако эта формула вернет точно такой же результат, как функция пользователя выше и макросы совершенно не нужны.

Теперь разберем эту формулу поподробнее
Самая основная часть — ЯЧЕЙКА(«filename»;A2). Функция ЯЧЕЙКА (CELL) с записанным первым аргументом «filename» возвращает полный путь к книге, включая имя листа и адрес ячейки, в которой записана функция:
C:UsersДмитрийDesktop[Tips_All_GetShName.xls]Лист1
Т.к. нам нужно только имя листа — мы применяем ПСТР (MID) , которая возвращает часть текста, начиная с указанной позиции символа. ПОИСК (SEARCH) ищет нам именно эту позицию — позицию символа «]».
Если по шагам просмотреть этапы работы формулы, то будет нечто вроде:
=ПСТР(ЯЧЕЙКА(«filename»; A2 );ПОИСК(«]»;ЯЧЕЙКА(«filename»; A2 ))+1;31)
Шаг1 =>
=ПСТР(ЯЧЕЙКА(«filename»; A2 );ПОИСК(«]»;C:UsersДмитрийDesktop[Tips_All_GetShName.xls]Лист1)+1;31)
Шаг2 =>
=ПСТР(ЯЧЕЙКА(«filename»; A2 );49+1;31)
Шаг3 =>
=ПСТР(C:UsersДмитрийDesktop[Tips_All_GetShName.xls]Лист1;50;31)
Шаг4 =>
=Лист1
Первый момент: почему применяю цифру 31 последним аргументом ПСТР? По факту, там необходимо указывать точное количество символов, но если указать больше — то будут взяты все символы от указанного и до последнего. Т.е можно было бы указать и 99, но 31 — это максимальное количество символов, которое можно использовать в имени листа.
Второй момент: первым аргументом функции ЯЧЕЙКА указывается текст, обозначающий тип сведений. В русской локализации он доступен на русском — «имяфайла». Однако при открытии файла с этой функцией в другой локализации тип сведений не будет переведен и функция не сможет работать. Поэтому я указываю на английском, т.к. он является универсальным в данном случае и будет работать в любой локализации. Однако нет никакой ошибки, если указать на русском: ЯЧЕЙКА(«имяфайла»; A2 )

Если вторым аргументом функции ЯЧЕЙКА ничего не указывать( =ЯЧЕЙКА(«filename») ), то функция вернет полный путь с именем того листа, который активен в данный момент(даже если это лист другой книги).

Правда, у этой формулы есть свои недостатки: обязательно необходимо, чтобы книга была сохранена на диске. Это означает, что формула не сработает для книги, которая была только что создана и не сохранена. Связано это с ограничениями возможностей параметра «filename» функции ЯЧЕЙКА(CELL). Она не может получить путь к файлу, который еще не сохранен.
Функция пользователя(UDF) GetShName (приведенная в самом начале статьи) лишена этого недостатка.

Для чего вообще может быть нужно записывать имя листа в ячейку? Ну, например, если имя листа периодически меняется, а в своих формулах вы используете функции вроде ДВССЫЛ со ссылкой на этот лист. Либо для создания более наглядного оглавления через гиперссылки.

Читать еще:  Высота и ширина ячейки в excel

Кто-то уже явно догадался, что подобным же образом можно получить не только имя листа — но и имя книги:
=ПСТР(ЯЧЕЙКА(«filename»);ПОИСК(«[«;ЯЧЕЙКА(«filename»))+1;ПОИСК(«]»;ЯЧЕЙКА(«filename»))-ПОИСК(«[«;ЯЧЕЙКА(«filename»))-1)
так же как и для имени листа — можно указать ячейку из другой книги и тогда формула вернет имя той книги, из которой указана ячейка.
Если ячейка не указана — функция вернет имя активной в данный момент книги.

Так же можно получить полный путь к книге и имя книги(без квадратных скобок и имени листа):
=ПОДСТАВИТЬ(ПСТР(ЯЧЕЙКА(«filename»;A1);1;ПОИСК(«]»;ЯЧЕЙКА(«filename»;A1))-1);»[«;»»)

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

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

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

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

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

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

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

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

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

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

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

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


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

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

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

Vba excel имя текущего листа

Аэроплан » 23.10.2003 (Чт) 13:59

Читать еще:  Функция countif в excel

Макрос создает новый лист.
Неоднократно все время пока файл открыт.
Имена создаваемых файлов меняются Лист1, Лист2.
Как узнать его имя?

.
Я знаю только точное его положение. Он всегда третий слева.

Tony » 23.10.2003 (Чт) 14:29

Но ведь это совершенно не по взрослому 😉

Аэроплан » 23.10.2003 (Чт) 14:51

А как же тогда пользоваться
Код: Выделить всё Workbook_NewSheet(ByVal Sh As Object)
Ведь это, насколько я понимаю, как раз и есть инструмент для выхватывания «листовых новообразований» в файле.
Я вот только не знаю, как изменить имя листа.

Код типа Sh.Name = «имя_листа» не работает и вообще при компиляции он говорит, что Sh.Name содержит в себе имя ВСЕГО документа, т.е. Имя_файла.xls.

Вот такая петрушка.

Tony » 23.10.2003 (Чт) 14:55

Аэроплан » 23.10.2003 (Чт) 15:54

Да нет же. Не имеет значения, каким образом создается ЛИСТ.
Просто эта процедура как-то реагирует и что-то возвращает при создании нового листа.. Т.е. она точно реагирует и отрабатывается сразу при появлении нового листа. Однако она не возвращает ИМЯ нового созданного листа. Точнее я незнаю как его оттуда выдрать. Вот.
Код: Выделить всё Sub Workbook_NewSheet(ByVal Sh As Object)

‘ тут чтото я незнаю’

Sh.Name = Date
Sheets(newSheetName).Select
Sheets(newSheetName).Move After:=Sheets(3)
Sheets(«Хронология»).Select
Range(«A1:K3»).Select
Selection.Copy

Sheets(newSheetName).Select
ActiveSheet.Paste
Sheets(«Хронология»).Application.CutCopyMode = False

End Sub

Так примерно я наваял. А вот Sh.Name не работает.
Точнее работает, но не так. При компиляции выделенный Name возвращает имя ВСЕГО файла Имя_файла.xls и на этом вылетает. в ошибку.

RayShade » 23.10.2003 (Чт) 16:07

Что то ты мудришь по ходу.

У меня все работает на ура. И с выводом имени листа и с его изменением. Версия офиса какая? Сервиспаки?

Vitaly1 » 23.10.2003 (Чт) 16:41

Если знаешь расположение то так, для активной книги:

Извиняюсь.

Аэроплан » 23.10.2003 (Чт) 17:11

Я извиняюсь. Действительно перемудрил. Код этот работает.
блин, я его собрал, и стал жаловаться на судьбу, а проверить еще не успел.. Проблема в другом.
Совсем в другом.

Если в один день создается несколько листов, то необходимо из
Код: Выделить всё newSheetName = Date

сделать Код: Выделить всё newSheetName = Date + Time

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

Огромное спасибо за помощь и внимание.

Буду теперь из времени выдергивать двоеточия и менять их на «_».
Снова в поиск.

RayShade » 23.10.2003 (Чт) 17:17

Советую воспользоваться таким водом:

Код: Выделить всё newSheetName = replace(cstr(date+time),»:»,»_». vbtextcompare)

Vitaly1 » 23.10.2003 (Чт) 17:29

там функция есть говорил RayShade но я ее не знаю, поэтому используй мой программный код преобразования:

S = »20:10:3»
s1 =»»
for i=1 to len(s)
if mid(s,i,1) <>«:» then
s1=s1+mid(s,i,1)
else
s1=s1+» »
end if
next i

в s1 символ : замениться на пробел

RayShade » 23.10.2003 (Чт) 18:07

. без комментариев

На самом деле код Vitaly1 можно модифицировать избавив себя от необходимости заводить лишнюю строковую переменную (только правда в случае, когда искомая подстрока и подстрока замены одинаковы в длине):

Код: Выделить всё s = »20:10:3»
for i=1 to len(s)
if m
next i

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