Настоящая заметка продолжает знакомство с VBA, в ней приводятся примеры управления рабочими книгами и листами Excel с помощью VBA.[1]
Рис. 1. Столбцы и строки скрыты за исключением рабочего диапазона
Скачать заметку в формате Word или pdf, примеры в архиве (политика безопасности провайдера не позволяет загружать файлы Excel с поддержкой макросов)
Сохранение всех рабочих книг
Следующая процедура циклически просматривает все рабочие книги в коллекции Workbooks и сохраняет каждый файл, который сохранялся ранее.
Public Sub SaveAllWorkbooks()
Dim Book As Workbook
For Each Book In Workbooks
If Book.Path <> "
"
Then Book.Save
Next Book
End Sub
Обратите внимание на то, как используется свойство Path. Если для какой-либо рабочей книги свойство Path не задано, значит, файл еще не сохранялся (это новая рабочая книга). Данная процедура игнорирует такие рабочие книги и сохраняет только те из них, свойство Path которых имеет ненулевое значение.
Сохранение и закрытие всех рабочих книг
Следующая процедура циклически просматривает коллекцию Workbooks. Программа сохраняет и закрывает все рабочие книги.
Sub CloseAllWorkbooks()
Dim Book As Workbook
For Each Book In Workbooks
If Book.Name <> ThisWorkbook.Name Then
Book.Close savechanges:=True
End If
Next Book
ThisWorkbook.Close savechanges:=True
End Sub
Обратите внимание, что процедура использует оператор If, чтобы определить, содержит ли данная рабочая книга текущий выполняемый код. Это необходимо, так как при закрытии рабочей книги, содержащей процедуру, программа автоматически завершает свое выполнение, причем остальные рабочие книги не будут сохранены и закрыты.
Частичное сокрытие элементов рабочего листа
В примере из этого раздела скрываются все строки и столбцы рабочего листа за исключением тех из них, которые находятся в текущем выделенном диапазоне (рис. 1).
Sub HideRowsAndColumns()
Dim row1 As Long, row2 As Long
Dim col1 As Long, col2 As Long
If TypeName(Selection) <> "
Range"
Then Exit Sub
'
Если последняя строка либо последний столбец скрыты,
'
отобразить все и выйти
If Rows(Rows.Count).EntireRow.Hidden Or _
Columns(Columns.Count).EntireColumn.Hidden Then
Cells.EntireColumn.Hidden = False
Cells.EntireRow.Hidden = False
Exit Sub
End If
row1 = Selection.Rows(1).Row
row2 = row1 + Selection.Rows.Count — 1
col1 = Selection.Columns(1).Column
col2 = col1 + Selection.Columns.Count — 1
Application.ScreenUpdating = False
On Error Resume Next
'
Скрыть строки
Range(Cells(1, 1), Cells(row1 — 1, 1)).EntireRow.Hidden = True
Range(Cells(row2 + 1, 1), Cells(Rows.Count, _
1)).EntireRow.Hidden = True
'
Скрыть столбцы
Range(Cells(1, 1), Cells(1, col1 — 1)).EntireColumn.Hidden = True
Range(Cells(1, col2 + 1), Cells(1, _
Columns.Count)).EntireColumn.Hidden = True
End Sub
Чтобы показать все строки и столбцы, встаньте на перекрестие строк и столбцов (выделено красным на рис. 1), и пройдите по меню: Главная –> Ячейки –> Формат –> Скрыть или отобразить –> Отобразить строки. Повторите этот путь, чтобы отобразить столбцы (рис. 2).
Рис. 2. Показать все строки и столбцы
Если выделенный диапазон включает несвязанные диапазоны, видимым останется только первый из них.
Синхронизация выделенного диапазона на листах рабочей книги
Если вы работаете с рабочими книгами, состоящими из нескольких листов, то, вероятно, знаете, что Excel не может «синхронизировать» листы в рабочей книге. Другими словами, не существует автоматического способа сделать так, чтобы все листы имели одинаковые выделенные диапазоны и верхние левые ячейки. Макрос VBA, показанный ниже, берет за основу активный рабочий лист и выполняет следующие действия со всеми остальными рабочими листами в книге:
- выделяет тот же диапазон, что и в активном листе;
- задает ту же левую верхнюю ячейку, что и на активном листе (рис. 3).
Рис. 3. Синхронизация выделенного диапазона на листах рабочей книги
Sub SynchSheets()
'
Дублирование выделенного диапазона активного листа
'
и верхней левой ячейки активного диапазона на всех листах
If TypeName(ActiveSheet) <> "
Worksheet"
Then Exit Sub
Dim UserSheet As Worksheet, sht As Worksheet
Dim TopRow As Long, LeftCol As Integer
Dim UserSel As String
Application.ScreenUpdating = False
'
Запоминание текущего листа
Set UserSheet = ActiveSheet
'
Сохранение сведений об активной ячейке
TopRow = ActiveWindow.ScrollRow
LeftCol = ActiveWindow.ScrollColumn
UserSel = ActiveWindow.RangeSelection.Address
'
Циклический обход рабочих листов
For Each sht In ActiveWorkbook.Worksheets
If sht.Visible Then '
пропуск скрытых листов
sht.Activate
Range(UserSel).Select
ActiveWindow.ScrollRow = TopRow
ActiveWindow.ScrollColumn = LeftCol
End If
Next sht
'
Восстановление исходного положения
UserSheet.Activate
Application.ScreenUpdating = True
End Sub
[1] По материалам книги Джон Уокенбах. Excel 2010. Профессиональное программирование на VBA. – М: Диалектика, 2013. – С. 348–351.
Спасибо, полезная информация!