VBA VLOOKUP e XLOOKUP

Este artigo demonstrará como usar as funções VLOOKUP e XLOOKUP no VBA.

As funções VLOOKUP e XLOOKUP (PROCV e PROCX) do Excel são extremamente úteis. Elas também podem ser usadas na codificação VBA.

VLOOKUP

Há duas maneiras de chamar a função VLOOKUP no VBA:

• Application.WorksheetFunction.Vlookup
• Application.Vlookup

Os dois métodos funcionam de forma idêntica, exceto pela maneira como tratam os erros.

Application.WorksheetFunction.VLookup

Este exemplo demonstrará o primeiro método.

 

Aqui, criamos uma fórmula VLOOKUP (PROCV) no Excel que será replicada no VBA:

Sub PesquisaPreco()
   ActiveCell = Application.WorksheetFunction.VLookup(Range("E3"), Range("B2:C6"), 2, False)
End Sub

Isso resultará no seguinte:

Observe que o resultado real foi gravado na célula F3 em vez de na fórmula!

Se quisermos retornar uma fórmula para o Excel em vez de um valor, escreva o VLOOKUP em uma fórmula:

Sub PesquisaPreco()
   ActiveCell = "=VLOOKUP(E3,B2:C6,2,FALSE)"
End Sub

Ou você pode escrever a fórmula em uma célula usando a notação R1C1, que cria uma fórmula com referências relativas que podem ser usadas em um intervalo de células:

Sub PesquisaPreco() 
  ActiveCell = "=VLOOKUP(RC[-1],RC[-4]:R[3]C[-3],2,FALSE)"
End Sub

Você também pode usar variáveis para definir as entradas VLOOKUP:

Sub PesquisaPreco()
 Dim strProduct As String
 Dim rng As Range 
 strProduct = Range("E3") 
 Set rng = Range("B2:C6") 
 ActiveCell = Application.WorksheetFunction.VLookup(strProduct, rng, 2, False)
End Sub

É claro que, em vez de gravar os resultados da função em uma célula, você pode gravar os resultados em uma variável:

Sub PesquisaPreco() 
  Dim strProduct As String
  Dim rng As Range 
  Dim strResult as String
 
  strProduct = Range("E3") 
  Set rng = Range("B2:C6") 
  strResult = Application.WorksheetFunction.VLookup(strProduct, rng, 2, False) 
End Sub

Application.VLookup vs. Application.WokrsheetFunction.VLookup

Nos exemplos acima, podemos usar qualquer um dos métodos e eles retornarão o mesmo resultado. No entanto, e se estivéssemos procurando um produto que não existe – que resultado isso retornaria? Cada função retornaria um resultado muito diferente.

Normalmente, ao usar a função PROCV no Excel, se a pesquisa não encontrar a resposta correta, ela retornará #N/D para a célula. No entanto, se usarmos o exemplo acima e procurarmos um produto que não existe, acabaremos com um erro de VBA.

Podemos detectar esse erro em nosso código e retornar uma mensagem mais amigável ao usuário quando o item que estamos procurando não for encontrado.

Sub PesquisaPreco()
  On Error GoTo eh
  ActiveCell = Application.WorksheetFunction.VLookup(Range("E3"), Range("B2:C6"), 2, False)
  Exit Sub
eh:
  MsgBox "Produto não encontrado - tente outro produto"
End Sub

Como alternativa, podemos alterar nosso código VBA para usar Application.Vlookup em vez de Application.WorksheetFunction.VLookup.

Sub PesquisaPreco()
  ActiveCell = Application.VLookup(Range("E3"), Range("B2:C6"), 2, False)
End Sub

Quando fazemos isso, o #N/D que esperamos quando um valor de pesquisa não é encontrado é retornado à célula.

WorksheetFunction.XLOOKUP

A função XLOOKUP (PROCX) foi projetada para substituir as funções VLOOKUP e HLOOKUP no Excel. Ela só está disponível para o Office 365, portanto, o uso dessa função é bastante restritivo se alguns dos seus usuários estiverem usando versões mais antigas do Excel.

Ela funciona de maneira muito semelhante à função VLOOKUP no VBA.

Sub PesquisaPrecoV()
   ActiveCell = Application.WorksheetFunction.XLookup(Range("E3"), Range("B2:B6"), Range("C2:C6"))
End Sub

OU

Sub PesquisaPrecoV() 
   ActiveCell = Application.XLookup(Range("E3"), Range("B2:B6"), Range("C2:C6")) 
End Sub

Mais uma vez, se quisermos usar variáveis, podemos escrever o seguinte código

Sub PesquisaPrecoV()
   Dim strProduct As String
   Dim rngProduct As Range
   Dim rngPrice As Range
   strProduct = Range("E3")
   Set rngProduct = Range("B2:B6")
   Set rngPrice = Range("C2:C6")
   ActiveCell = Application.WorksheetFunction.XLookup(strProduct, rngProduct, rngPrice)
End Sub

Se quisermos retornar uma fórmula para o Excel em vez de um valor, precisaremos escrever a fórmula no Excel com o código VBA.

Sub PesquisaPreco()
   ActiveCell = "=XLOOKUP(E3,B3:B6,C3:C6)"
End Sub

Ou você pode escrever a fórmula em uma célula usando a notação R1C1.

Sub PesquisaPreco() 
  ActiveCell.Formula2R1C1 = "=XLOOKUP(RC[-1],RC[-4]:R[3]C[-4],RC[-3]:R[3]C[-3])"
End Sub

Uma das principais vantagens do uso do XLOOKUP em relação ao VLOOKUP é a capacidade de pesquisar um intervalo de colunas e retornar o valor em cada coluna.

Vejamos o exemplo a seguir:

Criamos a fórmula na célula H3, onde ela está procurando os valores no intervalo C2:E6. Devido ao fato de serem várias colunas, ela preencherá automaticamente as colunas I e J com os resultados correspondentes encontrados. A fórmula transborda para as colunas I e J sem que tenhamos que usar CTRL+SHIFT para uma fórmula de matriz – essa capacidade da fórmula de transbordar é uma das novas adições do Excel 365.

Para replicar isso com o código VBA, podemos digitar o seguinte em nossa macro:

Sub PesquisaPreco()
   ActiveCell.Formula2R1C1 = "=XLOOKUP(RC[-1],RC[-6]:R[3]C[-6],RC[-5]:R[3]C[-3])"
End Sub

Onde a fórmula está usando a sintaxe R1C1 (linhas e colunas) em vez da sintaxe A1 (intervalo). Isso fará com que as fórmulas sejam inseridas no Excel conforme mostrado no gráfico acima.

Não é possível pesquisar várias colunas se você estiver usando o método WorksheetFunction.