Fórmula personalizada que no se actualiza

Si encuentras algo que no comprendes nos puedes dejar un comentario y te ayudaremos lo mas rápido que podamos.

Solución:

Excel vuelve a calcular las UDF solo cuando cambia su entrada

Ha creado una función definida por el usuario (UDF). Excel solo ejecuta el código de una UDF cuando cambian las celdas que sirven como entrada para la función.

Por ejemplo, supongamos que tengo esta UDF:

Public Function MyFunction(Target As Range)
MsgBox "The target cell's address is " &  Target.Address
End Function

Y en la celda A1 de mi hoja de trabajo hago referencia a la celda con la fórmula:

=MyFunction(A2)

Durante el uso normal de mi libro de trabajo, el código de mi UDF solo se llamará si hay un cambio en el contenido de la celda A2 u otra celda a la que hace referencia una fórmula en A2.

Soluciones

Puede solucionar este comportamiento de varias formas:

1. Obligar a Excel a recalcular manualmente todas las fórmulas en el libro de trabajo incluso si no han cambiado desde el último cálculo presionando control+Alt+F9 en Windows y (supongo que en Mac) Cmd+Alt+F9. Si eso no funciona, también puede intentar agregar Cambio que además vuelve a comprobar las fórmulas dependientes antes de volver a calcular.

2. Incluya una función volátil en una de las celdas a las que hace referencia su UDF. Haga esto modificando la definición de su función VBA para aceptar un parámetro adicional:

   Public Function MyFunction(Target As Range, Optional VolatileParameter As Variant)
   

   Luego edite la celda que hace referencia a su UDF para pasar el resultado de una función volátil como Now() a la UDF:

   =MyFunction(A2,Now())
   

   El resultado neto será que Excel considerará que la celda que contiene la referencia a su UDF necesita ser recalculada cada vez que se cambia la hoja de trabajo porque hace referencia a una función volátil.

3. Edite la celda que contiene la UDF. Simplemente ingresando a la celda y luego presionando Enter sin hacer cambios debería ser suficiente para activar una actualización.

4. Crear macro botón que ejecuta la siguiente línea de código:

   Application.CalculateFull
   

5. Coloque el siguiente código en el evento Open de su libro de trabajo:

   ActiveWorkbook.ForceFullCalculation = True
   

   Esto hace que Excel siempre recalcular todas las fórmulas, independientemente de si cree que es necesario volver a calcularlas. Esta configuración permanece en vigor hasta que se reinicia Excel.

---

¿Por qué Excel no recalcula siempre mi UDF?

Cuando Excel realiza un recálculo automático, no vuelve a calcular todas y cada una de las fórmulas del libro. En su lugar, solo actualiza aquellas celdas que contienen fórmulas que se refieren a la celda modificada más recientemente. Este método evita el proceso mucho más largo de realizar innecesariamente muchos cálculos en todo el libro de trabajo solo para obtener el mismo resultado para la gran mayoría de ellos.

Con una UDF, las únicas referencias en el libro de trabajo que el motor de cálculo de Excel tiene en cuenta son las identificadas en las entradas de la función. Excel no puede examinar el código VBA dentro de la UDF e identificar otro celdas que pueden influir en la salida de la función. Entonces, si bien el autor de la función puede construir la función para devolver diferentes resultados en función de cualquier número de cambios realizados en el libro de trabajo, las únicas celdas que Excel sabe que cambiarán el resultado de la función son las entradas declaradas. Por lo tanto, los cambios en estas celdas son los únicos a los que Excel presta atención al decidir si es necesario volver a calcular la UDF.

Una función definida por el usuario se puede marcar como volátil por sí misma, lo que obliga a volver a calcular cada vez que algo cambia en el libro de trabajo.

Para hacer eso, tienes que llamar Application.Volatile desde dentro de la función.

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Application.Volatile

Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
    If SUM = True Then
       For Each rCell In rRange
        If rCell.Interior.ColorIndex = lCol Then
                vResult = WorksheetFunction.SUM(rCell) + vResult
        End If
       Next rCell
    Else
        For Each rCell In rRange
        If rCell.Interior.ColorIndex = lCol Then
                vResult = 1 + vResult
        End If
       Next rCell
End If
ColorFunction = vResult
End Function

Golpeé esto con la esperanza de actualizar todas las funciones definidas por el usuario cada vez que se cambiaba un cuadro de texto ActiveX, incluso si no afectaban las celdas vinculadas que se usaban como entradas a la UDF. Por accidente descubrí que incluyendo alguna La referencia de celda indirecta en la llamada de función parece hacer que se ejecute siempre que (aparentemente) se edite cualquier otra celda. Presumiblemente, esto se debe a que Excel tiene menos garantías sobre lo que puede confiar que no se modificará cuando se use un modificador indirecto. Esto es similar a la segunda respuesta de Twisty Impersonator pero agrega menos cruft a su código vba y fórmulas (siempre que se use al menos un rango en la función).

Ejemplo: esta función siempre debe devolver el valor en ‘A2’

Public Function test(some_arg)
test = Sheets("Sheet1").Range("A2").Value
End Function

Si lo llamas como =test(B1) no se actualizará cada vez que A2 cambie si lo llama como =test(INDIRECT(ADDRESS(ROW(B1),COLUMN(B1))) es funcionalmente equivalente y se actualizará siempre que A2 cambie.

=test(INDIRECT("B1")) también funcionará y puede ser más claro de entender para este ejemplo, pero debido a que “B1” es un string no podrá copiar la fórmula en nuevos valores.

Aquí tienes las reseñas y puntuaciones

Te invitamos a añadir valor a nuestro contenido informacional asistiendo con tu veteranía en las notas.

---

---

Utiliza Nuestro Buscador