Funções de planilha do Excel VBA
A função de planilha no VBA é usada quando temos que nos referir a uma planilha específica, normalmente quando criamos um módulo, o código é executado na planilha atualmente ativa da pasta de trabalho, mas se quisermos executar o código na planilha específica, usamos a função de planilha, esta função tem vários usos e aplicações em VBA.
A melhor coisa sobre o VBA é, assim como usamos fórmulas em planilhas, o VBA também tem suas próprias funções. Se este for o melhor, então também tem uma coisa linda. Ou seja, “também podemos usar funções de planilha no VBA”.
Sim!!! Você ouviu direito; também podemos acessar as funções de planilha no VBA. Podemos acessar algumas das funções da planilha enquanto escrevemos o código e torná-lo parte do nosso código.
Como usar funções de planilha no VBA?
Na planilha, todas as fórmulas começam com o sinal de igual (=), da mesma forma na codificação VBA, para acessar as fórmulas da planilha, devemos usar a palavra “WorksheetFunction”.
Antes de inserir qualquer fórmula de planilha, você precisa mencionar o nome do objeto “WorksheetFunction” e colocar um ponto (.), Então você obterá uma lista de todas as funções disponíveis sob este objeto.
Neste artigo, vamos nos concentrar exclusivamente em como usar funções de planilha na codificação VBA, o que agregará mais valor ao seu conhecimento de codificação.
# 1 - Funções simples da planilha SUM
Ok, para começar com as funções da planilha, aplique a função SUM simples no Excel para adicionar números da planilha.
Suponha que você tenha vendas mensais e dados de custo na planilha como a abaixo.
Em B14 e C14, precisamos chegar ao total dos números acima. Siga as etapas abaixo para iniciar o processo de aplicação da função “SUM” no Excel VBA.
Etapa 1: Crie um nome de macro simples do Excel.
Código:
Sub Planilha_Função_Exemplo1 () End Sub
Etapa 2: como precisamos do resultado na célula B14, inicie o código como Faixa (“B14”). Valor =
Código:
Sub Planilha_Função_Exemplo1 () Intervalo ("B14"). Valor = End Sub
Etapa 3: em B14, precisamos do valor como o resultado da soma dos números. Portanto, para acessar a função SUM na planilha, inicie o código como “WorksheetFunction”.
Código:
Sub Planilha_Função_Exemplo1 () Intervalo ("B14"). Valor = PlanilhaFunção. End Sub
Passo 4: No momento em que você colocar um ponto (.), Ele começará a exibir as funções disponíveis. Portanto, selecione SUM a partir disso.
Código:
Sub Worksheet_Function_Example1 () Intervalo ("B14"). Value = WorksheetFunction.Sum End Sub
Passo 5: Agora forneça a referência dos números acima, ou seja, Range (“B2: B13”).
Código:
Sub Worksheet_Function_Example1 () Range ("B14"). Value = WorksheetFunction.Sum (Range ("B2: B13")) End Sub
Etapa 6: Da mesma forma, para a próxima coluna, aplique um código semelhante alterando as referências das células.
Código:
Sub Worksheet_Function_Example1 () Range ("B14"). Value = WorksheetFunction.Sum (Range ("B2: B13")) Range ("C14"). Value = WorksheetFunction.Sum (Range ("C2: C13")) End Sub
Etapa 7: agora execute este código manualmente ou usando a tecla F5 para obter um total nas células B14 e C14.
Uau, temos nossos valores. Uma coisa que você precisa observar aqui é que não temos nenhuma fórmula na planilha, mas acabamos de obter o resultado da função “SUM” no VBA.
# 2 - Use VLOOKUP como uma função de planilha
Veremos como usar VLOOKUP em VBA. Suponha que a seguir estão os dados que você tem em sua planilha do Excel.
Na célula E2, você criou uma lista suspensa de todas as zonas.
Based on the selection you made in the E2 cell, we need to fetch the Pin Code for the respective zone. But this time through VBA VLOOKUP, not worksheet VLOOKUP. Follow the below steps to apply VLOOKUP.
Step 1: Create a simple macro name in the Sub Procedure.
Code:
Sub Worksheet_Function_Example2() End Sub
Step 2: We need the result in the F2 cell. So start the code as Range (“F2”).Value =
Code:
Sub Worksheet_Function_Example2() Range ("F2").Value = End Sub
Step 3: To access the worksheet function, VLOOKUP starts the code as “WorksheetFunction.VLOOKUP.”
Code:
Sub Worksheet_Function_Example2() Range ("F2").Value = WorksheetFunction.Vlookup( End Sub
Step 4: One of the problems here is syntax will not give you any sort of guidance to work with VLOOKUP. You need to be absolutely sure about the syntax you are working on.
The first syntax of VLOOKUP is “Lookup Value.” In this case, our lookup value is E2 cell value, so write the code as Range (“E2”).Value
Code:
Sub Worksheet_Function_Example2() Range ("F2").Value = WorksheetFunction.Vlookup(Range ("E2").Value, End Sub
Step 5: Now, the second argument is our table array. In this case, our table array range is from A2 to B6. So the code will be Range (“A2:B6”)
Code:
Sub Worksheet_Function_Example2() Range ("F2").Value = WorksheetFunction.Vlookup(Range ("E2").Value,Range ("A2:B6"), End Sub
Step 6: The Third argument will be from which column we need the data from the table array. Here we need the data from the 2nd column, so the argument will be 2.
Code:
Sub Worksheet_Function_Example2() Range ("F2").Value = WorksheetFunction.Vlookup(Range ("E2").Value,Range ("A2:B6"),2, End Sub
Step 7: The final argument is range lookup, we need an exact match, so the argument is zero (0).
Code:
Sub Worksheet_Function_Example2() Range("F2").Value = WorksheetFunction.VLookup(Range("E2").Value, Range("A2:B6"), 2, 0) End Sub
So, we are done with the coding part. Now go to the worksheet and select any of the range.
Now go to your coding module and run the macro Using the F5 key or manually to get the pin code of the selected zone.
Não podemos voltar e executar a macro todas as vezes, então vamos atribuir uma macro às formas. Insira uma das formas em uma planilha.
Adicione um valor de texto à forma inserida.
Agora clique com o botão direito e atribua o nome da macro a esta forma.
Clique em ok após selecionar o nome da macro.
Agora, esta forma contém o código de nossa fórmula VLOOKUP. Portanto, sempre que você alterar o nome da zona, clique no botão, ele irá atualizar os valores.
Coisas para lembrar
- Para acessar as funções da planilha, precisamos escrever a palavra “WorksheetFunction” ou “Application.WorksheetFunction”
- Não temos acesso a todas as funções, apenas a algumas.
- Não vemos a sintaxe real das funções da planilha, então precisamos ter certeza absoluta da função que estamos usando.
