Vba excel передача параметров в процедуру
В самых простых вариантах, когда можно получить и изменять данные напрямую в таблицах, можно обойтись объектом Recordset. Однако во многих ситуациях возможностей только этого объекта недостаточно. Как уже говорилось, предпочтительнее производить любое внесение изменений на источник данных при помощи хранимых процедур. Часто существует потребность в создании временных таблиц и других объектов на сервере. Бизнес-логика многих приложений (начисление процентов, абонентской платы, формирование специальных отчетов с вычислениями и т.п.) также реализована в виде хранимых процедур, поэтому в реальных приложениях одним объектом Recordset не обойтись.
Для выполнения команд SQL на сервере (в том числе запуска хранимых процедур, команд DDL для создания объектов, выполнения служебных операций типа резервного копирования, восстановления, изменения параметров работы) необходимо использовать объект Command.
Создание этого объекта производится очень просто:
Следующее, что нужно сделать — назначить объекту Command объект подключения Connection. Для этой цели предназначено свойство Command.ActiveConnection. Ему можно передать готовый объект Connection, а можно сформировать этот объект неявно, используя в качестве значения свойства ActiveConnection строку подключения. Рекомендуется всегда предавать готовый объект подключения: во-первых, так для соединения можно настроить больше параметров, а во-вторых, если вы используете в приложении несколько объектов Command, можно использовать для каждого такого объекта одно-единственное подключение, что экономит ресурсы. В нашем примере мы используем созданный нами ранее объект Connection:
Следующая наша задача — выбрать тип команды. В принципе, для многих источников можно его и не выбирать — модули ADO постараются сами выяснить у источника данных, что это за команда (хранимая процедура, SQL-запрос и т.п.), однако лучше всегда его определять: экономится время и системные ресурсы, уменьшается вероятность ошибок. Для выбора типа команды используется свойство CommandType. Значения, которые ему можно присвоить, аналогичны возможным значениям параметра Options метода Open() объекта Recordset, которое было рассмотрено выше. Например, если мы передаем команду на выполнение хранимой процедуры, то присвоить соответствующее значение можно так:
Следующее действие — определить текст команды, которая будет выполняться. Делается это при помощи свойства CommandText. Например, если мы хотим запустить на выполнение хранимую процедуру CustOrderHist, то соответствующий код может выглядеть так:
Чаще всего хранимая процедура требует передачи ей одного или нескольких параметров. Делается это при помощи коллекции Parameters и объектов Parameter. Для определения параметров можно использовать два способа:
- создать объекты Parameter автоматически путем запроса к серверу (используется метод Refresh() коллекции Parameters) и затем присвоить им значения:
- создать объекты Parameter вручную и вручную добавить их в коллекцию Parameters. Этот способ более экономичен (нет необходимости лишний раз обращаться на сервер), но требует предварительных выяснений точных свойств параметра и кода большего размера:
Dim Prm As ADODB.Parameter
Set Prm = cmd.CreateParameter("CustomerID", adVarWChar, adParamInput, 5, "ALFKI")
После этого команду необходимо запустить на выполнение. Для этого используется метод Execute(). Самый простой способ его вызова выглядит так:
Этот метод принимает также три необязательных параметра, при помощи которых можно дополнительно определить параметры, тип вызываемой команды и т.п.
Некоторые хранимые процедуры и передаваемые команды не требуют возврата каких-либо значений (кроме кода ошибки), но так бывает редко. Как же принять значения, возвращаемые выполняемой командой?
Если же, как в нашем примере с CustOrderHist, возвращаемое значение просто сбрасывается в поток вывода (в нашем случае возвращается набор записей), то можно использовать два способа:
В большинстве случаев для процедуры требуются некоторые сведения о обстоятельствах, в которых она была вызвана. Процедура, выполняющая повторяющиеся или общие задачи, использует разные сведения для каждого вызова. Эти сведения состоят из переменных, констант и выражений, которые передаются в процедуру при ее вызове.
Параметр представляет значение, которое процедура предполагает указать при ее вызове. В объявлении процедуры определяются ее параметры.
Можно определить процедуру без параметров, один параметр или несколько. Часть определения процедуры, указывающая параметры, называется списком параметров.
Аргумент представляет значение, указываемое в параметре процедуры при вызове процедуры. Вызывающий код предоставляет аргументы при вызове процедуры. Часть вызова процедуры, указывающая аргументы, называется списком аргументов.
На следующем рисунке показан код, вызывающий процедуру safeSquareRoot из двух различных мест. Первый вызов передает значение переменной x (4,0) в параметр number , а возвращаемое значение в root (2,0) присваивается переменной y . Второй вызов передает литеральное значение 9,0 в number , а затем присваивает возвращаемое значение (3,0) переменной z .
Тип данных параметра
Тип данных для параметра определяется с помощью As предложения в его объявлении. Например, следующая функция принимает строку и целое число.
Если параметр проверки типов (оператор Option строго) является Off, As предложением необязательным, за исключением того, что если какой-либо параметр использует его, все параметры должны использовать его. Если проверка типа имеет значение On , As предложение является обязательным для всех параметров процедуры.
Если вызывающий код должен предоставить аргумент с типом данных, отличным от типа соответствующего параметра, например, Byte в качестве String параметра, необходимо выполнить одно из следующих действий.
Указывайте только аргументы с типами данных, которые расширяются до типа данных параметра;
Задайте значение Option Strict Off , разрешающее неявные сужающие преобразования;
Используйте ключевое слово преобразования для явного преобразования типа данных.
Параметры типа
Универсальная процедура также определяет один или несколько параметров типа в дополнение к обычным параметрам. Универсальная процедура позволяет вызывающему коду передавать различные типы данных при каждом вызове процедуры, чтобы можно было адаптировать типы данных к требованиям каждого отдельного вызова. См. раздел Generic Procedures in Visual Basic.
При вызове процедуры необходимо следовать имени процедуры со списком аргументов в круглых скобках. Укажите аргумент, соответствующий каждому обязательному параметру, определяемому процедурой, и при необходимости можно указать аргументы для Optional параметров. Если Optional в вызове не указан параметр, необходимо включить запятую, чтобы пометить ее место в списке аргументов, если вы предоставляете последующие аргументы.
Если предполагается передать аргумент типа данных, отличный от того, который имеет соответствующий параметр, например, Byte String можно установить параметр проверки типов (оператор Option строго) в значение Off . Если Option Strict имеет значение On , необходимо использовать расширяющие преобразования или ключевые слова явного преобразования. Дополнительные сведения см. в разделе расширяющие и сужающие преобразования и функции преобразования типов.
Дополнительные сведения см. в разделе Параметры и аргументы процедуры.
Передача одного или нескольких аргументов в процедуру
В операторе вызова используйте имя процедуры с круглыми скобками.
Внутри круглых скобок вставьте список аргументов. Включите аргумент для каждого обязательного параметра, определяемого процедурой, и разделите аргументы запятыми.
Убедитесь, что каждый аргумент является допустимым выражением, результатом которого является тип данных, преобразуемый в тип, определяемый процедурой для соответствующего параметра.
Если параметр определен как необязательный, его можно включить в список аргументов или опустить. Если опустить его, процедура использует значение по умолчанию, определенное для этого параметра.
Если опустить аргумент для Optional параметра и в списке параметров есть другой параметр, можно пометить место пропущенного аргумента на дополнительную запятую в списке аргументов.
в следующем примере вызывается MsgBox функция Visual Basic.
Вторая запятая в списке аргументов отмечает место пропущенного второго аргумента, а последняя строка передается необязательному третьему параметру MsgBox , который является текстом, отображаемым в заголовке окна.
В отличии от языка VBScript, VBA процедуры классифицируются не на два типа (процедура-функция и процедура-подпрограмма), а четырех типов: процедура-функция, процедура-подпрограмма, процедура свойств и обработка событий. Также существуют некоторые дополнения в плане передачи параметров (по значению или по ссылке). Третьим моментом является область видимости – в VBA вызов процедуры может осуществляться как в пределах текущего модуля (макроса), так и за его пределами – во всех проектах. Все это обусловлено тем, что VBA – это не столько язык программирования, сколько программный пакет, с возможностью создания форм и проектов.
Давайте сначала кратко рассмотрим типы VBA процедур:
Подпрограммы – блоки кода заключенные в конструкцию Sub …. End Sub. Сама по себе подпрограмма не возвращает никакого значения, а просто выполняет прописанные в ней команды.
Функции – также блок кода, но прописанный в конструкцию Function … End Function. После выполнения функции возвращается определенное значение, доступ к которому можно получить через имя VBA функции.
Помимо этого, стоит упомянуть про обработку событий (нажатие кнопки клавиатуры или перемещение мыши) и доступ к объектам, но это отдельная тема.
VBA процедуры типа Sub – подпрограммы
После того как вы добавили в проект новый модуль, для объявления процедуры VBA нужно ее заключить в специальную конструкцию:
Sub ИмяПодпрограмм([аргументы])
Операторы
[Exit Sub]
операторы
End Sub
После ключевого слова Sub следует имя подпрограммы, в круглых скобках можно указывать или не указывать аргументы. Аргументы – это переменные (параметры), значение которых может обрабатываться, аргументы разделяются запятыми. Конструкция Exit Sub также не является обязательной, она говорит том, что нужно произвести выход из подпрограммы и продолжить выполнение кода, следующего после выражения End Sub.
Вызов VBA процедуры осуществляется с помощью ключевого слова call, например, Call MySub.
Давайте напишем простой пример: добавьте в проект новую форму и новый модуль. На поверхность формы добавьте два текстовых поля (TextBox), одну метку (Label) и одну кнопку (CommandButton). Создайте связь между формой и модулем, прописав в редакторе кода для модуля:
Я назвал форму SubForm, а модуль – SubModule, за имя отвечает свойство Name.
Теперь в редакторе кода для формы пропишите:
Тут все предельно просто, вначале мы объявили процедуру Hipotenuze, которой будут передаваться два аргумента, далее происходит проверка на нулевые значения. Вызов происходит при нажатии на кнопку, находящуюся на форме, параметрами будут значения, хранящиеся в текстовых полях TextBox1 и TextBox2. Результат отображается в метке Label1.
Вызов процедуры VBA может осуществляться и без использования ключевого слова Call, в таком случае, параметры не надо заключать в круглые скобки. Так же, при определении аргументов можно явно указать тип данных, например:
Sub MySub (a As Integer, b As String) … End Sub
Static – данное ключевое слово, прописанное перед ключевым словом Sub позволяет сохранять в памяти значения всех переменных после выполнения процедуры. Его мы рассматривали в с статье – переменные VBA.
ParamArray – данное ключевое слово позволяет передавать процедуре переменное количество параметров, оно может использоваться только для последнего элемента в списке аргументов.
ParamArray нельзя использовать вместе со словами ByRef, ByVal или Optional, например:
Как видим, мы фактически с помощью ParamArray показываем, что передаем массив, для его обработки мы использовали оператор For …. Each. Тут мы передали при вызове VBA процедуры пять параметров, при этом, первый будет храниться в аргументе a, а остальные в аргументе b, который обрабатывается как массив.
Optional – позволяет указать, что аргумент не является обязательным и одновременно задать значение по умолчанию.
В данном примере на поверхности формы находится всего одна метка и три кнопки. Каждая из кнопок будет производить VBA вызов процедуры MyArguments с различными значениями.
Передача параметров по ссылке и по значению – по умолчанию, при вызове процедуры все параметры ей передаются по ссылке. Передача по ссылке – в простом варианте, это передача адреса по которому хранится значение. При передаче параметра по ссылке, передается не адрес, а копия значения.
Что бы все стало понятно, рассмотрим следующий пример:
MySub1 – тут происходит объявление переменной MyVar и присвоение ей значения 100, далее в теле происходит вызов VBA процедуры MySumm1, ей в качестве параметры мы передаем значение переменной MyVar – 100. Сама процедура MySumm принимает значение по ссылке, на что указывает ключевое слово ByRef, к принятому значению прибавляется число 100. Стоит обратить внимание, что ByRef можно было и не писать. После VBA вызова процедуры MySumm1 происходит запись значения переменной MyVar в свойство Caption объекта Label1, в итоге, отобразится число 200.
MySub2 – аналог предыдущей процедуры, но тут происходит вызов MySumm2, в которой происходит передача параметров по значению, о чем говорит ключевое слово ByVal, в итоге, значение переменной MyVar не изменится.
VBA процедуры типа Function – функции
Пользовательским функциям языка VBA присущи практически те же правила, что и подпрограммам. Общая структура функции:
Function ИмяФункции ([аргументы]) [As ТипДанных]
Операторы
[Exit Function]
Операторы
[ИмяФункции=Выражение]
End Function
Видим, что тут при объявлении функции можно указать ее тип, данный тип будет содержать возвращаемое значение. Что бы функция возвращала значение, в конце нужно его присвоить переменной с именем функции, например:
Спасибо за внимание. Автор блога Владимир Баталий
Читайте также: