Vba excel порядок листов
Как создать новую книгу с определенным количеством листов?
Обычно, когда вы создаете новую книгу, по умолчанию она будет включать три листа в Excel 2007/2010 или один лист в Excel 2013. Позже вы можете добавлять или удалять вкладки по своему усмотрению. Но пробовали ли вы когда-нибудь создавать новую книгу с определенным количеством листов каждый раз, когда вам нужно?
Создать новую книгу с определенным количеством листов с помощью кода VBA
Вы можете использовать следующий код VBA для создания новой книги, содержащей определенное количество вкладок.
1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модулии вставьте следующий код в Модули Окно.
Код VBA: создать новую книгу с определенным количеством листов
3. После вставки кода нажмите F5 ключ для запуска этого кода, и в поле подсказки введите количество листов, которое вы хотите в книге, см. снимок экрана:
4. Затем нажмите OK кнопка, новая книга создана с указанным количеством листов, которые вам нужны.
Создайте новую книгу с определенным количеством листов с помощью Kutools for Excel
Если вы не знакомы с кодом VBA, вы можете применить удобный инструмент - Kutools for Excel, С его Создать рабочие листы последовательностей Утилита, вы можете быстро создать новую книгу с именами листов по умолчанию, именами настраиваемых листов и т. д.
После установки Kutools for Excel, пожалуйста, сделайте следующее:
1. Нажмите Предприятие > Рабочий лист > Создать рабочий лист последовательности, см. снимок экрана:
2. В Создать рабочие листы последовательностей диалоговом окне выполните следующие операции:
(1.) Выберите Пустой рабочий лист под Базовый рабочий лист раскрывающийся список;
(2.) Введите количество листов в новой книге под Количество Create текстовое окно;
(3.) Из Имена листов на основе раздел, выберите Имя по умолчанию в Excel опцию.
3. Затем нажмите OK , и будет создана новая книга с указанным вами количеством листов.
В настоящей заметке описана разработка утилиты VBA для Excel. Показан процесс анализа задачи и последующего ее решения. Пример рассмотрен в расчете на начинающих.[1] Цель — разработать утилиту, которая изменяет порядок следования листов рабочей книги, сортируя их названия по алфавиту (без кода VBA это сделать невозможно). Если вы часто создаете книги с большим количеством листов, то знаете, что иногда сложно найти интересующий вас лист. Если же их упорядочить по названиям, то любой рабочий лист найти будет значительно проще.
Рис. 1. Метод Move объекта Sheets
Начнем с перечисления требований к приложению. В процессе разработки вы будете обращаться к этому перечню для проверки правильности выполнения действий.
Часто самой сложной частью проекта является определение того, с чего же начать. В данном случае начнем с перечисления особенностей Excel, которые могут повлиять на соблюдение требований к проекту.
Вот предварительный план, описывающий общие задачи:
- идентифицировать активную рабочую книгу;
- получить список названий всех листов в рабочей книге;
- посчитать листы;
- отсортировать их (определенным образом);
- изменить порядок следования листов в соответствии с параметрами сортировки.
Если вам недостаточно информации о конкретных методах и свойствах, обратитесь к электронной справочной системе. Однако для начала лучше всего включить функцию записи макросов и посмотреть, что записывается в результате выполнения действий, связанных с решением поставленной задачи.
Откройте новую рабочую книгу, содержащую три рабочих листа. Включите функцию записи макросов и перетащите третий рабочий лист на место первого. Остановите запись макроса. Изучите код:
Найдите в справочной системе слово Move (это метод, перемещающий лист в рабочей книге на новое место). Для этого, например, находясь в окне VBE, нажмите F2 (рис. 1; см. также справку в Интернете). Данный метод имеет один аргумент, определяющий будущее положение листа.
Вам также необходимо узнать количество листов в активной рабочей книге. Активизируем окно отладки (Immediate) в VBE (нажав Ctrl+G) и введем такой оператор:
VBA вернула значение 3 (рис. 2).
Рис. 2. Использование окна отладки в VBE для тестирования оператора
Теперь введем в окне отладки (Immediate) следующий оператор:
В результате будет получено название первого листа — ЛистЗ.
Конструкция For Each-Next используется для циклического просмотра всех членов коллекции (см., например, Основы программирования на VBA, раздел Управление объектами и коллекциями):
Sub Test()
For Each Sht In ActiveWorkbook.Sheets
MsgBox Sht.Name
Next Sht
End Sub
Что касается сортировки, справочная система подскажет, что метод Sort относится к объекту Range. Поэтому одним из решений задачи могло быть перенесение названия листов в диапазон ячеек и сортировка этого диапазона. Однако такая задача слишком сложна. Возможно, целесообразнее сформировать из названий листов массив строк, а затем отсортировать этот массив с использованием кода VBA.
Однако прежде следует задать первоначальные настройки:
- Создайте пустую рабочую книгу с пятью рабочими листами: названия — Лист1, Лист2, ЛистЗ, Лист4 и Лист5.
- Разместите листы произвольно, чтобы они следовали не по порядку.
- Сохраните рабочую книгу как Test.xlsm.
- Перейдите в VBE (меню Разработчик –> Visual Basic) и выберите проект Personal.xlsb в окне Project (Проект). Если Personal.xlsb не отображается в окне Project, значит вы никогда не использовали личную книгу макросов. Excel создаст для вас эту книгу, когда вы запишете макрос (любой) и определите, что он должен сохраняться в личной книге макросов (подробнее см. Создание личной книги макросов).
- Добавьте новый модуль VBA (используя команду Inserts –>Module).
- Создайте пустую процедуру с названием SortSheets (рис. 3).
- Перейдите в Excel. Выберите команду Разработчик –> Код –> Макросы для отображения диалогового окна Макрос.
- В диалоговом окне Макрос выберите процедуру SortSheets и щелкните на кнопке Параметры. В открывшемся окне Параметры макроса выберите Ctrl+Shift+S.
Рис. 3. Пустая процедура в модуле, находящемся в персональной книге макросов
Макрос можно сохранить в любом модуле личной книги макросов. Однако лучше хранить каждый макрос в отдельном модуле. Таким образом, вы сможете легко экспортировать модуль и импортировать его в другой проект.
Начинаем писать код процедуры
Вначале необходимо поместить названия листов в массив строк. Так как пока неизвестно, сколько листов содержит активная рабочая книга, для объявления массива используем оператор Dim с пустыми скобками. Помните, что затем нужно применить оператор ReDim и изменить размерность массива на требуемое число элементов (подробнее см. Основы программирования на VBA, раздел Массивы). В цикл добавим функцию MsgBox, чтобы убедиться, что названия листов на самом деле вводятся в массив.
Sub SortSheets()
' Сортировка листов в активной рабочей книге
Dim SheetNames() as String
Dim i as Long
Dim SheetCount as Long
SheetCount = ActiveWorkbook.Sheets.Count
ReDim SheetNames(1 To SheetCount)
For i = 1 To SheetCount
SheetNames(i) = ActiveWorkbook.Sheets(i).Name
MsgBox SheetNames(i)
Next i
End Sub
Этот прием не столь навязчив по сравнению с использованием операторов MsgBox. Не забудьте только удалить оператор по завершении тестирования.
Рис. 4. Использование метода Print объекта Debug в целях тестирования. Разместите курсор внутри текста процедуры Sub SortSheets(), откройте окно Immediate (Ctrl+G), нажмите Run. В окне Immediate отразятся номера листов книги; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Пока процедура SortSheets всего лишь создает массив названий листов в соответствии с порядком их следования в активной рабочей книге. Теперь нужно отсортировать значения в массиве SheetNames и изменить порядок следования листов в книге согласно отсортированному массиву.
Создание процедуры сортировки
Можно вставить программу сортировки в процедуру SortSheets, но лучше написать общую процедуру сортировки, которую можно будет использовать и в других проектах (сортировка массивов — довольно популярная операция).
Существует несколько способов сортировки массивов. Мы выбрали пузырьковый метод (хотя это не очень быстрый прием, но его легко запрограммировать). В данном конкретном приложении высокая скорость выполнения операций не так уж важна. В пузырьковом методе используется вложенный цикл For-Next, в котором оценивается каждый элемент массива. Если элемент массива больше, чем следующий, то эти два элемента меняются местами. Такое сравнение повторяется для каждой пары элементов (т.е. n – 1 раз).
Sub BubbleSort(List() As String)
' Сортировка массива List по возрастанию
Dim First As Long, Last As Long
Dim i As Long, j As Long
Dim Temp As String
First = LBound(List)
Last = UBound(List)
For i = First To Last – 1
For j = i + 1 To Last
If List(i) > List(j) Then
Temp = List(j)
List (j) = List(i)
List(i) = Temp
End If
Next j
Next i
End Sub
Эта процедура имеет один аргумент: одномерный массив с названием List. Массив, который передается в процедуру, может быть любой длины. Для присвоения нижней и верхней границ массива переменным First и Last использовались функции Lbound и UBound соответственно.
Ниже приведен код для тестирования процедуры BubbleSort:
Sub SortTester()
Dim x(1 To 5) As String
Dim i As Long
x(1) = " собака "
x(2) = " кот "
x(3) = " слон "
x(4) = " трубкозуб "
x(5) = " птица "
Call BubbleSort(x)
For i = 1 To 5
Debug.Print i, x(i)
Next i
End Sub
Процедура SortTester создает массив из пяти строк, передает его процедуре BubbleSort и отображает отсортированный массив в окне отладки Immediate. После того как код выполнил свое предназначение, он был удален.
Убедившись в том, что код работает надежно, я изменил процедуру SortSheets путем добавления вызова в процедуру BubbleSort, передачи массива SheetNames в качестве аргумента. Начиная с этого момента, модуль приобретает следующий вид.
Sub SortSheets()
' Сортировка листов в активной рабочей книге
Dim SheetNames() as String
Dim i as Long
Dim SheetCount as Long
SheetCount = ActiveWorkbook.Sheets.Count
ReDim SheetNames(1 To SheetCount)
For i = 1 To SheetCount
SheetNames(i) = ActiveWorkbook.Sheets(i).Name
Next i
Call BubbleSort(SheetNames)
End Sub
По окончании работы процедуры SortSheets образуется массив, состоящий из отсортированных названий листов активной рабочей книги. Чтобы проверить это, можно отобразить содержимое массива в окне отладки, добавив в конец процедуры перед оператором End Sub такой код:
For i = 1 То SheetCount
Debug.Print SheetNames(i)
Next i
Напишем цикл For-Next, который просматривает каждый лист и перемещает его в соответствующее место, указанное в массиве SheetNames.
Например, в первой итерации цикла счетчик i = 1. Первый элемент массива SheetNames – Лист1. Следовательно, выражение для метода Move в цикле будет таким:
Вторая итерация цикла:
В конец процедуры SortSheets добавим новый код:
Теперь необходимо собрать весь код. Объявим все переменные, используемые в процедурах, и добавим несколько комментариев, а также пустых строк, чтобы программу можно было легче прочесть. В результате процедура SortSheets будет приведена к следующему виду:
Sub SortSheets ()
' Эта процедура сортирует листы
' активной рабочей книги по возрастанию.
' Нажмите клавиши для выполнения
Dim SheetNames() As String
Dim SheetCount As Long
Dim i As Long
' Определение количества листов и массива ReDim
SheetCount = ActiveWorkbook.Sheets.Count
ReDim SheetNames(1 To SheetCount)
SheetNames(i) = ActiveWorkbook.Sheets(i).Name
' Заполнение массива названиями листов
For i = 1 To SheetCount
SheetNames(i) = ActiveWorkbook.Sheets(i).Name
Next i
' Сортировка массива по возрастанию
Call BubbleSort(SheetNames)
Дополнительное тестирование
Наверное, вы считаете, что работа окончена. Однако тот факт, что процедура работает с рабочей книгой Test.xlsm, не означает, что она будет работать со всеми рабочими книгами. Чтобы проверить программу, загрузим несколько других рабочих книг и вновь запустим программу. Скоро вы убедитесь в том, что приложение неидеально (если быть точным, оно далеко от идеала). Были обнаружены следующие проблемы:
Устранение проблем
Проблема 1. Чтобы решить проблему обновления изображения на экране вставьте в начале процедуры SortSheets инструкцию: Application.ScreenUpdating = False. Этот оператор «замораживает» окна Excel во время выполнения макроса. Еще один положительный момент заключается в том, что увеличивается скорость выполнения макроса. Когда выполнение макроса завершится, обновление экрана включится автоматически.
Проблема 2. Можно использовать функцию UCase для сравнения названия листов в верхнем регистре. В процедуре BubbleSort место строки If List(i) > List(j) Then вставьте строку If UCase(List(i)) > UCase(List(j)) Then. Проблему регистра можно решить иначе: добавьте в начало модуля оператор: Option Compare Text. В этом случае VBA выполняет сравнение строк на основе нечувствительных к регистру правил сортировки. Другими словами, А считается тем же, что и а.
On Error Resume Next
SheetCount = ActiveWorkbook.Sheets.Count
If Err <> 0 Then Exit Sub ' нет активной рабочей книги
Можно и не использовать оператор On Error Resume Next. Альтернатива – поместить следующий оператор в верхнюю часть процедуры SortSheets:
If ActiveWorkbook Is Nothing Then Exit Sub
Проблема 4. Обычно для защиты структуры рабочей книги имеется серьезная причина. Мы не будем снимать защиту; программа должна отображать предупреждение, чтобы пользователь снял защиту и снова выполнил макрос. Проверку защищенной структуры книги выполнить легко — свойство ProtectStructure объекта WorkBook возвращает True, если книга защищена. Поэтому добавим в проект следующий код:
' Проверка защиты структуры рабочей книги
If ActiveWorkbook.ProtectStructure Then
MsgBox ActiveWorkbook.Name & " защищена. " , _
vbCritical, " Невозможно отсортировать листы. "
Exit Sub
End If
Проблема 5. Для повторной активизации листа после завершения сортировки я написал код, который сопоставляет исходный лист с объектной переменной OldActiveSheet, а также активизирует этот лист после завершения процедуры. Ниже показан оператор, который инициализирует переменную.
Set OldActive = ActiveSheet
А следующий оператор активизирует рабочий лист, который был изначально активным:
Будьте внимательны, когда отключаете прерывание макроса, выполняемое с помощью клавиш Ctrl+Break. Если программа попадет в бесконечный цикл, выйти из него вы не сможете. Лучше использовать этот оператор, когда все работает идеально.
Проблема 7. Для предотвращения проблемы, возникающей из-за случайной сортировки листов, перед отключением клавиш Ctrl+Break в процедуру был добавлен следующий оператор:
If MsgBox( " Сортировать листы в активной рабочей книге? " , _
vbQuestion + vbYesNo) <> vbYes Then Exit Sub
Рис. 6. Окно подтверждения необходимости сортировки листов
Финальный код процедуры можно найти в модуле VBA приложенного Excel-файла.
Доступность
Макрос SortSheets сохранен в личной книге макросов, поэтому он всегда доступен при запуске Excel. На этом этапе макрос может выполняться при выборе названия макроса в диалоговом окне Макрос. Это окно можно отобразить, пройдя по меню Вид –> Макросы –> Макросы, или нажав Alt+F8. Можно сразу запустить макрос нажав Ctrl+Shift+S. Команду вызова макроса можно также добавить на ленту. Для этого:
- Щелкните правой кнопкой мыши на ленте и в контекстном меню выберите команду Настройка ленты.
- На вкладке Настройка ленты диалогового окна Параметры Excel в списке Выбрать команды выберите категорию Макросы.
- Щелкните на значке XLSB!SortSheets.
- Используйте элементы управления в правом окне для создания новой вкладки и группы ленты (вы не сможете добавить команду в существующую группу.)
Я создал группу Мои макросы во вкладке Вид и переименовал новый, добавленный в эту группу элемент, на Сортировка листов (рис. 7).
Рис. 7. Добавление новой команды на ленту
Правда, в процедуре все еще присутствует одна небольшая проблема: сортировка достаточно строгая и не всегда кажется «логичной». Например, после сортировки лист Лист10 размещается перед Лист2. Большинство пользователей предпочитают видеть Лист2 перед Лист10. Решить эту проблему довольно сложно. Если вы знаете, что листов будет более девяти, можно рекомендовать называть их Лист01, Лист02, … Лист09, Лист10, …
1 комментарий для “Сортировка листов книги Excel с помощью процедуры VBA”
И в чем именно «сложность» решения проблемы с размещением Лист2 перед Лист10 при сортировке?
Разве нелья просто выделять из названия листа текстовую часть вида «Лист» и числовую часть «1», «2» и т.п., после чего отсортировать эти несчастные первые девять листов в порядке возрастания числовых значений, содержащихся в их названиях?
Более того, можно же просто уже после проведения первоначальной сортировки разыскать названия первых девяти листов, задавая их в явном виде «Лист1», «Лист2» и т.п. и расставить их порядку возрастания номеров в начале отсортированного массива.
Настоящая заметка продолжает знакомство с VBA. В ней представлены некоторые пользовательские функции, которые можно применять в формулах рабочего листа.[1] Помните, что эти процедуры функций необходимо определить в модуле VBA, а не в модуле кода соответствующей рабочей книги, листа или пользовательской формы.
Рис. 1. Активная ячейка имеет полужирное начертание, выполненное с помощью условного форматирования
Получение информации о форматировании ячейки
Можно написать пользовательскую функцию, возвращающую информацию о форматировании ячейки. Такие функции используются при сортировке данных на основе форматирования (например, в случае, когда ячейки, выделенные полужирным шрифтом, должны располагаться рядом).
Предупреждение. Эти функции не всегда обновляются автоматически — изменение форматирования не приводит к пересчету формул Excel. Чтобы вызвать глобальный пересчет формул (и обновить все пользовательские функции), нажмите клавиши . В функцию можно также добавить следующий оператор: Application.Volatile. При наличии этого оператора пересчет функции производится после нажатия клавиши .
Следующая функция возвращает ИСТИНА, если аргумент, состоящий из одной ячейки, выделен полужирным шрифтом. Если диапазон передается в качестве аргумента, функция использует его верхнюю левую ячейку.
Следующая функция возвращает ИСТИНА, если используемая в качестве аргумента ячейка выделена курсивом.
Обе предыдущие функции возвращают ошибку, если ячейка имеет смешанное форматирование. Функция, приведенная ниже, возвращает ИСТИНА только тогда, когда все символы в ячейке выделены полужирным шрифтом.
Следующая функция возвращает целое число, соответствующее индексу цвета заливки ячейки. Если ячейка не имеет заливки, то функция возвращает значение 4142. Эта функция не может использоваться для определения цветов заливки таблиц (которые создаются с помощью команды Ctrl+T) или сводных таблиц. В подобных случаях воспользуйтесь объектом DisplayFormat.
Беседа с рабочим листом
Функция Saylt применяет синтезатор речи Excel для озвучивания аргумента.
Эта функция носит развлекательный характер, но может использоваться и в серьезных целях. Например, ее можно включить в следующую формулу:
=ЕСЛИ(СУММ(А:А)>25000;Saylt( " Цель достигнута " ))
Если сумма значений в столбце А превышает 25 000, вы услышите синтезированный голос, сообщающий о том, что цель достигнута. Метод Speak можно также включить в конец длинной процедуры, и компьютер известит вас о том, что выполнение процедуры завершено.
Отображение даты сохранения файла или вывода файла на печать
Рабочая книга Excel содержит несколько встроенных свойств документа, к которым можно получить доступ с помощью свойства BuiltinDocumentProperties объекта Workbook. Следующая функция возвращает дату и время последнего сохранения рабочей книги.
К свойствам можно обратиться по номеру, например, BuiltinDocumentProperties(5), или по имени, как в примере выше. Получить перечень свойств можно с помощью процедуры BDPNames(), код которой приведен в приложенном Excel-файле.
Значения, возвращаемые этой функцией, совпадают со значениями даты и времени, которые отображаются в разделе Связанные даты после выбора команды Файл –> Сведения (рис. 2). Обратите внимание, что на значения даты и времени оказывает влияние свойство AutoSave. Поэтому время последнего сохранения необязательно имеет отношение ко времени сохранения файла пользователем.
Рис. 2. Сведения о рабочей книге; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Не ко всем свойствам можно получить доступ с помощью BuiltinDocumentProperties. Например, при попытке получить доступ к свойству Number of Bytes, указывающему размер файла, будет генерироваться ошибка.
Основы иерархии объектов
Объектная модель Excel представляет собой определенную структуру: одни объекты содержатся в других объектах. На вершине этой иерархии находится объект Application. Excel содержит другие объекты, в которые, в свою очередь, вложены более низкоуровневые объекты и т.д. Следующая иерархия показывает, как в этой структуре представлен объект Range.
Объект Application
Объект Workbook
Объект Worksheet
Объект Range
Следующая функция получает один аргумент (диапазон) и возвращает имя рабочего листа, который содержит указанный диапазон. При этом используется свойство Parent объекта Range. Свойство Parent возвращает объект, содержащий объект Range.
Следующая функция возвращает название рабочей книги для конкретной ячейки. Обратите внимание, что эта функция использует свойство Parent дважды. Первое свойство Parent возвращает объект Worksheet, а второе свойство Parent возвращает объект Workbook.
Следующая функция переносит это упражнение на следующий логический уровень, обращаясь к свойству Parent трижды. Такая функция возвращает имя объекта Application для заданной ячейки. Указанная функция всегда будет возвращать значение Microsoft Excel.
Подсчет количества ячеек между двумя значениями
Следующая функция возвращает количество значений в диапазоне (первый аргумент), которые попадают в область, заданную вторым и третьим аргументами.
Оператор Application.Volatile вызывает выполнение функции всякий раз, когда пересчитываются формулы на рабочем листе. Оператор Rows.Count возвращает количество строк на рабочем листе (используется именно он, а не жестко заданное значение, из соображений совместимости; новые версии Excel могут включать большее количество строк на рабочем листе). Ссылка rng.Column возвращает номер столбца левой верхней ячейки в аргументе rng. Благодаря ссылке rng.Parent функция работает корректно, даже если аргумент rng ссылается на другой лист или рабочую книгу. Метод End (с аргументом xlUp) эквивалентен переходу к последней ячейке столбца и нажатию и . Функция IsEmpty проверяет, пуста ли ячейка. Если ячейка пуста, функция возвращает пустую строку. Без этого оператора пустой ячейке соответствовал бы результат 0.
Код функции LastlnRow можно найти в модуле VBA приложенного Excel-файла.
Соответствует ли строка шаблону
Следующая функция возвращает значение ИСТИНА, если строка соответствует заданному шаблону. Функция представляет собой «оболочку», позволяющую использовать в формулах оператор VBA Like.
Функция IsLike принимает два аргумента: text — текстовая строка или ссылка на ячейку, содержащую текстовую строку; pattern — строка, содержащая групповые символы согласно таблице (рис. 3).
Рис. 3. Групповые символы
Представленная ниже формула возвращает ИСТИНА, так как * соответствует любому количеству символов. Она возвращает ИСТИНА, если первый аргумент — любой текст, начинающийся с g.
=IsLike( " guitar " , " g* " )
Следующая формула возвращает ИСТИНА, так как ? соответствует любому отдельному символу. Если бы первым аргументом функции был «Unit12», то функция возвращала бы ЛОЖЬ.
=IsLike( " Unit1 " , " Unit? " )
Следующая формула возвращает ИСТИНА, так как первый аргумент является одним из символов списка во втором аргументе.
=IsLike( " а " , " [aeiou] " )
Следующая формула возвращает ИСТИНА, если ячейка А1 содержит один из символов: а, е, i, о, u, А, Е, I, О, U. При использовании функции ПРОПИСН (UPPER) в аргументе функция становится нечувствительной к регистру.
=IsLike(ПРОПИСН(А1), ПРОПИСН( " [aeiou] " ))
Следующая формула возвращает ИСТИНА, если в ячейке А1 находится значение, начинающееся с 1 и состоящее ровно из трех цифр (т.е. любое целое число от 100 до 199).
Возвращение из строки n-го элемента
ExtractElement — специальная функция рабочего листа, которая извлекает элемент из текстовой строки.
В этой процедуре используется VBA-функция Split, возвращающая массив констант, из которого состоит текстовая строка. Массив начинается с нулевого элемента (а не с первого), поэтому текущий элемент имеет индекс n – 1.
Например, если ячейка содержит следующий текст " 123-456-789-0133-8844 " , вы можете использовать функцию ExtractElement для извлечения любых подстрок между дефисами. Следующая формула возвращает 0133, т.е. четвертый элемент в строке.
=ExtractElement( " 123-456-789-0133-8844 " ,4, " — " )
Функция ExtractElement принимает три аргумента:
- txt — текстовая строка, из которой извлекается подстрока (символьная строка или ссылка на ячейку);
- n — целое число, представляющее номер извлекаемого элемента;
- Separator — отдельный символ, используемый как разделитель.
Если в качестве символа-разделителя задать пробел, то несколько пробелов подряд будут рассматриваться как один, что не всегда соответствует требованиям. Если n превышает количество элементов в строке, функция возвращает пустую строку.
Преобразование чисел в текст
Функция SpellDollars возвращает текст, в который преобразуется исходное число – сумма в долларах и центах. Например, формула =SpellDollars(23,45) возвращает строку " двадцать три и 45/100 доллара " (рис. 4). Обратите внимание, что отрицательные числа заключаются в круглые скобки. Код функции SpellDollars приведен в приложенном Excel-файле.
Рис. 4. Примеры использования функции SpellDollars
Универсальная функция
Можно сделать так, чтобы одна функция рабочего листа работала как несколько функций. StatFunction имеет два аргумента: диапазон (rng) и операция (ор). В зависимости от значения аргумента ор функция возвращает значение, вычисленное с помощью одной из следующих функций Excel: СУММ, СРЗНАЧ, МЕДИАНА, МОДА, СЧЁТ, МАКС, МИН, ДИСП, СТАНДОТКЛОН. Например, результат формулы =StatFunction($A$33:$A$37;C36) зависит от содержимого ячейки С36, в которой должна быть текстовая строка с именем одной из допустимых функций. В нашем случае – СЧЁТ. Код функции StatFunction приведен в модуле VBA приложенного Excel-файла.
Рис. 5. Примеры использования функции StatFunction
Функция SheetOffset
В Excel ограничена поддержка «трехмерных рабочих книг». Например, чтобы сослаться на другой рабочий лист в книге, включите в формулу имя рабочего листа. Данная проблема будет оставаться незначительной до тех пор, пока вы не попытаетесь скопировать формулу из одного листа в другой. Скопированные формулы продолжают ссылаться на первоначальное имя рабочего листа, и ссылки на листы не изменяются, как это происходит в реальной трехмерной рабочей книге.
Функция VBA SheetOffset обеспечивает установку относительных ссылок на рабочие листы. Например (рис. 6), можно сослаться на ячейку А1 следующего рабочего листа с помощью такой формулы: =SheetOffset(1; А1). Первый аргумент представляет лист и может быть положительным, отрицательным или нулевым. Второй аргумент должен быть ссылкой на одну ячейку. Можете скопировать эту формулу в другие листы, и в скопированных формулах будет использована относительная ссылка.
Как отсортировать листы в алфавитном / буквенно-цифровом порядке в Excel?
Обычно вы можете отсортировать или упорядочить порядок вкладок рабочего листа в Excel, перетащив вкладки листа на панель вкладок листа. Но для того, чтобы сделать это с несколькими листами, вы можете рассмотреть следующие хитрые способы быстрой сортировки листов в алфавитном / буквенно-цифровом порядке в большой книге.
Quickly sort all worksheers in alphabetical/alphanumeric order in Excel:
The Sort Sheets utility of Kutools for Excel can help you quickly sorting all worksheets in alphabetical or alphanumeric order in Excel with only several clicks. Download the full feature 60-day free trail of Kutools for Excel now!
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools : Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools : Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color ; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment.
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Сортировка листов в алфавитном / буквенно-цифровом порядке с кодом VBA
В центре поддержки Майкрософт есть макрос для сортировки листов по альфа-каналу. Мы можем применить его, выполнив следующие шаги:
1. Удерживая нажатой ALT + F11 ключи, и он открывает Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модули, а затем вставьте следующий макрос в Окно модуля.
VBA: сортировка листов в алфавитном / буквенно-цифровом порядке
3. нажмите F5 ключ для запуска этого макроса. В следующем окне подсказки щелкните Да, все листы будут отсортированы по возрастанию в алфавитном порядке; и нажмите Нет, все листы будут отсортированы по убыванию в алфавитном порядке.
Сортировка листов в алфавитном / буквенно-цифровом порядке с помощью Kutools for Excel
Если вы не знакомы с макросами или предпочитаете другие способы, вы можете попробовать Kutools for Excel. Kutools for Excel's Сортировать листы инструмент может легко сортировать все листы.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Нажмите Кутулс Плюс > Рабочий лист > Сортировать листы. Смотрите скриншот:
2. В Сортировать листы В диалоговом окне выберите один тип сортировки, который вам нужен на правой панели, например Альфа-сортировка, Буквенно-цифровая сортировка, а затем нажмите OK кнопка. Смотрите скриншот:
Затем все листы сортируются на основе указанного типа сортировки. Смотрите скриншот:
Kutools for Excel's Сортировать листы инструмент может быстро переупорядочить все листы в активной книге. Он поддерживает несколько типов сортировки, в том числе Альфа-сортировка, Буквенно-цифровая сортировка, Цвет Сортировка и Обратный. Кроме того, вы также можете перемещать листы вверх / вниз и сбрасывать сортировку.
Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Office Tab - Tabbed Browsing, Editing, and Managing of Workbooks in Excel:
Office Tab brings the tabbed interface as seen in web browsers such as Google Chrome, Internet Explorer new versions and Firefox to Microsoft Excel. It will be a time-saving tool and irreplaceble in your work. See below demo:
Excel дает возможность управлять своими объектами (листами, ячейками, графиками) с помощью языка VBA. Чтобы создавать первые программы в Excel нужно иметь представление об объектной модели приложения.
Объектная модель Excel
Перед созданием своей первой программы в 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.
Читайте также: