As células avaliadas pelo módulo definido pelo usuário não são atualizadas automaticamente

Question

Eu li as perguntas existentes, mas nenhuma resposta pareceu ajudar. Eu tenho uma função que eu escrevi em VB, que avalia a soma das células em um determinado intervalo por cor da fonte, tudo funciona bem quando eu digito a função em (a avaliação está correta).

Mas quando eu altero a cor de uma célula (uma célula que está no intervalo da função), o valor da soma não é alterado. Ele só muda (naquela certa célula) quando eu re-entrar na função (eu não tenho que apagar completamente, eu só tenho que pressionar enter novamente ao marcar a função).

Qual poderia ser o problema? Todas as configurações parecem corretas e eu tentei abrir uma nova planilha, mas o problema insiste.

Nota:

Ctrl + Alt + F9 funciona, mas Application.Volatile não (embora eu esteja no Excel 2013).

Aqui está meu código mesmo assim (embora esteja funcionando):

Public Function SumByColor(rng As Range, clr As String) As Double
Application.Volatile

Dim s As Double
s = 0
Dim r As Range
Dim c As Long
If clr = "red" Then
    c = RGB(256, 0, 0)
End If
If clr = "black" Then
    c = RGB(0, 0, 0)
End If

For Each r In rng
    If r.Font.Color = c Then
        s = s + r.Value
    End If
Next r

SumByColor = s
End Function

    

Answer 1

Application.Volatile não é suficiente ......... a função será calculada toda vez que a planilha for calculada e isso pode não acontecer se você apenas mudar de cor.

EDIT # 1:

Primeiro, fazemos uma mudança de uma linha para o seu UDF () para que ele aceite um argumento variante opcional que não usa!:

Public Function SumByColor(rng As Range, clr As String, Optional v As Variant) As Double
   Application.Volatile
   Dim s As Double
   s = 0
   Dim r As Range
   Dim c As Long

   If clr = "red" Then
       c = RGB(256, 0, 0)
   End If
   If clr = "black" Then
       c = RGB(0, 0, 0)
   End If

   For Each r In rng
       If r.Font.Color = c Then
           s = s + r.Value
       End If
   Next r

   SumByColor = s
End Function

e, em seguida, na planilha, use algo como:

=SumByColor(A1:A14,"red",NOW())

A idéia é que, se você tocar em F9 , NOW() será recalculado e o mesmo será UDF () .