Entirerow vba excel описание

Entirerow vba excel описание

Рассмотрим некоторые основные методы объекта Range.

Метод Activate

Метод Activate задает активную ячейку в выделенном диапазоне ячеек (для выделения ячейки следует использовать метод Select объекта Range). Например, если объект Range представляет диапазон ячеек А1:С5, то результатом выполнения приведенных ниже инструкций будет активизация ячейки С1:

Метод ClearContents

Метод ClearContents очищает формулы и значения, содержащиеся в ячейках, представляемых объектом Range. Данный метод очищает только содержимое ячеек, сохраняя их форматирование.

Метод ClearFormats

Метод ClearFormats удаляет все форматирование ячеек, не воздействуя при этом на хранящиеся в них данные.

Метод Сору

Метод Сору позволяет скопировать содержимое ячеек в буфер обмена.

Метод Delete

Метод Delete удаляет ячейки, определяемые объектом Range. Синтаксис этого метода:

Элементы синтаксиса метода Delete представлены в табл.15.

Элемент Описание
Expression Выражение, возвращающее объект Range
Shift Необязательный параметр типа Variant. Этот параметр задает механизм сдвига ячеек в процессе замены удаленных ячеек. Его значение может равняться значению одной из двух констант: xiShiftToLeft или xlShiftUp. Если аргумент Shift отсутствует, то MS Excel определяет способ сдвига ячеек в зависимости от формы диапазона

Таблица 15. Элементы синтаксиса метода Delete

Метод FillDown

Метод FillDown предназначен для распространения содержимого и форматирования ячейки (или ячеек) из верхней строки диапазона в остальные строки диапазона. Действия, выполняемые данным методом, аналогичны действиям, выполняемым с помощью интерфейса пользователя при распространении содержимого ячейки с использованием мыши.

Ниже представлен пример, который позволяет заполнить все ячейки диапазона С1:С5 содержимым ячейки С1:

Методы FillUp, FillLeft, FillRight

Методы FillUp, FillLeft, FillRight выполняют действия, аналогичные действиям, выполняемым методом FillDown. Отличие состоит в том, что метод FillUp копирует ячейки, содержащиеся в нижней строке диапазона, метод FillLeft копирует ячейки, содержащиеся в крайнем правом столбце диапазона, а метод FillRight – в крайнем левом столбце.

Метод Insert

Метод Insert позволяет вставить ячейку или диапазон ячеек в рабочий лист. При вставке другие ячейки сдвигаются одним из способов, который определяется аргументом Shift. Синтаксис метода Insert:

Элементы синтаксиса метода Insert представлены в табл. 16.

Элемент Описание
Expression Выражение, возвращающее объект Range
Shift Необязательный параметр типа Variant. Предназначен для определения способа сдвига ячеек. Значение данного параметра может быть равно одной из двух констант: xlShiftToRight (ячейки сдвигаются вправо) или xlShiftDown (ячейки сдвигаются вниз).
Если этот аргумент отсутствует, то MS Excel определяет способ сдвига в зависимости от формы диапазона

Таблица 16. Элементы синтаксиса метода Insert

Пример использования метода Insert:

Здесь выполняется добавление новых ячеек в диапазон ячеек A1:D15. При этом ячейки диапазона сдвигаются вниз.

Метод Merge

Метод Merge позволяет объединить несколько ячеек в одну. При этом в качестве объединяемых используются ячейки, определяемые объектом Range. Синтаксис этого метода:

Элементы синтаксиса метода Merge описаны в табл. 17.

Элемент Описание
Expression Выражение, возвращающее объект Range
Across Необязательный параметр типа Variant. Если данный параметр имеет значение True, то ячейки в каждой строке диапазона объединяются как раздельные объединенные ячейки. В противном случае (False) все ячейки диапазона объединяются в одну ячейку

Таблица 17. Элементы синтаксиса метода Merge

Примечание:

Ячейка, получившаяся в результате объединения, содержит значение ячейки, расположенной в левом верхнем углу диапазона.

Метод Select

Метод Select позволяет выделить ячейки, определяемые объектом Range. Ниже в качестве примера приведена инструкция, реализующая выделение диапазона ячеек C1:F5:

Метод UnMerge

Метод UnMerge разбивает объединенную ячейку на отдельные ячейки (чтобы узнать, является ли ячейка результатом объединения нескольких ячеек, используйте свойство MergeCells).

Настоящая заметка продолжает знакомство с VBA, в ней описана работа с диапазонами в VBA.[1]

Рис. 1. Пример, демонстрирующий, как выделять диапазоны различной формы в VBA$ чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Скачать заметку в формате Word или pdf, примеры в архиве (политика безопасности провайдера не позволяет загружать файлы Excel с поддержкой макросов)

Копирование диапазона

Функция записи макросов Excel используется не столько для создания хорошего кода, сколько для поиска названий необходимых объектов, методов и свойств. Например, при записи операции копирования и вставки можно получить код:

Sub Макрос()
Range( " A1 " ).Select
Selection.Copy
Range( " B1 " ).Select
ActiveSheet.Paste
End Sub

Обратите внимание, что данная программа выделяет ячейки. Однако в VBA для работы с объектом не обязательно его выделять. Данную процедуру можно заменить значительно более простой — применить метод Сору, который использует аргумент, представляющий адрес места вставки копируемого диапазона.

Sub CopyRange()
Range( " А1 " ).Copy Range( " В1 " )
End Sub

Предполагается, что рабочий лист является активным и операция выполняется на активном рабочем листе. Чтобы скопировать диапазон на другой рабочий лист или в другую книгу, необходимо задать ссылку:

Sub CopyRange2()
Workbooks( " File1.xlsx " ).Sheets( " Лист1 " ).Range( " A1 " ).Copy _
Workbooks( " File2.xlsx " ).Sheets( " Лист2 " ).Range( " A1 " )
End Sub

Еще одним подходом к решению этой задачи является использование для представления диапазонов объектных переменных:

Sub CopyRange3()
Dim Rngl As Range, Rng2 As Range
Set Rngl = Workbooks( " File1.xlsx " ).Sheets( " Лист1 " ).Range( " A1 " )
Set Rng2 = Workbooks( " File2.xlsx " ).Sheets( " Лист2 " ).Range( " A1 " )
Rngl.Copy Rng2 End Sub

Можно копировать большой диапазон. Адрес места вставки определяется единственной ячейкой (представляющей верхний левый угол вставляемого диапазона):

Sub CopyRange4 ()
Range( " А1:С800 " ).Copy Range( " D1 " )
End Sub

Для перемещения диапазона ячеек вместо метода Сору используется метод Cut.

Если размер копируемого диапазона не известен используется свойство CurrentRegion, возвращающее объект Range, который соответствует прямоугольнику ячеек вокруг заданной ячейки:

Sub CopyCurrentRegion2()
Range( " A1 " ).CurrentRegion.Copy Sheets( " Лист2 " ).Range( " A1 " )
End Sub

Метод End имеет один аргумент, определяющий направление, в котором увеличивается выделение ячеек. Следующий оператор выделяет диапазон от активной ячейки до последней непустой ячейки внизу:

Range (ActiveCell, ActiveCell.End(xlDown)).Select

Три остальные константы имитируют комбинации клавиш при выделении в других направлениях: xlUp (вверх), xlToLeft (влево) и xlToRight (вправо).

В прилагаемом Excel-файле определено несколько распространенных типов выделения ячеек (см. рис. 1). Код любопытен тем, что является также примером создания контекстного меню.

Запрос значения ячейки

Следующая процедура запрашивает значение у пользователя и вставляет его в ячейку А1:

Читайте также:  Значок апострофа на клавиатуре

Sub GetValuel()
Range( " A1 " ).Value = InputBox( " Введите значение " )
End Sub

Однако при выполнении этой процедуры возникает проблема. Если пользователь щелкнет на кнопке Отмена в окне ввода данных, то процедура удалит данные, которые находились в текущей ячейке. Модифицированная версия процедуры адекватно реагирует на щелчок на кнопке Отмена и не выполняет при этом никаких действий:

Sub GetValue2()
Dim UserEntry As Variant
UserEntry = InputBox( " Введите значение " )
If UserEntry <> " " Then Range( " A1 " ).Value = UserEntry
End Sub

Во многих случаях следует проверить правильность данных, введенных пользователем. Например, необходимо обеспечить введение только чисел в диапазоне от 1 до 12 (рис. 2). Это можно сделать при помощи процедуры GetValue3(), код которой приведен в Модуле1 приложенного Excel-файла. Некорректные данные игнорируются, и окно запроса значения отображается снова. Этот цикл будет повторяться, пока пользователь не введет правильное значение или не щелкнет на кнопке Отмена.

Рис. 2. Проверка данных, введенных пользователем

Ввод значения в следующую пустую ячейку

Если требуется ввести значение в следующую пустую ячейку столбца или строки, используйте код (рис. 3):

Sub GetData()
Dim NextRow As Long
Dim Entry1 As String, Entry2 As String
Do
‘ Определение следующей пустой строки
NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
‘ Запрос данных
Entry1 = InputBox( " Введите имя " )
If Entry1 = " " Then Exit Sub
Entry2 = InputBox( " Введите сумму " )
If Entry2 = " " Then Exit Sub
‘ Запись данных
Cells(NextRow, 1) = Entry1
Cells(NextRow, 2) = Entry2
Loop
End Sub

Рис. 3. Макрос вставляет данные в следующую пустую строку рабочего листа

Это бесконечный цикл. Для выхода из него (щелкните на кнопке Cancel) использовались операторы Exit Sub. Обратите внимание строку, в который определяется значение переменной NextRow. Если вам трудно ее понять, проанализируйте содержимое ячейки: перейдите в последнюю ячейку столбца А и нажмите и . После этого будет выделена последняя непустая ячейка в столбце А. Свойство Row возвращает номер этой строки; чтобы получить расположенную под ней строку (следующую пустую строку), к этому номеру прибавляется 1.

Приостановка работы макроса для определения диапазона пользователем

В некоторых ситуациях макрос должен взаимодействовать с пользователем. Например, можно создать макрос, который приостанавливается, когда пользователь указывает диапазон ячеек. Для этого воспользуйтесь функцией Excel InputBox. Не путайте метод Excel InputBox с функцией VBA InputBox. Несмотря на идентичность названий, это далеко не одно и то же.

Процедура, представленная ниже, демонстрирует, как приостановить макрос и разрешить пользователю выбрать ячейку. Затем автоматически формула вставляется в каждую ячейку выделенного диапазона.

Sub GetUserRange()
Dim UserRange As Range
Prompt = " Выберите диапазон для случайных чисел. "
Title = " Выбор диапазона "
‘ Отображение поля ввода
On Error Resume Next
Set UserRange = Application.InputBox( _
Prompt:=Prompt, _
Title:=Title, _
Default:=ActiveCell.Address, _
Type:=8) ‘ Выделение диапазона
On Error GoTo 0
‘ Отменено ли отображение поля ввода?
If UserRange Is Nothing Then
MsgBox " Отменено. "
Else
UserRange.Formula = " =RAND() "
End If
End Sub

Окно ввода данных показано на рис. 4. Важный момент в этой процедуре – определение аргумента Туре равным 8 (в этом случае InputBox вернет диапазон; подробнее см. Application.InputBox Method).

Рис. 4. Использование окна ввода данных с целью приостановки выполнения макроса

Оператор On Error Resume Next игнорирует ошибку, если пользователь не выберет диапазон, а щелкает Отмена. В таком случае объектная переменная UserRange не получает значения. В этом случае отобразится окно сообщения с текстом «Отменено». Если же пользователь щелкнет на кнопке OK, то макрос продолжит выполняться. Строка On Error Go То указывает на переход к стандартной обработке ошибки. Проверка корректного выделения диапазона необязательна. Excel позаботится об этом вместо вас.

Обязательно проверьте, включено ли обновление экрана при использовании метода InputBox для выделения диапазона. Если обновление экрана отключено, вы не сможете выделить рабочий лист. Чтобы проконтролировать обновление экрана, в процессе выполнения макроса используйте свойство ScreenUpdating объекта Application.

Подсчет выделенных ячеек

Работая с макросом, который обрабатывает выделенный диапазон ячеек, можно использовать свойство Count, чтобы определить, сколько ячеек содержится в выделенном (или любом другом) диапазоне. Например, оператор MsgBox Selection.Count демонстрирует окно сообщения, которое отображает количество ячеек в текущем выделенном диапазоне. Свойство Count использует тип данных Long, поэтому наибольшее значение, которое может храниться в нем, равно 2 147 483 647. Если выделить лист целиком, то ячеек будет больше, и свойство Count сгенерирует ошибку. Используйте свойство CountLarge, которое не имеет таких ограничений.

