Как просмотреть действие пользовательской функции vba excel

Argument ‘Topic id’ is null or empty

Сейчас на форуме

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

ООО «Планета Эксел»
ИНН 7735603520
ОГРН 1147746834949

ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРНИП 310633031600071

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

подскажите как из функции узнать в какой ячейке ее вызвали (нужно для дальнейших операция) P.S. вообще я это делаю, чтобы вот такой треш:

=ЕСЛИ(И(ЕЧИСЛО($A1351);ЕЧИСЛО($C1351);ЕЧИСЛО(ДВССЫЛ(АДРЕС(ПОИСКПОЗ(ДАТА(ГОД($A1351)-1;МЕСЯЦ($A1351);ДЕНЬ($A1351));$A$1:$A$100000);3))));$C1351-ДВССЫЛ(АДРЕС(ПОИСКПОЗ(ДАТА(ГОД($A1351)-1;МЕСЯЦ($A1351);ДЕНЬ($A1351));$A$1:$A$100000);3));"")

завернуть в функцию и вызывать просто функцию с параметром ( -1 надо заменить на параметр — delta ) Это же нормальная практика — вытаскивать под функции сложные коды, которые используются во многих ячейках?

Отслеживать
задан 21 янв 2022 в 19:29
37.4k 4 4 золотых знака 28 28 серебряных знаков 74 74 бронзовых знака

Из функции никак не узнать, ведь функция может быть прописана в нескольких ячейках. Если это нужно для определения строки/столбца, используйте тип ссылок RC. Вы бы лучше описали, что получаете этим монстром.

Пользовательская функция может не вычислить ожидаемое значение в Excel

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

Причина

Эта проблема возникает при выполнении указанных ниже условий.

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

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

Обходной путь

Чтобы обойти эту проблему, используйте любой из следующих методов.

Метод 1. Измените функцию так, чтобы были переданы все соответствующие диапазоны ячеек.

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

Метод 2. Обеспечение переменной пользовательской функции

Корпорация Майкрософт предоставляет примеры программирования только в целях демонстрации без явной или подразумеваемой гарантии. Данное положение включает, но не ограничивается этим, подразумеваемые гарантии товарной пригодности или соответствия отдельной задаче. Эта статья предполагает, что пользователь знаком с представленным языком программирования и средствами, используемыми для создания и отладки процедур. Инженеры службы поддержки Майкрософт могут помочь объяснить функциональные возможности конкретной процедуры, но они не будут изменять эти примеры, чтобы предоставить дополнительные функциональные возможности или процедуры создания в соответствии с конкретными требованиями. Если сделать пользовательскую функцию переменной, эта проблема не будет возникать. Чтобы сделать пользовательскую функцию переменной, добавьте в нее следующую строку кода.

Application.Volatile

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

Метод 3. Принудительное вычисление всех открытых книг в Excel

Нажмите клавиши CTRL+ALT+F9, чтобы пересчитать значения во всех открытых книгах. В Microsoft Office Excel 2007 нажмите клавиши CTRL+ALT+SHIFT+F9, чтобы пересчитать значения во всех открытых книгах.

Дополнительные сведения

Пример проблемы

Чтобы продемонстрировать эту проблему, выполните следующие действия.

Закройте и сохраните открытые книги, а затем откройте новую книгу.
Запустите редактор Visual Basic (нажмите клавиши ALT+F11).
В меню Вставка выберите пункт Модуль.
Введите следующий код в лист модуля:

' This function counts the number of blank cells by starting from the cell ' referred to by the rngStartCell argument and moving up the column. Function FindTextUp(rngStartCell As Range) As Single Dim iIndex As Integer For iIndex = 0 To 100 If rngStartCell.Value <> "" Then FindTextUp = iIndex Exit Function Else Set rngStartCell = rngStartCell.Offset(-1, 0) End If Next iIndex End Function

=FindTextUp(A9)

Формула по-прежнему возвращает значение 7, если ожидается значение 4. В этом примере функция FindTextUp явно ссылается на ячейку A9. Однако функция может зависеть от ячеек A1:A8 в зависимости от данных, введенных на листе.

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

Function FindTextUp(rngStartCell As Range, rngOtherCells As Range) As Single

Теперь, если заменить вызов функции на шаге 7 следующим вызовом функции, функция всегда возвращает ожидаемый результат.

=FindTextUp(A9,A1:A8)

Создание пользовательских функций в Excel

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

Пользовательская функция — это общий термин, который является взаимозаменяемым с определяемой пользователем функцией. Оба условия применяются к надстройкам VBA, COM и Office.js. В документации по надстройкам Office термин «настраиваемая функция » используется при обращении к пользовательским функциям, использующим API JavaScript для Office.

Обратите внимание, что настраиваемые функции доступны в Excel на следующих платформах.

Office для Windows

Подписка на Microsoft 365
Розничный бессрочный Office 2016 и более поздних версий

В настоящее время пользовательские функции Excel не поддерживаются в следующих приложениях:

