Как обратиться к листу excel vba по имени
Добрый день! Есть такой макрос.
Как сделать так, чтобы он обращался не к конкретному листу "Лист1" файла "Книга3.xlsm", а просто к активному листу данного файла безотносительно его наименования?
Добрый день! Есть такой макрос.
Как сделать так, чтобы он обращался не к конкретному листу "Лист1" файла "Книга3.xlsm", а просто к активному листу данного файла безотносительно его наименования? Влад777
Как сделать так, чтобы он обращался не к конкретному листу "Лист1" файла "Книга3.xlsm", а просто к активному листу данного файла безотносительно его наименования? Автор - Влад777
Дата добавления - 01.07.2015 в 15:02
Дата добавления - 01.07.2015 в 15:09
ТЕРПЕНИЕ И ТРУД - ВСЁ ПЕРЕТРУТ!
[/vba] Автор - МакрописецДата добавления - 01.07.2015 в 15:14
Такой вариант не подходит - макрос не видит к какому файлу надо обращаться. Необходимо сохранить отсылку к файлу Книга3.
Такой вариант не подходит - макрос не видит к какому файлу надо обращаться. Необходимо сохранить отсылку к файлу Книга3. Влад777
Такой вариант не подходит - макрос не видит к какому файлу надо обращаться. Необходимо сохранить отсылку к файлу Книга3. Автор - Влад777
Дата добавления - 01.07.2015 в 15:22
[vba] Ну так обращайтесь к файлу. В чем проблема? Можно в начале макроса вообще сделать так:
[vba] [/vba] Автор - SkyPro
Дата добавления - 01.07.2015 в 15:27 Макрос в файле Приложение 1. копирует ячейку из файла Книга 3. Мне необходимо чтобы он копировал не из конкретного листf, а просто из активного листа. Макрос в файле Приложение 1. копирует ячейку из файла Книга 3. Мне необходимо чтобы он копировал не из конкретного листf, а просто из активного листа. Влад777
SkyPro, Как в таком случае будет выглядеть строчка?
В ней и проблема. Мне надо избавится от "Лист1" и заменить на просто активный лист.
SkyPro, Как в таком случае будет выглядеть строчка?
В ней и проблема. Мне надо избавится от "Лист1" и заменить на просто активный лист. Влад777
В ней и проблема. Мне надо избавится от "Лист1" и заменить на просто активный лист. Автор - Влад777
Дата добавления - 01.07.2015 в 15:33
Так подойдет?[vba] Влад777, да без разницы какая книга, Вам просто показали, как получить ее имя и имя активного листа.
Так подойдет?[vba]
ЯД: 410013299366744 WM: R193491431804
[/vba] Автор - ManyashaДата добавления - 01.07.2015 в 15:35 [/vba] Автор - RAN
Дата добавления - 01.07.2015 в 15:37
Макрописец, добрый день.
Ваш вариант все равно обращается к конкретному листу "Лист1". Именно в этой строчке у меня и была проблема. Как раз, ее и исправила мне Manyasha.
Макрописец, добрый день.
Ваш вариант все равно обращается к конкретному листу "Лист1". Именно в этой строчке у меня и была проблема. Как раз, ее и исправила мне Manyasha. Влад777
Ваш вариант все равно обращается к конкретному листу "Лист1". Именно в этой строчке у меня и была проблема. Как раз, ее и исправила мне Manyasha. Автор - Влад777
Дата добавления - 02.07.2015 в 09:47
Обращение к листу Excel по имени
Добрый день! Есть такой код string workpath =.
Обращение к листу по имени в Microsoft.Office.Interop.Excel
Судя по всему так делать нельзя: Excel.Worksheet Sheet =.
Как обратиться к листу, но не указывая его имени?
Подскажите, как обратиться к листу, но не указывая его имени. т.е. Private Sub.
Обратиться к кодовому имени листа из надстройки
Здравствуйте дорогие форумяне! помогите пожалуйста с ситуацией. Есть в книге кодовое имя листа.
Это не кодовое, а "обычное" имя.
Добавлено через 5 минут
А почему бы не передавать в процедуру ссылку на сам объект, вместо имени?
Насколько я знаю у листа есть еще одно имя - кодовое, если посмотреть в свойствах листа, оно написано на против свойства (Name) и именно там написано ЛистN и поэтому подходит как раз таки второй вариант. [B][Но/B] если например мне нужно перечислить два листа то наверное было бы удобней что нибудь в этом роде
Если в коде каким либо образом можно изменить тип переменной и не потерять её значение, то наверно да, можно на 100% использовать ваш второй вариант, поэтому если вы знаете как это сделать буду очень благодарен Не очень ясна суть проблемы, но я привык так перечислять нужные листы
Не очень ясна суть проблемы, но я привык так перечислять нужные листы
Госпади, читайте внимательно, во первых код не работает, во вторых thisworkbook.worksheets(S) почти абсолютно идентично Sheets(imiaLista), а мне нужно кодовое имя листа (Name в скобках) imiaLista, т.е вы видете в редакторе Лист1(название), Лист2(название) и т.п. нужно обратиться к Лист1 а не к его названию
Добавлено через 23 минуты
Перефразирую вопрос можно ли свойство (Name) (первое из свойств листа, прошу заметить в скобках не случайно) использовать как переменную, как использовать 10-е свойство по списку Name (прошу заметить без скобок) я знаю, и мне это не подходит
Номера листов соответствуют порядку, в котором расположены их ярлычки.
Коллекция sheets включает как рабочие листы, так и листы других типов (диаграммы, листы макросов).
Для обращения только к рабочим листам следует использовать коллекцию worksheets.
А кодовое имя оставьте в покое, оно нужно только в очень специфических случаях.
На другом форуме (ссылки на другие форумы давать нельзя ) выяснили, что кодовое имя может меняться, если файл открывают в иноязычной версии Excel!
Я знаю что можно и по номеру, но мне нужно обратиться именно к свойству (Name), по поводу других языков, можно написать Sheet1 вместо лист1, думаю вряд ли кто то будет использовать китайский перевод, кроме того номер листа изменится если его переместить, лучше уж тогда к свойству Name обращаться.
У меня и есть специфический случай, нужно обратиться именно к (Name), если вдруг кому то захочется переименовать лист макрос работать не будет
Добавлено через 12 минут
тут либо принудительно проверять соответствует ли свойство Name заданному образцу если не обнаружен лист, то проверить значение ячеек всех листов, либо запретить каким то образом изменение свойства Name.
Обращение к полю используя вместо его имени переменную с его именем
Здравствуйте! Подскажите пожалуйста, как обратиться к полю (не столбцу) используя вместо его имени.
Обращение к переменной по его имени
Всем привет. Не смог найти, как можно обратиться к массиву, если его имя лежит в другой.
Обращение к объекту не зная его имени
Есть у нас объект tefal в объекте machines, и у него есть метод run(); Зная название объекта.
Обращение к листу с графиками
Цель следующая. Есть документ с листами: Лист с графиком, лист с данными, лист с графиком, лист с.
Обращение к листу через InputBox
Подскажите пожалуйста как из тела макроса обратиться к определенному листу введенному в InputBox.
Обращение к листу через переменную
День добрый, форумчане! Столкнулся вот с какой проблемой. При задании переменной через.
Перед созданием своей первой программы в Excel необходимо разобраться в объектную модели приложения. Имея четкое понимания об объектной модели Excel вы сможете управлять практически всеми объектами, создавать/изменять листы, графики, отдельные файлы и многое другое. Возможно на первый взгляд она покажется довольно сложной, но не пугайтесь на конкретных примерах у вас сложится целостная картина.
Структура объектов
Проще всего рассматривать объектную модель как некое дерево или иерархическую структуру, так как каждый объект имеет свое ответвление. Кусочек этой структуры вы можете увидеть на рисунке далее.
Самый главный объект, вершина нашей структуры, это собственно само приложение Excel - объект Application. Какие объекты входят в Excel? правильно книги (файлы Excel), поэтому в объекте Application содержится коллекция из книг - объект Workbooks. Из чего состоят книги - из листов, диаграмм, соответственно внутри объекта Workbooks мы видим объект Worksheets и Charts. Идем дальше, лист в свою очередь состоит из строк, столбцов, ячеек это объекты Rows, Columns, Range.
Это, конечно, как вы понимаете только часть объектной модели Excel, мы перечислили только одни их самых основных объектов. Полное дерево объектов исчисляется сотнями объектов. Возможно она сейчас кажется сложной, не переживайте со временем вы начнете быстро в ней ориентироваться. Главное сейчас - это понять, что есть некие объекты, которые могут состоять из других объектов.
Объекты и коллекции объектов
Многие объекты, которые мы перечислили выше являются коллекциями. Коллекция - это группа однотипных объектов (или классов). Например Workbook s - это коллекция всех открытых в данный момент книг (объектов Workbook). А коллекция Worksheet s состоит из листов книги (объектов Worksheet), и так далее. Как правило все коллекции заканчиваются буквой S.
Давайте посмотрим, как обращаться к конкретным элементам коллекции в коде VBA.
Мы можем указать порядковый номер (начиная с единицы) элемента в коллекции. Worksheets(3) - в этом случае мы обращаемся к 3-му листу книги.
Мы также можем указать название листа в кавычках Worksheets("Название листа").
Аналогичным образом можно обратиться к конкретной книге Workbook("Названием книги").
Чтобы обратиться к объекту Excel (к листу, или ячейке) необходимо указать ссылку на этот объект, перечислив полный путь к нему в соответствии с объектной моделью.
Например, для ячейки A1 полная ссылка будет выглядеть следующим образом:
На самом деле полный путь писать не обязательно. Application - можно практически всегда не указывать. Workbooks("Название книги") - можно не указывать, если необходимо обратиться к ячейке из активной книги, а Worksheets("Название листа") можно не писать в случае если код должен выполнять действия на активном листе. Т.е. в можно упростить до:
Но всегда имейте ввиду, что это будет ячейка на активном листе.
Свойства объектов
Все объекты имеют свойства и методы. Например, объект Range (диапазон ячеек) имеет свойство Value, в котором содержится значение.
Вы можете получать значения из свойств объекта, просто указав путь к нужному свойству. Следующий код выведет значение ячейки A1 на активном листе.
А следующий код присвоит свойству Value новое значение 12345.
Протестируйте эти функции в своем файле.
Кстати, есть свойства объектов, которые доступны только для чтения. Т.е. вы сможете только получить значения этих свойств, но не сможете присвоить им другие значения. Например, свойство Range("A1").Address которое содержит адрес ячейки. При попытке записать новое значение в такое свойство будет возникать ошибка.
Или же свойство Worksheets.Count - вы можете только получить количество листов, но не можете изменить количество листов, задав новое свойство Count. Для этого существуют методы объектов.
Методы объектов
В отличие от свойств, которые просто сохраняют или передают значение, методы выполняют какие-либо действия с объектом. Например метод Worksheets.Add создает новый пустой лист в книге. Еще один пример это метод Clear, который позволяет очистить содержимое ячеек. Следующий код очистит данные и форматы из диапазона ячеек A1:C10.
Есть аналогичная функция, которая не удаляет при этом форматирование ячеек, а только очищает их от значений
Методы могут иметь дополнительные аргументы. Например, метод AddComment имеет обязательный аргумент Text, в котором необходимо передать текст комментария к ячейке.
Также можно использовать следующий код для вызова методов. Возможно это более привычная запись, когда по аналогией с функцией Excel мы в скобках передаем аргументы этой функции.
Приемы и лайфхаки
Автоматическое заполнение кода по начальным символам
Часто, особенно на первых порах, вы не запомните точное название всех объектов, но будете помнить их первые символы. В редакторе кода вы можете использовать отобразить список названий и автоматически записать объект по первым символам. Для этого используйте сочетание клавиш Ctrl+ J и увидите список из предложенных вариантов.
Просмотр свойств и методов у объекта
Когда вы будете писать свои программы, то редактор кода VBE будет подсказывать список свойств и методов у объекта. Чтобы отобразить этот список достаточно ввести объект, например, Worksheets и поставить точку, отобразится список свойств и методов. Выберите нужный с помощью мышки или же используйте стрелки. Вы также можете начать писать название свойства и редактор кода подберет подходящее. Когда выбор сделан - нажимайте клавишу Tab.
Явно указывайте тип переменных
Предыдущий лайфхак не будет работать для созданных локальных переменных до тех пор, пока вы явно не укажите их тип. Например, если вы захотели создать переменную MySheet в которой хотите хранить текущий лист. То список свойств и методов у такой переменной вы не увидите. Но стоит добавить строчку кода и явно указать тип переменной:
И подсказки снова заработают.
Быстрый просмотр справки
Все свойства и методы знать невозможно, так как их тысячи. Первое время вы будете активно пользоваться справкой. Советую использовать официальную справку на сайте Microsoft.
Чтобы быстро посмотреть информацию по интересующему вас объекту, свойству или методу просто поставьте курсор на интересующий элемент кода и нажмите клавишу F1. В окне браузера откроется официальная справка по этому элементу.
Поиск по объектной модели
Также вначале пути у вас постоянно будут возникать вопрос "А как называется свойство, которое отвечает за. ". Иногда мы и не заем есть ли такое свойство/метод вообще. Хороший вариант изучения - просмотр объектной модели. Переходите в редактор кода VBA и нажимайте клавишу F2. Откроется окно с поиском по объектной модели. Тут вы найдете все имеющиеся свойства, методы, события и прочие элементы в модели. Просматривайте их, как правило в названии элемента заложена его суть и вы найдете то что искали. А в процессе поиска будете запоминать и другие элементы, которые будут попадаться на глаза.
Давайте разберем на примере. Допустим нам надо защитить лист, но мы не знаем как точно зазывается свойство или метод.
- Открываем объектную модель клавишей F2.
- Так как мы хотим защитить лист, то логично предположить, что это метод в объекте Worksheet. Введем его в строку поиска и нажмем бинокль.
- В результатах поиска выбираем наш объект Worksheet и просматриваем его элементы.
- Находим метод Protect (Защита).
- Мы также можем просмотреть описание всех аргументов этого метода
- Конечно если что-то не понятно, то жмем клавишу F1 и открываем справку по этому методу с подробным описанием каждого аргумента.
На этом данную статью завершим, а в следующих мы более подробно поговорим про самый распространенный объект Excel - объект Range.
Поговорим про то, как в 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 .
Часто при разработке различных проектов в VBA требуется где-то хранить различные служебные данные: списки, константы(вроде путей к файлам, email-ов и т.п.). Обычно это делается на отдельных листах, которые потом делают очень скрытыми. А на листах делает еще и потому, что списки удобнее хранить именно на них, да и порой необходимо дать конечному пользователю возможность самому изменять некие данные. Например, программа должна собирать информацию с различных файлов. Но файлы могут располагаться в различных папках - зависит от того, на каком компьютере программа запускается. Или лист содержит списки артикулов, на основании которых программа отбирает некоторые файлы. И эти артикулы могут изменяться со временем. В таких случаях лист не скрывают, а оставляют на виду, чтобы пользователь сам мог прописать путь к нужным папкам и файлам, вписать нужные артикулы и т.п.
В коде к этому листу можно обращаться по имени. Предположим, лист называется "SETS". Тогда код получения данных из ячейки A2 будет выглядеть так:
Dim v v = ThisWorkbook.Sheets("SETS").Range("A2").Value
Подробнее про обращение к диапазонам можно узнать из статьи: Как обратиться к диапазону из VBA
Все бы ничего, но есть одна ложка дегтя: пользователь может случайно этот лист переименовать. Тогда обращение к листу по заранее заданному имени вызовет ошибку( 9 - Subscript out of range ). Можно пойти другим путем - обратиться к листу по его порядковому номеру. Например, наш лист "SETS" расположен вторым по порядку в книге:
Тогда код получения данных из ячейки A2 будет таким:
Dim v v = ThisWorkbook.Sheets(2).Range("A2").Value
Но и здесь проблема: пользователь может удалить первый лист или просто переместить наш лист "SETS" в другое место. Что опять либо вызовет ошибку 9 - Subscript out of range (если количество листов в книге меньше указанного числа. В нашем примере если в книге будет только один лист) либо запишет нам в переменную v значение из совершенно другого листа и как следствие - это будет не то значение, которое нам нужно.
Как же быть? Есть такое понятие - кодовое имя листа. Т.е. у листа есть два имени: одно отображаемое - это то, которое мы видим на ярлыке(на рисунке выше это Лист1, SETS, Лист2). И есть кодовое имя, которое не видно напрямую. Чтобы увидеть кодовое имя листа необходимо перейти в редактор Visual Basic for Application( Alt + F11 ), отобразить окно проводника проекта -View -Project explorer(или Ctrl + R ), раскрыть папку Microsoft Excel Objects. Там мы увидим все объекты проекта VBA и их имена. Само название листов в этом окне состоит из двух частей: сначала идет кодовое имя листа, а в скобках - имя, отображаемое на ярлыке:
Где искать разобрались. Что нам это дает? Т.к. это имя просто так не найти - пользователь не сможет уже изменить его имя. А обратиться к такому листу из кода VBA проще простого - как и к любому из компонентов проекта - просто по его кодовому имени(для нашего листа "SETS" это Лист2)
Dim v v = Лист2.Range("A2").Value
Теперь ни переименование ярлыка, ни перемещение не вызовет ошибку. Ошибку теперь может вызвать удаление этого листа. Но это другая история.
Удаление листов можно запретить, защитив структуру книги(Рецензирование (Review) -Защитить книгу (Protect workbook) )
И еще важно помнить: обращение к листу по его кодовому имени приведенным выше способом возможно исключительно внутри той книги, в которой этот лист расположен. Обратиться к нему по привычке с указанием книги уже не получится: ActiveWorkbook.Лист2.Range("A2").Value . Т.е. по факту нельзя обратиться к листу по кодовому имени из другой книги. Хотя в большинстве случаев это и не надо. Если уж реальная необходимость возникнет - ниже приведена функция поиска листа по его кодовому имени, которую легко можно переделать под получение отображаемого имени листа, найдя его по кодовому имени.
Чтобы самостоятельно изменить имя кодового листа, необходимо выделить этот лист в проводнике проекта -отобразить окно свойств, если оно еще не отображено(View -Properties Window или F4 ) и просто изменить свойство Name:
Для чего переименовывать? Во-первых, не на всех ПК кириллица воспринимается нормально. Поэтому лучше в кодах использовать латиницу. Во-вторых - код будет более читаемый и наглядный, если обращение будет к объектам вроде wsSets и wsData , чем к объектам вида Лист1 , Лист2 и т.п.
Но при переименовании необходимо помнить, что кодовое имя листа не должно содержать пробелов и иных знаков препинания(кроме нижнего подчеркивания), не должно начинаться с цифр. В общем такие же требования, как и к именам макросов.
Порой необходимо проверить - есть ли лист с указанным кодовым именем. Это к слову о том, как избежать ситуации обращения к несуществующему листу, если обращение идет по кодовому имени(и при этом в проектах Вы почему-то не используете Option Explicit). Можно применить такую простенькую функцию:
'--------------------------------------------------------------------------------------- ' Procedure : RenameSheetCodeName ' Purpose : Ищет в указанной книге лист с указанным кодовым именем ' wb - Книга, лист в которой необходимо найти ' sName - Кодовое имя листа, которое надо проверить на наличие в книге '--------------------------------------------------------------------------------------- Function FindSheetByCodeName(wb As Workbook, sName As String) Dim ws As Worksheet 'цикл по всем листам For Each ws In wb.Worksheets If StrComp(ws.CodeName, sName, 1) = 0 Then 'сравнение имени без учета регистра 'назначаем функции возвращаемое значение FindSheetByCodeName = True Exit Function End If Next End Function
Проверить при этом наличие листа с кодовым именем wsSets можно так:
И вдогонку код, который поможет быстро переименовать кодовое имя листа по его отображаемому имени. Т.е. все что надо - знать имя листа, отображаемое на ярлыке. И указать новое кодовое имя:
Вызвать переименование кодового имени листа можно будет так:
Для кодового имени листа программно необходимо, чтобы было проставлено доверие к объектной модели проекта VBA и изменяемый проект не должен быть защищен. Подробнее читайте в статье: Что необходимо для внесения изменений в проект VBA(макросы) программно
Без этого будет невозможно программное вмешательство в проект VBA.
Читайте также: