La UDF que utiliza una referencia de rango de Application.Range falla cuando se está abriendo otro libro de trabajo.

29 Jul, 2023 Programación 0

Estoy utilizando la UDF que depende del nombre del objeto de lista que se le pasa. El usuario no tiene que saber dónde se encuentra una tabla de referencia (objeto de lista) en el libro, ya que es un libro grande y complejo. Por esta razón, la UDF se refiere a la tabla por su nombre, omitiendo el nombre de la hoja de cálculo, de la siguiente manera:

Set Obj = Application.Range(NombreTabla).ListObject

Esto funciona perfectamente siempre y cuando el código esté en el libro activo. No funciona si se activa otro libro. Por alguna razón, cuando se abre otro libro, el código VBA de la UDF se activa y falla, lo que resulta en salidas de #VALUE en todo mi libro.
He intentado varias formas de hacer referencia explícita a un objeto de lista, y la única que funciona parece ser:

ObjetoLibro.ObjetoHoja.ListObject(NombreTabla)

Sin embargo, como mencioné antes, estoy tratando de evitar utilizar una referencia de hoja de cálculo.

¿Es posible hacer referencia a un objeto de lista en un libro no activo a través de una referencia explícita, sin necesidad de utilizar una referencia de hoja de cálculo?

Si no es posible, ¿cuál es la mejor manera de evitar que una UDF se ejecute innecesariamente cuando se abren otros libros? He intentado forzar la recalculación de la UDF utilizando el método Application.Volatile, pero debido al tamaño del libro, ralentiza considerablemente su funcionamiento.
Cualquier ayuda es muy apreciada.

Aclaración basada en el comentario de @BigBen:
Debido a que la mayor parte del libro actúa en segundo plano, el nombre de la tabla no se pasa como un valor explícito, en su lugar, la UDF toma las entradas del usuario (cadenas) y decide el nombre de la tabla a llamar en base a estas entradas.

Editar:

Gracias a todos. Ahora he resuelto el problema, basado en la sugerencia de Tim Williams, de la siguiente manera:

Dim ws As Worksheet, LObj As ListObject
If ActiveWorkbook Is ThisWorkbook Then
    Set Obj = Application.Range(Tabl).ListObject
Else
    For Each ws In ThisWorkbook.Worksheets
        For Each LObj In ws.ListObjects
           If LObj.Name = Tabl Then Set Obj = LObj: Exit For
        Next
    Next
End If

Disculpenme por ser tan estúpido antes.

Hacer referencia a un ListObject en un libro de trabajo no activo sin utilizar una referencia de Worksheet

No es posible hacer referencia directamente a un ListObject en un libro de trabajo no activo sin utilizar una referencia de Worksheet. Sin embargo, hay una solución alternativa para lograr esto sin utilizar una referencia de Worksheet. En su lugar, puedes buscar el ListObject en todas las hojas de cálculo del libro de trabajo activo y asignarlo a un objeto. Aquí tienes un ejemplo de código:

Dim ws As Worksheet, LObj As ListObject For Each ws In ThisWorkbook.Worksheets For Each LObj In ws.ListObjects If LObj.Name = NombreTabla Then Set Obj = LObj: Exit For Next Next

Evitar ejecuciones innecesarias de una UDF cuando se abre otro libro de trabajo

Una forma de evitar ejecuciones innecesarias de una UDF cuando se abre otro libro de trabajo es comprobar si el libro de trabajo activo es el mismo que contiene la UDF. Si no son el mismo, entonces la UDF debería devolver un valor en blanco. Aquí tienes un ejemplo de código:

Function MiUDF(Tabl As String) As Variant If ActiveWorkbook Is ThisWorkbook Then 'tu código de UDF aquí' Else MiUDF = "" End If End Function

Otra solución es utilizar el evento Worksheet_Change para establecer un indicador cuando se recalcule la UDF. El indicador se puede comprobar al principio de la UDF para determinar si es necesario recalcularla o no. Aquí tienes un ejemplo de código:

Private NecesitaRecalculo As Boolean Private Sub Workbook_Open() NecesitaRecalculo = True End Sub Private Sub Workbook_SheetCalculate(ByVal Sh As Object) NecesitaRecalculo = True End Sub Function MiUDF(Tabl As String) As Variant If Not NecesitaRecalculo Then Exit Function NecesitaRecalculo = False 'tu código de UDF aquí' End Function

Ten en cuenta que este método puede ralentizar el libro de trabajo si es grande, pero puede ser efectivo para libros de trabajo más pequeños.

En general, es importante considerar las necesidades y características específicas de tu libro de trabajo para determinar el mejor enfoque para hacer referencia a un ListObject en un libro de trabajo no activo y evitar ejecuciones innecesarias de una UDF.

Answer

davy.ai

July 29, 2023 at 1:20 pm
Posibles formas de hacer referencia a un ListObject en un libro de trabajo no activo y evitar ejecuciones innecesarias de una UDF

Hacer referencia a un ListObject en un libro de trabajo no activo sin utilizar una referencia de Worksheet

No es posible hacer referencia directamente a un ListObject en un libro de trabajo no activo sin utilizar una referencia de Worksheet. Sin embargo, hay una solución alternativa para lograr esto sin utilizar una referencia de Worksheet. En su lugar, puedes buscar el ListObject en todas las hojas de cálculo del libro de trabajo activo y asignarlo a un objeto. Aquí tienes un ejemplo de código:
```
Dim ws As Worksheet, LObj As ListObject
For Each ws In ThisWorkbook.Worksheets
    For Each LObj In ws.ListObjects
        If LObj.Name = NombreTabla Then Set Obj = LObj: Exit For
    Next
Next
```
Evitar ejecuciones innecesarias de una UDF cuando se abre otro libro de trabajo

Una forma de evitar ejecuciones innecesarias de una UDF cuando se abre otro libro de trabajo es comprobar si el libro de trabajo activo es el mismo que contiene la UDF. Si no son el mismo, entonces la UDF debería devolver un valor en blanco. Aquí tienes un ejemplo de código:
```
Function MiUDF(Tabl As String) As Variant
    If ActiveWorkbook Is ThisWorkbook Then
        'tu código de UDF aquí'
    Else
        MiUDF = ""
    End If
End Function
```
Otra solución es utilizar el evento Worksheet_Change para establecer un indicador cuando se recalcule la UDF. El indicador se puede comprobar al principio de la UDF para determinar si es necesario recalcularla o no. Aquí tienes un ejemplo de código:
```
Private NecesitaRecalculo As Boolean

Private Sub Workbook_Open()
    NecesitaRecalculo = True
End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    NecesitaRecalculo = True
End Sub

Function MiUDF(Tabl As String) As Variant
    If Not NecesitaRecalculo Then Exit Function
    NecesitaRecalculo = False
    'tu código de UDF aquí'
End Function
```
Ten en cuenta que este método puede ralentizar el libro de trabajo si es grande, pero puede ser efectivo para libros de trabajo más pequeños.

En general, es importante considerar las necesidades y características específicas de tu libro de trabajo para determinar el mejor enfoque para hacer referencia a un ListObject en un libro de trabajo no activo y evitar ejecuciones innecesarias de una UDF.

Comments are closed.

es.davy.ai

¿Tienes una pregunta?

La UDF que utiliza una referencia de rango de Application.Range falla cuando se está abriendo otro libro de trabajo.

Answer

davy.ai

Posibles formas de hacer referencia a un ListObject en un libro de trabajo no activo y evitar ejecuciones innecesarias de una UDF

Hacer referencia a un ListObject en un libro de trabajo no activo sin utilizar una referencia de Worksheet

Evitar ejecuciones innecesarias de una UDF cuando se abre otro libro de trabajo