Если активный лист содержит диапазон data, то следующий оператор присваивает количество ячеек в диапазоне data переменной с названием CellCount:

CellCount = Range( " data " ).Count

Вы можете также определить, сколько строк или столбцов содержится в диапазоне. Следующее выражение вычисляет количество столбцов в выделенном диапазоне:

Следующий оператор пересчитывает количество строк в диапазоне с названием data и присваивает это количество переменной RowCount.

RowCount = Range( " data " ).Rows.Count

Просмотр выделенного диапазона

Вы можете столкнуться с трудностями при создании макроса, который оценивает каждую ячейку в диапазоне и выполняет операцию, определенную заданному критерию. Если выделен целый столбец или строка, то работа макроса может занять много времени. Процедура ColorNegative устанавливает красный цвет для ячеек, которые содержат отрицательные значения. Цвет фона для других ячеек не определяется. Код процедуры можно найти в Модуле4 приложенного Excel-файла.

Усовершенствованная процедура ColorNegative2, создает объектную переменную WorkRange типа Range, которая представляет собой пересечение выделенного диапазона и диапазона рабочего листа (рис. 5). Если выделить столбец F (1048576 ячеек), то его пересечение с рабочим диапазоном В2:I16) даст область F2:F16, которая намного меньше исходного выделенного диапазона. Время, затрачиваемое на обработку 15 ячеек, намного меньше времени, уходящего на обработку миллиона ячеек.

Рис. 5. В результате пересечения используемого диапазона и выделенного диапазона рабочего листа уменьшается количество обрабатываемых ячеек

И всё же процедура ColorNegative2 недостаточно эффективна, поскольку обрабатывает все ячейки в диапазоне. Поэтому предлагается процедура ColorNegative3. В ней используется метод SpecialCells, с помощью которого генерируются два поднабора выделенной области: один поднабор (ConstantCells) включает ячейки, которые содержат исключительно числовые константы; второй поднабор (FormulaCells) включает ячейки, содержащие числовые формулы. Обработка ячеек в этих поднаборах осуществляется с помощью двух конструкций For Each-Next. Благодаря тому, что исключается обработка пустых и нетекстовых ячеек, скорость выполнения макроса существенно увеличивается.

Читайте также:  Ремонт ирригатора waterpik своими руками

Sub ColorNegative3()
‘ Окрашивание ячеек с отрицательными значениями в красный цвет
Dim FormulaCells As Range, ConstantCells As Range
Dim cell As Range
If TypeName(Selection) <> " Range " Then Exit Sub
Application.ScreenUpdating = False
‘ Создание поднаборов исходной выделенной области
On Error Resume Next
Set FormulaCells = Selection.SpecialCells(xlFormulas, xlNumbers)
Set ConstantCells = Selection.SpecialCells(xlConstants, xlNumbers)
On Error GoTo 0
‘ Обработка ячеек с формулами
If Not FormulaCells Is Nothing Then
For Each cell In FormulaCells
If cell.Value ‘ Обработка ячеек с константами
If Not ConstantCells Is Nothing Then
For Each cell In ConstantCells
If cell.Value " Пустые строки удалены. "
End Sub

Первый шаг — определить последнюю используемую строку и присвоить этот номер строки переменной LastRow. Это не так просто, как можно ожидать, поскольку текущий диапазон необязательно начинается со строки 1. Следовательно, значение LastRow вычисляется таким образом: к найденному количеству строк используемого диапазона прибавляется номер первой строки текущего диапазона и вычитается 1.

В процедуре применена функция Excel СЧЁТЗ, определяющая, является ли строка пустой. Если данная функция для конкретной строки возвращает 0, то эта строка пустая. Обратите внимание, что процедура просматривает строки снизу вверх и использует отрицательное значение шага в цикле For-Next. Это необходимо, поскольку при удалении все последующие строки перемещаются «вверх» в рабочем листе. Если бы в цикле просмотр выполнялся сверху вниз, то значение счетчика цикла после удаления строки оказалось бы неправильным.

В макросе используется еще одна переменная, Counter, с помощью которой подсчитывается количество удаленных строк. Эта величина отображается в окне сообщения по завершении процедуры.

Дублирование строк

Пример, рассматриваемый в этом разделе, демонстрирует использование возможностей VBA для создания дубликатов строк. На рис. 6 показан пример рабочего листа, используемого организаторами лотереи. В столбце А вводится имя. В столбце В содержится количество лотерейных билетов, приобретенных одним покупателем. В столбце С находится случайное число сгенерированное с помощью функции СЛЧИС. Победитель определяется путем сортировки данных в третьем столбце (выигрыш соответствует наибольшему случайному числу).

Рис. 6. Дублирование строк на основе значений в столбце В

А теперь нужно продублировать строки, в результате чего количество строк для каждого участника лотереи будут соответствовать количеству купленных им билетов. Например, если Барбара приобрела два билета, для нее создаются две строки. Ниже показана процедура, выполняющая вставку новых строк.

Sub DupeRows()
Dim cell As Range
‘ 1-я ячейка, содержащая сведения о количестве билетов
Set cell = Range( " B2 " )
Do While Not IsEmpty(cell)
If cell > 1 Then
Range(cell.Offset(1, 0), cell.Offset(cell.Value _
— 1,0)).EntireRow.Insert
Range(cell, cell.Offset(cell.Value — 1, — 1)). _
EntireRow.FillDown
End If
Set cell = cell.Offset(cell.Value, 0)
Loop
End Sub

Объектная переменная cell была инициализирована ячейкой В2, первой ячейкой, в которой находится числовая величина. Вставка новых строк осуществляется в цикле, а их копирование происходит с помощью метода FillDown. Значение переменной cell увеличивается на единицу, после чего выбирается следующий участник лотереи, Цикл выполняется до тех пор, пока не встретится пустая ячейка. На рис. 7 показан рабочий лист после выполнения этой процедуры.

Рис. 7. В соответствии со значением в столбце В добавлены новые строки

Определение диапазона, находящегося в другом диапазоне

Функция InRange имеет два аргумента, оба — объекты Range. Функция возвращает значение True (Истина), если первый диапазон содержится во втором.

Function InRange(rng1, rng2) As Boolean
‘ Возвращает True, если rng1 является подмножеством rng2
InRange = False
If rng1.Parent.Parent.Name = rng2.Parent.Parent.Name Then
If rng1.Parent.Name = rng2.Parent.Name Then
If Union(rng1, rng2).Address = rng2.Address Then
InRange = True
End If
End If
End If
End Function

Возможно, функция InRange кажется сложнее, чем того требует ситуация, поскольку в коде должна быть реализована проверка принадлежности двух диапазонов одной и той же книге и рабочему листу. Обратите внимание, что в процедуре используется свойство Parent, которое возвращает объект-контейнер заданного объекта. Например, следующее выражение возвращает название листа для объекта rng1:

Следующее выражение возвращает название рабочей книги rng1:

Функция VBA Union возвращает объект Range, который представляет собой объединение двух объектов типа Range. Объединение содержит все ячейки, относящиеся к исходным диапазонам. Если адрес объединения двух диапазонов совпадает с адресом второго диапазона, первый диапазон входит в состав второго диапазона.

Определение типа данных ячейки

В состав Excel входит ряд встроенных функций, которые могут помочь определить тип данных, содержащихся в ячейке. Это функции ЕНЕТЕКСТ, ЕЛОГИЧ и ЕОШИБКА. Кроме того, VBA поддерживает функции IsEmpty, IsDate и IsNumeric.

Ниже описана функция CellType, которая принимает аргумент-диапазон и возвращает строку, описывающую тип данных левой верхней ячейки этого диапазона (рис. 8). Такую функцию можно использовать в формуле рабочего листа или вызвать из другой процедуры VBA.

Рис. 8. Функция CellType, возвращающая тип данных ячейки

