Como calcular / resolver automaticamente uma célula nomeada dentro de uma fórmula?

Question

Eu tenho A1 = 5 e adicionei A1 como um nome " Protagonista ".

Se eu fizer =Protagonist , vai aparecer 5 .

Digamos que em D12 eu quero fazer =Protagonist+1 . Vai aparecer 6 .

• O que eu quero alcançar é substituir o " protagonista " pelo 5 e a fórmula D12 permanece em =5+1 .

Eu preciso fazer isso, pois A1 muda constantemente seu valor e eu só preciso manter o valor que ele tem no momento de fazer uma fórmula que o use.

(Manualmente eu sei que eu posso selecionar Protagonista e apertar F9 para resolver apenas essa parte da fórmula, mas isso é um grande consumo de tempo nas quantidades que eu tenho que fazer isso)

Isso pode acontecer depois que a fórmula é inserida e resolvida. Apesar de que seria incrível se pudesse acontecer na barra de fórmulas durante a digitação, mas eu acho que o vba não se estende tão longe.

    

Answer 1

Se você precisar substituí-lo pelo valor calculado, então:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count = 1 Then
    If Left(Target.Formula, 1) = "=" And IsNumeric(Intersect(Range("B1:F1000"), Target)) Then
      Dim a As Variant
      For Each a In Array("protagonist", "anotherNameToChange")
        While InStr(1, Target.Formula, a, 1)
          Target.Formula = Replace(Target.Formula, a, Evaluate(ThisWorkbook.Names(a).Value), , , 1)
        Wend
      Next
    End If
  End If
End Sub

Fará isso para todos os nomes na matriz também automaticamente conforme desejado ...

Se houver necessidade de fazer isso para cada nome, altere as seguintes linhas:

For Each a In Array("protagonist", "anotherNameToChange")

muda para:

For Each a In ThisWorkbook.Names

e

Target.Formula = Replace(Target.Formula, a, Evaluate(ThisWorkbook.Names(a).Value), , , 1)

para

Target.Formula = Replace(Target.Formula, a, Evaluate(a.Value), , , 1)

Apenas observe que isso não diferencia maiúsculas de minúsculas:)

    

Answer 2

Experimente:

Sub qwerty()
   Dim N As Name, FixString As String

   Set N = ActiveWorkbook.Names("Protagonist")
   FixString = N.RefersToRange

   For Each cell In Cells.SpecialCells(xlCellTypeFormulas)
      cell.Formula = Replace(cell.Formula, "Protagonist", FixString)
   Next cell
End Sub

EDIT # 1:

Para chamar qwerty de uma Macro de eventos , use algo como:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Intersect(Target, Range("A1:C100")) Is nothen Then
   Else
      Application.EnableEvents = False
         Call qwerty
      Application.EnableEvents = True
   End If
End Sub

    

Answer 3

Pelo que entendi, isso não é possível.

Eu suspeito que a resposta, se é que existe, é entender o raciocínio (a lógica de negócios) por trás de você querendo esse comportamento, reformatando a pasta de trabalho para se adequar.