Tengo dificultades con una función de VBA llamada desde una celda de Excel que no se actualiza automáticamente o que no se actualiza manualmente con F9, Shift+F9, etc.
Estoy usando "Microsoft® Excel® para Microsoft 365 MSO (versión 2207, compilación 16.0.15427.20166) de 32 bits" en Windows 10.
Tengo una tabla de varios valores que se utilizan para preparar un presupuesto. Cada columna es un miembro del equipo y cada fila es una tarea. A cada usuario se le asignan horas para cada tarea. A cada tarea se le asigna un multiplicador por nombre.
Para un ejemplo sencillo:
| Tarea [celdaA1] | Persona 1 | Persona 2 | Multiplicador |
|---|---|---|---|
| Primera tarea | 1.00 | 0,25 | Tema |
| Segunda tarea | 0,05 | 0,15 | Partícipe |
Como se muestra, la Persona 1 requiere 1 hora por tema y 0,05 horas por participante. La persona 2 requiere 0,25 horas por tema y 0,15 horas por Participante.
El número de temas y participantes está contenido en rangos con nombre (es decir, "_topics", "_participants"). "Tema" y "Participante" son nombres de marcadores de posición. Cuáles son no es importante (podrían ser "Perros" o "Gatos"). Lo importante es que cada uno tiene asignado un único valor numérico. Las tareas pueden compartir multiplicadores (es decir, más de una fila puede usar "Tema").
El objetivo es modelar los requerimientos de un presupuesto complejo (muchos miembros y muchas tareas), con entradas muy simples (cambiar el número de temas, número de participantes, etc.)
Si fuera una tabla pequeña, usaría una fórmula dentro de la celda como:
=sumif(D1:D2,"Topic",B1:B2) * _topic + sumif(D1:D2,"Participant",B1:B2) * _participants
Si _topic = 5 y _participant = 10, el total para la Persona 1 sería 5,5 (1 * 5 + 0.05 * 10).
En mi caso, hay muchas filas y muchos multiplicadores, y también hay una segunda columna que asigna la tarea a una fase del proyecto, así que escribí una función VBA para mantener legible el contenido de la celda y facilitar la adición de elementos. el futuro. No es elegante, pero hace lo que necesito.
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
La función se llama dentro de la celda en una tabla como:
| Fase [celdaA1] | Persona 1 | Persona 2 | Total |
|---|---|---|---|
| Patada inicial | =SUM_MEMBER_VALUES($A2,[rango de persona1],[rango de fase de tarea],[rango de multiplicador de tarea]) | =SUM_MEMBER_VALUES($A2,[rango de persona2],[rango de fase de tarea],[rango de multiplicador de tarea]) | =SUMA(B2:B3) |
Esta función funciona. El problema que tengo es que no se actualiza automáticamente cuando cambio los valores de las variables nombradas. Entonces, cuando _topic cambia de 5 a 10, los resultados de SUM_MEMBER_VALUESno cambian.
Las opciones de cálculo están configuradas en F9 automático, MAYÚS+F9 y los botones de cinta para volver a calcular no activan la actualización. El archivo está habilitado para macros y he intentado cerrar/reabrir el archivo y reiniciar la computadora.
La única forma en que he podido actualizar los resultados es ingresando manualmente y presionando regresar para cada celda que llama SUM_MEMBER_VALUES. Ignorar eso es engorroso, también es peligroso ya que es fácil pasar por alto la actualización de una celda (especialmente si alguien que no sea yo usa el modelo que es la intención).
Lo único que se me ocurre es que es posible que no esté reconociendo que los valores cambiaron debido a la forma en que la función los pasa o hace referencia a ellos (es decir, pasé un valor como Rango y el Rango no cambió, incluso si el contenido de la gama lo hizo). Aunque no se me ocurre ninguna solución.
PREGUNTA:¿Hay alguna manera de garantizar que a medida que cambian los valores de entrada, la función VBA se actualice automáticamente?
ACTUALIZAR
SHIFT+CTRL+ALT+F9Reconstruye el árbol de dependencias y realiza un nuevo cálculo completo. Además, agregar Application.Volatileen la parte superior de la función debería forzar un recálculo cada vez que se realiza un cambio en la hoja de trabajo; sin embargo, algunos colaboradores han advertido contra esto, ya que fuerza la actualización incluso si no es necesaria.1
Esto resuelve mis necesidades inmediatas, pero tengo curiosidad por saber si alguien tiene una solución entre las dos soluciones (sin necesidad de volver a calcular manualmente y no volver a calcular cada vez que cambia una fuente).
Respuesta1
Un posible compromiso sería agregar variables estáticas a la función que contengan los valores "antiguos" en la tabla y omitir el resto del código si no hay cambios... ¿claro como el barro?
Digamos que el nombre de ListObject es Tbl1 y los nombres de ListColumn son los nombres de los encabezados de sus columnas (Tarea, Persona 1, Persona 2, Multiplicador).
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
Por supuesto, podrías usar una matriz en lugar de variables individuales, pero este ejemplo es conceptual.
La función aún se activaría, pero al menos no gastaría mucho tiempo de procesamiento comparando los valores antiguos con los actuales.