Function CellType(Rng)
‘ Возвращает тип ячейки, находящейся в левом верхнем углу диапазона
Dim TheCell As Range
Set TheCell = Rng.Range( " A1 " )
Select Case True
Case IsEmpty(TheCell)
CellType = " Пустая "
Case TheCell.NumberFormat = " @ "
CellType = " Текст "
Case Application.IsText(TheCell)
CellType = " Текст "
Case Application.IsLogical(TheCell)
CellType = " Логический "
Case Application.IsErr(TheCell)
CellType = " Ошибка "
Case IsDate(TheCell)
CellType = " Дата "
Case InStr(1, TheCell.Text, " : " ) <> 0
CellType = " Время "
Case IsNumeric(TheCell)
CellType = " Число "
End Select
End Function

Обратите внимание на использование оператора SetTheCell. Функция CellType получает аргумент-диапазон произвольного размера, но этот оператор указывает, что функция оперирует только левой верхней ячейкой диапазона (представленной переменной TheCell).


Добрый день!

Некоторое время назад меня попросили «помочь с Экселем», а потом и работа подвернулась такая, так что за последние пару месяцев я узнал много полезного, чем и хочу поделиться в догонку к недавней статье.

Предполагается, что вы знаете основы Visual Basic. Я не буду рассказывать, как создавать формы или модули, здесь только примеры кода.

Visual Basic

Опции

Во-первых, в VB массивы могут начинаться с индекса 1, что для многих странно, поэтому в начале модулей можно прописывать:

Так же рекомендуется прописать:

В этом случае интерпретатор потребует заблаговременного объявления всех переменных. Переменные объявлять нужно потому, что:
— VB запомнит их нАпиСание и не будет исправлять во всём коде на последний введенный вариант;
— иногда возникают ошибки с передачей переменных byRef, если они не объявлены (то есть надо или объявить переменную, или приписать в функции/процедуре перед ней byVal).

Читайте также:  Как подключить внешнюю видеокарту к моноблоку

Ещё одним важным оператором является ON ERROR. Привожу варианты:

Возможности языка

Хотя VB довольно прост, полезно почитать документацию по его синтаксису. Я, например, с удивлением узнал, что можно прописывать сложные усолвия в SELECT’ах (аналог switch):

Ускорение работы макросов

Часто макросы требуют долгого времени выполнения, которое можно значительно сократить. В начале и в конце каждой ресурсоёмкой функции вызвать Prepare и Ended.

По порядку:
1. Отключить перерисовку объектов на экране, чтобы ничего не мигало.
2. Выключить расчет. Внимание, если макрос прерваляс посреди работы, то расчет так и останется в ручном режиме!
3. Не обрабатывать события.
4. Отображение границ страниц, тоже почему-то помогает.
5. В статусной строке выводятся различные данные, что замедляет работу, отключаем.
6. Это если нужно. Выключает сообщения Экселя. Например, мы делаем Workbook.Close, Эксель хочет спросить сохранить ли изменения. При выключении этого параметра все ответы будут даны автоматически (изменения не сохранятся).

Важно понимать, что VBA выполняет все действия так же, как и пользователь. Поэтому для того, чтобы установить параметры страницы, он каждый раз открывает и закрывает окно параметров. У меня выставлялись параметры для 10 листов, это реально не быстро. Поэтому делаем так:

Далее, часто нужно просмотреть различные диапазоны ячеек и что-то с ними сделать. Тут важно не использовать циклы for с перебором индексов, они медленные. Можно использовать встроенные функции Экселя, но удобнее всего такой вариант:
Данный код просматривает указанный диапазон, выбирает в нем «специальные ячейки», в данном случае все, в которых есть формулы (т.е. начинаются со знака равно). Для каждой ячейки смотрится, если она не закрашена, то её надо защитить (см. далее) и покрасить. Такой код работает очень быстро.

Для любых переменных, которым вы собираетесь присвоить книгу, лист, диапазон (ячейку) нужно предварительно объявить как Variant.

Естественно, что если вам нужны однотипные значения в ячейках, нужно использовать автозаполнение, всё равно как «растягивание» ячеек пользователем.

Второй диапазон должен включать первый, а второй необязательный параметр указывает тип автозаполнения.

Загрузка книги и события

При открытии книги каждый раз срабатывает процедура.
В данном случае настройки печати (поля, ориентация) сбрасываются на дефолтные. Можно и другую инициализацию выполнять. Важно, что если макросы отключены, то и не выполнится ничего. Если в Экселе вылезла вверху панелька с предупреждением о макросах и пользователь нажал «Включить», то именно в этот момент выполнится процедура Workbook_open().