Office для iPad
корпоративные бессрочные версии Office 2019 или более ранних версий

Ниже на анимированном изображении показано, как рабочая книга вызывает функцию, созданную вами с помощью JavaScript или TypeScript. В этом примере пользовательская функция =MYFUNCTION.SPHEREVOLUME рассчитывает объем сферы.

Приведенный ниже код определяет пользовательскую функцию =MYFUNCTION.SPHEREVOLUME .

/** * Returns the volume of a sphere. * @customfunction * @param radius */ function sphereVolume(radius) < return Math.pow(radius, 3) * 4 * Math.PI / 3; >

Как определена пользовательская функция в коде

Если использовать генератор Yeoman для надстроек Office для создания в Excel проекта надстройки с пользовательскими функциями, он создаст файлы, управляющие вашими функциями и областью задач. Мы сосредоточимся на файлах, которые важны для пользовательских функций.

Файл	Формат файла	Описание
./src/functions/functions.js или ./src/functions/functions.ts	JavaScript или TypeScript	Содержит код, который определяет пользовательские функции.
./src/functions/functions.html	HTML	Предоставляет со ссылкой на файл JavaScript, который определяет пользовательские функции.
./manifest.xml	XML	Указывает расположение нескольких файлов, которые используются пользовательскими функциями, например JavaScript, JSON и HTML-файлов. А также среду выполнения, которую должны использовать пользовательские функции, расположение файлов области задач и командных файлов.

Генератор Yeoman для надстроек Office предлагает несколько проектов пользовательских функций Excel . Рекомендуется выбрать тип проекта Excel Custom Functions с помощью общей среды выполнения и типа скрипта JavaScript.

Файл скрипта

Файл скрипта (./src/functions/functions.js или ./src/functions/functions.ts) содержит код, определяющий пользовательские функции, и комментарии, определяющие функцию.

Приведенный ниже код определяет пользовательскую функцию add . Примечания кода используются для создания файла метаданных JSON с описанием пользовательской функции для Excel. Обязательный комментарий @customfunction объявлен первым, чтобы указать, что это пользовательская функция. Затем объявляются еще два параметра: first и second , за которыми следуют их свойства description . Наконец, дается описание returns . Дополнительные сведения о том, какие комментарии являются обязательными для вашей пользовательской функции, см. в статье Автоматическое создание метаданных JSON для пользовательских функций.

/** * Adds two numbers. * @customfunction * @param first First number. * @param second Second number. * @returns The sum of the two numbers. */ function add(first, second)

Файл манифеста

Файл манифеста XML для надстройки, определяющий пользовательские функции (./manifest.xml в проекте, созданном генератором Yeoman для надстроек Office), выполняет несколько задач.

Определяет пространство имен для пользовательских функций. Пространство имен добавляется к пользовательским функциям, чтобы клиенты могли определить ваши функции в рамках надстройки.
Использует и , уникальные для манифеста пользовательских функций. Эти элементы содержат сведения о расположении JavaScript, JSON и HTML-файлов.
Указывает, какую среду выполнения использовать для пользовательской функции. Рекомендуется всегда использовать общую среду выполнения, если нет особой потребности в использовании другой среды, так как общая позволяет делиться данными между функциями и областью задач.

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

Если вы будете тестировать надстройку в нескольких средах (например, в среде разработки, в промежуточной среде, в демонстрационной среде и т. п.), рекомендуем использовать отдельный XML-файл манифеста для каждой среды. В каждом файле манифеста можно:

Указать URL-адреса, соответствующие среде.
Настроить значения метаданных, такие как DisplayName , и метки в Resources для указания среды, чтобы конечные пользователи могли определить соответствующую среду надстройки, загруженной без публикации.
Настроить пользовательские функции namespace , чтобы указать среду, если ваша надстройка определяет пользовательские функции.

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

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

Excel для Интернета и Windows, подключенный к подписке Microsoft 365, дает конечным пользователям возможность совместно редактировать данные в Excel. Если книга пользователя использует пользовательскую функцию, то коллеге этого пользователя по совместному редактированию будет предложено загрузить надстройку с соответствующими пользовательскими функциями. После загрузки этой надстройки обоими пользователями эта пользовательская функция будет предоставлять результаты обоим пользователям с помощью совместного редактирования.

Дополнительные сведения о совместном редактировании см. в статье О совместном редактировании в Excel.

Дальнейшие действия

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

Еще одно простое средство ознакомления с пользовательскими функциями — Script Lab, надстройка, в которой можно экспериментировать с пользовательскими функциями прямо в Excel. Вы можете попробовать создать собственные пользовательские функции или поиграть с готовыми примерами.

См. также

Сведения о программе для разработчиков Microsoft 365
Наборы обязательных элементов пользовательских функций
Правила именования пользовательских функций
Создание пользовательских функций, совместимых с функциями XLL, определенными пользователями
Настройка надстройки Office для использования общей среды выполнения
Среды выполнения в надстройках Office

Как просмотреть действие пользовательской функции vba excel