Вставить только значения vba excel

Обновлено: 17.05.2024

На этом шаге мы рассмотрим некоторые особенности работы с кодом VBA .

Несмотря на то, что журнал формируется относительно просто, наличие в нем формул может вызвать его "сбой" при случайном изменении или удалении одной из формул, сортировке списка. Кроме того, пользователю необходимо постоянно вводить дату проведения операции. Устраним эти недостатки.

Для этого разработайте последовательность ввода данных. Пример будет описан на примере заполнения строки 16 по вводу данных расходного ордера №4.

Главное условие - автоматизация этого процесса должна уменьшить трудоемкость операции ввода данных и уменьшить возможность ввода ошибки. Далее предполагаем, что заполнение журнала и формирование ордеров происходит не задним числом, а день в день.

Приемы автоматизации заполнения журнала кассовых документов

запись макросов ввода отдельных формул и последующая замена формул на определенные ими значения;
создание кода VBA поиска первой пустой строки в списке и перемещения по этой строке;
соединение всего кода VBA созданных макросов в одну подпрограмму, выполняющую всю последовательность действий в автоматическом режиме.

Изменение параметров Excel перед записью макросов

Завершить ввод данных в ячейки, если это не редактирование их содержимого, можно нажатием на самые различные клавиши: клавиши перемещения на одну ячейку (вниз, вверх, влево, вправо), Home, End, Page Up или Page Down . Самый же классический способ завершения ввода данных в Excel - нажатие на клавишу Enter , после чего, как правило, табличный курсор перемещается на ячейку ниже. И это довольно удобно. Большинство пользователей полагают, что это неизменяемое свойство Excel . На самом же деле этот параметр устанавливается при инсталляции Excel по умолчанию и при желании может быть изменен.

И если при работе по вводу данных непосредственно на рабочем листе, перемещение табличного курсора на ячейку ниже после фиксации ввода клавишей Enter - удобство, то при записи макроса - недостаток.

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

В последующем, при выполнении записанного макроса, эта операция будет выполняться, как один из переходов на зафиксированную ячейку. Это увеличивает продолжительность выполнения макроса и "утяжеляет" файл Excel на количество символов этой строки. А при редактировании кода макроса в Редакторе Microsoft Visual Basic будет потрачено время на удаление этих строк.

Поэтому, прежде чем записывать макросы, связанные с операциями ввода данных, с помощью команды Сервис | Параметры откройте диалоговое окно Параметры и на вкладке Правка (рисунок 1) удалите флажок с опции Переход к другой ячейке после ввода .

Рис.1. Диалоговое окно Параметры , вкладка Правка с открытым раскрывающимся списком В направлении при активизированной опции Переход к другой ячейке после ввода

Иначе при записи макроса, перемещение табличного курсора после нажатия на клавишу Enter на ячейку, по заданному этой опцией в направлении, выбранном в раскрывающемся списке В направлении , будет отражено в сгенерированном коде макроса.

Запись кода VBA при вводе формул в процессе записи макроса

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

Рис.2. Последовательность предстоящих операций, подлежащих автоматизации при формировании записи расходного кассового ордера в журнале

Автоматизация ввода текущей даты

Согласно правил заполнения журнала первым действием должен быть ввод даты проведения этой операции. Если операция заполнения журнала производится в день ее выполнения, то для автоматического ввода даты достаточно ввести в ячейку F16 функцию ввода текущей даты. Для этого используется функция СЕГОДНЯ (рисунок 3), которую можно ввести либо с помощью кнопки Мастер функций на стандартной панели инструментов, либо с клавиатуры:

Рис.3. Панель функции СЕГОДНЯ

Функция СЕГОДНЯ не имеет аргументов и возвращает текущую дату.

введите в ячейку В16 функцию СЕГОДНЯ и не перемещайте табличный курсор;
выполните процедуру начала записи макроса, которому присвойте имя РасходныйОрдер и при необходимости введите его описание;
запись макроса заключается в последовательном нажатии на клавишу F2 (редактирование содержимого ячейки) и клавишу Enter ;
произведите остановку записи макроса.

Рис.4. Окно программы с кодом макроса РасходныйОрдер

При записи макроса РасходныйОрдер режим перехода на ячейку вниз отключен не был (рисунок 1). Поэтому на рисунке 4 вы видите вторую строку кода:

которую удалите при редактировании. В этой строке находится объект 4-го уровня иерархии - Range . Этот объект используется для ссылок на ячейку или диапазон ячеек. смысл которой заключается в том, что в активную ячейку (в которой находился до начала записи макроса табличный курсор) ввести формулу ввода текущей даты. Следует заметить, что все функции в коде VBA записываются на английском языке, а вводимые формулы заключаются в кавычки.

