VBA – Como Utilizar as Funções de Planilha (e uma Lista Completa)

Há muitas maneiras de usar funções no VBA. O VBA vem carregado com muitas funções internas. Você pode até mesmo 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 deseja chamar. No exemplo abaixo, chamaremos a função Max do Excel:

Dim valormax as long
valormax = Application.WorksheetFunction.Max(Range("a1").Value, Range("a2").Value)

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

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

Método WorksheetFunction

WorksheetFunction é um método do objeto Application. Ele permite o acesso a muitas (não todas) as funções padrão da planilha do Excel. Em geral, 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. Application

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

Application.WorksheetFunction (como visto acima):

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

ou você pode omitir a WorksheetFunction

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

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

Se você usar Application, 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 em tempo de execução. É claro que você pode tratar o erro do VBA, mas geralmente é melhor evitar o erro em primeiro lugar.

Vamos dar uma olhada em um exemplo para ver a diferença:

Tratamento de Erros da WorksheetFunction do Vlookup

Tentaremos executar 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 o WorksheetFunction. Observe como o método

Em seguida, omitiremos a WorksheetFunction. Observe que nenhum erro é lançado e que, em vez disso, a função ‘value’ contém o valor de erro do Vlookup.

Lista de Funções de Planilha do VBA

Abaixo você encontrará uma lista da maioria das WorksheetFunctions comuns do VBA.

