sobre - Haciendo que las funciones de Excel afecten a ''otras'' celdas

todas las formulas de excel

Question

Digamos que creo un Sub (no una función) cuya misión en la vida es tomar la celda activa (es decir, Selección) y establecer una celda adyacente a algún valor. Esto funciona bien

Cuando intentes convertir ese Sub en una Función e intentes evaluarlo desde una hoja de cálculo (es decir, establecer su fórmula en "= MiFunción ()"), Excel ladrará al ver que estás intentando afectar el valor de la inactividad. celda, y simplemente fuerce la función para devolver #VALOR sin tocar la celda adyacente.

¿Es posible desactivar este comportamiento protector? Si no, ¿cuál es una buena forma de evitarlo? Estoy buscando algo que un desarrollador competente podría lograr durante un período de 1-2 semanas, si es posible.

Saludos, Alan.

Nota: Estoy usando 2002, por lo que preferiría una solución que funcione para esa versión. Habiendo dicho eso, si las versiones futuras lo hacen significativamente más fácil, me gustaría saberlo también.

Answer 1

De acuerdo con Cómo crear funciones de Excel definidas por el usuario personalizado :

Limitaciones de UDF

• No se puede colocar un valor en una celda que no sea la celda (o rango) que contiene la fórmula. En otras palabras, las UDF deben usarse como "fórmulas", no necesariamente como "macros".

Entonces, parece que no se puede hacer.

Answer 2

Estoy usando Excel 2007, y no funciona. Excel menciona que crea una referencia circular. No creo que puedas alterar otras celdas de una función, solo devuelve un valor.

Es una especie de programación funcional, sin efectos secundarios. Si pudieras simplemente alterar otras celdas dentro de una función (usada desde una hoja de trabajo), entonces no hay manera de que Excel sepa el orden y qué recalcular si cambia una celda.

Este artículo también contiene mucha información acerca de cómo Excel hace el recálculo. Pero nunca dice que las otras celdas están congeladas.

No sé qué estás tratando de hacer, pero, ¿por qué no colocas otra función en la celda adyacente, que toma la primera celda como parámetro?

Ejemplo:

Public Function Bar(r As Range) As Integer
  If r.Value = 2 Then
    Bar = 0
  Else
    Bar = 128
  End If
End Function

Answer 3

Gracias a todos por responder. ¡Es posible hacer esto! Un poco Digo ''algo'' porque técnicamente hablando, la ''función'' no está afectando a las células a su alrededor. En términos prácticos, sin embargo, ningún usuario podría notar la diferencia.

El truco es usar una API de Win32 para iniciar un temporizador, y tan pronto como se apague, haga lo que quiera en cualquier celda y apague el temporizador.

Ahora no soy un experto en cómo funciona el subprocesamiento COM (aunque sé que VBA tiene Single Apartment Threaded), pero tenga cuidado con su temporizador que se escapa con su proceso de Excel y lo bloquea. Esto realmente no es algo que sugeriría como una solución para cada otra hoja de cálculo.

Solo crea un módulo con estos contenidos:

Option Explicit

Declare Function SetTimer Lib "user32" (ByVal HWnd As Long, _
  ByVal IDEvent As Long, ByVal mSec As Long, _
  ByVal CallFunc As Long) As Long

Declare Function KillTimer Lib "user32" (ByVal HWnd As Long, _
  ByVal timerId As Long) As Long

Private timerId As Long

Private wb As Workbook
Private rangeName As String
Private blnFinished As Boolean

Public Sub RunTimer()

    timerId = SetTimer(0, 0, 10, AddressOf Woohoo)


End Sub


Public Sub Woohoo()

    Dim i As Integer

''    For i = 0 To ThisWorkbook.Names.Count - 1
''        ThisWorkbook.Names(i).Delete
''    Next

     ThisWorkbook.Worksheets("Sheet1").Range("D8").Value = "Woohoo"

     KillTimer 0, timerId

End Sub

Answer 4

Si bien no puede hacer esto en Excel, es posible en Resolver One (aunque todavía es algo bastante extraño de hacer).

Es una hoja de cálculo que le permite definir funciones personalizadas en Python que luego puede llamar desde una fórmula de celda en la grilla.

Como ejemplo de lo que está preguntando, es posible que desee definir una función de safeDivide que le ZeroDivisionError informado sobre el problema (en lugar de generar un ZeroDivisionError ), coloreando la celda del denominador y colocando un mensaje de error al lado. Puedes definirlo así:

def safeDivide(numerator, cellRange):
    if not isinstance(cellRange, CellRange):
        raise ValueError(''denominator must be a cell range'')
    denominator = cellRange.Value
    if denominator == 0:
        cell = cellRange.TopLeft
        cell.BackColor = Color.Red
        cell.Offset(1, 0).Value = ''Tried to divide by zero''
        return 0
    return numerator / denominator

Hay una arruga adicional: las funciones que obtienen células pasadas pasan el valor de la celda, por lo tanto, para evitarlo, insistimos en pasar un rango de células de una celda para el denominador.

Si intenta hacer cosas inusuales con hojas de cálculo que no encajan en Excel, o si está interesado en utilizar el poder de Python para trabajar con sus datos de hoja de cálculo, vale la pena echarle un vistazo a Resolver One.

Answer 5

Aquí hay una solución alternativa de VBA que funciona. Para este ejemplo, abra un nuevo libro de Excel y copie el siguiente código en el área de código para Sheet1 (no ThisWorkbook o un Module VBA). Luego vaya a Sheet1 y coloque algo en una de las celdas de la parte superior izquierda de la hoja de trabajo. Si escribe un número y presiona Enter, la celda de la derecha se actualizará con 4 veces el número y el fondo de la celda se volverá azul claro. Cualquier otro valor hace que se borre la siguiente celda. Aquí está el código:

Dim busy As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
  If busy Then Exit Sub
  busy = True
  If Target.Row <= 10 And Target.Column <= 10 Then
    With Target.Offset(0, 1)
      If IsNumeric(Target) Then
        .Value = Target * 4
        .Interior.Color = RGB(212, 212, 255)
      Else
        .Value = Empty
        .Interior.ColorIndex = xlColorIndexNone
      End If
    End With
  End If
  busy = False
End Sub

La subrutina captura todos los eventos de cambio celular en la hoja. Si la fila y la columna son ambas <= 10, entonces la celda de la derecha se establece en 4 veces la celda modificada si el valor es numérico; de lo contrario, se borrará la celda de la derecha.

Answer 6

No se puede hacer, lo cual tiene sentido porque:

• Cuando se llama a una función de hoja de cálculo, la celda que contiene la función no es necesariamente la celda activa. Entonces no puede encontrar la celda adyacente de manera confiable.

• Cuando Excel vuelve a calcular una hoja de trabajo, necesita mantener dependencias entre las celdas. Por lo tanto, no puede permitir que las funciones de la hoja de trabajo modifiquen arbitrariamente otras celdas.

Lo mejor que puedes hacer es uno de:

• Maneje el evento SheetChange. Si una celda que contiene su función está cambiando, modifique la celda adyacente.

• Coloque una función de hoja de cálculo en la celda adyacente para devolver el valor que desea.

Actualizar

Respecto al comentario: "Me gustaría que esta función funcione en una hoja de cálculo en blanco, así que no puedo confiar en el evento SelectionChange de las hojas de cálculo que aún no existen, pero necesitaré llamar a esta función":

• ¿Puedes poner tu función en un complemento XLA? Entonces su complemento XLA puede manejar el evento Application SheetChange (*) para todos los libros que se abren en esa instancia de Excel?

En cuanto al comentario: "Aún así, si mantiene Excel en CalculationMode = xlManual y completa solo los valores, debería estar bien"

• Incluso cuando CalculationMode es xlManual, Excel necesita mantener un árbol de dependencias de referencias entre celdas para que pueda calcular en el orden correcto. Y si una de las funciones puede actualizar una celda arbitraria, esto arruinará el orden. Que es presumiblemente por qué Excel impone esta restricción.

(*) Originalmente escribí SelectionChange arriba, corregido ahora; por supuesto, el evento correcto es SheetChange para el libro de trabajo u objetos de la aplicación, o cambio para el objeto de la hoja de trabajo.

Actualización 2 Algunos comentarios sobre la publicación de AlanR que describen cómo ''hacer'' que funcione usando un temporizador:

• No está claro cómo la función del temporizador ("Woohoo") sabrá qué celdas actualizar. No tiene información que indique qué celda contiene la fórmula que activó el temporizador.

• Si la fórmula existe en más de una celda (en el mismo libro o en libros diferentes), entonces se llamará al UDF varias veces durante un nuevo cálculo, sobrescribiendo el timerId. Como resultado, no podrá destruir el temporizador de manera confiable y perderá recursos de Windows.