Запись кода формул определения порядкового номера и замены строки с формулами на значения

Запись макросов Макрос2 и Макрос3 осуществляется аналогично - сначала вводятся формулы в ячейку А16 :

после чего осуществляется запись кода этих формул, как и в предыдущем макросе (рисунок 5).

Рис.5. Окно программы с кодом макросов Макрос2 , Макрос3 и Макрос4

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

установите курсор в любую пустую ячейку, и выполните команду Правка | Копировать (или комбинация клавиш Ctrl+С );
выполните команду Правка | Специальная вставка и в появившемся диалоговом окне Специальная вставка активизируйте переключатель Значения , после чего нажмите кнопку ОК ;
остановите запись макроса.

Метод Специальная вставка

Проанализируем записанный код VBA при вставке из буфера обмена скопированной области через диалоговое окно Специальная вставка :

PasteSpecial - метод диалогового окна Специальная вставка .

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

xlAll - все;
xlFormulas - формулы;
xlValues - значения;
xlFormats - форматы.

xlNone - нет;
xlAdd - сложить;
xlSubtract - вычесть;
xlMultiply - умножить;
xlDivide - разделить.

Аргументам SkipBlanks ( Пропускать пустые ячейки ) и Transpose ( Транспонировать ) могут присваиваться два значения False ( Ложь ) и True ( Истина ) .

В связи с тем, что задача Макрос4 производит только вставку значений, удалите ненужный код VBA (рисунок 8).

Соединение макросов

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

Рис.6. Последовательность действий полного макроса РасходныйОрдер

перенос написанных макросов Макрос2 , Макрос3 и Макрос4 в один макрос РасходныйОрдер в той последовательности, в которой они записывались;
редактирование полученного макроса РасходныйОрдер и добавления процедур с целью последовательного выполнения операций показанных на рисунке 6;
ввод примечаний.

Для соединения макросов в один примените метод копирования. Для этого в окне Редактора Visual Basic выделите область от конца последнего символа вверх кода VBA , включая первый встречающийся знак апострофа, как это показано на рисунке 7.

Рис.7. Выделение фрагмента макроса для копирования и вставки в другой макрос

После соединения всех макросов получился макрос, показанный на рисунке 8.

Рис.8. Макрос, полученный в результате соединения четырех макросов

Но данный макрос "работать" не будет, потому что он произведет вставку всех формул в одной и той же выделенной ячейке, которая перед выполнением макроса была активна.

Абсолютная и относительная ссылки при выполнении макроса

Как правило, при механической записи макросов, Excel выполняет абсолютную запись, то есть сохраняет точные адреса ячеек при их активизации. В свою очередь "точный" адрес ячейки ведет свой отсчет от левого верхнего угла рабочего листа. Например, адрес ячейки В3 - (3,2) или пересечение третьей строки и второго столбца.

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

После выполнения записанной в Макрос1 операции с активной ячейкой F16 , на которую предварительно устанавливается табличный курсор перед запуском макроса на выполнение, необходимо перейти на ячейку Е16 для проведения операции записанной в Макрос2 . Для этого задайте относительную ссылку перехода на эту ячейку. При вводе метода Select записанная строка означает команду - передвинуться от активной ячейки на 1 столбец влево и на 0 строк вниз (вверх) и выделить (активизировать) ее. Код VBA операции записывается так:

в которой знак минус указывает, что необходимо передвинуться на один столбец влево, а значение 0 - передвинуться на ноль значений вниз и считать эту ячейку активной (ActiveCell) и выделенной (Select) . Эту строку введите перед фрагментом, скопированным из Макрос2 .

Для выполнения фрагмента из Макрос3 необходимо передвинуться на 4 столбца влево:

Перед выполнением фрагмента из Макрос4 необходимо не только добавить относительный переход, но и выделить диапазон следующим образом:

Если макрос записывается в относительном режиме, то предполагается, что ячейка, в которой находится табличный курсор, имеет адрес А1 , а указанный адрес ячейки (или диапазона) после относительного перехода, принимается по отношению к этой ячейке А1 , поэтому если указать адрес А1:F1 , то будет выделено шесть ячеек вправо, включая и активную ячейку, в которой находился табличный курсор. Или в нашем примере - ячейки А16:F16 .

И последний элемент выполнения подпрограммы - переход на ячейку G16 для ввода суммы приходного ордера. Следовательно, надо задать команду перехода от активной ячейки на 6 ячеек вправо:

Методы ввода кода VBA

