Como usar funções de planilha vba no excel 2016

Embora VBA oferece uma variedade razoável de funções embutidas, você não pode sempre encontrar exatamente o que você precisa. Felizmente, você também pode usar a maioria das funções de planilha do Excel em seus procedimentos VBA. As únicas funções de planilha que você não pode usar são aqueles que têm uma função VBA equivalente. Por exemplo, você não pode usar a função RAND do Excel (que gera um número aleatório) porque o VBA tem uma função equivalente: Rnd.

VBA faz funções de planilha do Excel disponível através do objeto WorksheetFunction, que está contido no objeto Application. Aqui está um exemplo de como você pode usar a função SUM do Excel em um comunicado VBA:

Total = Application.WorksheetFunction.SUM (Range ( “A1: A12”))

Você pode omitir qualquer parte aplicativo ou a parte WorksheetFunction da expressão. Em ambos os casos, VBA descobre o que você está fazendo. Em outras palavras, essas três expressões todas funcionam exatamente da mesma:

Total = Application.WorksheetFunction.SUM (Range ( “A1: A12”)) Total = WorksheetFunction.SUM (Range ( “A1: A12”)) Total = Application.Sum (Range ( “A1: A12”))

Minha preferência pessoal é usar a parte WorksheetFunction só para deixar bem claro que o código está usando uma função do Excel.

Exemplos de funções de folha de cálculo

Aqui, você descobrirá como usar funções de planilha em suas expressões VBA.

Encontrar o valor máximo em uma gama

Aqui está um exemplo que mostra como usar a função de planilha MAX do Excel em um procedimento VBA. Este procedimento mostra o valor máximo na coluna A da planilha ativa:

Sub showmax () Dim TheMax Como DoubleTheMax = WorksheetFunction.MAX (Range ( “A: A”)) MsgBox TheMaxEnd Sub

Você pode usar a função MIN para obter o menor valor em um intervalo. E como você poderia esperar, você pode usar outras funções de planilha de uma maneira similar. Por exemplo, você pode usar a função GRANDE para determinar a kth-maior valor em um intervalo. A expressão a seguir demonstra isso:

Video: Como habilitar a guia desenvolvedor no excel 2010/2013

SecondHighest = WorksheetFunction.LARGE (Range ( “A: A”), 2)

Observe que a função GRANDE usa dois argumentos. O segundo argumento representa o kth parte - 2, neste caso (o segundo maior valor).

Calculando um pagamento de hipoteca

O próximo exemplo usa a função PMT planilha para calcular um pagamento da hipoteca. Três variáveis ​​são usadas para armazenar os dados que são passados ​​para a função Pmt como argumentos. Uma caixa de mensagem exibe o pagamento calculado.

Sub PmtCalc () Dim INTRATE Como DoubleDim LoanAmt como períodos DoubleDim Como LongIntRate = 0,0625 / 12Periods = 30 * = 12LoanAmt 150000MsgBox WorksheetFunction.PMT (INTRATE, Períodos, -LoanAmt) End Sub

Como mostra o seguinte declaração, você também pode inserir os valores diretamente como os argumentos da função:

MsgBox WorksheetFunction.PMT (0,0625 / 12, 360, -150000)

No entanto, o uso de variáveis ​​para armazenar os parâmetros torna o código mais fácil de ler e modificar, se necessário.

Utilizando uma função de pesquisa

O exemplo a seguir usa funções InputBox e MsgBox VBA, mais a função PROCV do Excel. Ele solicita um número de peça e, em seguida, recebe o preço a partir de uma tabela de pesquisa. Abaixo, intervalo A1: B13 é chamado Lista de preços.

Sub getPrice () Dim partNum Como VariantDim preço Como DoublePartNum = InputBox ( “Digite o número Part”) Sheets ( “Preços”). ActivatePrice = WorksheetFunction.VLOOKUP (partNum, Range ( “Lista de preços”), 2, False) MsgBox partNum & “custos“ & PriceEnd Sub

Veja como o procedimento getPrice funciona:

• função InputBox do VBA pede ao usuário para um número de peça.

• O número parte do utilizador entra é atribuído a variável partNum.

• A próxima instrução ativa a planilha preços, apenas no caso de ele já não é a planilha ativa.

• O código usa a função PROCV para encontrar o número de peça na tabela.

• Observe que os argumentos que você usa nesta declaração são os mesmos que você usaria com a função em uma fórmula de planilha. Esta declaração atribui o resultado da função para a variável preço.

• O código exibe o preço para a parte com a função MsgBox.

Este procedimento não tem qualquer manipulação de erro, e ele falhar miseravelmente se você digitar um número de peça inexistente. (Experimente.) Se isso fosse uma aplicação real que é usado em um negócio real, você gostaria de adicionar algumas declarações que lidam com os erros mais graciosamente.

Entrando funções de planilha

Você não pode usar a caixa de diálogo Colar função Excel para inserir uma função de planilha em um módulo VBA. Em vez disso, introduzir tais funções a maneira antiga: com a mão. No entanto, você posso use a caixa de diálogo Colar função para identificar a função que deseja usar e saber mais sobre os seus argumentos.

Você também pode tirar proveito da opção Auto List Members do VBE, que exibe uma lista drop-down de todas as funções de planilha. tipo de apenas Application.WorksheetFunction, seguido por um período. Em seguida, você verá uma lista das funções que você pode usar. Se este recurso não está funcionando, escolha Ferramentas do VBE → comando Opções, clique na guia Editor, e colocar uma seleção ao lado de Auto List Members.

Mais sobre como usar funções de planilha

Recém-chegados ao VBA muitas vezes confundem funções internas do VBA e funções de pasta de trabalho do Excel. Uma boa regra a lembrar é que o VBA não tentar reinventar a roda. Para a maior parte, VBA não duplicar funções de planilha do Excel.

Para a maioria das funções de planilha que não estão disponíveis como métodos do objeto WorksheetFunction, você pode usar um operador equivalente VBA built-in ou função. Por exemplo, a função de folha de cálculo MOD não está disponível no objecto WorksheetFunction porque VBA tem um equivalente: a sua embutido operador mod.

linha de fundo? Se você precisa usar uma função, em primeiro lugar determinar se o VBA tem algo que atenda às suas necessidades. Se não, confira as funções de planilha. Se tudo isso falhar, você pode ser capaz de escrever uma função personalizada usando VBA.