Fórmula personalizada não está atualizando

Question

Eu segui este guia para criar a seguinte função personalizada que conta o número de células de uma determinada cor na minha planilha:

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
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

Ele funciona bem e me fornece o resultado esperado se eu clicar no marcador verde na barra de fórmulas, mas não atualizarei automaticamente depois que eu colorir outra célula. Como configuro para atualizar automaticamente a contagem toda vez que eu colorir manualmente uma célula em outro lugar na planilha?

Informações adicionais: estou usando o Excel no Mac e verifiquei as configurações e o cálculo automático está ativado.

    

Answer 1

O Excel recalcula os UDFs somente quando suas alterações de entrada

Você criou uma função definida pelo usuário (UDF). O Excel só executa o código de uma UDF quando as células que servem como entrada para a função são alteradas.

Por exemplo, suponha que eu tenha essa UDF:

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

E na célula A1 em minha planilha, faço referência à célula com a fórmula:

=MyFunction(A2)

Durante o uso normal da minha pasta de trabalho, o código da minha UDF só será chamado se houver uma alteração no conteúdo da célula A2 ou outra célula referenciada por uma fórmula na A2.

Soluções

Você pode contornar esse comportamento de várias maneiras:

1. Force o Excel a recalcular manualmente todas as fórmulas da pasta de trabalho, mesmo que elas não tenham sido alteradas desde o último cálculo, por pressionando Ctrl + Alt + F9 no Windows e (suponho no Mac) Cmd + Alt + F9 . Se isso não funcionar, você também pode tentar adicionar Shift que adicionalmente verifica as fórmulas dependentes antes de recalcular.

2. Inclua uma função volátil em uma das células referenciadas pelo seu UDF. Faça isso modificando a definição da sua função VBA para aceitar um parâmetro adicional:

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

   Em seguida, edite a célula que faz referência à sua UDF para passar o resultado de uma função volátil, como Now() , para a UDF:

   =MyFunction(A2,Now())
   

   O resultado líquido será que o Excel considerará a célula que contém a referência ao seu UDF como precisando ser recalculada toda vez que a planilha for alterada porque ela faz referência a uma função volátil.

3. Edite a célula que contém o UDF. Basta entrar na célula e, em seguida, pressionar Enter sem fazer alterações devem ser suficientes para acionar uma atualização.

4. Crie um botão de macro que execute a seguinte linha de código :

   Application.CalculateFull
   

5. Coloque o código a seguir no evento Open da sua Pasta de Trabalho:

   ActiveWorkbook.ForceFullCalculation = True
   

   Isso faz com que o Excel sempre recalcule todas as fórmulas , independentemente de ser necessário recalcular. Essa configuração permanece em vigor até que o Excel seja reiniciado.

Por que o Excel nunca recalcula meu UDF?

Quando o Excel executa um recálculo automático, ele não recalcula todas as fórmulas na pasta de trabalho. Em vez disso, ele apenas atualiza as células que contêm fórmulas que se referem à célula modificada mais recentemente. Esse método evita o processo muito mais demorado de executar desnecessariamente muitos cálculos em toda a pasta de trabalho apenas para obter o mesmo resultado para a grande maioria deles.

Com um UDF, as únicas referências na pasta de trabalho das quais o mecanismo de cálculo do Excel está ciente são aquelas identificadas nas entradas da função. O Excel não pode examinar o código VBA dentro da UDF e identificar outras células que podem influenciar a saída da função. Portanto, embora o autor da função possa construir a função para retornar resultados diferentes com base em qualquer número de alterações feitas na pasta de trabalho, as únicas células que o Excel sabe alterarão o resultado da função são as entradas declaradas. Portanto, as alterações nessas células são as únicas em que o Excel presta atenção ao decidir se a UDF precisa ser recalculada.

    

Answer 2

As alterações em

Format (que incluem Color Changes) não são consideradas eventos que valem a pena um recálculo no Excel. Isso não está relacionado a funções definidas pelo usuário. Portanto, qualquer alteração no formato não resultará em um recálculo.

Isso não é arbitrário, mas faz algum sentido, já que normalmente mudanças de formato não influenciam mais nada, então um recálculo - que é caro - seria desperdiçado. Como o Excel agora permite que o VB use informações de formato e reaja a ele, isso pode se tornar um problema, assim como é para você.

Você pode ser capaz de definir funções OnChange para todas as células e disparar um recálculo, mas uma alteração de formato pode simplesmente não disparar essa função (nunca tentei).