Запись кода VBA можно производить только с клавиатуры, а можно и с помощью диалогового окна Просмотр объектов (рисунок 9), которое активизируется командой Вид | Просмотр объектов или нажатием на клавишу F2 .

Рис.9. Приложение Редактор Microsoft Visual Basic с открытым диалоговым окном Просмотр объектов в правом верхнем углу

При помощи окна Просмотр объектов можно просматривать в объектных библиотеках объекты, классы, методы, свойства, события, константы и функции. При помощи этого диалогового окна удобно использовать справочную систему Microsoft Visual Basic . Для этого выделите интересующий объект и нажмите на клавишу F1 .

в раскрывающемся списке Проект | Библиотека выберите библиотеку Excel ;
в окне Компонент выделите объект ActiveCell и скопируйте в буфер обмена;
перейдите в окно программы и, установив курсор в теле подпрограммы, произведите вставку скопированного;
после ввода команды Точка появится список свойств и методов, которые могут быть использованы для дальнейшего написания кода для этого объекта. С помощью полосы прокрутки найдите нужное свойство или метод. Поиск можно ускорить при вводе после точки первых символов кода. Для ввода названия свойства или метода в подпрограмму дважды щелкните по нему правой кнопкой мыши и т.д.

при выполнении команды Правка | Список свойств/методов ;
при помощи контекстного меню;
комбинации клавиш Ctrl+J ;
нажатии на кнопку Список свойств/методов на панели инструментов Правка (рисунок 10).

Рис.10. Панель инструментов Правка

нажатии на кнопку Завершить слово на панели инструментов Правка (рисунок 10);
выполнении команды Правка | Завершить слово ;
используя контекстное меню;
комбинации клавиш Ctrl+Space .

Примечания

И последний элемент редактирования подпрограммы - добавление примечаний для описания действий выполняемых подпрограммой. Примечания не являются командами и при выполнении макроса игнорируются.

поместите курсор в начало строки и введите знак апострофа с клавиатуры;
поместите курсор на строку и выделите блок строк, после чего нажмите на кнопку Закомментировать блок на панели инструментов Правка (рисунок 10).

Для того чтобы убрать знак комментария, удалите знак апострофа, используя клавиши Delete или Backspace , или выделив строку (блок строк) нажмите кнопку Раскомментировать блок на панели инструментов Правка .

В приложении Excel все данные как правило находятся в ячейках на листах, с которыми макросы работают как с базой данных. Поэтому, начинающему программисту VBA важно понимать как читать значения из ячейки Excel в переменные или массивы и, наоборот, записывать какие-либо значения на лист в ячейки.

Обращение к конкретной ячейке

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

Полный путь к ячейке A1 в Книге1 на Листе1 можно записать двумя вариантами:

Пример 1: Обратиться к ячейке A3 находящейся в Книге1 на Листе1

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

Пример 2: Обратиться к ячейке A1 в текущей книге на активном листе

Если всё же путь к книге или листу необходим, но не хочется его писать при каждом обращении к ячейкам, можно использовать конструкцию With End With. При этом, обращаясь к ячейкам, необходимо использовать в начале "." (точку).

Пример 3: Обратиться к ячейке A1 и B1 в Книге1 на Листе2.

Так же, можно обратиться и к активной (выбранной в данный момент времени) ячейке.

Пример 4: Обратиться к активной ячейке на Листе3 текущей книги.

Чтение значения из ячейки

Есть 3 способа получения значения ячейки, каждый из которых имеет свои особенности:

По-умолчанию, если при обращении к ячейке не указывать способ чтения значения, то используется способ Value.

Пример 5: В ячейке A1 активного листа находится дата 01.03.2018. Для ячейки выбран формат "14 марта 2001 г.". Необходимо прочитать значение ячейки всеми перечисленными выше способами и отобразить в диалоговом окне.

Пример 6: В ячейке С1 активного листа находится значение 123,456789. Для ячейки выбран формат "Денежный" с 3 десятичными знаками. Необходимо прочитать значение ячейки всеми перечисленными выше способами и отобразить в диалоговом окне.

При присвоении значения переменной или элементу массива, необходимо учитывать тип переменной. Например, если оператором Dim задан тип Integer, а в ячейке находится текст, при выполнении произойдет ошибка "Type mismatch". Как определить тип значения в ячейке, рассказано в следующей статье.

Пример 7: В ячейке B1 активного листа находится текст. Прочитать значение ячейки в переменную.

Таким образом, разница между Text, Value и Value2 в способе получения значения. Очевидно, что Value2 наиболее предпочтителен, но при преобразовании даты в текст (например, чтобы показать значение пользователю), нужно использовать функцию Format.

Запись значения в ячейку

