Vba excel чем отличается sheets от worksheets
In this article you will be learning about sheets vs worksheets in excel VBA and how to use of these functions when manipulating spreadsheets.
Difference between Worksheets and Sheets in VBA
In VBA, you have two collections that can be a bit confusing at times. In a workbook, you can have worksheets and as well as chart sheets.
- The ‘Worksheets’ collection would refer to the collection of all the worksheet objects in a workbook.
- The ‘Sheets’ collection would refer to all the worksheets as well as chart sheets in the workbook.
To run the VBA code in Excel, perform the following first
- Under the developer tab, click visual basic
- Click the insert option and choose a module
- Enter your codes and click run.
Sheets
Now we know, ‘sheets’ is the collection of worksheets and chart sheets.
Looping through each Object in the Sheets collection
To loop through every sheet,
Code:
Looping through every Sheet in the Sheets collection
We can also count the sheets, then loop using a For loop.
Code
This method of looping by counting the objects will work equally well with Charts and Worksheets.
Worksheets
When you have to work with worksheets only, use the ‘Worksheets’ collection, and when you have to refer to all sheets, then use the ‘Sheets’ collection.
Let’s see worksheets in detail.
Referencing a Worksheet in VBA
You can refer a worksheet in the following methods.
Using the Worksheet Name
This is the easiest way to refer to a worksheet.
When you are working with a workbook with three worksheets namely Sheet 1, Sheet 2, Sheet 3 (which is common in any excel file) and you want to activate Sheet 3.
Use the following code:
Code:
You can also use the sheets collection method to activate the sheets, as we are using the name of the sheet as the key point.
Code:
Using the Index Number
The difficult part of using the name of the sheet to refer them is you need to know the exact name of the sheet or the program doesn’t work.
In this case, you can use the index number of the worksheets. The indexing starts from 1 in the collection of sheets.
Use this code to activate Sheet3:
Code
Important: A chart sheet is not a part of the worksheets collection.
This is because when we use the index numbers in the Worksheet collection, it will only refer to the worksheets in the workbook.
Note: Indexing goes from left to right. So if you shift Sheet3 to the left of Sheet2, then Worksheets (2) would refer to Sheet3.
Using the Worksheet Code Name
You can use the code name of the worksheet to refer to a worksheet. This code name can be assigned in the VB Editor and it won’t change when you change the name of the worksheet.
To give your worksheet a code name, follow these steps:
- Under the Developer tab, click the Visual Basic option.
- This will open the VB Editor.
- Now, Click the View option in the menu and click on Project Window.
- Click on the sheet name in the project explorer that you want to rename.
- In the Properties pane, change the name in the field in front of (Name).
Note: Don’t include spaces in the name.
This would change the name of your Worksheet in the VBA, i.e., the code name. Therefore, when you change the worksheet name it doesn’t affect the code in your VBA.
Now, you can use either the Worksheets collection to refer to the worksheet or use the codename.
The following code uses both worksheet collection method and name of the sheet method.
Code
(I have code named my sheet as SH3)
Referring to a Worksheet in a Different Workbook
If you need to access a worksheet in a different workbook,
Code
Adding a Worksheet
When you need to add a worksheet
Code
Deleting a Worksheet
When you want to delete a worksheet:
Code
Click ok on the warning prompt. The worksheet gets deleted.
To avoid the warning prompt, use the below code:
Code
Note: You can’t undo this delete option. So be sure.
Delete a specific worksheet.
If you want to delete a specific sheet,
Code
You can also use the code name of the sheet to delete it.
Renaming the Worksheets
When you want to rename the sheets using VBA code:
Code
Adding Multiple Sheets
When you need to add multiple sheets
Code
Assigning Worksheet Object to a Variable
You can assign a worksheet to an object variable, and then use the variable instead of the worksheet references.
Code
Hide Worksheets Using VBA
You can hide and unhide worksheets using VBA. Normally when a worksheet is hidden, you can easily unhide the worksheet by right-clicking on any sheet tab.
But if you don’t want to unhide the worksheet in this method, you can do this using VBA.
The code below would hide all the worksheets in the workbook (except the active sheet), such that you cannot unhide it by right-clicking on the sheet name.
Code
If you want to hide sheets that can be unhidden easily, use the below code.
Code
To unhide the sheets
Code:
Hide Sheets Based on the Text in it
You can hide sheets based on the text in it. You can do this using the VBA INSTR function.
The below code would hide all the sheets except the ones with the text 2020 in it.
Code:
Sorting the Worksheets in an Alphabetical Order
Using VBA, you can quickly sort the worksheets based on their names.
Use the below code to quickly sort sheets in an ascending order.
Code
Creating a Table of Contents of All Worksheets with Hyperlinks
To create a table of contents of all worksheets:
Code
The above code inserts a new worksheet and names it Index.
It then loops through all the worksheets and creates a hyperlink for all the worksheets in the Index sheet.
Поговорим про то, как в 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 :)
0
0
0
0
А что же я тогда читал на этот счёт.
0
Думал, что можно только так:
ThisWorkbook.VBProject.VBComponents("Sheet2").Name= "NewCodeName"
Спасибо, что сказали ))))
0
0 Спам
0
К "Вариант 5" - я обычно переименовываю CodeName часто используемой страницы на манер имени переменной. Например CodeName "Лист1" -> CodeName "wsData" и сразу работаю с ним.
0
Всё правильно. Я, когда статью писал, тоже пытался переименовывать и с разбега почему-то не вышло, хотя действовал казалось бы по инструкциям. Где-то что-то упустил, ясное дело, но так и не разобрался в итоге
Каждая рабочая книга состоит из листов. Совокупность всех листов рабочей книги задается объектом Sheets . Этот объект , задающий коллекцию, представляет все листы рабочей книги независимо от их типа. При программной работе часто полезно иметь дело с подколлекциями коллекции Sheets , содержащими листы только одного определенного типа. Основных типов листов в рабочей книге два - WorkSheet и Chart , соответственно имеются и две коллекции для этих типов листов. К рассмотрению коллекции WorkSheets мы сейчас и переходим.
Коллекция WorkSheets
Эта коллекция является частью коллекции Sheets , - ее элементами являются объекты класса WorkSheet , представляющие рабочие листы - листы электронных таблиц. По умолчанию при создании каждой новой рабочей книги в ее состав включаются три таких листа. С объектной точки зрения это означает, что при создании новой книги автоматически создается коллекция WorkSheets , содержащая три элемента. Как и всякая коллекция в Excel и Office 2000 данная коллекция содержит типичный набор свойств: Application, Count, Creator, Parent, Item . Кроме этих свойств имеется менее типичное свойство для коллекций свойство Visible , которое позволяет сделать видимыми или невидимыми рабочие листы книги. У коллекции WorkSheets есть еще два свойства VpageBreaks и HpageBreaks , возвращающие одноименные коллекции, элементы которых задают вертикальное и горизонтальное деление рабочего листа на страницы, которые могут быть распечатаны. Дело в том, что рабочий лист Excel имеет большие размеры по ширине и длине, так что его полностью нельзя увидеть ни на экране дисплея, ни при выводе на печать. Поэтому при печати часто приходится делить рабочий лист, вставляя разрывы по горизонтали и вертикали. Коллекции VpageBreaks и HpageBreaks содержат объекты, задающие эти разрывы.
Методов у коллекции WorkSheets немного, и практически все они типичны для коллекций:
- Function Add([Before], [After], [Count], [Type]) As Object - позволяет добавить новый рабочий лист в книгу, возвращая соответствующий объект в качестве результата. Добавленный лист становится активным. Параметры Before и After позволяют указать, куда поместить добавленный лист, - перед или после листа, который до выполнения операции был активным. Параметр Count позволяет одновременно добавить несколько листов, задавая число этих листов. Параметр Type обычно не указывается, ранее он позволял добавлять листы макросов в версии Excel4.
- Sub Copy([Before], [After]) - метод Copy вызывается объектом WorkSheets , чаще всего, для создания копии рабочей книги. В этом случае параметры метода не задаются. При копировании отдельной страницы параметры указывают, куда поместить ее копию. Понятно, что только один из этих параметров может быть указан в момент вызова метода.
- Sub Delete() - удаляет коллекцию рабочих листов.
- Sub FillAcrossSheets(Range As Range, [Type As XlFillWith = xlFillWithAll]) - область, заданная параметром Range , копируется в соответствующее место всех рабочих листов. Тип копирования задается вторым параметром, можно, например, копировать формулы, по умолчанию копируется все содержимое области, заданной параметром Range. Копируемый объект, естественно, должен быть частью одного из рабочих листов коллекции. Вот простой пример, демонстрирующий применение этого метода:
Вот еще один небольшой пример на применение методов:
Как Вы понимаете, большинство методов - Copy, Move, Select и другие - коллекция WorkSheets "унаследовала" от своих потомков. Чаще всего эти методы применяются к отдельным листам, а не ко всей коллекции в целом. Нам придется еще с ними столкнуться, при рассмотрении методов объекта WorkSheet . Прежде, чем перейти к изучению этого объекта, скажу только, что коллекция WorkSheets , также как и все другие коллекции, событий не имеет.
Объект WorkSheet
Объект Worksheet - рабочий лист является элементом коллекции Worksheets . Он представляет основной тип страниц рабочей книги. Именно на этих страницах разворачиваются основные действия в ячейках электронной таблицы. Основу рабочего листа составляет прямоугольная таблица ячеек. Главная особенность электронной таблицы состоит в том, что в ее ячейки можно вводить не только данные, но и формулы. Формулы Excel, также как и обычные математические формулы, также как и выражения в языках программирования, оперируют при вычислении значений константами, переменными и функциями. В электронной таблице роль переменных играют ячейки таблицы. Существует некоторый алгоритм, определяющий порядок, согласно которому вычисляются формулы в ячейках электронной таблицы. При изменении данных таблиц, инициированных пользователем, внешними ссылками или выполнением макросов программного проекта, пересчитываются и формулы. Это делает таблицу живой, - изменение значения одной ячейки приводит, возможно, к пересчету всей таблицы.
Также как для документов Word работа с текстом является главным занятием пользователей, работающих с документом, так и работа с ячейками - ввод данных и формул в ячейки, и, тем самым, инициирование вычислений лежит в основе работы с рабочим листом. С объектной точки зрения отдельные ячейки электронной таблицы и области, содержащие совокупности этих ячеек, то, что называется объектами Range , являются основными объектами рабочего листа. Но, естественно, рабочий лист состоит не только из объектов Range , есть и другие компоненты. Объектная модель рабочего листа Worksheet достаточно сложна, что отражает, впрочем, сложность изучаемого объекта. Давайте познакомимся с ней поближе.
Свойства объекта Worksheet
Среди свойств, как всегда, наибольший интерес представляют свойства-участники, возвращающие некоторый отдельный объект или коллекцию в качестве результата. Эти свойства определяют структуру объекта Worksheet , задавая непосредственно вложенные в него объекты.
Свойства - участники
Дадим краткую характеристику свойствам - участникам, входящим в рабочий лист:
- Range, Cells, Rows, Columns, UsedRange, CircularReference - начнем с группы свойств, возвращающих объект Range . Я уже говорил, что объект Range - это основной объект электронной таблицы. Он позволяет задать, как отдельную ячейку таблицу, диапазоны ячеек, представляющие прямоугольную область таблицы, так и области более сложной конфигурации. Именно объект Range со своими свойствами и методами позволяет осуществлять непосредственную работу, как с отдельной ячейкой, так и с областями ячеек. Об этом объекте я еще много буду говорить, но уже сейчас хочу заметить, что большинство свойств рабочего листа Worksheet , о которых пойдет речь, характерны и для объекта Range , задающего часть рабочего листа. Объект Range возвращается в качестве результата при вызове следующих свойств:
- Range(Cell1, [Cell2]) As Range - возвращает объект Range , определяемый параметрами свойства. Синтаксис параметров таков, что он позволяет определить достаточно изощренный объект. Я расскажу об этом подробнее чуть позже, когда мы займемся подробным рассмотрением объекта Range .
- Cells As Range - возвращает коллекцию ячеек электронной таблицы. Вызванное объектом WorkSheet это свойство возвращает всю таблицу ячеек рабочего листа, которая, конечно, представляет собой объект Range . Поскольку Cells одновременно является объектом Range и коллекцией ячеек, то можно использовать индексы, чтобы добраться до отдельного элемента коллекции - ячейки таблицы. Важным свойством Cells обладают и объекты, стоящие на более низких ступенях иерархии, в частности, им обладает и сам объект Range , что позволяет получить коллекцию ячеек для любой заданной области таблицы.
- Rows As Range и Columns As Range - соответственно возвращают коллекции строк и столбцов таблицы. По индексу можно добраться до отдельной строки или столбцу таблицы. Одновременно эти коллекции являются объектами Range , поскольку задают некоторую область рабочего листа.
- UsedRange As Range - возвращает используемую область рабочего листа. Как правило, лишь небольшая часть рабочего листа занята данными, формулами, рисунками, диаграммами и графиками. Свойство UsedRange позволяет получить минимальную прямоугольную область, содержащую используемую область рабочего листа.
- CircularReference As Range - возвращает объект Range , содержащий первую циклическую ссылку, если таковые имеются на рабочем листе. В противном случае возвращается значение Nothing . О циклических ссылках поговорим подробнее чуть позже.
Одну и ту же область таблицы - один и тот же объект Range - можно получить разными способами. Приведем пример, показывающий два способа получения ячейки "А1":
Оба оператора здесь эквивалентны, но только в одном случае используется свойство Range , в другом - Cells . В следующем примере работа идет над отдельным столбцом и строкой, но, фактически, и здесь действует тот же объект Range :
Подводя итоги, заметим, что рабочий лист помимо того, что он представляет электронную таблицу ячеек, может содержать и другие элементы: диаграммы, рисунки, OLE -объекты. В нем могут быть также расположены сводные таблицы и таблицы, построенные на основе запросов к внешним источникам данных. Некоторые из ячеек рабочего листа снабжаются комментариями и имеют ссылки на внешние адреса. Ячейки и области данных могут иметь имена. Наконец, данные разрешается свернуть и отобразить структуру такого листа с нужной степенью подробности.
Изменения в объектной модели объекта WorkSheet
Изменения объектной модели не обошли стороной и рассматриваемый нами объект WorkSheet. Многие объекты, встроенные в объект WorkSheet, как, например, уже упоминавшийся объект QueryTable приобрели новые свойства и методы. Два новых свойства появились и у самого объекта Worksheet. Если новое терминальное свойство DisplayRightToLeft вряд ли представляет интерес для российских программистов, поскольку связано с правосторонними языками, то свойство-участник Scripts, возвращающее коллекцию объектов класса Script, представляет несомненный интерес. Каждый элемент этой коллекции задает блок script-кода, используемого в возможных сценариях при публикации рабочей книги в интернет.
I use the terms Sheet and Worksheet interchangeably when talking about Excel, I think most users do. Google also appears to think they are the same thing; If I search for “How to loop through Sheets with Excel VBA”, all the results returned on the first page refer to Worksheets.
Yet, Sheets and Worksheets from a VBA perspective are definitely not the same. Unfortunately, most of the VBA code I write doesn’t really consider the differences… bad habits, I know. Hopefully, by writing this post it will inspire me to correct my bad habits, and prevent you from creating bad habits.
The difference between Sheets and Worksheets
- Worksheet – the sheet with the gridlines and cells
- Chart – the sheet which contains a single chart
- DialogSheet – an Excel 5 dialog sheet. These are effectively defunct as they have been replaced by VBA UserForms
- Macro sheets – A sheet containing Excel 4 macros. These were replaced by VBA in 1995.
- International Macro sheet – A sheet containing an internationally compatible Excel 4 macro (also replaced in 1995).
Since DialogSheets, and both forms of Macro sheets were replaced in the 90’s, we can pretty much ignore them. That leaves just two types of sheets we are likely to encounter: Charts and Worksheets.
So, in summary, when we refer to Sheets in VBA, we are talking about Charts and Worksheets. And when we refer to Worksheets, we are excluding Charts.
What type of sheet is it?
Different sheet types have their own properties. For example, on a Worksheet you can select cells, but you can’t on a Chart sheet, because there are no cells. So, if we want to perform certain actions on the active sheet, we need to know which type of sheet we are on.
Running the code above will generate a message box with either “Worksheet” or “Chart” depending on the type of sheet.
Objects and collections
Understanding objects and collections will help us master the use of Charts, Worksheets and Sheets.
Worksheet – A Worksheet is an object which refers to a single worksheet. Each worksheet is a member of the Worksheets and Sheets collection objects.
Worksheets – Worksheets is a collection object which contains all the individual Worksheet objects in a workbook.
- Can be contained within a Chart Object for a chart contained on the face of the worksheet (outside the scope of this post).
- Can be a member of the Charts and Sheets collection objects.
Charts – Charts is a collection object which contains all the individual Chart sheet objects within a workbook. This should not be confused with the ChartObjects object, which is the collection of charts contained on the face of the worksheet.
Sheets – Sheets is a collection object which contains all the individual Worksheet and Chart sheet objects in a workbook.
Looping through Sheets, Worksheets and Charts
When using the For Each loop, we are looping through each object within a collection.
Example 1 – Looping through each Worksheet in the Worksheets collection
The VBA code below will loop through each Worksheet in the Worksheets collection.
Example 2 – Looping through each Chart in the Charts collection
The following code will loop through each Chart in the Charts collection.
Example 3 – Looping through each Object in the Sheets collection
To loop through every sheet, we cannot declare a Sheet object, because it doesn’t exist. The example below loops through every object (i.e. it will include Chart and Worksheet objects) contained within the Sheets collection.
Example 4 – Looping through every Sheet in the Sheets collection
As an alternative to Example 3, we could count the sheets, then loop using a For loop.
This method of looping by counting the objects will work equally well with Charts and Worksheets.
Get our FREE VBA eBook of the 30 most useful Excel VBA macros.Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.
Don’t forget:
If you’ve found this post useful, or if you have a better approach, then please leave a comment below.
Do you need help adapting this to your needs?
I’m guessing the examples in this post didn’t exactly meet your situation. We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it’s clear and concise. List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts:Are there any differences or different properties between them? Can they be used synonymously?
Excel Facts
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Ron Coderre
MrExcel MVP
The SHEETS object is a parent object for:
Worksheets
Chart SheetsIf a workbook has 3 worksheets and 1 chart sheet, in VBA:
Sheets.Count will include both types. 4
Worksheets.Count will include only worksheets. 3Jacques
Board Regular
Chekie
Board Regular
scottylad2
Well-known Member
Almost 1:30am here. Worksheets. for me to hide from my boss in the morning!
Sheets. for me, any minute soon, with a pillow and duvet of course lol
Ron Coderre
MrExcel MVP
pgc01
MrExcel MVP
Not really relevant, as you probably will never encounter it.
Sheets collection contains all types of sheets. Although today we only use 2 types of Sheets, Worksheets and Chart Sheets, there used to be 3 more types of Sheets, like Dialog Sheets or Macro Sheets. You may still have in your company old excel files that use them. In that case if you check the Sheets collection you'll seem them all there.
Sheets - collection of the Sheets of all types
Worksheets - collection of Sheets of type Worksheet
Charts - collection of Sheets of type Chart Sheetdumitrudan608
New Member
/!\ In VBA you should use Worksheets() instead of Sheets() when referencing a sheet from another sheet in a formula. This solved some issues i had, maybe you need to be specific about the sheet type this way.
Not really relevant, as you probably will never encounter it.
Sheets collection contains all types of sheets. Although today we only use 2 types of Sheets, Worksheets and Chart Sheets, there used to be 3 more types of Sheets, like Dialog Sheets or Macro Sheets. You may still have in your company old excel files that use them. In that case if you check the Sheets collection you'll seem them all there.
Sheets - collection of the Sheets of all types
Worksheets - collection of Sheets of type Worksheet
Charts - collection of Sheets of type Chart SheetЧитайте также: