Função de planilha VBA | Como usar WorksheetFunction no VBA?

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 é, como usamos fórmulas em planilhas, o VBA também tem suas próprias funções. Se este for o melhor, então ele também tem uma coisa bonita que é “podemos usar funções de planilha em VBA também”.

Sim!!! Você ouviu direito, podemos acessar as funções de planilha no VBA também. 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?

Você pode baixar este modelo de planilha VBA de função aqui - planilha VBA modelo de função

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 (.). Em seguida, você obterá uma lista de todas as funções disponíveis sob este objeto.

Neste artigo, vamos nos concentrar exclusivamente em como usar a função de planilha na codificação VBA, o que agregará mais valor ao seu conhecimento de codificação.

# 1 - Funções simples de 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 resultado da soma dos números. Portanto, para acessar a função SUM da 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, Intervalo (“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 o 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.

Com base na seleção que você fez na célula E2, precisamos buscar o código PIN para a respectiva zona. Mas desta vez por meio de VBA VLOOKUP, não de planilha VLOOKUP. Siga as etapas abaixo para aplicar VLOOKUP.

Etapa 1: Crie um nome de macro simples no Subprocedimento.

Código:

 Sub Planilha_Função_Exemplo2 () 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 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 F5 key or manually to get the pin code of the selected zone.

We cannot go back and run the macro every time, so let’s assign a macro to shapes. Insert one of the shapes in a worksheet.

Add a text value to inserted shape.

Now right click and assign the macro name to this shape.

Click on ok after selecting the macro name.

Now, this shape holds the code of our VLOOKUP formula. So whenever you change the zone name click on the button, it will update the values.

Things to Remember

To access worksheet functions we need to write the word “WorksheetFunction” or “Application.WorksheetFunction”
We don’t have access to all the functions only a few.
We don’t see the actual syntax of worksheet functions, so we need to be absolutely sure of the function we are using.