Uma mudança empolgante aconteceu à função XLOOKUP na atualização do Office Insiders lançada em 1º de novembro de 2019. Muitos Insiders receberão essa atualização quando chegarem para trabalhar na segunda-feira, 4 de novembro de 2019.
Se você estiver usando a nova função XLOOKUP e se tiver usado o argumento Match_Mode para procurar o valor apenas maior ou menor, suas funções XLOOKUP existentes serão interrompidas.
A nova mudança em XLOOKUP: o argumento If_Not_Found, que foi originalmente adicionado como um sexto argumento opcional, foi movido para ser o quarto argumento.
Considere a seguinte fórmula, que anteriormente solicitava a próxima correspondência maior:
=XLOOKUP(A2,H2:H99,J2:J99,1)
Quando você abre uma pasta de trabalho com uma fórmula como esta, a fórmula não é interrompida imediatamente. O recálculo inteligente do Excel não recalcula a fórmula até que você edite a fórmula ou até que você edite um dos números em H2: H99 ou J2: J99.
No entanto, depois de editar a tabela de pesquisa, o Excel recalcula todas as funções XLOOKUP que usaram a tabela. Antes da mudança, você estava solicitando uma correspondência aproximada que retornasse o próximo valor maior. Após a alteração, você está solicitando uma correspondência exata (porque sua fórmula original não tem um quinto argumento) e também especificando acidentalmente que se uma correspondência exata não for encontrada, você deseja inserir um 1 como resultado.
"É realmente um jogo insidioso de whack-a-mole", disse Bill Jelen, editor do.com. Você pressiona F2 para ver uma fórmula e a fórmula para de funcionar. Outras fórmulas na planilha podem parecer que continuam funcionando, mas são uma bomba-relógio esperando para se tornar errada quando um recálculo for acionado. "
Para ver a mudança acontecendo, assista da marca de 0:35 a 0:55 segundos neste vídeo:
Assistir vídeo
Quando você se inscreve no programa Office Insiders, o parágrafo 7c dos Termos e Condições diz que "Podemos lançar os Serviços ou seus recursos em uma versão prévia ou beta, que pode não funcionar corretamente ou da mesma forma que a versão final pode funcionar . "
A equipe do Excel informa que você precisa ajustar todas as fórmulas XLOOKUP que estavam usando os argumentos opcionais. Se você usa o XLOOKUP com frequência, o código a seguir examinará uma pasta de trabalho e identificará as possíveis fórmulas de problemas.
Versão Básica
O código a seguir procura as células da fórmula que começam com =XLOOKUPe contêm mais de 2 vírgulas.
Sub findXLOOKUPs() Dim sht As Worksheet Dim cll As Range Dim foundCells As String Set sht = ActiveSheet For Each cll In sht.UsedRange If cll.HasFormula Then If InStr(cll.Formula, "=XLOOKUP") = 1 Then If UBound(Split(cll.Formula, ","))> 2 Then foundCells = foundCells & vbCrLf & cll.Address End If End If End If Next cll If foundCells = "" Then MsgBox sht.Name & " is not affected with XLOOKUP function structure update.", vbOKOnly + vbInformation, "No errors" Else MsgBox sht.Name & " is likely affected with XLOOKUP function structure update. Please check formulas in the following cells:" & foundCells, vbOKOnly + vbExclamation, "Error(s) found" End If End SubVersão Regex
O código a seguir está usando Regex para localizar várias funções XLOOKUP usadas na mesma fórmula ou usadas com outras funções podem conter vírgulas adicionais.
* Você precisa adicionar a referência das Expressões regulares do Microsoft VBScript no Visual Basic para usar este código (Ferramentas> Referências no VBA).
Sub advancedFindXLOOKUPs() Dim sht As Worksheet Dim cll As Range Dim rgx As RegExp Dim rMatches As Object Dim rMatch As Object Dim foundCells As String Set sht = ActiveSheet Set rgx = New RegExp With rgx .Pattern = "XLOOKUP(((^,))*,)(3,)(^,)*)" .MultiLine = False .IgnoreCase = True .Global = True End With For Each cll In sht.UsedRange If cll.HasFormula Then Set rMatches = rgx.Execute(cll.Formula) If rMatches.Count Then For Each rMatch In rMatches 'Debug.Print rMatch foundCells = foundCells & vbCrLf & cll.Address Next rMatch End If End If Next cll If foundCells = "" Then MsgBox sht.Name & " is not affected with XLOOKUP function structure update.", vbOKOnly + vbInformation, "No errors" Else MsgBox sht.Name & " is likely affected with XLOOKUP function structure update. Please check formulas in the following cells:" & foundCells, vbOKOnly + vbExclamation, "Error(s) found" End If End Sub
