As funções como PROCV, CONT.SE, SOMASE são chamadas de funções de planilha. Geralmente, as funções predefinidas no Excel e prontas para uso em uma planilha são funções de planilha. Você não pode alterar ou ver o código por trás dessas funções no VBA.
Por outro lado, as funções definidas pelo usuário e as funções específicas do VBA, como MsgBox ou InputBox, são funções do VBA.
Todos nós sabemos como usar as funções do VBA no VBA. Mas e se quisermos usar VLOOKUP em um VBA. Como fazemos isso? Neste artigo, exploraremos exatamente isso.
Usando funções de planilha no VBA
Para acessar a função de planilha, usamos uma classe de aplicativo. Quase todas as funções de planilha são listadas na classe Application.WorksheetFunction. E usando o operador de ponto, você pode acessar todos eles.
Em qualquer sub-rotina, escreva Application.WorksheetFunction. E comece a escrever o nome da função. O intellisense do VBA mostrará o nome das funções disponíveis para uso. Depois de escolher o nome da função, ele solicitará as variáveis, como qualquer função no Excel. Mas você terá que passar as variáveis no formato compreensível do VBA. Por exemplo, se você deseja passar um intervalo A1: A10, você terá que passá-lo como um objeto de intervalo como Range ("A1: A10").
Então, vamos usar algumas funções de planilha para entendê-lo melhor.
Como usar a função VLOOKUP em VBA
Para demonstrar como você pode usar uma função VLOOKUP no VBA, aqui tenho dados de amostra. Preciso mostrar o nome e a cidade do ID de login fornecido em uma caixa de mensagem usando o VBA. Os dados estão espalhados no intervalo A1: K26.
Pressione ALT + F11 para abrir o VBE e inserir um módulo.
Veja o código abaixo.
Sub WsFuncitons () Dim loginID As String Dim name, city As String loginID = "AHKJ_1-3357042451" 'Usando a função VLOOKUP para obter o nome do id fornecido na tabela name = Application.WorksheetFunction.VLookup (loginID, Range ("A1: K26" ), 2, 0) 'Usando a função VLOOKUP para obter a cidade do id fornecido na tabela city = Application.WorksheetFunction.VLookup (loginID, Range ("A1: K26"), 4, 0) MsgBox ("Name:" & name & vbLf & "Cidade:" e cidade) End Sub
Ao executar este código, você obterá esse resultado.
Você pode ver a rapidez com que o VBA imprime o resultado em uma caixa de mensagem. Agora vamos examinar o código.
Como funciona?
1.
Dim loginID As String
Dim nome, cidade como string
Primeiro, declaramos duas variáveis do tipo string para armazenar o resultado retornado pela função VLOOKUP. Usei variáveis do tipo string porque tenho certeza de que o resultado retornado por VLOOKUP será um valor string. Se sua função de planilha deve retornar número, data, intervalo, etc. tipo de valor, use esse tipo de variável para armazenar o resultado. Se você não tiver certeza de que tipo de valor será retornado pela função de planilha, use variáveis de tipo variante.
2.
loginID = "AHKJ_1-3357042451"
Em seguida, usamos a variável loginID para armazenar o valor de pesquisa. Aqui, usamos um valor codificado. Você também pode usar referências. Por exemplo. Você pode usar Intervalo ("A2"). Valor para atualizar dinamicamente o valor de pesquisa do intervalo A2.
3.
name = Application.WorksheetFunction.VLookup (loginID, Range ("A1: K26"), 2, 0)
Aqui usamos a função PROCV para obter. Agora, quando você corrigir a função e abrir o parêntese, ele mostrará os argumentos necessários, mas não tão descritivos quanto no Excel. Veja por si mesmo.
Você precisa se lembrar de como e qual variável você precisa usar. Você sempre pode voltar à planilha para ver os detalhes descritivos da variável.
Aqui, o valor de pesquisa é Arg1. Para Arg1, usamos loginID. A tabela de pesquisa é Arg2. Para Arg2, usamos Range ("A1: K26"). Observe que não usamos A2: K26 diretamente como fazemos no Excel. O índice das colunas é Arg3. Para Arg3, usamos 2, já que o nome está na segunda coluna. O tipo de pesquisa é Arg4. Usamos 0 como Arg4.
city = Application.WorksheetFunction.VLookup (loginID, Range ("A1: K26"), 4, 0)
Da mesma forma, obtemos o nome da cidade.
4.
MsgBox ("Nome:" & nome & vbLf & "Cidade:" & cidade)
Finalmente imprimimos o nome e a cidade usando Messagebox.
Por que usar a função de planilha no VBA?
As funções de planilha possuem poder de cálculos imensos e não será inteligente ignorar o poder das funções de planilha. Por exemplo, se quisermos o desvio padrão de um conjunto de dados e você quiser escrever o código inteiro para isso, pode levar horas. Mas se você souber como usar a função de planilha STDEV.P em VBA para obter o cálculo de uma vez.
Sub GetStdDev () std = Application.WorksheetFunction.StDev_P (Range ("A1: K26")) End Sub
Usando Múltiplas Funções de Planilha VBA
Digamos que precisamos usar uma correspondência de índice para recuperar alguns valores. Agora, como você escreveria a fórmula em VBA. Isso é o que eu acho que você escreverá:
Sub IndMtch () Val = Application.WorksheetFunction.Index (result_range, _ Application.WorksheetFunction.Match (lookup_value, _ lookup_range, match_type)) End Sub
Isso não está errado, mas é demorado. A maneira certa de usar funções múltiplas é usando um bloco With. Veja o exemplo abaixo:
Sub IndMtch () Com Application.WorksheetFunction Val = .Index (intervalo_resultado, .Match (valor_procurado, intervalo_procurado, tipo_correspondente)) val2 = .VLookup (arg1, arg2, arg3) val4 = .StDev_P (números) End With End Sub
Como você pode ver, usei o bloco With para informar ao VBA que estarei usando as propriedades e funções de Application.WorksheetFunction. Portanto, não preciso defini-lo em todos os lugares. Acabei de usar o operador ponto para acessar as funções INDEX, MATCH, VLOOKUP e STDEV.P. Uma vez que usamos a instrução End With, não podemos acessar funções sem usar nomes de função totalmente qualificados.
Portanto, se você tiver que usar várias funções de planilha no VBA, use com bloco.
Nem todas as funções de planilha estão disponíveis por meio de Application.WorksheetFunction
Algumas funções de planilha estão diretamente disponíveis para uso no VBA. Você não precisa usar o objeto Application.WorksheetFunction.
Por exemplo, funções como Len () que é usado para obter o número de caracteres em uma string, esquerda, direita, meio, corte, deslocamento, etc. Essas funções podem ser usadas diretamente no VBA. Aqui está um exemplo.
Sub GetLen () Strng = "Hello" Debug.Print (Len (strng)) End Sub
Veja, aqui usamos a função LEN sem usar o objeto Application.WorksheetFunction.
Da mesma forma, você pode usar outras funções como esquerda, direita, meio, char, etc.
Sub GetLen () Strng = "Hello" Debug.Print (Len (strng)) Debug.Print (left (strng, 2)) Debug.Print (right (strng, 1)) Debug.Print (Mid (strng, 3, 2)) End Sub
Quando você executa o sub acima, ele retornará:
5 ele o ll
Então sim pessoal, é assim que você pode usar a função de planilha do Excel no VBA. Espero ter sido explicativo o suficiente e este artigo ajudou você. Se você tiver alguma dúvida sobre este artigo ou qualquer outra coisa relacionada ao VBA, pergunte na seção de comentários abaixo. Até então, você pode ler sobre outros tópicos relacionados abaixo.
O que é a função CSng no Excel VBA | A função SCng é uma função VBA que converte qualquer tipo de dados em um número de ponto flutuante de precisão simples ("dado que é um número"). Eu uso principalmente a função CSng para converter números formatados em texto em números reais.
Como obter texto e número em reverso por meio do VBA no Microsoft Excel | Para reverter número e texto, usamos loops e função mid no VBA. 1234 será convertido para 4321, "você" será convertido para "uoy". Aqui está o trecho.
Formate dados com formatos de números personalizados usando VBA no Microsoft Excel | Para alterar o formato de número de colunas específicas no Excel, use este trecho de VBA. Abrange o formato de número do formato especificado para o formato especificado em um clique.
Usando o evento de alteração da planilha para executar a macro quando qualquer alteração é feita | Portanto, para executar sua macro sempre que a planilha for atualizada, usamos os Eventos de Planilha do VBA.
Executar macro se houver alguma alteração feita na folha no intervalo especificado | Para executar seu código de macro quando o valor em um intervalo especificado for alterado, use este código VBA. Ele detecta qualquer alteração feita no intervalo especificado e acionará o evento.
Código VBA mais simples para destacar a linha e a coluna atuais usando | Use este pequeno trecho de VBA para destacar a linha e coluna atuais da planilha.
Artigos populares:
50 atalhos do Excel para aumentar sua produtividade | Torne sua tarefa mais rápida. Esses 50 atalhos farão você trabalhar ainda mais rápido no Excel.
A função VLOOKUP no Excel | Esta é uma das funções mais usadas e populares do Excel, que é usada para pesquisar valores em diferentes intervalos e planilhas.
CONT.SE no Excel 2016 | Conte valores com condições usando esta função incrível. Você não precisa filtrar seus dados para contar valores específicos. A função Countif é essencial para preparar seu painel.
Como usar a função SUMIF no Excel | Esta é outra função essencial do painel. Isso ajuda você a somar valores em condições específicas.