У меня возникли трудности с функцией VBA, вызванной из ячейки Excel, которая не обновляется автоматически или обновляется вручную с помощью F9, Shift+F9 и т. д.
Я использую «Microsoft® Excel® для Microsoft 365 MSO (версия 2207, сборка 16.0.15427.20166), 32-разрядная версия» в Windows 10.
У меня есть таблица различных значений, используемых для подготовки бюджета. Каждый столбец — это член команды, а каждая строка — это задача. Каждому пользователю назначаются часы для каждой задачи. Каждой задаче назначается множитель по имени.
Простой пример:
| Задача [cellA1] | Человек 1 | Человек 2 | Множитель |
|---|---|---|---|
| Первое задание | 1.00 | 0,25 | Тема |
| Второе задание | 0,05 | 0,15 | Участник |
Как показано, Человеку 1 требуется 1 час на тему и 0,05 часа на участника. Человеку 2 требуется 0,25 часа на тему и 0,15 часа на участника.
Количество тем и участников содержится в именованных диапазонах (например, "_topics", "_participants"). "Topic" и "Participant" — это имена-заполнители. Что это такое — неважно (это могут быть "Dogs" или "Cats"). Важно то, что каждому из них присвоено одно числовое значение. Задачи могут совместно использовать множители (например, "Topic" может использоваться более чем в одной строке).
Цель состоит в том, чтобы смоделировать требования сложного бюджета (много участников и много задач) с очень простыми входными данными (изменение количества тем, количества участников и т. д.).
Если бы это была небольшая таблица, я бы использовал внутриячейковую формулу, например:
=sumif(D1:D2,"Topic",B1:B2) * _topic + sumif(D1:D2,"Participant",B1:B2) * _participants
Если _topic = 5 и _participant = 10, то общая сумма для человека 1 составит 5,5 (1 * 5 + 0.05 * 10).
В моем случае есть много строк и много множителей, а также есть еще один столбец, который назначает задачу фазе проекта, поэтому я написал функцию VBA, чтобы сохранить содержимое ячейки разборчивым и упростить добавление элементов в будущем. Это не элегантно, но это делает то, что мне нужно.
Function SUM_MEMBER_VALUES(phase As String, data_range As Range, phase_range As Range, mult_range As Range)
p = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Project")
mc = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Market_collections") * _
Range("_markets_collections").Value
mr = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Market_calls") * _
Range("_markets_calls").Value
c = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Caller") * _
Range("_callers").Value
b = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Brand") * _
Range("_brands").Value
bmc = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Brand-Market_collections") * _
Range("_brand_markets_collections").Value
bmr = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Brand-Market_calls") * _
Range("_brand_markets_calls").Value
bmpc = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Brand-Market-Product_collections") * _
Range("_brand_market_products_collections").Value
bmpr = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Brand-Market-Product_calls") * _
Range("_brand_market_products_calls").Value
bp = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Brand-Product") * _
Range("_brand_products").Value
uc = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Unsuccessful Calls") * _
Range("_calls_unsuccessful").Value
br = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Brand-Responses") * _
Range("_brand_responses").Value
pr = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Product-Responses") * _
Range("_product_responses").Value
SUM_MEMBER_VALUES = p + mc + mr + c + b + bmc + bmr + bmpc + bmpr + bp + uc + br + pr
End Function
Функция вызывается внутри ячейки таблицы следующим образом:
| Фаза [cellA1] | Человек 1 | Человек 2 | Общий |
|---|---|---|---|
| Подать мяч | =SUM_MEMBER_VALUES($A2,[диапазон person1],[диапазон фазы задачи],[диапазон множителя задачи]) | =SUM_MEMBER_VALUES($A2,[диапазон person2],[диапазон фазы задачи],[диапазон множителя задачи]) | =СУММ(B2:B3) |
Эта функция работает. Проблема в том, что она не обновляется автоматически, когда я меняю значения именованных переменных. Поэтому, когда _topic меняется с 5 на 10, результаты SUM_MEMBER_VALUESне меняются.
Параметры расчета установлены на автоматический режим. Кнопки F9, SHIFT+F9 и ленты для пересчета не запускают обновление. В файле включены макросы, и я пробовал закрыть/снова открыть файл и перезагрузить компьютер.
Единственный способ, которым я смог обновить reslts, — это вручную вводить и нажимать return для каждой ячейки, вызывающей SUM_MEMBER_VALUES. Игнорировать это обременительно, а также опасно, так как легко пропустить обновление ячейки (особенно если кто-то другой, а не я, использует модель, которая является целью).
Все, что я могу придумать, это то, что он может не распознавать, что значения изменились из-за того, как значения передаются или ссылаются на функцию (т. е. я передал значение как Range, и Range не изменился, даже если содержимое Range изменилось). Однако я не могу придумать обходного пути.
ВОПРОС:Есть ли способ гарантировать, что при изменении входных значений функция VBA обновляется автоматически?
ОБНОВЛЯТЬ
SHIFT+CTRL+ALT+F9Перестраивает дерево зависимостей и выполняет полный пересчет. Кроме того, добавление Application.Volatileв начало функции должно вызывать пересчет каждый раз, когда вносятся изменения в рабочий лист; однако некоторые участники предостерегают от этого, поскольку это вызывает обновление, даже если обновление не требуется.1
Это решает мои насущные потребности, но мне интересно, есть ли у кого-нибудь решение между этими двумя решениями (не требующее ручного пересчета и не пересчитывающее каждый раз при изменении шрифта).
решение1
Возможным компромиссом было бы добавить в функцию статические переменные, содержащие «старые» значения в таблице, и пропустить оставшуюся часть кода, если нет никаких изменений... ясно как день?
Допустим, имя ListObject — Tbl1, а имена ListColumn — это имена заголовков столбцов (Task, Person 1, Person 2, Multiplier).
Static sTaskOld$, sP1Old$, sP2Old$, sMultOld$
Dim sTask$, sP1$, sP2$, sMult$
Application.Volatile
sTask = Join(Application.Transpose([Tbl1[Task]]),"")
sP1 = Join(Application.Transpose([Tbl1[Person 1]]),"")
sP2 = Join(Application.Transpose([Tbl1[Person 2]]),"")
sMult = Join(Application.Transpose([Tbl1[Multiplier]]),"")
'first recalc, static variables will be null
if sTask<>sTaskOld or sP1<>sP1Old or sP2<>sP2Old or sMult<>sMultOld then
...
end if
'save existing values to static variables
sTaskOld = sTask
sP1Old = sP1
sP2Old = sP2
sMultOld = sMult
Конечно, вместо отдельных переменных можно использовать массив, но этот пример концептуальный.
Функция все равно сработает, но, по крайней мере, вам не придется тратить много времени на обработку, сравнивая старые и текущие значения.