Use funções de planilha em uma macro - exemplos de código VBA

Existem muitas maneiras de usar funções no VBA. O VBA vem carregado com muitas funções integradas. Você pode até criar suas próprias funções (UDFs). No entanto, você também pode utilizar muitas das funções do Excel no VBA usando Application.WorksheetFunction.

Como usar funções de planilha no VBA

Para acessar uma função do Excel no VBA, adicione Application.WorksheetFunction na frente da função que você deseja chamar. No exemplo abaixo, chamaremos de Função Máxima do Excel:

12 Dim maxvalue enquantomaxvalue = Application.WorksheetFunction.Max (Range ("a1"). Value, Range ("a2"). Value)

A sintaxe das funções é a mesma, no entanto, você inserirá os argumentos da função como faria com qualquer outra função VBA.

Observe que a sintaxe da função Max aparece quando você digita (semelhante às funções VBA):

Método de planilha de função

WorksheetFunction é um método do objeto Aplicativo. Ele permite que você acesse muitas (não todas) as funções padrão de planilha do Excel. Geralmente, você não terá acesso a nenhuma função de planilha que tenha uma versão VBA correspondente.

Você pode ver uma lista de muitas das funções de planilha mais comuns abaixo.

Application.WorksheetFunction vs. Aplicativo

Na verdade, existem duas maneiras de acessar essas funções:

Application.WorksheetFunction (como visto acima):

1 maxvalue = Application.WorksheetFunction.Max (Range ("a1"). Value, Range ("a2"). Value)

ou você pode omitir o WorksheetFunction

1 maxvalue = Application.Max (Range ("a1"). Value, Range ("a2"). Value)

Infelizmente, omitir o WorksheetFunction eliminará o Intellisense que exibe a sintaxe (veja a imagem acima). No entanto, tem uma grande vantagem potencial: Manipulação de erros.

Se você usar o aplicativo e sua função gerar um erro, ela retornará o valor do erro. Se você usar o método WorksheetFunction, o VBA lançará um erro de tempo de execução. Claro, você pode lidar com o erro do VBA, mas geralmente é melhor evitar o erro em primeiro lugar.

Vejamos um exemplo para ver a diferença:

Vlookup WorksheetFunction Error Handling

Tentaremos realizar um Vlookup que não resultará em uma correspondência. Portanto, a função Vlookup retornará um erro.

Primeiro, usaremos o método WorksheetFunction. Observe como o VBA gera um erro:

Em seguida, omitimos WorksheetFunction. Observe como o

Em seguida, omitiremos WorksheetFunction. Observe como nenhum erro é gerado e, em vez disso, a função de "valor" contém o valor de erro do Vlookup.

Lista de funções da planilha VBA

Abaixo, você encontrará uma lista da maioria das funções comuns de planilha do VBA.

