Access vba функции: Оператор Function (VBA) | Microsoft Learn

Пользовательские функции в Access Часть 1

Пакеты прикладных программ офисного назначения и программирование – задание 1
В этом посте, мои дорогие читатели, я дам вам несколько советов, как создавать и применять пользовательские функции в Access, а также как передавать разное число аргументов в функцию и использовать массивы. Язык VBA.
Задание
— Создать новую базу данных, а в ней – таблицу с записями приведенной структуры:

Предприятие – название предприятия, Продукция – название продукции, Вып_1, … Вып_6 – данные о выпуске продукции (целые числа) по месяцам полугодия, выраженные в одной и той же единице измерения.
— Заполнить таблицу данными (примерно 15 – 20 записей). Данные выбирать произвольно. Данные должны демонстрировать различные варианты выполнения последующего запроса, для чего их в процессе работы нужно корректировать.
— Создать общий модуль, а в нем – пользовательскую функцию для вычисления -**** Варианты задания ****- из массива числовых переменных. В функции использовать массив и цикл.
— Создать запрос для получения справки следующего вида:

В запросе должно присутствовать обращение к созданной пользовательской функции.
— Выполнить запрос.
Варианты:

0. Максимальное значение. Запрос — максимальный месячный выпуск
1. Характер изменения значений в массиве переменных (от первого к последнему). Возможные варианты : «Рост», «Падение», «Колебание», «Постоянен». Запрос – Динамика изменения выпуска.
2. Номера минимальных значений. Если их несколько, вернуть строкой через пробел. Запрос – номера месяцев  с минимальным выпуском.
3. Номера элементов, содержащих значения больше среднего по массиву. Вернуть строкой. Запрос — Номера месяцев с  выпуском больше среднего.
4. Номера элементов, содержащих значения меньше среднего по массиву. Вернуть строкой. Запрос — Номера месяцев с  выпуском меньше среднего.
5. Количество элементов, содержащих значения больше среднего по массиву. Запрос — Число месяцев с  выпуском больше среднего.
6. Максимальное и минимальное значение из массива элементов (в виде текста). Запрос — Максим-й  и миним-й месячный выпуск.
7. Разность максимального и минимального значений из массива числовых переменных. Запрос — Разность максим-го и миним-го выпусков.
8. Количество максимальных значений в массиве (предполагается, что их несколько). Запрос — Число месяцев с наибольшим выпуском.
9. Номера максимальных значений. Запрос — Номера месяцев с максимальным выпуском.

Нам нужно создать функцию, которая принимала бы список аргументов, обрабатывала их и возвращала результат. Список параметров функции может быть постоянным и переменным.
Постоянное число параметров – когда функция принимает определенное число, которое прописано в программе. То есть в нашем случае — это шесть значений выпуска. Семь значений, или пять, или все выпуски за год уже будет не посчитать. Думаю, что в этом задании как раз и предусматривается подобный вариант.
Но на всякий случай рассмотрим функцию с переменным числом параметром. Она гораздо удобнее в работе и позволяет расширить возможности программы. В этом случае в функцию передается число аргументов, указанное пользователем, и массив значений. То есть можно посчитать и за квартал, и за полугодие.
Сделаем оба способа, и вы поймете разницу.
Итак, создаем базу данных в Access. Создаем в ней таблицу на восемь полей. Два поля – текстовые, остальные можно сделать целыми. Придумываем предприятия и продукцию, заполняем числовые данные. Я не описываю все подробно. Если уж с вас спрашивают программирование VBA, то создание баз не является трудностью, скорее всего.
Для создания функции переходим в редактор Visual Basic. На вкладке «Создание» в разделе «Другие» из раскрывающегося списка «Макрос» выбираем пункт «Модуль». Открывается редактор кода VBA. Или Alt+F11.
Функцию можно вставить с помощью меню Insert->Procedure или написать прямо в модуле.

Обратите внимание, выбираем именно функцию (Function), а не процедуру, так как нужно получить возвращаемое значение.
Создаем функцию для расчета максимального выпуска (0 вариант).
1 способ
Принимаем шесть аргументов, заполняем массив и ищем максимум.
Текст функции:

Public Function MaxV(v1 As Integer, v2 As Integer, v3 As Integer, v4 As Integer, v5 As Integer, v6 As Integer) As Integer
    Dim prod(1 To 6) As Integer    ‘массив значений выпуска за 6 месяцев
    Dim i As Integer            ‘счетчик цикла
    Dim max As Integer             ‘максимальное значение выпуска
    ‘заполняем массив
    prod(1) = v1
    prod(2) = v2
    prod(3) = v3
    prod(4) = v4
    prod(5) = v5
    prod(6) = v6
    ‘определяем максимум — 1 элемент массива
    max = prod(1)
    For i = 2 To 6
        ‘если элемент массива больше максимума
        If prod(i) > max Then
            max = prod(i)           ‘новое значение максимума
        End If
    Next i
    MaxV = max   ‘возвращаем результат
End Function

Теперь идем в конструктор запросов и создаем там запрос, добавляем в него поля Предприятие и Продукция. На третьем поле нажимаем правую кнопку мыши и выбираем пункт меню «Построить». Открывается Построитель выражений. Слева, в разделе «Элементы выражений» раскрываем список функций и выбираем название нашей базы данных – тест. Появляются доступные модули и созданные пользовательские функции. Выбираем нашу функцию – MaxV, два раза щелкаем на ней.

Вместо параметров в кавычках нужно выбрать значения выпуска из таблицы «Продукция». Они добавляются после двойного щелчка в разделе «Категории выражений». Не забывайте убирать выражения «Выражение», а то выдаст ошибку. Должно получиться следующее:

Нажимаем ОК, меняем название поля с «Выражение1» на что-нибудь подходящее и запускаем запрос.
Второй способ
Сейчас создадим второй вариант этой функции, назовем его MaxV1.

Public Function MaxV1(ByVal arg As Integer, ParamArray Prod()) As Integer
    Dim i As Integer            ‘счетчик цикла
    Dim max As Integer             ‘максимальное значение выпуска
    ‘определяем максимум — 1 элемент массива
    max = Prod(0)
    For i = 1 To arg — 2
        ‘если элемент массива больше максимума
        If Prod(i) > max Then
            max = Prod(i)           ‘новое значение максимума
        End If
    Next i
    MaxV1 = max   ‘возвращаем результат
End Function

Функция получает аргументы – число аргументов (в данном случае, это 6 значений выпуска плюс первый аргумент – всего 7) и массив аргументов ParamArray.
Нужно иметь в виду, что нумерация элементов в этом массиве начинается с 0, поэтому первый элемент у нас Prod(0), а последний – Prod(arg-2).
Преимущества этого способа — мы не заполняем вручную массив, он передается по ссылке, а также можно рассчитывать не только выпуски за 6 месяцев, но и за другое число. Но при этом при создании запроса необходимо менять первый аргумент функции, зато код функции остается прежним.
Вот как выглядит в данном случае выражение в третьем поле запроса:

Можете выбрать вариант, который вам понятней или больше нравится.

Еще по этому заданию

Пользовательские функции в Access — варианты 1-4
Пользовательские функции в Access — варианты 5-9

Общий список всех сообщений по этой работе  

VBA-Урок 14.2. Создание пользовательской функции

ВВЕРХ

❮
❯

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

Чтобы отобразить значение (B2) если ячейка (C2) имеет значение («YES»), мы могли бы использовать функцию IF в формуле = IF (C2 = «YES», B2,0) :

Нашей целью в этом случае является создание функции, которая могла бы делать это = IF (C2 has a green background, B2,0) , и которую мы бы записали, типа: =IF_GREEN(C2,B2):

Создание собственной функции

Начнем создавать функцию:

Function IF_GREEN(paid As Range, amount)
     
End Function

Аргументы:

  • paid As Range : ячейка, которую мы будем проверять;
  • amount : значение, которое мы будем вставлять, если TRUE .

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

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

Function IF_GREEN(paid As Range, amount)
     green_color = Sheets("Sheet1"). Range("K1").Interior.color
End Function

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

Function IF_GREEN(paid As Range, amount)
     green_color = 5296274 'Зеленый
End Function

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

Sub test_color()
     MsgBox ActiveCell.Interior.color
End Sub

Теперь, все что нам необходимо сделать, это проверить цвет ячейки используя IF:

Function IF_GREEN(paid As Range, amount)
     
     Application.Volatile
     
     green_color = 5296274 'Зеленый
    
     If paid.Interior.color = green_color Then 'ЕСЛИ ПРАВДА
        IF_GREEN = amount
     Else 'ЕСЛИ ЛОЖЬ
        IF_GREEN = 0
     End If
     
End Function

Теперь наша функция готова к использованию.

Application.Volatile говорит нам, что приложение является меняющимся (как это имеет место для функции IF ). Это означает, что она должна быть перечислена каждый раз, когда значение изменяется. Например, если вы измените любую из сумм (или, на самом деле, любую другую ячейку), эта функция будет перечислена и правильная сумма будет отражена.

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

Sub refresh_macro()
     Application.Calculate
End Sub

Дополнительно

Ниже есть другой способ написания такой же функции:

Function IF_GREEN(paid As Range, amount)
     Application.Volatile
     IF_GREEN = 0 'ЕСЛИ ЛОЖЬ
     If paid.Interior.color = 5296274 Then IF_GREEN = amount 'ЕСЛИ ПРАВДА
End Function

Вы можете скачать Excel файл, содержащий этот пример, нажав здесь.

Статьи по теме:

  • VBA-Урок 14. 1. Использование Excel функций

    • База данных Microsoft Access. Создание пользовательских функций д ожидать

    База данных Microsoft Access обеспечивает широкий набор предопределенных функций, которые можно было бы использовать. Однако вы заметите, что это не так.

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

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

    Если вы придерживаетесь и используете основную прикладную функцию Access по управлению данными, даже несмотря на то, что он также имеет значительный набор функций, вам все равно будет лучше использовать функции, принадлежащие MS Access, и вам не нужно полагаться на какие-либо внешние наименования. чтобы поддержать это. В случае с Access это обычно означает работу с запросами. Запросы пишутся на стандартном языке под названием SQL (язык структурированных запросов), который используется всеми программами баз данных. Встроенные функции Access должны быть совместимы с SQL, что, в свою очередь, может ограничить его вычислительную мощность.

    Итак, вам нужно положиться на некоторый (но базовый) код VBA, чтобы прийти на помощь. Если в Access нет этой функции, вам нужно создать собственную.

    UDF (расшифровывается как «Определяемые пользователем функции») — это термин, относящийся к пользовательским функциям, который может применяться к большинству версий, начиная с Access 97 (да, в прошлом веке!).

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

    1. Во-первых, вам нужно добавить модуль, в котором ваш код будет храниться, а затем вызываться. Если вы новичок в этом, взгляните на инструменты здесь.
    2. Я бы дал ему осмысленное имя, например «Пользовательские функции», и сохранил бы ваш модуль как-то вроде «mod_CustomFunctions».
    1. Необязательно (и рекомендуется). Если два ключевых слова « Option Explicit » не отображаются в верхней части модуля, введите их в модуль (ниже должно быть написано Option CompareDatabase ).
    1. Теперь, чтобы создать функцию с именем Age , где вы укажете действительное значение даты рождения (в качестве значения даты/времени в качестве ее параметра), вы создаете подпись: Возраст публичной функции (дата рождения) как целое число

      Это автоматически добавит под ней закрывающую подпись End Function .
    1. Теперь добавьте следующий код между двумя новыми строками: Age = Int((Date – DOB)/365.25) который вернет одно (целое значение – отсюда Integer ), представляющее количество лет на введенную дату.
    1. Сохраните изменения, и теперь вы можете проверить это. В представлении редактора Visual Basic (где вы только что создали пользовательскую функцию) нажмите Ctrl + G , чтобы открыть окно Immediate (если оно еще не открыто). Теперь введите следующее: ? Возраст («08.04.1970») и нажмите клавишу Введите , чтобы увидеть ответ.

    Вы также можете создать форму, отчет или даже запрос и вызвать эту функцию, как любую встроенную функцию в Access. Просто помните, что эта функция хранится в одном файле базы данных, и другие файлы базы данных Microsoft Access также должны иметь доступ (и файл должен оставаться открытым и доступным). Возможно, вам придется создать лучший и более глобальный способ сделать это доступным, но это другой сезон в другом блоге.

    А пока следите за моими предстоящими Обучение работе с базой данных Microsoft Access VBA Электронная книга — скоро выйдетJ

    Теги: база данных Microsoft Access

    sql — Использование функции VBA в Access Query

    Задавать вопрос

    спросил

    Изменено
    2 года, 2 месяца назад

    Просмотрено
    520 раз

    Я хочу написать запрос SQL, используя доступ MS, который вычисляет серийный номер, используя функцию VBA.

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

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

    Для сокращения текстовых полей у меня есть следующая функция:

     Функция GetFirstLetters(rng As String)
Тусклый обр
Дим я пока
arr = VBA.Split(rng, "")
Если Массив(обр) Тогда
    Для I = LBound (приб.) To UBound (приб.)
        GetFirstLetters = GetFirstLetters & Left(arr(I), 1)
    Далее я
Еще
    GetFirstLetters = Слева (приб., 1)
Конец, если
Конечная функция

    Я попытался выполнить приведенный ниже SQL для достижения этой цели, но потерпел неудачу из-за синтаксической ошибки.

     ВЫБОР
(getfirstletters(выбрать sl.locationname из samplebasicinformation as sbi присоединиться к samplelocation как sl
на sbi.samplelocationid = sl. samplelocationid)),
SampleBasicInformationID
из SampleBasicInformation как sbi1

    Кто-нибудь может дать совет?

    • sql
    • vba
    • ms-access

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

    Ваш запрос довольно запутан, я думаю, что это так же просто:

     SELECT
  getfirstletters(sl.locationname),
  sbi.samplebasicinformationid
ОТ образца базовой информации как sbi
  Расположение образца INNER JOIN как sl
  на sbi.samplelocationid = sl.samplelocationid

    Обратите внимание, что вам нужно ВНУТРЕННЕЕ СОЕДИНЕНИЕ в Access Sql.

    2

    Зарегистрируйтесь или войдите в систему

    Зарегистрируйтесь с помощью Google

    Зарегистрироваться через Facebook

    Зарегистрируйтесь, используя электронную почту и пароль

    Опубликовать как гость

    Электронная почта

    Требуется, но никогда не отображается

    Опубликовать как гость

    Электронная почта

    Требуется, но не отображается

    Нажимая «Опубликовать свой ответ», вы соглашаетесь с нашими условиями обслуживания и подтверждаете, что прочитали и поняли нашу политику конфиденциальности и кодекс поведения.