Параметры выбора макроса в excel

Обновлено: 04.07.2024

Установка переменной диапазона выполняется с помощью процедуры, которая возвращает диапазон. Диапазоны являются ядром Excel, VBA предоставляет множество таких процедур. Основные из них обсуждаются здесь.

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

Использование методов Range

Application.Range — общее использование

Приведенный ниже код возвращает [Book2]Sheet2!$A$3:$B$5 в окне интерпретации. Он показывает, что вы можете указать любой диапазон в открытой книге на любом листе.

Если в приведенном выше примере вы пропустите рабочий лист или рабочую книгу, метод Application.Range будет считаться активным:

Application.Range("Лист2!A3:B5") — в активной книге
Application.Range("A3:B5") — в активной книге

Аргумент Cell1 требует имени диапазона. Это может быть объект Range, содержащий одну ячейку, целый столбец или всю строку, или это может быть строка, которая называет одну ячейку на языке макроса.

Примечание
Если рабочая книга или рабочий лист отсутствуют, запуск кода выдаст ошибку времени выполнения 1004 «Определенное приложением или объектом- определенная ошибка”

Диапазон – верхний левый и нижний правый

Диапазон имеет два аргумента, которые используются для указания области на листе. В предыдущем разделе использовалась только Cell1. Если также используется необязательный аргумент Cell2, Cell1 — это ячейка в левом верхнем углу, а Cell2 — в правом нижнем углу диапазона.

Выберите диапазон в диалоговом окне выбора диапазона

Когда вам нужно указать диапазон, проще всего использовать диалоговое окно выбора диапазона Code VBA. При открытии отображается только часть диапазона $B$2. Вы можете включить имя листа Data! путем явного переключения на этот лист.

Рабочий лист.Диапазон

В предыдущем разделе свойство .Range вызывалось из объекта Application. При написании бизнес-кода, как правило, лучше указать, в какой книге и на каком листе вы работаете. Обычно они уже доступны как переменные в вашем коде. В приведенном ниже примере используется именованный диапазон «Компания», что делает более понятным, какие данные содержатся в этом диапазоне.

UsedRange возвращает объект Range, представляющий область рабочего листа. Подробнее . - используется в широком смысле, включая данные, форматирование и другие виды использования.

Установить диапазон из другого диапазона

Учитывая, что объект-диапазон является наиболее точным указанием того, с чего начать поиск, существует довольно много методов, использующих его для получения нового диапазона.

Диапазон.Смещение

Установите диапазон с позицией относительно начального диапазона. Положительные значения смещаются вниз, а отрицательные значения смещаются вверх. Например, приведенный ниже код выводит $D$3. На 2 строки вниз (RowOffset) и на 3 столбца вправо (ColumnOffset) от начальной позиции A1.

Диапазон.Изменить размер

Range.Resize изменяет размер указанного диапазона. RowSize указывает количество строк в новом диапазоне, ColumnSize количество столбцов. Если какой-либо из аргументов опущен, количество строк или столбцов в диапазоне остается прежним.

Используйте «Изменить размер», чтобы удалить верхнюю строку

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

Если изменить размер на одну строку меньше, Rows.Count - 1 вернет текущий диапазон за вычетом нижней строки. Вот почему в приведенном выше коде сначала диапазон берется на 1 строку ниже rng.Offset(1, 0) .

Выделение на активном листе или в окне

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

Тип возвращаемого объекта зависит от текущего выбора. Например, если выбрана ячейка, это свойство возвращает объект Range, но если выбрана прямоугольная форма, возвращается объект типа Rectange. Если это возможный сценарий, возможны два подхода, которые обсуждаются в следующих разделах.

Обработка ошибки 13: несоответствие типов

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

Проверить, имеет ли выбор правильный тип объекта

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

Пересечение двух или более диапазонов

Application.Intersect возвращает объект Range, представляющий пересечение двух или более диапазонов. В приведенном ниже примере адрес rngIntersect — B2:C3. Если диапазоны не пересекаются, rngIntersect Is Nothing

Примечание
Если указан один или несколько диапазонов из другого рабочего листа, ошибка времени выполнения "1004": метод "Пересечение ' объекта '_Application' будет возвращена ошибка.

Объединение двух или более диапазонов.

Application.Union возвращает объединение двух или более диапазонов. В приведенном ниже примере адресом rngUnion для A1:C3 и C3:D4 является A1:C4, а счетчик равен 12, что предполагает, что диапазон можно использовать, как и ожидалось, при переборе ячеек, даже если третья строка перекрывается.

Если область не может быть красиво объединена в 1 прямоугольник, адрес становится составным. Объединение A1:C3 и B2:D4 имеет адрес A1:C3,B2:D4 и количество равно 18. Это показывает, что ячейки в перекрытии интерпретируются как встречающиеся дважды!

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

Создание функции без аргументов

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

Возможно, вы захотите создать функцию, которая при каждом вызове возвращает статическое значение — что-то вроде константы.

Если бы вы запускали функцию, она всегда возвращала бы значение 50.

Вы также можете создавать функции, которые ссылаются на объекты в VBA, но вам нужно использовать ключевое слово Set, чтобы вернуть значение из функции.

Если бы вы использовали приведенную выше функцию в своем коде VBA, функция всегда возвращала бы диапазон ячеек от A1 до G4 на любом листе, с которым вы работаете.

Вызов функции из подпроцедуры

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

Всегда будет возвращаться значение 50.

Вы также можете вызвать функцию GetRange из подпроцедуры.

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

Создание функций

Один аргумент

Вы также можете назначить параметр или параметры своей функции. Эти параметры можно назвать аргументами.

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

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

В приведенном выше примере у нас есть 2 процедуры, каждая из которых использует функцию для вычисления стоимости килограммов, переданных им в аргументе dblKilo функции.

Несколько аргументов

Вы можете создать функцию с несколькими аргументами и передать значения функции с помощью подпроцедуры.

Затем мы можем вызвать функцию для вычисления количества дней между двумя датами.

Необязательные аргументы

Вы также можете передать в функцию необязательные аргументы. Другими словами, иногда вам может понадобиться аргумент, а иногда нет — в зависимости от того, с каким кодом вы используете функцию.

Программирование VBA стало проще

Прекратите искать код VBA в Интернете. Узнайте больше об AutoMacro — конструкторе кода VBA, который позволяет новичкам создавать процедуры с нуля с минимальными знаниями в области кодирования и множеством функций, позволяющих сэкономить время для всех пользователей!

Значение аргумента по умолчанию

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

ByVal и ByRef

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

ByVal означает, что вы передаете в функцию копию переменной, тогда как ByRef означает, что вы ссылаетесь на исходное значение переменной. Когда вы передаете копию переменной (ByVal), исходное значение переменной НЕ изменяется, но когда вы ссылаетесь на переменную, функция изменяет исходное значение переменной.

В приведенной выше функции ByRef можно было бы опустить, и функция работала бы так же.

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

Обратите внимание, что в окнах отладки оба раза отображается значение 40. Когда вы передаете в функцию переменную IntVal — в функцию передается значение 10, которое умножается на 4. Использование ключевого слова ByRef (или полное его отсутствие) ИСПРАВЛЯЕТ значение переменной IntVal. Это видно, когда вы сначала показываете результат функции в непосредственном окне (40), а затем значение переменной IntVal в окне отладки (тоже 40).

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

Теперь, если мы вызовем функцию из подпроцедуры, значение переменной IntVal останется равным 10.

Выход из функции

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

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

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

Использование функции на листе Excel

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

Нажмите fx, чтобы открыть диалоговое окно "Вставить функцию".

Выберите «Определено пользователем» из списка категорий

Выберите нужную функцию из доступных пользовательских функций (UDF).

В качестве альтернативы, когда вы начинаете писать свою функцию в Excel, функция должна появиться в раскрывающемся списке функций.

Если вы не хотите, чтобы функция была доступна на листе Excel, вам нужно поместить слово Private перед словом Function при создании функции в коде VBA.

Теперь он не будет отображаться в раскрывающемся списке доступных функций Excel.

Интересно, однако, что вы по-прежнему можете использовать эту функцию — она просто не будет отображаться в списке при поиске!

Если вы объявили второй аргумент необязательным, вы можете опустить его как на листе Excel, так и в коде VBA.

Вы также можете использовать созданную вами функцию без аргументов на листе Excel.

Генератор кода VBA

Надстройка примеров кода VBA

Легкий доступ ко всем примерам кода на нашем сайте.

Просто перейдите в меню, нажмите, и код будет вставлен прямо в ваш модуль. Надстройка .xlam.

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

Разделы:

Создайте макрос для принятия значения

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

Вот наш образец макроса:

Этот макрос принимает значение в переменной сообщения, а затем выводит его во всплывающее окно окна сообщения.

Передать текстовое значение в макрос

Когда вы перейдете к запуску этого макроса из интерфейса рабочего листа Excel, вы фактически не увидите его в списке доступных макросов.

Нажмите Alt + F8, чтобы открыть окно "Выполнить макрос":

Мы должны ввести имя макроса, а затем значение аргумента.

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

Примечание: текст заключен в двойные кавычки, а затем весь текст заключен в одинарные кавычки.

Нажмите «Выполнить», и все готово:

Передать числовое значение в макрос

Нажмите Alt + F8, чтобы запустить макрос.

Имя макроса не будет отображаться в окне, поэтому мы должны ввести его в том месте, где написано Имя макроса.

Обратите внимание на цифру 5 после имени макроса. Именно так вы передаете числовой аргумент макросу; просто поставьте пробел после имени макроса и введите число.

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

Запустите макрос, и вы увидите результат:

Передача нескольких значений в макрос

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

Во-первых, я добавил макрос, который принимает два аргумента.

Нажмите Alt + F8, чтобы открыть окно макроса. Вы не увидите этот макрос, так как он принимает аргументы.

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

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

Запускаем макрос и видим это:

Оба значения успешно переданы макросу.

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

Передать значение ячейки в макрос

Вы также можете передать значение ячейки в макрос.

Нажмите Alt + F8, чтобы открыть окно макроса. Помните, что вы не увидите макрос в списке.

В том месте, где написано Имя макроса, введите имя макроса и аргумент, как и в предыдущих примерах, с одним отличием: на этот раз мы используем Evaluate(), чтобы получить ссылку на ячейку в макрос:

Evaluate() используется для ссылки на ячейку; ссылка на ячейку внутри Evaluate() должна быть заключена в двойные кавычки.

Примечание: все должно быть заключено в одинарные кавычки.

Запустите макрос, и вы должны увидеть это:

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

Передать значение имени в макрос

Если в вашей электронной таблице есть имена, вы также можете передать их в макрос.

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

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

Примечание: все должно быть заключено в одинарные кавычки, как вы можете видеть в примере.

Запустите макрос, и вы должны увидеть это:

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

Вызов макроса из кнопки или другого объекта/фигуры

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

Вот пример:

Примечание: вы должны заключать все в одинарные кавычки, а текст всегда должен заключаться в двойные кавычки. Если вы хотите отправить значение ячейки, имя или несколько аргументов одновременно, обратитесь к приведенным выше примерам для этого; он работает так же, как вызов макроса с кнопки, так и из окна макроса, к которому вы попадаете, нажимая Alt + F8.

Теперь всякий раз, когда я нажимаю кнопку, я вижу следующее:

Альтернативный формат для ссылки на макросы

Вы также можете сослаться на макрос следующим образом:

Вместо того, чтобы ставить пробел после имени макроса, а затем аргументов, вы ставите скобки, а затем аргументы внутри них.

Формат аргументов остается таким же, как в приведенных выше примерах.

Примечания

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

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

Вопрос? Спросите об этом на нашем форуме Excel

Курс Excel VBA — от новичка до эксперта

200+ видеоуроков 50+ часов обучения 200+ руководств Excel

С помощью этого онлайн-курса станьте мастером VBA и макросов в Excel и узнайте, как автоматизировать все свои задачи в Excel. (Опыт работы с VBA не требуется.)

Курс VBA — от новичка до эксперта

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

Использование макросов с пользовательскими формами
Учебное пособие. В этом учебном пособии объясняется, как макросы взаимодействуют с пользовательскими формами. Это включает объяснение.

Переменные в макросах VBA для Excel - Практическое руководство
Учебное пособие: это практическое руководство по использованию переменных в макросах и VBA для Excel. Я скажу вам .

Автоматически запускать макрос через несколько секунд, минут или часов после открытия книги Excel
Макрос: запускать макрос по прошествии определенного времени с момента открытия книги Excel.

Предотвращение появления предупреждений и сообщений Excel при выполнении макроса в Excel
Учебник: Как предотвратить появление окна предупреждения или сообщения Excel при выполнении макроса. Тэ.

Удалить модуль VBA из Excel
Макрос: Удалите модуль макроса VBA из Excel с помощью этого макроса. Этот макрос позволяет полностью .

Подпишитесь на еженедельные уроки

БОНУС: подпишитесь сейчас, чтобы загрузить нашу электронную книгу Top Tutorials!

Ссылка на наши 15 лучших учебных пособий была отправлена вам, проверьте свою электронную почту, чтобы загрузить ее!

(Если вы не видите электронное письмо, проверьте папку "Спам" или "Промоакции" и обязательно добавьте нас в список контактов, чтобы получать наши электронные письма в будущем.)

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

Есть ли простое решение для передачи аргумента макросу при нажатии кнопки?

На самом деле вы можете ввести имя и аргумент макроса, заключенные в одинарные кавычки (например, «макрос 5»), но не используйте этот метод, если ваш файл имеет формат .xlsb.

9 ответов 9

Да, вы можете назначить макрос кнопке (или другим элементам управления/действиям меню Excel) и передать ему постоянные ИЛИ переменные аргументы.

В окне "Назначить макрос" (щелкните объект правой кнопкой мыши и выберите "Назначить макрос"):

Заключите имя макроса в одинарные кавычки, например. передать 2 константы: 'Button1_Click("Строка!", 7)'
Выберите «Эта книга» в поле «Макросы в»
Если вы хотите передать переменную (например, значение ячейки), заключите параметр в Evaluate()

Например, чтобы передать значение Sheet1!$A$1 функции кнопки, в поле "Имя макроса:" должен быть следующий текст:

Если вы не заключите аргумент переменной с функцией "Оценить", Excel вернет ошибку "Формула слишком сложна для назначения объекту".

Если бы это было разрешено, я бы включил изображение в свой первый пост.

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

Мне помогло заключение имени макроса в кавычки: я установил для кнопки имя макроса "Выбрать 1,2", и он правильно вызвал мой метод Select.

Спасибо! Это решение работало для передачи одного аргумента — что, если аргументов несколько?

Для нескольких аргументов повторите компонент "Вычислить(. )": "Button1_Click(Вычислить("Лист1!$A$1"), Вычислить("Лист1!$A$1"), . )"

Скобки должны быть удалены. 'Button1_Click "Строка!", 7' работает, 'Button1_Click("Строка!", 7)' не работает.

Предположим, что у вас есть аргумент public sub take 1, как показано ниже (просто для пояснения).

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

Теперь вы можете ввести свое дополнительное имя + пробел + аргумент вручную в одинарных кавычках, как показано ниже, нажмите "ОК".

Тогда вы видите, проблема решена.

Это абсолютно должен быть ответ. ОП искал, как это сделать с помощью экрана назначения макроса, а не с помощью кода.

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

Используйте кнопку команды управления ActiveX и в методе нажатия кнопки вызовите подпрограмму и передайте аргумент:

Чтобы вызвать эту подпрограмму с помощью кнопки:

<р>. имейте в виду, что Sub не будет указан в доступных макросах, потому что у него есть параметр. Просто введите вызов в одинарных кавычках: «TestButton «Hello»»

При вызове из обычной кнопки "Формы" на листе вы можете сделать что-то вроде этого:

Где вызывающая кнопка называется (например) "Button_3"

Или (проще) щелкните правой кнопкой мыши кнопку и введите "TestMe2 5" (включая одинарные кавычки)

У меня возникли проблемы с моей версией Personal.xlsb!'testForXXXX("Test Test")'. У меня возникла ошибка при нажатии кнопки, содержащей макрос.

Однако мне удалось это исправить, удалив "(" и ")". Итак, Personal.xlsb!'testForXXXX "Test Test"' сработал (обратите внимание на пробел между testForXXXX и "Test.").

На самом деле мне не нужен был Personal.xlsb! и только что смог использовать 'testForXXXX "Test Test"'.

Я столкнулся с той же проблемой, когда кнопка назначения не была очень полезной, пока не понял, что все потенциальные отображаемые макросы были только теми в моем файле Personal.xlsb, которые не принимали аргументов. Затем я набрал Personal.xlsb!'macroNameWhichTakesArguments("arg1", . )', и лист подхватил его.

Т.е. Personal.xlsb!'testForXXXX("Тестовый тест")'

Там, где макрос testForXXXX принимает строку в качестве входных данных, лист работает нормально.

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

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