FunçãoDescrição
Lógico
EVerifica se todas as condições foram atendidas. VERDADEIRO FALSO
E SESe a condição for atendida, faça algo; se não, faça outra coisa.
IFERRORSe o resultado for um erro, faça outra coisa.
OUVerifica se alguma condição foi atendida. VERDADEIRO FALSO
Pesquisa e Referência
ESCOLHEREscolhe um valor de uma lista com base em seu número de posição.
HLOOKUPProcure um valor na primeira linha e retorne um valor.
ÍNDICERetorna um valor com base em seus números de coluna e linha.
OLHO PARA CIMAProcura valores horizontal ou verticalmente.
PARTIDAPesquisa um valor em uma lista e retorna sua posição.
TRANSPORInverte a orientação de um intervalo de células.
PROCVProcure um valor na primeira coluna e retorne um valor.
Data hora
ENCONTRORetorna uma data do ano, mês e dia.
DATEVALUEConverte uma data armazenada como texto em uma data válida
DIARetorna o dia como um número (1-31).
DAYS360Retorna dias entre 2 datas em um ano de 360 ​​dias.
EDATERetorna uma data, n meses de distância de uma data de início.
EOMONTHRetorna o último dia do mês, faltando n meses.
HORARetorna a hora como um número (0-23).
MINUTORetorna o minuto como um número (0-59).
MÊSRetorna o mês como um número (1-12).
NETWORKDAYSNúmero de dias úteis entre 2 datas.
NETWORKDAYS.INTLDias úteis entre 2 datas, fins de semana personalizados.
AGORARetorna a data e hora atuais.
SEGUNDODevolve o segundo como um número (0-59)
TEMPORetorna a hora de uma hora, minuto e segundo.
VALOR DO TEMPOConverte uma hora armazenada como texto em uma hora válida.
WEEKDAYRetorna o dia da semana como um número (1-7).
WEEKNUMRetorna o número da semana em um ano (1-52).
DIA DE TRABALHOA data em n dias úteis a partir de uma data.
ANORetorna o ano.
YEARFRACRetorna a fração de um ano entre 2 datas.
Engenharia
CONVERTERConverta o número de uma unidade para outra.
Financeiro
FVCalcula o valor futuro.
PVCalcula o valor presente.
NPERCalcula o número total de períodos de pagamento.
PMTCalcula o valor do pagamento.
AVALIARCalcula a taxa de juros.
NPVCalcula o valor presente líquido.
IRRA taxa interna de retorno para um conjunto de CFs periódicos.
XIRRA taxa interna de retorno para um conjunto de CFs não periódicos.
PREÇOCalcula o preço de um título.
INTRATEA taxa de juros de um título totalmente investido.
Em formação
ISERRTesta se o valor da célula é um erro, ignora # N / A. VERDADEIRO FALSO
ISERRORTeste se o valor da célula é um erro. VERDADEIRO FALSO
ISEVENTeste se o valor da célula é par. VERDADEIRO FALSO
ISLOGICALTeste se a célula é lógica (VERDADEIRO ou FALSO). VERDADEIRO FALSO
ISNATeste se o valor da célula é # N / A. VERDADEIRO FALSO
ISNONTEXTTeste se a célula não é texto (células em branco não são texto). VERDADEIRO FALSO
ISNUMBERTeste se a célula é um número. VERDADEIRO FALSO
É ESTRANHOTeste se o valor da célula é ímpar. VERDADEIRO FALSO
ISTEXTTeste se a célula é um texto. VERDADEIRO FALSO
MODELORetorna o tipo de valor em uma célula.
Matemática
abdômenCalcula o valor absoluto de um número.
AGREGARDefina e execute cálculos para um banco de dados ou uma lista.
TETOArredonda um número para cima, para o múltiplo especificado mais próximo.
COSRetorna o cosseno de um ângulo.
GRAUSConverte radianos em graus.
DSUMSoma os registros do banco de dados que atendem a certos critérios.
ATÉArredonda para o número par mais próximo.
EXPCalcula o valor exponencial para um determinado número.
FACTORetorna o fatorial.
PISOArredonda um número para baixo, para o múltiplo especificado mais próximo.
GCDRetorna o maior divisor comum.
INTArredonda um número para baixo até o número inteiro mais próximo.
LCMRetorna o mínimo múltiplo comum.
LNRetorna o logaritmo natural de um número.
REGISTRORetorna o logaritmo de um número para uma base especificada.
LOG10Retorna o logaritmo de base 10 de um número.
MROUNDArredonda um número para um múltiplo especificado.
ÍMPARArredonda para o número inteiro ímpar mais próximo.
PIO valor de PI.
POTÊNCIACalcula um número elevado a uma potência.
PRODUTOSMultiplica uma matriz de números.
QUOCIENTERetorna o resultado inteiro da divisão.
RADIANOSConverte um ângulo em radianos.
RANDBETWEENCalcula um número aleatório entre dois números.
VOLTAArredonda um número para um determinado número de dígitos.
ARREDONDAR PARA BAIXOArredonda um número para baixo (em direção a zero).
ARREDONDAR PARA CIMAArredonda um número para cima (longe de zero).
PECADORetorna o seno de um ângulo.
SUBTOTALRetorna uma estatística resumida para uma série de dados.
SOMASoma os números.
SUMIFSoma os números que atendem a um critério.
SUMIFSSoma os números que atendem a vários critérios.
SUMPRODUTOMultiplica matrizes de números e soma a matriz resultante.
BRONZEADORetorna a tangente de um ângulo.
Estatísticas
MÉDIANúmeros médios.
AVERAGEIFMédia de números que atendem a um critério.
MÉDIOSMédia de números que atendem a vários critérios.
CORRELCalcula a correlação de duas séries.
CONTARConta as células que contêm um número.
CONT.valorConte as células que não estão em branco.
CONTAR EM BRANCOConta as células que estão em branco.
CONT.SEConta as células que atendem a um critério.
COUNTIFSConta as células que atendem a vários critérios.
PREVISÃOPreveja valores de y futuros a partir da linha de tendência linear.
FREQUÊNCIAConta os valores que se enquadram nos intervalos especificados.
CRESCIMENTOCalcula os valores Y com base no crescimento exponencial.
INTERCEPTARCalcula a interceptação Y para uma linha de melhor ajuste.
AMPLARetorna o maior valor k.
PROJETORetorna estatísticas sobre uma linha de tendência.
MAXRetorna o maior número.
MEDIANARetorna o número mediano.
MINRetorna o menor número.
MODORetorna o número mais comum.
PERCENTILRetorna o percentil k.
PERCENTIL.INCRetorna o percentil k. Onde k é inclusivo.
PERCENTIL.EXCRetorna o percentil k. Onde k é exclusivo.
QUARTILRetorna o valor quartil especificado.
QUARTILE.INCRetorna o valor do quartil especificado. Inclusivo.
QUARTILE.EXCRetorna o valor do quartil especificado. Exclusivo.
CLASSIFICAÇÃOClassificação de um número em uma série.
RANK.AVGClassificação de um número em uma série. Médias.
RANK.EQClassificação de um número em uma série. Melhor classificação.
DECLIVECalcula a inclinação da regressão linear.
PEQUENARetorna o menor valor k.
STDEVCalcula o desvio padrão.
STDEV.PCalcula o SD de uma população inteira.
STDEV.SCalcula o SD de uma amostra.
STDEVPCalcula o SD de uma população inteira
TENDÊNCIACalcula os valores Y com base em uma linha de tendência.
Texto
LIMPARRemove todos os caracteres não imprimíveis.
DÓLARConverte um número em texto no formato de moeda.
ACHARLocaliza a posição do texto em uma célula. Sensível ao caso.
DEIXOUTrunca o texto com vários caracteres da esquerda.
LENConta o número de caracteres no texto.
MIDExtrai texto do meio de uma célula.
APROPRIADOConverte o texto para maiúsculas e minúsculas.
SUBSTITUIRSubstitui o texto com base em sua localização.
REPTRepete o texto várias vezes.
DIREITOTrunca o texto com vários caracteres da direita.
PROCURARLocaliza a posição do texto dentro de uma célula. Não diferencia maiúsculas de minúsculas.
SUBSTITUTOLocaliza e substitui texto. Maiúsculas e Minúsculas.
TEXTOConverte um valor em texto com um formato de número específico.
APARARRemove todos os espaços extras do texto.

Você vai ajudar o desenvolvimento do site, compartilhando a página com seus amigos

wave wave wave wave wave