Метод pastespecial vba excel

Обновлено: 05.01.2025

На этом шаге мы рассмотрим некоторые особенности работы с кодом 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 , или выделив строку (блок строк) нажмите кнопку Раскомментировать блок на панели инструментов Правка .

Pastes a Range object that has been copied into the specified range.

Syntax

expression.PasteSpecial (Paste, Operation, SkipBlanks, Transpose)

expression A variable that represents a Range object.

Parameters

Name	Required/Optional	Data type	Description
Paste	Optional	XlPasteType	The part of the range to be pasted, such as xlPasteAll or xlPasteValues.
Operation	Optional	XlPasteSpecialOperation	The paste operation, such as xlPasteSpecialOperationAdd.
SkipBlanks	Optional	Variant	True to have blank cells in the range on the clipboard not be pasted into the destination range. The default value is False.
Transpose	Optional	Variant	True to transpose rows and columns when the range is pasted. The default value is False.

Return value

Example

This example replaces the data in cells D1:D5 on Sheet1 with the sum of the existing contents and cells C1:C5 on Sheet1.

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Вклеит объект Range, который был скопирован в указанный диапазон.

Синтаксис

выражения. PasteSpecial (вастер, операция, SkipBlanks, Transpose)

выражение: переменная, представляющая объект Range.

Параметры

Имя	Обязательный или необязательный	Тип данных	Описание
Paste	Необязательный	XlPasteType	Часть диапазона, который необходимо вклеить, например xlPasteAll или xlPasteValues.
Операция	Необязательный	XlPasteSpecialOperation	Операция вклейки, например xlPasteSpecialOperationAdd.
SkipBlanks	Необязательный	Variant	Правда, чтобы пустые ячейки в диапазоне на буфере обмена не вклеились в диапазон назначения. Значение по умолчанию — False.
Transpose	Необязательный	Variant	True для перезаповки строк и столбцов при вклеивании диапазона. Значение по умолчанию — False.

Возвращаемое значение

Пример

В этом примере данные в ячейках D1:D5 на листе1 заменяют сумму существующего содержимого и ячеек C1:C5 на sheet1.

Поддержка и обратная связь

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.

Вклеить содержимое буфера обмена на лист с помощью указанного формата. С помощью этого метода вклеить данные из других приложений или вклеить данные в определенном формате.

Синтаксис

выражения. PasteSpecial (Формат, Ссылка, DisplayAsIcon, IconFileName, IconIndex, IconLabel, NoHTMLFormatting)

выражение Переменная, представляюная объект "Таблица".

Параметры

Имя	Обязательный или необязательный	Тип данных	Описание
Format	Необязательный	Variant	Строка, которая указывает формат буфера обмена данными.
Ссылка	Необязательный	Variant	True, чтобы установить ссылку на источник вклеит данных. Если исходные данные не подходят для ссылок или исходные приложения не поддерживают ссылки, этот параметр игнорируется. Значение по умолчанию — False.
DisplayAsIcon	Необязательный	Variant	True для отображения вклеить данные в качестве значка. Значение по умолчанию — False.
IconFileName	Необязательный	Variant	Имя файла, который содержит значок для использования, если DisplayAsIcon является true.
IconIndex	Необязательный	Variant	Номер индекса значка в файле значок.
IconLabel	Необязательный	Variant	Текстовая метка значка.
NoHTMLFormatting	Необязательный	Variant	True, чтобы удалить все форматирование, гиперссылки и изображения из HTML. False для вклеить HTML как есть. Значение по умолчанию — False.

Заметки

NoHTMLFormatting имеет значение только при формате = "HTML"; во всех остальных случаях NoHTMLFormatting игнорируется.

Перед использованием этого метода необходимо выбрать диапазон назначения.

Этот метод может изменять выбор листа в зависимости от содержимого буфера обмена.

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

Аргумент Format	Эквивалент строки
0	"Изображение (PNG)"
1	"Изображение (JPEG)"
2	"Изображение (GIF)"
3	"Picture (Enhanced Metafile)"
4	"Bitmap"
5	"Microsoft Office объект рисования"

Пример

В этом примере в Microsoft Word объекта документа из буфера обмена данными в ячейку D1 на листе1.

В этом примере вклейка объекта изображения не отображается в качестве значка.

Поддержка и обратная связь

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