Осуществить запись значения в ячейку можно 2 способами: с помощью Value и Value2. Использование Text для записи значения не возможно, т.к. это свойство только для чтения.

Пример 8: Записать в ячейку A1 активного листа значение 123,45

Все три строки запишут в A1 одно и то же значение.

Пример 9: Записать в ячейку A2 активного листа дату 1 марта 2018 года

В данном примере тоже запишется одно и то же значение в ячейку A2 активного листа.

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

Подобно функции листа в Excel, мы также можем копировать данные вставки или набор значений в VBA. В обычных данных мы используем CTRL + C, чтобы скопировать выборку данных, а затем CTRL + V, чтобы вставить выбранные данные в целевую ячейку. Но то же самое в VBA делают определенные коды, которые мы узнаем в этой статье.

Как мы используем копирование и вставку в Excel VBA? Для этого сначала нам нужно активировать целевой лист или рабочую книгу, из которой мы хотим скопировать любой диапазон данных, и этот выбор копируется определенным кодом, когда этот выбор успешно скопирован, мы можем продолжить и вставить данные в целевой диапазон ячеек.

Синтаксис для копирования данных в VBA выглядит следующим образом:

Теперь синтаксис для вставки данных в другой лист или в любой другой диапазон ячеек выглядит следующим образом:

Мы используем оператор Dot (.) Для использования методов копирования и вставки в VBA.

Мы можем скопировать весь столбец и вставить его в другой столбец, а также мы также можем скопировать всю строку и вставить ее в другую строку. Мы узнаем все это в этой статье.

Примечание. Чтобы использовать VBA в Excel, убедитесь, что вкладка «Разработчик» включена из вкладки «Файл», а затем в раздел настроек параметров.

Как использовать Excel VBA Copy Paste?

Мы научимся использовать VBA Copy Paste с несколькими примерами в Excel.

Вы можете скачать этот шаблон VBA Copy Paste Excel здесь - Шаблон VBA Copy Paste Excel

VBA Copy Paste - Пример № 1

Для демонстрации у меня есть случайное значение в ячейке A1, которое я хочу скопировать и вставить в ячейку B1, используя код VBA. Посмотрите, какие данные находятся в ячейке A1, а ячейка B1 пуста.

Выполните следующие шаги, чтобы использовать Excel VBA Copy Paste:

Шаг 1: Перейдите на вкладку разработчика и нажмите на Visual Basic, чтобы открыть VB Editor.

Шаг 2: Как только редактор VB откроется, нажмите на кнопку вставки, а затем на кнопку Вставить модуль, чтобы вставить окно кода.

Шаг 3: Объявите подфункцию, чтобы начать писать код.

Код:

Шаг 4. Сначала активируйте лист, чтобы использовать свойства листа с помощью следующего кода.

Код:

Шаг 5: Скопируйте данные, которые находятся в ячейке A1, с помощью следующего кода.

Код:

Шаг 6: Теперь вставьте скопированные данные в целевую ячейку, которая является ячейкой B1, с помощью следующего кода.

Код:

Шаг 7: Запустите приведенный выше код с помощью кнопки запуска и посмотрите результат в ячейке B1.

VBA Copy Paste - Пример №2

У меня есть данные в столбце C, и я хочу скопировать все данные или значения и вставить их в столбец D, используя код VBA. Посмотрите ниже, что это за данные в столбце C, и что столбец D пуст.

Выполните следующие шаги, чтобы использовать Excel VBA Copy Paste:

Шаг 1: Перейдите на вкладку разработчика и нажмите на Visual Basic, чтобы открыть VB Editor.

Шаг 2: Нажмите на вставленный модуль, чтобы открыть окно кода,

Шаг 3: Объявите подфункцию, чтобы начать писать код.

Код:

Шаг 4. Сначала активируйте рабочий лист с помощью следующего кода.

Код:

Шаг 5: Скопируйте данные в столбце C с помощью следующего кода.

Код:

Шаг 6: Теперь, чтобы вставить данные в столбец D, используйте следующий код.

Код:

Шаг 7: Запустите следующий код с помощью кнопки запуска или нажмите F5.

Запустите код, чтобы увидеть следующий результат.

VBA Copy Paste - Пример № 3

Теперь для этого примера у меня есть целый диапазон данных в диапазоне ячеек G1: H3, и я хочу скопировать данные в диапазоне ячеек I1: J3. Посмотрите ниже на данные, которые у меня есть в диапазоне ячеек G1: H3, а диапазон ячеек I1: J3 пуст.

Выполните следующие шаги, чтобы использовать Excel VBA Copy Paste:

Шаг 1: Перейдите на вкладку разработчика и нажмите на Visual Basic, чтобы открыть VB Editor.

Шаг 2: Нажмите на вставленный модуль, чтобы открыть окно кода,

Шаг 3: Объявите подфункцию, чтобы начать писать код.

Код:

Шаг 4. Сначала активируйте лист, чтобы использовать его свойства с помощью следующего кода.

Код:

Шаг 5: Скопируйте данные в целевой диапазон ячеек с помощью следующего кода.

Код:

Шаг 6: Теперь, чтобы вставить данные в ячейку назначения, используйте следующий код.

Код:

Шаг 7: Запустите приведенный выше код с помощью кнопки запуска или нажмите F5, чтобы увидеть следующий результат.

VBA Copy Paste - пример № 4

Для этого примера у меня есть данные в строке 10, и я хочу вставить данные в строке 11. Посмотрите ниже, чтобы увидеть, какие данные находятся в строке 10, а строка 11 свободна.

Выполните следующие шаги, чтобы использовать Excel VBA Copy Paste:

Шаг 1: Перейдите на вкладку разработчика и нажмите на Visual Basic, чтобы открыть VB Editor.

Шаг 2: Нажмите на вставленный модуль, чтобы открыть окно кода,

Шаг 3: Объявите подфункцию, чтобы начать писать код.

Код:

Шаг 4. Активируйте рабочий лист, чтобы использовать свойства рабочего листа.

Код:

Шаг 5: Скопируйте строку 10 со следующим кодом.

Код:

Шаг 6: Вставьте данные строки 10 в строку 11 с помощью следующего кода.

Код:

Шаг 7: Запустите приведенный выше код, нажав F5, чтобы увидеть следующий результат.

То, что нужно запомнить

Чтобы использовать данные из любого листа для их копирования, нам нужно сначала активировать лист.
Точно так же, когда нам нужно вставить данные в любой другой лист в VBA, нам нужно сначала активировать целевой лист.
Если мы копируем весь столбец или строку и вставляем данные в любой другой столбец, тогда данные в любом месте строки или столбца в целевых ячейках копируются и вставляются в целевые ячейки. Это может привести к определенным нежелательным данным.
Лучший способ скопировать данные - скопировать определенный диапазон и вставить данные в целевые ячейки.

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

Следующая процедура запрашивает значение у пользователя и вставляет его в ячейку А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. Если вам трудно ее понять, проанализируйте содержимое ячейки: перейдите в последнюю ячейку столбца А и нажмите <End> и <↑>. После этого будет выделена последняя непустая ячейка в столбце А. Свойство 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. Использование окна ввода данных с целью приостановки выполнения макроса

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

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

Если активный лист содержит диапазон 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. Благодаря тому, что исключается обработка пустых и нетекстовых ячеек, скорость выполнения макроса существенно увеличивается.

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 < 0 Then
cell.Interior.Color = RGB(255, 0, 0)
Else
cell.Interior.Color = xlNone
End If
Next cell
End If
' Обработка ячеек с константами
If Not ConstantCells Is Nothing Then
For Each cell In ConstantCells
If cell.Value < 0 Then
cell.Interior.Color = RGB(255, 0, 0)
Else
cell.Interior.Color = xlNone
End If
Next cell
End If
End Sub

Оператор On Error необходим, поскольку метод SpecialCells генерирует ошибку, если не находит в диапазоне ячеек указанного типа.

Удаление всех пустых строк

Следующая процедура удаляет все пустые строки в активном рабочем листе. Она достаточно эффективна, так как не проверяет все без исключения строки, а просматривает только строки в так называемом «используемом диапазоне», определяемом с помощью свойства UsedRange объекта Worksheet.

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

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

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

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

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

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

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

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

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

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

Возможно, функция 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).

Читайте также:

Вставить только значения vba excel

Приемы автоматизации заполнения журнала кассовых документов

Изменение параметров Excel перед записью макросов

Запись кода VBA при вводе формул в процессе записи макроса

Автоматизация ввода текущей даты

Запись кода формул определения порядкового номера и замены строки с формулами на значения

Метод Специальная вставка

Соединение макросов

Абсолютная и относительная ссылки при выполнении макроса

Методы ввода кода VBA

Примечания

Обращение к конкретной ячейке

Чтение значения из ячейки

Запись значения в ячейку

Как использовать Excel VBA Copy Paste?

VBA Copy Paste - Пример № 1

VBA Copy Paste - Пример №2

VBA Copy Paste - Пример № 3

VBA Copy Paste - пример № 4

То, что нужно запомнить

Рекомендуемые статьи

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

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

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

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

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

Удаление всех пустых строк

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

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

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