O'Reilly logo

Programming Excel with VBA and .NET by Steve Saunders, Jeff Webb

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

The WorksheetFunction Object

Another useful top-level object to know about is the WorksheetFunction object. That object provides the functions from the Excel formula bar to your Visual Basic code. Some of these members are redundant with those provided by the VBA object library, however, others provide very useful (and advanced) analytical and statistical functions, as described in Table 4-8.

Table 4-8. Members of the WorksheetFunction object

Member

Description

Acos

Returns the arccosine, or inverse cosine, of a number. The arccosine is the angle whose cosine is a number. The returned angle is given in radians in the range 0 to π.

Acosh

Returns the inverse hyperbolic cosine of a number.

And

The same as the Visual Basic And operator.

Asc

Converts double-byte characters to single-byte characters.

Asin

Returns the arcsine, or inverse sine, of a number. The arcsine is the angle whose sine is the given number. The returned angle is given in radians in the range −π/2 to π/2.

Asinh

Returns the inverse hyperbolic sine of a number.

Atan2

Returns the arctangent, or inverse tangent, of the specified x- and y-coordinates.

Atanh

Returns the inverse hyperbolic tangent of a number.

AveDev

Returns the average of the absolute deviations of data points from their mean. AveDev is a measure of the variability in a data set.

Average

Returns the average (arithmetic mean) of the arguments.

BetaDist

Returns the beta cumulative distribution function.

BetaInv

Returns the inverse of the cumulative distribution function for a specified beta distribution.

BinomDist

Returns the individual term binomial distribution probability.

Ceiling

Returns a number rounded up, away from 0, to the nearest multiple of significance.

ChiDist

Returns the one-tailed probability of the chi-squared distribution.

ChiInv

Returns the inverse of the one-tailed probability of the chi-squared distribution.

ChiTest

Returns the test for independence. Determines whether hypothesized results are verified by an experiment.

Choose

Uses an index to return a value from the list of value arguments.

Clean

Removes all nonprintable characters from text.

Combin

Returns the number of combinations for a given number of items.

Confidence

Returns a value that you can use to construct a confidence interval for a population mean.

Correl

Returns the correlation coefficient of two cell ranges.

Cosh

Returns the hyperbolic cosine of a number.

Count

Counts the number of cells that contain numbers and also numbers within the list of arguments.

CountA

Counts the number of cells that are not empty and the values within the list of arguments.

CountBlank

Counts empty cells in a specified range of cells.

CountIf

Counts the number of cells within a range that meet the given criteria.

Covar

Returns covariance, the average of the products of deviations for each data point pair.

CritBinom

Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value.

DAverage

Averages the values in a column of a list or database that match conditions you specify.

Days360

Returns the number of days between two dates based on a 360-day year (12 30-day months), which is used in some accounting calculations.

Db

Returns the depreciation of an asset for a specified period using the fixed-declining-balance method.

DCount

Counts the cells that contain numbers in a column of a list or database that match conditions you specify.

DCountA

Counts the nonblank cells in a column of a list or database that match specified conditions.

Ddb

Returns the depreciation of an asset for a specified period using the double-declining-balance method or some other method you specify.

Degrees

Converts radians into degrees.

DevSq

Returns the sum of squares of deviations of data points from their sample mean.

DGet

Extracts a single value from a column of a list or database that matches specified conditions.

DMax

Returns the largest number in a column of a list or database that matches specified conditions.

DMin

Returns the smallest number in a column of a list or database that matches specified conditions.

Dollar

Formats a number as currency using the local currency symbol.

DProduct

Multiplies the values in a column of a list or database that match conditions you specify.

DStDev

Estimates the standard deviation of a population based on a sample by using the numbers in a column of a list or database that match specified conditions.

DStDevP

Calculates the standard deviation of a population based on the entire population, using the numbers in a column of a list or database that match specified conditions.

DSum

Adds the numbers in a column of a list or database that match specified conditions.

DVar

Estimates the variance of a population based on a sample by using the numbers in a column of a list or database that match conditions you specify.

DVarP

Calculates the variance of a population based on the entire population by using the numbers in a column of a list or database that match specified conditions.

Even

Returns a number rounded up to the nearest even integer.

ExponDist

Returns the exponential distribution.

Fact

Returns the factorial of a number.

FDist

Returns the F probability distribution.

Find

Finds the location of one string within another (similar to Instr).

FindB

Finds the location of one double-byte string within another (similar to Instr).

FInv

Returns the inverse of the F probability distribution.

Fisher

Returns the Fisher transformation at x. This transformation produces a function that is normally distributed rather than skewed.

FisherInv

Returns the inverse of the Fisher transformation. Use this transformation when analyzing correlations between ranges or arrays of data.

Fixed

Rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text.

Floor

Rounds a number down, toward 0, to the nearest multiple of significance.

Forecast

Calculates, or predicts, a future value by using existing values.

Frequency

Calculates how often values occur within a range of values and then returns a vertical array of numbers.

FTest

Returns the result of an F-test. An F-test returns the one-tailed probability that the variances in array1 and array2 are not significantly different.

Fv

Returns the future value of an investment based on periodic, constant payments and a constant interest rate.

GammaDist

Returns the gamma distribution. You can use this function to study variables that may have a skewed distribution. The gamma distribution is commonly used in queuing analysis.

GammaInv

Returns the inverse of the gamma cumulative distribution.

GammaLn

Returns the natural logarithm of the gamma function (x).

GeoMean

Returns the geometric mean of an array or range of positive data. For example, you can use GeoMean to calculate average growth rate given compound interest with variable rates.

Growth

Calculates predicted exponential growth by using existing data.

HarMean

Returns the harmonic mean of a data set. The harmonic mean is the reciprocal of the arithmetic mean of reciprocals.

HLookup

Searches for a value in the top row of a table or an array of values and then returns a value in the same column from a row you specify in the table or array.

HypGeomDist

Returns the hypergeometric distribution. Hypergeometric distribution is the probability of a given number of sample successes, given the sample size, population successes, and population size.

Intercept

Calculates the point at which a line will intersect the y-axis by using existing x-values and y-values. The intercept point is based on a best-fit regression line plotted through the known x-values and known y-values.

Ipmt

Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.

Irr

Returns the internal rate of return for a series of cash flows represented by the numbers in values.

IsErr

Returns True if a cell contains an error other than #N/A.

IsError

Returns True if a cell contains an error.

IsLogical

Returns True if a cell contains a Boolean value.

IsNA

Returns True if a cell contains the #N/A error value.

IsNonText

Returns True if a cell does not contain text.

IsNumber

Returns True if a cell contains a numeric value.

Ispmt

Calculates the interest paid during a specific period of an investment.

IsText

Returns True if a cell contains a string.

Kurt

Returns the kurtosis of a data set. Kurtosis characterizes the relative peakedness or flatness of a distribution compared with the normal distribution.

Large

Returns the kth largest value in a data set. You can use this function to select a value based on its relative standing.

LinEst

Calculates the statistics for a line by using the least-squares method to calculate a straight line that best fits your data, and returns an array that describes the line.

Ln

Returns the natural logarithm of a number.

Log

Returns the logarithm of a number to the specified base.

Log10

Returns the base-10 logarithm of a number.

LogEst

Calculates an exponential curve that fits your data and returns an array of values that describes the curve. Returns an array of values.

LogInv

Returns the inverse of the lognormal cumulative distribution function of x, where ln(x) is normally distributed with parameters mean and standard_dev.

LogNormDist

Returns the cumulative lognormal distribution of x, where ln(x) is normally distributed with parameters mean and standard_dev.

Lookup

Finds a value in an array and returns that value. For two-dimensional arrays, it is better to use HLookup or VLookup.

Match

Returns the relative position of an item in an array that matches a specified value in a specified order.

Max

Returns the largest value in a set of values.

MDeterm

Returns the matrix determinant of an array.

Median

Returns the median of the given numbers.

Min

Returns the smallest number in a set of values.

MInverse

Returns the inverse matrix for the matrix stored in an array.

MIrr

Returns the modified internal rate of return for a series of periodic cash flows.

MMult

Returns the matrix product of two arrays. The result is an array with the same number of rows as array1 and the same number of columns as array2.

Mode

Returns the most frequently occurring, or repetitive, value in an array or range of data.

NegBinomDist

Returns the negative binomial distribution. NegBinomDist returns the probability that there will be number_f failures before the number_sth success, when the constant probability of a success is probability_s.

NormDist

Returns the normal distribution for the specified mean and standard deviation. This function has a wide range of applications in statistics, including hypothesis testing.

NormInv

Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation.

NormSDist

Returns the standard normal cumulative distribution function. The distribution has a mean of 0 and a standard deviation of 1. Use this function in place of a table of standard normal curve areas.

NormSInv

Returns the inverse of the standard normal cumulative distribution. The distribution has a mean of 0 and a standard deviation of 1.

NPer

Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.

Npv

Calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values).

Odd

Returns a number rounded up to the nearest odd integer.

Or

The same as the Visual Basic Or operator.

Parent

Returns the parent object for the specified object.

Pearson

Returns the Pearson product moment correlation coefficient, r, a dimensionless index that ranges from -1.0 to 1.0 inclusive and reflects the extent of a linear relationship between two data sets.

Percentile

Returns the kth percentile of values in a range. You can use this function to establish a threshold of acceptance. For example, you can decide to examine candidates who score above the 90th percentile.

PercentRank

Returns the rank of a value in a data set as a percentage of the data set.

Permut

Returns the number of permutations for a given number of objects that can be selected from number objects. A permutation is any set or subset of objects or events in which internal order is significant.

Phonetic

Extracts the phonetic (furigana) characters from a text string.

Pi

Returns the number 3.14159265358979, the mathematical constant π, accurate to 15 digits.

Pmt

Calculates the payment for a loan based on constant payments and a constant interest rate.

Poisson

Returns the Poisson distribution. A common application of the Poisson distribution is predicting the number of events over a specific time, such as the number of cars arriving at a toll plaza in one minute.

Power

Returns the result of a number raised to a power.

Ppmt

Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate.

Prob

Returns the probability that values in a range are between two limits. If upper_limit is not supplied, returns the probability that values in x_range are equal to lower_limit.

Product

Multiplies all the numbers given as arguments and returns the product.

Proper

Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters.

Pv

Returns the present value of an investment. The present value is the total amount that a series of future payments is worth now. For example, when you borrow money, the loan amount is the present value to the lender.

Quartile

Returns the quartile of a data set. Quartiles often are used in sales and survey data to divide populations into groups.

Radians

Converts degrees to radians.

Rank

Returns the rank of a number in a list of numbers. If you sort a list, the rank of the number is its position in the list.

Rate

Returns the interest rate per period of an annuity.

Replace

Replaces part of one string with another.

ReplaceB

Replaces part of one double-byte string with another.

Rept

Repeats text a given number of times.

Roman

Formats an Arabic numeral as a Roman numeral.

Round

Rounds a number to a specified number of digits.

RoundDown

Rounds a number down, toward 0.

RoundUp

Rounds a number up, away from 0.

RSq

Returns the square of the Pearson product moment correlation coefficient through data points in known_y's and known_x's.

RTD

Retrieves real-time data from a program that supports automation.

Search

Finds the location of one string within another (similar to Instr).

SearchB

Finds the location of one double-byte string within another (similar to Instr).

Sinh

Returns the hyperbolic sine of a number.

Skew

Returns the skewness of a distribution. Skewness characterizes the degree of asymmetry of a distribution around its mean.

Sln

Returns the straight-line depreciation of an asset for one period.

Slope

Returns the slope of the linear regression line through data points in known_y's and known_x's. The slope is the vertical distance divided by the horizontal distance between any two points on the line, which is the rate of change along the regression line.

Small

Returns the kth smallest value in a data set. Use this function to return values with a particular relative standing in a data set.

Standardize

Returns a normalized value from a distribution characterized by mean and standard deviation.

StDev

Estimates standard deviation based on a sample. The standard deviation is a measure of how widely values are dispersed from the average value (the mean).

StDevP

Calculates standard deviation based on the entire population given as arguments. The standard deviation is a measure of how widely values are dispersed from the average value (the mean).

StEyx

Returns the standard error of the predicted y-value for each x in the regression. The standard error is a measure of the amount of error in the prediction of y for an individual x.

Substitute

Substitutes new_text for old_text in a text string.

Subtotal

Returns a subtotal in a list or database.

Sum

Adds all the numbers in a range of cells.

SumIf

Adds cells that meet specified criteria.

SumProduct

Multiplies corresponding components in the given arrays and returns the sum of those products.

SumSq

Returns the sum of the squares of the arguments.

SumX2MY2

Returns the sum of the difference of squares of corresponding values in two arrays.

SumX2PY2

Returns the sum of the sum of squares of corresponding values in two arrays. The sum of the sum of squares is a common term in many statistical calculations.

SumXMY2

Returns the sum of squares of differences of corresponding values in two arrays.

Syd

Returns the sum-of-years digits depreciation of an asset for a specified period.

Tanh

Returns the hyperbolic tangent of a number.

TDist

Returns the percentage points (probability) for the student t-distribution where a numeric value (x) is a calculated value of t for which the percentage points are to be computed. The t-distribution is used in the hypothesis testing of small sample data sets.

Text

Converts a value to text in a specific number format.

TInv

Returns the t-value of the student t-distribution as a function of the probability and the degrees of freedom.

Transpose

Returns a vertical range of cells as a horizontal range or vice versa.

Trend

Returns values along a linear trend. Fits a straight line (using the method of least squares) to the arrays known_y's and known_x's. Returns the y-values along that line for the array of new_x's that you specify.

Trim

Removes all spaces from text except for single spaces between words.

TrimMean

Returns the mean of the interior of a data set. TrimMean calculates the mean taken by excluding a percentage of data points from the top and bottom tails of a data set. You can use this function when you wish to exclude outlying data from your analysis.

TTest

Returns the probability associated with a student t-test. Use TTest to determine whether two samples are likely to have come from the same two underlying populations that have the same mean.

USDollar

Formats a number as U.S. currency.

Var

Estimates variance based on a sample.

VarP

Calculates variance based on the entire population.

Vdb

Returns the depreciation of an asset for any period you specify, including partial periods, using the double-declining-balance method or some other method you specify. VDB stands for variable declining balance.

VLookup

Searches for a value in the leftmost column of a table and then returns a value in the same row from a column you specify in the table.

Weekday

Returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default.

Weibull

Returns the Weibull distribution. Use this distribution in reliability analysis, such as calculating a device’s mean time to failure.

ZTest

Returns the one-tailed probability-value of a z-test. For a given hypothesized population mean, that is, the observed sample mean.

There is no Help for members in Table 4-8 from Visual Basic, but you can look up these functions in the Excel Help file as shown in Figure 4-21.

Use Excel Help to find information on the WorksheetFunction members

Figure 4-21. Use Excel Help to find information on the WorksheetFunction members

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required