Список доступных событий можно посмотреть вверху редактора VB. Например, я делал на событие Change проверку, где лежит ячейка, в которой было изменения, и если это нужный диапазон, то делалась запись в лог со старым и новым значением.

Защита

Во-первых сразу отмечу, что MS Office не исполняет макросы на компьютерах, где он не нашел антивируса, если книга зашифрована. Сталкивался на компьютерах, где антивирус был, но видимо Windows XP об этом не знала.

Ещё антивирус может странным образом мешать работе, вызывать ошибки, не совсем объяснимые. Показал айтишникам, сказали ок, что-то сделали, не знаю.

Итак, нам надо защитить книгу, чтобы ввод был разрешен только в нужные ячейки (формулы и заголовки поменять нельзя). Во-первых, нужно сделать соответствующие ячейки «не защищенными». Для этого делаем одно из:
— выделяем диапазон, формат ячеек, снять галочку «Блокировать ячейку»;
— выводим кнопку «Блокировать ячейку» в быстрый доступ и нажимаем её, очень удобно смотреть на неё чтобы понять, защищена ячейка или же нет;
— а это пригодится, чтобы проверить третий вариант — написать макрос, который снимает защиту с нужных ячеек сам.

Далее нужно защитить лист. На вкладке Рецензирование есть такая кнопка. Окошко просит ввести пароль и установить исключения (что можно будет делать пользователю). К сожалению, список исключений маловат. Самое обидное, что нельзя разрешить сворачивать/разворачивать группы столбцов/строк. Поэтому действуем так, на загрузку книги прописываем:
Знак подчеркивания продолжает логическую строку на следующей физической строке. Итак, здесь мы:
1. Сняли защиту.
2. Включили группировку.
3. Поставили защиту, при этом:
— защита только от юзера, макросы продолжают иметь полный доступ (!), крайне важно;
— разрешили сортировку, фильтрацию и форматирование строк/столбцов (высота/ширина);
— DrawingObject в данном случае снимает защиту с примечаний к ячейкам, может и ещё с чего.

Тут мы сталкиваемся с парой сюрпризов. Во-первых, не все макросы будут работать даже так. Известный баг, ничего не сделаешь. Нельзя вставить строку, например. Приходится снимать и тут же ставить защиту. Если «злоумышленник» в этот момент нажмет ctrl+break, то защита слетит.

Во-вторых, скажем никаким способом нельзя удалять строки (AllowDeletingRows), в которых есть защищенные ячейки, хоть одна. Подробнее вот тут.

Решением (костылем) является добавление кнопки или сочетания клавиш для удаления. Заодно можно проверить, чтобы пользователь не удалил чего не надо. В Workbook_open добавляем:

Теперь процедура будет вызываться при нажатии shift+delete.
Знаю, код некрасивый, простите. Здесь я пытался проверить, что выделена строка, то есть строк там 1, а ячеек не меньше тысячи. Чтобы удалить не то, придется выделить тысячу ячеек начиная не с первого столбца. Далее проверяется имя листа и номера строк. Вместо 50 был расчет последенй строки (ведь их число меняется, если мы их удаляем и добавляем).

Заключение

VBA — весьма глючная вещь, которая позволяет сворачивать горы в MS Office. Многие предприятяи используют модели на Excel годами, и если они сделаны хорошо, то всё работает.

Для изучения VBA подходит он сам, во-первых там хорошая справка. Например, чтобы узнать все варианты что можно разрешить в методе Protect, нажимаем F1, Protect, ввод. И вуаля.

Во-вторых, можно проделать требуемые действия вручную, записав макрос, а потом просмотрев его код. Код будет ужасен (например, при изменении параметров страницы, макрос запишет значения всех параметров и полей, а не только измененного вами), но ответы найдутся. Хотя, например, .AutoFit, который записывается при изменении высоты ячейки по содержимому (двойной клик на границе слева), на самом деле не работает.

Предлагаю знатокам поделиться своим опытом, дать советы в комментариях. Спасибо за внимание, удачных разработок вам.

Ссылка на основную публикацию
Asus k56c установка ssd
Запись сделана для тех, кто собирается делать аналогичный апгрейд своего Asus N56VZ, но безболезненно и без танцев с бубнами. А...
Adblock detector