VBA Выбрать лист, активировать лист и получить активный лист
В этой статье будет обсуждаться объект ActiveSheet в VBA. Также будет обсуждаться, как активировать, выбирать и переходить к рабочим листам (и многое другое). Прочтите наше полное руководство по рабочим листам VBA для получения дополнительной информации о работе с рабочими листами в VBA.
ActiveSheet
В VBA ActiveSheet относится к текущему активному рабочему листу. Одновременно может быть активен только один лист.
Активировать рабочий лист (настройка ActiveSheet)
Чтобы установить ActiveSheet, используйте Worksheet.Activate:
| 1 | Рабочие листы («Ввод»). Активировать |
Команда «Активировать лист» фактически «перейдет» к листу, изменив видимый лист.
В приведенном выше примере используется имя листа (вкладка). Вместо этого вы можете использовать кодовое имя VBA для рабочего листа:
| 1 | Sheet1.Activate |
Имя ActiveSheet
Чтобы получить имя ActiveSheet:
| 1 | msgbox ActiveSheet.name |
Выбранные листы против ActiveSheet
В любой момент времени ActiveSheet может быть только один лист. Однако можно выбрать сразу несколько листов.
Когда выбрано несколько листов, активным считается только самый верхний лист (ActiveSheet).
Выберите рабочий лист
Если вы хотите выбрать лист вместо его активации. Вместо этого используйте .Select.
Это выбирает рабочий лист на основе его имени вкладки листа
| 1 | Таблицы («Ввод»). Выбрать |
Это выбирает рабочий лист на основе его положения относительно других вкладок.
| 1 | Рабочие листы (1) .Выбрать |
| 1 | Лист 1.Выбрать |
Выбор листов по имени кода может предотвратить ошибки, вызванные изменением имени листа.
Выбрать текущий лист
Чтобы выбрать текущий рабочий лист, используйте объект ActiveSheet:
| 1 | ActiveSheet.Select |
Дополнительные примеры активации / выбора листов
Это назначит ActiveSheet переменной объекта рабочего листа.
| 123 | Dim ws как рабочий листУстановить ws = ActiveSheet |
Это изменит имя ActiveSheet.
| 1 | ActiveSheet.Name = «NewName» |
С ActiveSheet
Использование оператора With позволяет оптимизировать код при работе с объектами (такими как листы или ActiveSheet).
| 12345 | С ActiveSheet.Name = «StartFresh».Cells.Clear.Range («A1»). Value = .NameКонец с |
Обратите внимание, что вам не нужно повторять ActiveSheet перед каждой строкой кода. Это может значительно сэкономить время при работе с длинным списком команд
Перебирать выбранные листы
Следующий макрос будет проходить по всем выбранным листам, отображая их имена.
| 12345678 | Sub GetSelectedSheetsName ()Dim ws как рабочий листДля каждого ws в ActiveWindow.SelectedSheetsMsgBox ws.NameСледующий wsКонец подписки |
Перейти к следующему листу
Этот код перейдет на следующий лист. Если ActiveSheet является последним листом, он перейдет к первому листу в рабочей книге.
Основные понятия MS Excel. Документ в программе Excel называется рабочей книгой (Книга1, Книга 2, и т.д.)
Документ в программе Excel называется рабочей книгой (Книга1, Книга 2, и т.д.). Книга состоит из рабочих листов.
Рабочая книга Excel – совокупность Рабочих листов, сохраняемых на диске в одном файле. Файл с произвольным именем и расширением *.xls.В каждом файле может размещаться 1 книга, а в книге – от 1 до 255 рабочих листов. По умолчанию в книге содержится 3 рабочих листа.
Рабочие листы можно вставлять, удалять, переставлять. Щелкая по ярлычку листа, можно переходить от одного листа к другому в пределах книги.
Электронная таблица Excel состоит из 65536 строк и 256 столбцов. Строки нумеруются числами (от 1 до 65536), а столбцы обозначаются буквами латинского алфавита A, B, C, …,Z. После столбца Z следуют столбцы AA, AB, AC, BA, BB,…
На пересечении столбца и строки расположена – ячейка,область электронной таблицы. Формат и размеры ячеек – ширину столбцов и высоту строк – можно изменить с помощью команд меню, или с помощью мыши, или клавиш.
Текущая (активная) ячейка –ячейка, в которой в данный момент находится курсор. Она выделена на экране жирной черной рамкой. Каждая ячейка таблицы имеет свой адрес,который используется для указания на ячейку — при ссылке на нее. Например, A1. Адрес и содержимое текущей ячейки выводятся в строке формул.
Ссылка –способ указания адреса ячейки. Адреса ячеек могут быть относительными или абсолютными. Ячейки могут иметь собственные имена. Ссылки на ячейки (адреса ячеек) используются в формулах, в функциях в качестве аргументов.
Типичными установками, принятыми по умолчанию на уровне всех ячеек таблицы, являются:
· Ширина ячейки – около 8 разрядов, высота – около 12 пунктов;
· Левое выравнивание для символьных данных;
· Основной формат для цифровых данных с выравниванием вправо.
Блок (диапазон) ячеек – это группа последовательных ячеек. Блок используемых ячеек может быть указан или выделен двумя путями:
· Непосредственным набором с клавиатуры начального и конечного адресов ячеек(A1:C4), формирующих диапазон;
· Выделением блока с помощью мыши или клавиш управления курсором;
Обозначение ячейки, составленное из номера столбца и номера строки, называется относительным адресом.
При копировании формул в Excel действует правило относительной ориентации ячеек, суть состоит в том, что при копировании формулы табличный процессор автоматически смещает адрес в соответствии с относительным расположением исходной ячейки и создаваемой копии.
Абсолютная ссылка создается из относительной ссылки путем вставки знака доллара ($) перед заголовком столбца или номером строки.
Например: $A$1, $B$1 – это абсолютные адреса ячеек A1 и B1, следовательно, при их копировании не будут меняться ни номер строки, ни номер столбца. Иногда используют смешанный адрес, в котором постоянным может быть один компонент.
$B7 – при копировании формул не будет изменяться номер столбца;
B$7 – не будет изменяться номер строки.
Для обозначения адреса ячейки с указанием листа используется имя листа и восклицательный знак. Например: Лист 2!B5, Итоги!B5
Для обозначения адреса ячейки с указанием книги используются квадратные скобки. Например: Лист2!А1
При назначении имени ячейки или диапазону следует соблюдать правила:
· Имя должно начинаться с буквы русского или латинского алфавита, символа подчеркивания или обратной косой черты (\). В имени могут быть точки и вопросительные знаки. Цифры могут быть в имени, только не в начале.
· В имени нельзя использовать пробелы, вместо них можно ставить подчеркивание.
· Длина имени ячейки не должна превышать 255 символов.
Для имени листа существуют следующие ограничения:
· Длина имени листа не больше 31 символа;
· Имя листа не должно содержать квадратных скобок;
· Имя не должно содержать следующие символы — двоеточие, косую черту (/), обратную косую черту (\), знак вопроса, звездочку(*).
Задание 1.
1. Создайте таблицу, представленную на рисунке 3.
2. Сохраните документ под именем Прайс.xls.
Рисунок 3 – Вид таблицы для задания 1
3. В ячейку А1 введите наименование организации.
4. В ячейку В3 введите курс $ 151,8.
5. Начиная с ячейки А5 введите 15 номеров, используя автозаполнение.
6. Напишите, используя абсолютную адресацию, в ячейку D5 формулу для вычисления цены товара в тенге (=C5*$B$3)(цена товара в $ в диапазоне от 2 до 2000), затем заполните ячейки до D19.
7. Напишите в ячейке G5 формулу для стоимости.
8. Используя автосуммирование, вычислите «Итого» в ячейке G20.
9. Нанесите сетку таблицы там, где это необходимо.
10. Измените курс доллара на 152,4. Посмотрите, что изменилось.
11. Найдите среднее, максимальное и минимальное значения – с использованием функций СРЗНАЧ, МАКС и МИН, результаты поместите в ячейки G21, G22, G23соответственно.
Method #2: Use a Formula Combining MID, CELL, and FIND Functions to Get Sheet Name in Excel
Another easy way to get sheet names in Excel is by using a combination of MID, CELL, and FIND functions.
- The MID function returns the text string characters from inside a text string, given a starting position and length.
- The CELL function returns information about a cell’s formatting, location, or contents.
- The FIND function is case-sensitive and returns the starting position of one text string within another.
We can use a formula that combines the three functions to get a worksheet name in Excel.
We have the following workbook, “Employee Performance Appraisal Reports,” which has five worksheets with different names:
We want to return the name of the current worksheet, “Qtr 1 2022”, in a cell in the workbook using a formula that combines the MID, CELL, and FIND functions.
We use the following steps:
- Select any cell in the current worksheet; in this example, we select cell A1 and enter the formula below:
Note: This formula will only work if the workbook has been saved at least once. Otherwise, the formula returns the #VALUE! error because it cannot locate the workbook.
- Press Enter.
The current worksheet’s name is returned in cell A1 as seen below:
If we copy the formula to any cell in the other worksheets, the worksheet’s respective name is displayed in the selected cell.
Let’s, for example, copy the formula to cell G1 of the “Qtr 1 2023” worksheet:
The formula returns the name of the worksheet in cell G1.
Explanation of the formula
=MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,31)
CELL(“filename”,A1) – The first CELL function’s info_type argument is set to “filename” and reference argument to cell A1 to return the full path to the active worksheet as shown below:
The full path to the worksheet is passed to the MID function as the text argument.
- FIND(“]”,CELL(“filename”,A1))+1 – The FIND function returns the position of the closing square bracket in the full path. The position is increased by 1 to calculate the starting position of the worksheet name. The computed result is passed to the MID function as the start_num argument.
- Finally, the value 31, the maximum number of characters allowed in a worksheet name, is passed to the MID function as the num_chars argument. The value ensures that the MID function extracts the full worksheet name to the right of the closing square bracket. The final result in our example is “Qtr 1 2022,” the name of the current worksheet.
Методы объекта Workbook и семейства Workbooks
|
Методы |
Выполняемые действия |
|
Activate |
Активизирует рабочую книгу так, что ее первый рабочий лист становится активным.
Workbook. Activate |
|
Add |
Создает новый объект для семейства Workbooks.
Add (Template) Template — задает шаблон, на основе которого создается новая рабочая книга. Допустимые значения: xlWBATChart, xlWBATExce14IntlMacroSheet, xlWBATExce14MacroSheet или xlWBATWorksheet. |
|
Protect |
Защищает рабочую книгу от внесения в нее изменений.
Protect (Password, Structure, Windows)
В следующем примере устанавливается защита для активной рабочей книги: ActiveWorkbook. Protect Password:= «ВинниПух» |
|
Unprotect |
Снятие защиты с рабочей книги.
Unprotect (Password) Password — строка, используемая в качестве пароля для защиты листа ActiveWorkbook. Unprotect Password: = «ВинниПух « |
|
Close |
Закрытие рабочей книги |
|
Open |
Открытие существующей рабочей книги |
|
OpenText |
Открытие текстового файла, содержащего таблицу данных |
|
Save |
Сохранение рабочей книги |
|
SaveAs |
Сохранение рабочей книги в другом файле.
SaveAs (Filename) Filename — строка, указывающая имя файла, в котором будет сохранена рабочая книга ActiveBook. SaveAs Filename: = „НоваяВерсия“ |
|
SaveAsCopy |
Сохранить рабочую книгу в другом файле, оставляя рабочую книгу в памяти с прежним именем.
SaveAs (Filename, FileFormat) Filename — строка, указывающая имя файла, в котором будет сохранена рабочая книга ActiveBook. SaveAsCopy Filename: = „ЗапаснаяВерсия“ |
|
PrintPreview |
Предварительный просмотр |
|
Printout |
Печать содержимого рабочей книги |
|
SendMail |
Отсылка почты используя встроенные средства Microsoft Mail (MAPI).
SendMail (Recipients, Subject, ReturnReceipt)
В следующем примере рабочая книга отсылается по электронной почте получателю Порфирию Заковыркину: ThisWorkbook.SendMail recipients: = “ Порфирий Заковыркин» |
Visual Design of Worksheets
Prepare for Print
If the user is likely to print any of the Excel worksheets, preview each sheet in Print Preview mode to ensure proper page orientation, page numbers, scaling, etc.
Adding the file’s name and location to the footer will save time when updates are needed to the file.
Page numbers are a must for Excel reports; not just the page number, but also the total number of pages. This ensures we aren’t missing any “last” pages.
Consistent Color Coding
Don’t go crazy with colors.
This is a perfect opportunity to think “less is more”. Stick with 2 to 3 color choices that denote specific messaging. Try to keep the colors more muted than bold. Ensure colors have consistent meaning: color code by department, profit/losses, region, etc.
Consider shading cells for data input differently from cells that perform calculations. You can shade cells to indicate input cells…
…or, shade cells to indicate calculation cells.
Format for Appreciation
Make sure your hard work behind the scenes doesn’t go unappreciated because the public-facing side of your spreadsheet is disorganized or fragmented in its messaging.
Take time to present your results in a clear, easy to read and easy to understand format.
If anyone asks, “what does this mean?”, it means your report needs some adjustments.
Published on: October 17, 2019
Last modified: February 28, 2023
Как перебрать листы в VBA Excel?
В языке программирования VBA (Visual Basic for Applications) можно легко перебрать все листы в книге Excel. Это может быть полезно, например, при выполнении определенных операций с каждым листом или при анализе данных на разных листах.
Для перебора листов в VBA Excel можно использовать цикл For Each, который позволяет последовательно обрабатывать каждый элемент коллекции. В нашем случае коллекция — это набор листов книги Excel.
Пример кода, демонстрирующего перебор листов в VBA Excel:
В данном примере мы объявляем переменную ws типа Worksheet, которая будет использоваться для хранения ссылок на каждый лист в книге. Затем мы используем цикл For Each для перебора каждого листа в коллекции ThisWorkbook.Sheets. Внутри цикла мы можем выполнять любые операции с текущим листом, например, выводить его название с помощью функции MsgBox.
Таким образом, использование цикла For Each в VBA Excel позволяет легко и эффективно перебирать все листы в книге и выполнять необходимые операции с каждым листом.
Строки в электронной таблице
Куда поместить код VBA
Хотите знать, где находится код VBA в вашей книге Excel?
В Excel есть серверная часть VBA, называемая редактором VBA. Вам необходимо скопировать и вставить код в окно кода модуля VB Editor.
Вот как это сделать:
- Перейдите на вкладку Разработчик.
- Выберите вариант Visual Basic. Это откроет редактор VB в бэкэнде.
- На панели Project Explorer в редакторе VB щелкните правой кнопкой мыши любой объект книги, в которую вы хотите вставить код. Если вы не видите Project Explorer, перейдите на вкладку View и нажмите Project Explorer.
- Перейдите во вкладку «Вставить» и нажмите «Модуль». Это вставит объект модуля для вашей книги.
- Скопируйте и вставьте код в окно модуля.
Вам также могут понравиться следующие руководства по Excel VBA:
- Как записать макрос в Excel.
- Создание пользовательской функции в Excel.
- Как создать и использовать надстройку в Excel.
- Как возобновить макрос, поместив его в личную книгу макросов.
- Получите список имен файлов из папки в Excel (с VBA и без).
- Как использовать функцию Excel VBA InStr (с практическими примерами).
- Как отсортировать данные в Excel с помощью VBA (пошаговое руководство).
Распознавание и загрузка сканов в 1С «одним нажатием»: УПД, ТОРГ-12, накладные, счета, номенклатура, заказы и т.д.
Универсальная программа для распознавания ЛЮБЫХ СКАНОВ ИЛИ ФОТО ТОВАРНЫХ ДОКУМЕНТОВ в 1С. Не требует указания параметров и предварительной настройки. Просто выбираете файл (PDF, JPG, DOC, XLS, HTML и т.д.) выбираете документ 1С и нажимаете кнопку «Распознать и загрузить». Программа сама находит таблицу в файле, необходимые для загрузки данные в ней (номенклатура, количество, НДС, цена, сумма) и загружает ее в 1С. Вместе с номенклатурой может найти контрагента, номер и дату документа, штрих-коды, серии ГТД, страну и т.д. Распознает товарные документы ЛЮБОЙ ФОРМЫ (УПД, ТОРГ-12, накладную, заказ клиента и т.д.). Содержит модуль работы с электронной почтой. Для поиска таблиц используются новейшие методы OCR-нейросети. Загружает только то, что нужно, т.е. пропускает повторы шапки таблицы, заголовки, промежуточные итоги, подписи и т.д.
5400
4968 руб.
292
Deleting a Worksheet
The below code would delete the active sheet from the workbook.
Sub DeleteSheet() ActiveSheet.Delete End Sub
The above code would show a warning prompt before deleting the worksheet.
If you don’t want to see the warning prompt, use the below code:
Sub DeleteSheet() Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True End Sub
When Application.DisplayAlerts is set to False, it will not show you the warning prompt. If you use it, remember to set it back to True at the end of the code.
Remember that you can’t undo this delete, so use the above code when you’re absolutely sure.
If you want to delete a specific sheet, you can do that using the following code:
Sub DeleteSheet()
Worksheets("Sheet2").Delete
End Sub
You can also use the code name of the sheet to delete it.
Sub DeleteSheet() Sheet5.Delete End Sub
Appending Text to the Worksheet Name
If printing a report that includes many worksheets, we could add more descriptive text to the worksheet name to help users quickly navigate the information.
For example, if we have a worksheet name “Qtr 1 2022,” we may want to add the text “Employee Performance Appraisal Report for” to the name so that the report’s title reads “Employee Performance Appraisal Report for Qtr 1 2022.”
We can achieve this by joining the formulas we have already described to the additional text we want using the ampersand (&) operator.
For example, the formula below adds the text “Employee Performance Appraisal Report for” to the worksheet name:
We can also use the CONCAT function as in the example below:
Sheet Name:
The sheet names are the values shown in the bottom panel of the excel workbook:
As you can see this workbook has three sheets with the names “Sheet1”, “New Name for Sheet3” and “Sheet2”. You could also see the sheet names using the project window. The values in the parenthesis:
Below you can see the sheet object’s names and the sheet names:
In order to change the sheet name there are 3 methods:
Method 1: Using the property window:
Method 2: Renaming the sheet using the panel at the bottom of the workbook:
Method 3: Unlike the sheet object variables name which could only be changed using the property window, you can change the sheets name programmatically:
After making the change to sheet1’s name, we will have:
Referencing Sheets Using Their Name:
You could also reference a sheet using the sheet name and the WorkSheets collection. In the example below there are 3 sheets with the names “MySheet1”, “MySheet2” and “MySheet3”. They are referenced using the sheet names through the WorkSheets collection.
The syntax for referencing sheets using their name is Sheets(“Sheet Name”) and Worksheets(“Sheet Name”). Both return the same results.
Note: Keep in mind that the sheet name could be modified by the user. This may cause errors in your code if your program references sheets using their name. You might need to consider locking to workbook from such changes.
Обращение к рабочим листам
Рабочий лист (Worksheet) принадлежит коллекции всех рабочих листов (Worksheets) книги Excel. Обратиться к листу можно как к элементу коллекции и, напрямую, по его уникальному имени.
Откройте редактор VBA и обратите внимание на вашу книгу в проводнике, где уникальные имена листов указаны без скобок, а в скобках — имена листов, отображаемые на ярлычках в открытой книге Excel. Уникальные имена листов отсортированы по алфавиту и их расположение по порядку не будет соответствовать их индексам (номерам), если листы перемещались по отношению друг к другу
Индексы листов смотрите по порядку расположения ярлычков в открытой книге. Переместили листы — изменились их индексы.
Обращение к рабочему листу в коде VBA Excel:
|
1 |
‘По уникальному имени Worksheets(N) Worksheets(«Имя листа») |
- УникИмяЛиста — уникальное имя листа, отображаемое в проводнике редактора VBA без скобок, с помощью кода VBA изменить его невозможно.
- N — индекс листа от 1 до количества всех листов в книге, соответствует порядковому номеру ярлычка этого листа в открытой книге Excel.
- Имя листа — имя листа, отображаемое в проводнике редактора VBA в скобках, с помощью кода VBA изменить его можно.
Количество листов в рабочей книге Excel определяется так:
|
1 |
‘В активной книге Worksheets.Count Workbooks(«Книга1.xlsm»).Worksheets.Count |
Загрузка документов и номенклатуры из Excel в 1С «одним нажатием»: УПД, ТОРГ-12, отчеты маркетплейсов, заказы, счета, прайсы
Универсальная обработка для загрузки документов из Excel в 1С. Не требует указания параметров (номера колонок, номер первой строки таблицы и т.д.) и предварительной настройки. Просто выбираете файл Excel, документ 1С и нажимаете кнопку «Загрузить». Обработка сама находит таблицу в файле Excel, необходимые для загрузки данные в ней (номенклатура, количество, НДС, цена, сумма) и загружает ее в 1С. Вместе с номенклатурой может найти контрагента, номер и дату документа, штрих-коды, серии ГТД, страну и т.д. Распознает документы ЛЮБОЙ ФОРМЫ (УПД, ТОРГ-12, заказ, отчет комиссионера и т.д.). Не требует MS Office. Для поиска таблиц используются методы эвристического поиска. Загружает только то, что нужно, т.е. пропускает повторы шапки таблицы, заголовки, промежуточные итоги, подписи и т.д. Содержит модуль работы с электронной почтой и api-загрузчик отчетов о продажах маркетплейсов.
5000
4000 руб.
923
Презентация на тему: » Модель объектов Excel В модели объектов Excel имеется более 100 объектов. Наиболее часто используемыми объектами Excel являются объекты Application, Workbooks.» — Транскрипт:
1
Модель объектов Excel В модели объектов Excel имеется более 100 объектов. Наиболее часто используемыми объектами Excel являются объекты Application, Workbooks и Workbook, Worksheets и Worksheet, Range, Selection.
2
Коллекция объектов Коллекция представляет собой объект, содержащий несколько других объектов, как правило, одного и того же типа. Модель объектов Excel П Объект Workbooks содержит все открытые объекты Workbook (рабочая книга).
3
Коллекция объектов Доступ к заданному элементу коллекции осуществляется либо по номеру, либо по имени. Модель объектов Excel П Например, Worksheets(1) обозначает первый рабочий лист активной книги, а Worksheets(Лист1) — рабочий лист с именем Лист1.
4
Методы и Свойства Модель объектов Excel С Объект.Метод П Application.Quit С Объект.Свойство = ЗначениеСвойства П 1.Application.Caption = Пример 2.MsgBox Worksheets.Count
5
Полная и неявная ссылка на объект Полная ссылка на объект состоит из ряда имен вложенных последовательно друг в друга объектов. Их имена в этом ряду разделяются точками. Ряд начинается с объекта Application и заканчивается именем самого объекта. Модель объектов Excel П Application.Workbooks(Архив). Worksheets(Продажа).Range(A1)
6
Полная и неявная ссылка на объект В неявной ссылке, в отличие от полной, активный в данный момент объект, как правило, можно опускать. Модель объектов Excel П Если программа выполняется в Excel, то Workbooks(Архив).Worksheets(Продажа).Range(A1) Если рабочая книга Архив является активной, то ссылку можно сократить: Worksheets(Продажа).Range(A1) Если рабочий лист Продажа активен, то Range(A1)
7
Объект Application Объект Application – это главный (корневой) объект в иерархии объектов Excel, представляет само приложение Excel. Он имеет огромное число свойств и методов, позволяющих установить общие параметры приложения Excel. Модель объектов Excel
8
Свойства объекта Application ActiveWorkbook (активная рабочая книга) ActiveSheet (активный рабочий лист) ActiveCell (активная ячейка) ActiveChart (активная диаграмма) Объект Application П With ActiveCell.Font.Bold = True.Value = Отчет за Май End With
9
Свойства и методы объекта Application Caption – Возвращает или устанавливает текст из заголовка главного окна Excel. Установка значения свойства равным Empty возвращает заголовок, используемый по умолчанию. П 1.Application.Caption = Отчет за 2000 год 2.Application.Caption = Empty Метод Quit – Закрывает приложение. П Application.Quit Объект Application
10
Коллекция Workbooks и объект Workbook Модель объектов Excel После объекта Application в иерархии объектов Excel следует коллекция Workbooks, которая содержит объекты Workbook. Объект Workbook представляет собой рабочую книгу Excel. Все открытые в Excel рабочие книги включены в коллекцию Workbooks.
11
Свойства коллекции Workbooks и объекта Workbook Коллекция Workbooks и объект Workbook Count — возвращает число объектов коллекции Workbooks. Worksheets – возвращает коллекцию всех рабочих листов книги. Charts – возвращает коллекцию всех диаграмм книги (которые не внедрены в рабочие листы)
12
Методы коллекции Workbooks и объекта Workbook Activate — активизирует рабочую книгу. Add – создает новый объект в коллекции Workbooks. Close –закрывает книги. П 1.MsgBox Workbooks.Count 2.Workbooks(Отдел кадров).Activate 3.Workbooks(1).Close 4.Workbooks.Close Коллекция Workbooks и объект Workbook
13
Обработка событий объекта Workbook 1.Перейти в редактор VBA; 2.В окошке Project Explorer выбрать объект ЭтаКнига и перейти в окошко кода для этого объекта; 3.В верхнем левом списке выбрать объект Workbook; 4.В верхнем правом списке выбрать необходимое событие. Коллекция Workbooks и объект Workbook
14
События объекта Workbook BeforeClose – при закрытии рабочей книги. Deactivate – когда рабочая книга теряет фокус. NewSheet –при добавлении нового листа. Open – при открытии рабочей книги. SheetActivate – при активизации любого рабочего листа. SheetBeforeDoubleClick – при двойном щелчке по любому листу рабочей книги. SheetChange – при изменении содержимого ячейки пользователем. SheetSelectionChange – при изменении выделенного диапазона ячеек. Коллекция Workbooks и объект Workbook
Сохранить копию книги (с отметкой времени)
Когда я работаю со сложными данными и панелью мониторинга в книгах Excel, я часто создаю разные версии своих книг. Это полезно на случай, если с моей текущей книгой что-то пойдет не так. По крайней мере, у меня была бы его копия, сохраненная под другим именем (и я потеряю только ту работу, которую проделал после создания копии).
Вот код VBA, который создаст копию вашей книги и сохранит ее в указанном месте.
Sub CreateaCopyofWorkbook () ThisWorkbook.SaveCopyAs Имя файла: = "C: \ Users \ sumit \ Desktop \ BackupCopy.xlsm" End Sub
Приведенный выше код будет сохранять копию вашей книги каждый раз, когда вы запускаете этот макрос.
Хотя это отлично работает, мне было бы удобнее, если бы я сохранял разные копии при каждом запуске этого кода
Причина, по которой это важно, заключается в том, что если я сделаю непреднамеренную ошибку и запущу этот макрос, он сохранит работу с ошибками. И у меня не будет доступа к работе, пока я не совершу ошибку
Чтобы справиться с такими ситуациями, вы можете использовать приведенный ниже код, который сохраняет новую копию работы каждый раз, когда вы ее сохраняете. И он также добавляет дату и метку времени как часть имени книги. Это может помочь вам отследить любую допущенную вами ошибку, поскольку вы никогда не потеряете ни одну из ранее созданных резервных копий.
Private Sub Workbook_BeforeSave (ByVal SaveAsUI As Boolean, Cancel As Boolean) ThisWorkbook.SaveCopyAs Имя файла: = "C: \ Users \ sumit \ Desktop \ BackupCopy" & Format (Now (), "dd-mm-yy-hh-mm-ss -AMPM ") &" .xlsm "End Sub
Приведенный выше код будет создавать копию при каждом запуске этого макроса и добавлять метку даты / времени к имени книги.
Renaming the Worksheets
You can modify the name property of the Worksheet to change its name.
The following code will change the name of Sheet1 to ‘Summary’.
Sub RenameSheet()
Worksheets("Sheet1").Name = "Summary"
End Sub
You can combine this with the adding sheet method to have a set of sheets with specific names.
For example, if you want to insert four sheets with the name 2018 Q1, 2018 Q2, 2018 Q3, and 2018 Q4, you can use the below code.
Sub RenameSheet() Dim Countsheets As Integer Countsheets = Worksheets.Count For i = 1 To 4 Worksheets.Add after:=Worksheets(Countsheets + i - 1) Worksheets(Countsheets + i).Name = "2018 Q" & i Next i End Sub
In the above code, we first count the number of sheets and then use a For Next loop to insert new sheets at the end. As the sheet is added, the code also renames it.
Referencing Sheets
There are several different ways to reference Sheets:
- ActiveSheet
- Sheet Tab Name
- Sheet Index Number
- Sheet Code Name
ActiveSheet
The ActiveSheet is the Sheet that’s currently active. In other words, if you paused your code and looked at Excel, it’s the sheet that is visible. The below code example will display a MessageBox with the ActiveSheet name.
Sheet Name
You are probably most familiar with referencing Sheets by their Tab Name:
This is the sheet name that’s visible to Excel users. Enter it into the sheets object, as a string of text, surrounded by quotations.
Sheet Index Number
The Sheet Index number is the sheet position in the workbook. 1 is the first sheet. 2 is the second sheet etc.:
Sheet Index Number – Last Sheet in Workbook
To reference the last Sheet in the workbook, use Sheets.Count to get the last Index Number and activate that sheet:
The Sheet Code Name is it’s Object name in VBA:
Referencing Sheets in Other Workbooks
It’s also easy to reference Sheets in other Workbooks. To do so, you need to use the Workbooks Object:
Important: The Workbook must be open before you can reference its Sheets.
Activate vs. Select Sheet
In another article we discuss everything about activating and selecting sheets. The short version is this:
When you Activate a Sheet it becomes the ActiveSheet. This is the sheet you would see if you looked at your Excel program. Only one sheet may be activate at a time.
Activate a Sheet
When you select a Sheet, it also becomes the ActiveSheet. However, you can select multiple sheets at once. When multiple sheets are selected at once, the “top” sheet is the ActiveSheet. However, you can toggle the ActiveSheet within selected sheets.
Select Multiple Sheets
Use an array to select multiple sheets at once:
Worksheet Variable
Assigning a worksheet to an object variable allows you to reference the worksheet by it’s variable name. This can save a lot of typing and make your code easier to read. There are also many other reasons you might want to use variables.
To declare a worksheet variable:
Assign a worksheet to a variable:
Now you can reference the worksheet variable in your code:
Loop Through All Sheets in Workbook
Worksheet variables are useful when you want to loop through all the worksheets in a workbook. The easiest way to do this is:
This code will loop through all worksheets in the workbook, displaying each worksheet name in a message box. Looping through all the sheets in a workbook is very useful when locking / unlocking or hiding / unhiding multiple worksheets at once.
Workbook Protection
Workbook protection locks the workbook from structural changes like adding, deleting, moving, or hiding worksheets.
You can turn on workbook protection using VBA:
or disable workbook protection:
Note: You can also protect / unprotect without a password by omitting the Password argument:
There are a variety of options when protecting worksheets (allow formatting changes, allow user to insert rows, etc.) We recommend using the Macro Recorder to record your desired settings.
We discuss worksheet protection in more detail here.
Worksheet Visible Property
You might already know that worksheets can be hidden:
There are actually three worksheet visibility settings: Visible, Hidden, and VeryHidden. Hidden sheets can be unhidden by any regular Excel user – by right-clicking in the worksheet tab area (shown above). VeryHidden sheets can only be unhidden with VBA code or from within the VBA Editor. Use the following code examples to hide / unhide worksheets:
Worksheet-Level Events
Events are triggers that can cause “Event Procedures” to run. For example, you can cause code to run every time any cell on a worksheet is changed or when a worksheet is activated.
Worksheet event procedures must be placed in a worksheet module:
There are numerous worksheet events. To see a complete list, go to a worksheet module, select “Worksheet” from the first drop-down. Then selecting an event procedure from the second drop-down to insert it into the module.
Worksheet Activate Event
Worksheet activate events run each time the worksheet is opened.
This code will select cell A1 (resetting the view area to the top-left of the worksheet) each time the worksheet is opened.
Worksheet Change Event
Worksheet change events run whenever a cell value is changed on the worksheet. Read our tutorial about Worksheet Change Events for more information.
VBA worksheets Cheatsheet