Function	Description
Logical
AND	Checks whether all conditions are met. TRUE/FALSE
IF	If condition is met, do something, if not, do something else.
IFERROR	If result is an error then do something else.
OR	Checks whether any conditions are met. TRUE/FALSE
Lookup & Reference
CHOOSE	Chooses a value from a list based on it's position number.
HLOOKUP	Lookup a value in the first row and return a value.
INDEX	Returns a value based on it's column and row numbers.
LOOKUP	Looks up values either horizontally or vertically.
MATCH	Searches for a value in a list and returns its position.
TRANSPOSE	Flips the oriention of a range of cells.
VLOOKUP	Lookup a value in the first column and return a value.
Date & Time
DATE	Returns a date from year, month, and day.
DATEVALUE	Converts a date stored as text into a valid date
DAY	Returns the day as a number (1-31).
DAYS360	Returns days between 2 dates in a 360 day year.
EDATE	Returns a date, n months away from a start date.
EOMONTH	Returns the last day of the month, n months away date.
HOUR	Returns the hour as a number (0-23).
MINUTE	Returns the minute as a number (0-59).
MONTH	Returns the month as a number (1-12).
NETWORKDAYS	Number of working days between 2 dates.
NETWORKDAYS.INTL	Working days between 2 dates, custom weekends.
NOW	Returns the current date and time.
SECOND	Returns the second as a number (0-59)
TIME	Returns the time from a hour, minute, and second.
TIMEVALUE	Converts a time stored as text into a valid time.
WEEKDAY	Returns the day of the week as a number (1-7).
WEEKNUM	Returns the week number in a year (1-52).
WORKDAY	The date n working days from a date.
YEAR	Returns the year.
YEARFRAC	Returns the fraction of a year between 2 dates.
Engineering
CONVERT	Convert number from one unit to another.
Financial
FV	Calculates the future value.
PV	Calculates the present value.
NPER	Calculates the total number of payment periods.
PMT	Calculates the payment amount.
RATE	Calculates the interest Rate.
NPV	Calculates the net present value.
IRR	The internal rate of return for a set of periodic CFs.
XIRR	The internal rate of return for a set of non-periodic CFs.
PRICE	Calculates the price of a bond.
INTRATE	The interest rate of a fully invested security.
Information
ISERR	Test if cell value is an error, ignores #N/A. TRUE/FALSE
ISERROR	Test if cell value is an error. TRUE/FALSE
ISEVEN	Test if cell value is even. TRUE/FALSE
ISLOGICAL	Test if cell is logical (TRUE or FALSE). TRUE/FALSE
ISNA	Test if cell value is #N/A. TRUE/FALSE
ISNONTEXT	Test if cell is not text (blank cells are not text). TRUE/FALSE
ISNUMBER	Test if cell is a number. TRUE/FALSE
ISODD	Test if cell value is odd. TRUE/FALSE
ISTEXT	Test if cell is text. TRUE/FALSE
TYPE	Returns the type of value in a cell.
Math
ABS	Calculates the absolute value of a number.
AGGREGATE	Define and perform calculations for a database or a list.
CEILING	Rounds a number up, to the nearest specified multiple.
COS	Returns the cosine of an angle.
DEGREES	Converts radians to degrees.
DSUM	Sums database records that meet certain criteria.
EVEN	Rounds to the nearest even integer.
EXP	Calculates the exponential value for a given number.
FACT	Returns the factorial.
FLOOR	Rounds a number down, to the nearest specified multiple.
GCD	Returns the greatest common divisor.
INT	Rounds a number down to the nearest integer.
LCM	Returns the least common multiple.
LN	Returns the natural logarithm of a number.
LOG	Returns the logarithm of a number to a specified base.
LOG10	Returns the base-10 logarithm of a number.
MROUND	Rounds a number to a specified multiple.
ODD	Rounds to the nearest odd integer.
PI	The value of PI.
POWER	Calculates a number raised to a power.
PRODUCT	Multiplies an array of numbers.
QUOTIENT	Returns the integer result of division.
RADIANS	Converts an angle into radians.
RANDBETWEEN	Calculates a random number between two numbers.
ROUND	Rounds a number to a specified number of digits.
ROUNDDOWN	Rounds a number down (towards zero).
ROUNDUP	Rounds a number up (away from zero).
SIN	Returns the sine of an angle.
SUBTOTAL	Returns a summary statistic for a series of data.
SUM	Adds numbers together.
SUMIF	Sums numbers that meet a criteria.
SUMIFS	Sums numbers that meet multiple criteria.
SUMPRODUCT	Multiplies arrays of numbers and sums the resultant array.
TAN	Returns the tangent of an angle.
Stats
AVERAGE	Averages numbers.
AVERAGEIF	Averages numbers that meet a criteria.
AVERAGEIFS	Averages numbers that meet multiple criteria.
CORREL	Calculates the correlation of two series.
COUNT	Counts cells that contain a number.
COUNTA	Count cells that are non-blank.
COUNTBLANK	Counts cells that are blank.
COUNTIF	Counts cells that meet a criteria.
COUNTIFS	Counts cells that meet multiple criteria.
FORECAST	Predict future y-values from linear trend line.
FREQUENCY	Counts values that fall within specified ranges.
GROWTH	Calculates Y values based on exponential growth.
INTERCEPT	Calculates the Y intercept for a best-fit line.
LARGE	Returns the kth largest value.
LINEST	Returns statistics about a trendline.
MAX	Returns the largest number.
MEDIAN	Returns the median number.
MIN	Returns the smallest number.
MODE	Returns the most common number.
PERCENTILE	Returns the kth percentile.
PERCENTILE.INC	Returns the kth percentile. Where k is inclusive.
PERCENTILE.EXC	Returns the kth percentile. Where k is exclusive.
QUARTILE	Returns the specified quartile value.
QUARTILE.INC	Returns the specified quartile value. Inclusive.
QUARTILE.EXC	Returns the specified quartile value. Exclusive.
RANK	Rank of a number within a series.
RANK.AVG	Rank of a number within a series. Averages.
RANK.EQ	Rank of a number within a series. Top Rank.
SLOPE	Calculates the slope from linear regression.
SMALL	Returns the kth smallest value.
STDEV	Calculates the standard deviation.
STDEV.P	Calculates the SD of an entire population.
STDEV.S	Calculates the SD of a sample.
STDEVP	Calculates the SD of an entire population
TREND	Calculates Y values based on a trendline.
Text
CLEAN	Removes all non-printable characters.
DOLLAR	Converts a number to text in currency format.
FIND	Locates position of text within a cell.Case-sensitive.
LEFT	Truncates text a number of characters from the left.
LEN	Counts number of characters in text.
MID	Extracts text from the middle of a cell.
PROPER	Converts text to proper case.
REPLACE	Replaces text based on it's location.
REPT	Repeats text a number of times.
RIGHT	Truncates text a number of characters from the right.
SEARCH	Locates position of text within a cell.Not Case-sensitive.
SUBSTITUTE	Finds and replaces text. Case-sensitive.
TEXT	Converts a value into text with a specific number format.
TRIM	Removes all extra spaces from text.