Class WorksheetFunction (Excel VBA)

Used as a container for Microsoft Excel worksheet functions that can be called from Visual Basic.

Themes

Some procedures in this class have been grouped together in themes and are described on separate theme pages

AccrInt with its procedures AccrInt and AccrIntM
Acos with its procedures Acos and Acosh
Acot with its procedures Acot and Acoth
Asin with its procedures Asin and Asinh
Atan with its procedures Atan2 and Atanh
Average with its procedures Average, AverageIf and AverageIfs
Bessel with its procedures BesselI, BesselJ, BesselK and BesselY
Beta with its procedures Beta_Dist and Beta_Inv
Bin2 with its procedures Bin2Dec, Bin2Hex and Bin2Oct
Binom with its procedures Binom_Dist, Binom_Dist_Range, Binom_Inv and BinomDist
Bit with its procedures Bitand, Bitlshift, Bitor, Bitrshift and Bitxor
Ceiling with its procedures Ceiling_Math and Ceiling_Precise
Chi with its procedures ChiSq_Dist, ChiSq_Dist_RT, ChiSq_Inv, ChiSq_Inv_RT, ChiSq_Test and ChiTest
Combin with its procedures Combin and Combina
Confidence with its procedures Confidence_Norm and Confidence_T
Cot with its procedures Cot and Coth
Count with its procedures Count, CountA, CountBlank, CountIf and CountIfs
CoupDays with its procedures CoupDays and CoupDaysNc
Covar with its procedures Covariance_P and Covariance_S
Csc with its procedures Csc and Csch
Days with its procedures Days and Days360
Db with its procedures Db and Dbcs
DCount with its procedures DCount and DCountA
Dec2 with its procedures Dec2Bin, Dec2Hex and Dec2Oct
Dollar with its procedures Dollar, DollarDe and DollarFr
DStDev with its procedures DStDev and DStDevP
DVar with its procedures DVar and DVarP
F_Dist with its procedures F_Dist and F_Dist_RT
F_Inv with its procedures F_Inv and F_Inv_RT
Fact with its procedures Fact and FactDouble
Filter with its procedures Filter and FilterXML
Find with its procedures Find and FindB
Fisher with its procedures Fisher and FisherInv
Forecast_ETS with its procedures Forecast_ETS, Forecast_ETS_ConfInt and Forecast_ETS_STAT
Gamma with its procedures Gamma, Gamma_Dist, Gamma_Inv and GammaLn_Precise
ImCos with its procedures ImCos and ImCosh
ImSin with its procedures ImSin and ImSinh
IsErr with its procedures IsErr and IsError
Log with its procedures Log, Log10 and LogEst
Max with its procedures Max and MaxIfs
Min with its procedures Min and MinIfs
NetworkDays with its procedures NetworkDays and NetworkDays_Intl
Odd with its procedures Odd, OddFPrice, OddFYield, OddLPrice and OddLYield
Percentile with its procedures Percentile_Exc and Percentile_Inc
PercentRank with its procedures PercentRank_Exc and PercentRank_Inc
Permut with its procedures Permut and Permutationa
Quartile with its procedures Quartile_Exc and Quartile_Inc
Rank with its procedures Rank_Avg and Rank_Eq
Replace with its procedures Replace and ReplaceB
Round with its procedures Round, RoundDown and RoundUp
Search with its procedures Search and SearchB
Sec with its procedures Sec and Sech
Skew with its procedures Skew and Skew_p
StDev with its procedures StDev_P and StDev_S
Sum with its procedures Sum, SumIf, SumIfs, SumSq, SumX2MY2, SumX2PY2 and SumXMY2
T_Dist with its procedures T_Dist, T_Dist_2T and T_Dist_RT
T_Inv with its procedures T_Inv and T_Inv_2T
Text with its procedures Text and TextJoin
Trim with its procedures Trim and TrimMean
WorkDay with its procedures WorkDay and WorkDay_Intl

Methods

Other Methods

Aggregate - Returns an aggregate in a list or database.

AmorDegrc - Returns the depreciation for each accounting period. This function is provided for the French accounting system.

AmorLinc - Returns the depreciation for each accounting period. This function is provided for the French accounting system.

And - Returns True if all its arguments are True; returns False if one or more arguments is False.

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.

Base - Converts a number into a text representation with the given radix (base).

Choose - Uses Arg1 as the index to return a value from the list of value arguments.

Complex - Converts real and imaginary coefficients into a complex number of the form x + yi or x + yj.

Convert - Converts a number from one measurement system to another. For example, Convert can translate a table of distances in miles to a table of distances in kilometers.

Correl - Returns the correlation coefficient of the Arg1 and Arg2 cell ranges.

CoupDayBs - Returns the number of days from the beginning of the coupon period to the settlement date.

CoupNcd - Returns a number that represents the next coupon date after the settlement date.

CoupNum - Returns the number of coupons payable between the settlement date and maturity date, rounded up to the nearest whole coupon.

CoupPcd - expression.CoupPcd (Arg1, Arg2, Arg3, Arg4)

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

CumIPmt - Returns the cumulative interest paid on a loan between start_period and end_period.

CumPrinc - Returns the cumulative principal paid on a loan between start_period and end_period.

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

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

Decimal - Converts a text representation of a number in a given base into a decimal number.

Delta - Tests whether two values are equal. Returns 1 if number1 = number2; otherwise, returns 0.

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 conditions that you specify.

Disc - Returns the discount rate for a security.

DMax - Returns the largest number in a column of a list or database that matches conditions that you specify.

DMin - Returns the smallest number in a column of a list or database that matches conditions that you specify.

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

DSum - Adds the numbers in a column of a list or database that match conditions that you specify.

Duration - Returns the Macauley duration for an assumed par value of $100. Duration is defined as the weighted average of the present value of the cash flows and is used as a measure of a bond price's response to changes in yield.

EDate - Returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date). Use EDate to calculate maturity dates or due dates that fall on the same day of the month as the date of issue.

Effect - Returns the effective annual interest rate, given the nominal annual interest rate and the number of compounding periods per year.

EoMonth - Returns the serial number for the last day of the month that is the indicated number of months before or after start_date. Use EoMonth to calculate maturity dates or due dates that fall on the last day of the month.

Expon_Dist - Returns the exponential distribution. Use Expon_Dist to model the time between events, such as how long an automated bank teller takes to deliver cash. For example, you can use Expon_Dist to determine the probability that the process takes at most 1 minute.

F_Test - Returns the result of an F-test. An F-test returns the two-tailed probability that the variances in array1 and array2 are not significantly different. Use this function to determine whether two samples have different variances. For example, given test scores from public and private schools, you can test whether these schools have different levels of test score diversity.

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_Math - Rounds a number down, to the nearest integer or to the nearest multiple of significance.

Floor_Precise - Rounds the specified number to the nearest multiple of significance.

Forecast_ETS_Seasonality - Returns the length of the repetitive pattern that Excel detects for the specified time series.

Forecast_Linear - Calculates, or predicts, a future value by using existing values. The predicted value is a y-value for a given x-value. The known values are existing x-values and y-values, and the new value is predicted by using linear regression. You can use this function to predict future sales, inventory requirements, or consumer trends.

Dim instance As WorksheetFunction
Dim Arg1 As Double
Dim Arg2 As Object
Dim Arg3 As Object
Dim returnValue As Double

returnValue = instance.Forecast_Linear(Arg1, Arg2, Arg3)

Frequency - Calculates how often values occur within a range of values, and then returns a vertical array of numbers. For example, use Frequency to count the number of test scores that fall within ranges of scores. Because Frequency returns an array, it must be entered as an array formula.

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

FVSchedule - Returns the future value of an initial principal after applying a series of compound interest rates. Use FVSchedule to calculate the future value of an investment with a variable or adjustable rate.

Gcd - Returns the greatest common divisor of two or more integers. The greatest common divisor is the largest integer that divides both number1 and number2 without a remainder.

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.

GeStep - Returns 1 if number ≥ step; otherwise, returns 0 (zero). Use this function to filter a set of values. For example, by summing several GeStep functions, you calculate the count of values that exceed a threshold.

Growth - Calculates predicted exponential growth by using existing data. Growth returns the y-values for a series of new x-values that you specify by using existing x-values and y-values. You can also use the Growth worksheet function to fit an exponential curve to existing x-values and y-values.

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

Hex2Bin - Converts a hexadecimal number to binary.

Hex2Oct - Converts a hexadecimal number to octal.

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 that you specify in the table or array. Use HLookup when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows. Use VLookup when your comparison values are located in a column to the left of the data that you want to find.

HypGeom_Dist - Returns the hypergeometric distribution. HypGeom_Dist returns the probability of a given number of sample successes, given the sample size, population successes, and population size. Use HypGeom_Dist for problems with a finite population, where each observation is either a success or a failure, and where each subset of a given size is chosen with equal likelihood.

IfError - Returns a value that you specify if a formula evaluates to an error; otherwise, returns the result of the formula. Use the IfError function to trap and handle errors in a formula.

IfNa - Returns the value that you specify if the expression resolves to #N/A; otherwise, returns the result of the expression.

ImDiv - Returns the quotient of two complex numbers in x + yi or x + yj text format.

ImPower - Returns a complex number in x + yi or x + yj text format raised to a power.

ImProduct - Returns the product of 2 to 29 complex numbers in x + yi or x + yj text format.

ImSub - Returns the difference of two complex numbers in x + yi or x + yj text format.

ImSum - Returns the sum of two or more complex numbers in x + yi or x + yj text format.

Index - Returns a value or the reference to a value from within a table or range. There are two forms of the Index function: the array form and the reference form.

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.

IntRate - Returns the interest rate for a fully invested security.

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. These cash flows do not have to be even, as they would be for an annuity. However, the cash flows must occur at regular intervals, such as monthly or annually. The internal rate of return is the interest rate received for an investment consisting of payments (negative values) and income (positive values) that occur at regular periods.

ISO_Ceiling - Returns a number that is rounded up to the nearest integer or to the nearest multiple of significance.

IsoWeekNum - Returns the ISO week number of the year for a given date.

Ispmt - Calculates the interest paid during a specific period of an investment. This function is provided for compatibility with Lotus 1-2-3.

Kurt - Returns the kurtosis of a data set. Kurtosis characterizes the relative peakedness or flatness of a distribution compared with the normal distribution. Positive kurtosis indicates a relatively peaked distribution. Negative kurtosis indicates a relatively flat distribution.

Large - Returns the k-th largest value in a data set. You can use this function to select a value based on its relative standing. For example, you can use Large to return the highest, runner-up, or third-place score.

Lcm - Returns the least common multiple of integers. The least common multiple is the smallest positive integer that is a multiple of all integer arguments number1, number2, and so on. Use Lcm to add fractions with different denominators.

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. Because this function returns an array of values, it must be entered as an array formula.

LogNorm_Dist - Returns the lognormal distribution of x, where ln(x) is normally distributed with parameters mean and standard_dev. Use this function to analyze data that has been logarithmically transformed.

LogNorm_Inv - Returns the inverse of the lognormal cumulative distribution function. Use the lognormal distribution to analyze logarithmically transformed data.

Lookup - Returns a value either from a one-row or one-column range or from an array. The Lookup function has two syntax forms: the vector form and the array form.

Match - Returns the relative position of an item in an array that matches a specified value in a specified order. Use Match instead of one of the Lookup functions when you need the position of an item in a range instead of the item itself.

Sub HighlightMatches()
    Application.ScreenUpdating = False
    
    'Declare variables
    Dim var As Variant, iSheet As Integer, iRow As Long, iRowL As Long, bln As Boolean
       
       'Set up the count as the number of filled rows in the first column of Sheet1.
       iRowL = Cells(Rows.Count, 1).End(xlUp).Row
       
       'Cycle through all the cells in that column:
       For iRow = 1 To iRowL
          'For every cell that is not empty, search through the first column in each worksheet in the
          'workbook for a value that matches that cell value.

          If Not IsEmpty(Cells(iRow, 1)) Then
             For iSheet = ActiveSheet.Index + 1 To Worksheets.Count
                bln = False
                var = Application.Match(Cells(iRow, 1).Value, Worksheets(iSheet).Columns(1), 0)
                
                'If you find a matching value, indicate success by setting bln to true and exit the loop;
                'otherwise, continue searching until you reach the end of the workbook.
                If Not IsError(var) Then
                   bln = True
                   Exit For
                End If
             Next iSheet
          End If
          
          'If you do not find a matching value, do not bold the value in the original list;
          'if you do find a value, bold it.
          If bln = False Then
             Cells(iRow, 1).Font.Bold = False
             Else
             Cells(iRow, 1).Font.Bold = True
          End If
       Next iRow
    Application.ScreenUpdating = True
End Sub

MDuration - Returns the modified Macauley duration for a security with an assumed par value of $100.

Median - Returns the median of the given numbers. The median is the number in the middle of a set of numbers.

MIrr - Returns the modified internal rate of return for a series of periodic cash flows. MIrr considers both the cost of the investment and the interest received on reinvestment of cash.

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_Mult - Returns a vertical array of the most frequently occurring, or repetitive, values in an array or range of data.

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

MRound - Returns a number rounded to the desired multiple.

MultiNomial - Returns the ratio of the factorial of a sum of values to the product of factorials.

NegBinom_Dist - Returns the negative binomial distribution. NegBinom_Dist returns the probability that there will be number_f failures before the number_s-th success, when the constant probability of a success is probability_s. This function is similar to the binomial distribution, except that the number of successes is fixed, and the number of trials is variable. Like the binomial, trials are assumed to be independent.

Nominal - Returns the nominal annual interest rate, given the effective rate and the number of compounding periods per year.

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

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

Norm_S_Dist - Returns the standard normal cumulative distribution function. The distribution has a mean of 0 (zero) and a standard deviation of one. Use this function in place of a table of standard normal curve areas.

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).

NumberValue - Converts text to number in a locale-independent manner.

Oct2Bin - Converts an octal number to binary.

Oct2Hex - Converts an octal number to hexadecimal.

Or - Returns True if any argument is True; returns False if all arguments are False.

PDuration - Returns the number of periods required by an investment to reach a specified value.

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.

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

Poisson_Dist - 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.

Price - Returns the price per $100 face value of a security that pays periodic interest.

PriceDisc - Returns the price per $100 face value of a discounted security.

PriceMat - Returns the price per $100 face value of a security that pays interest at maturity.

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.

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.

Quotient - Returns the integer portion of a division. Use this function when you want to discard the remainder of a division.

RandBetween - Returns a random integer number between the numbers that you specify. A new random integer number is returned every time the worksheet is calculated.

Rate - Returns the interest rate per period of an annuity. Rate is calculated by iteration and can have zero or more solutions. If the successive results of Rate do not converge to within 0.0000001 after 20 iterations, Rate returns the #NUM! error value.

Received - Returns the amount received at maturity for a fully invested security.

Rept - Repeats text a given number of times. Use Rept to fill a cell with a number of instances of a text string.

Roman - Converts an arabic numeral to roman, as text.

Rri - Returns an equivalent interest rate for the growth of an investment.

RSq - Returns the square of the Pearson product moment correlation coefficient through data points in known_y's and known_x's. For more information, see Pearson. The r-squared value can be interpreted as the proportion of the variance in y attributable to the variance in x.

RTD - This method connects to a source to receive real-time data (RTD).

SeriesSum - Returns the sum of a power series based on the following formula:.

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 k-th 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_dev.

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. Use Substitute when you want to replace specific text in a text string; use Replace when you want to replace any text that occurs in a specific location in a text string.

Subtotal - Creates subtotals.

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

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

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

TBillEq - Returns the bond-equivalent yield for a Treasury bill.

TBillPrice - Returns the price per $100 face value for a Treasury bill.

TBillYield - Returns the yield for a Treasury bill.

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. Use this function in place of a table of critical values for the t-distribution.

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.

USDollar - Converts a number to text format and applies a currency symbol. The name of the method (and the symbol that it applies) depends upon the language settings.

=DOLLAR(A2, 2)

Var - Estimates variance based on a sample.

Var_P - Calculates variance based on the entire population.

Var_S - Estimates variance based on a sample.

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

VLookup - Searches for a value in the first column of a table array and returns a value in the same row from another column in the table array.

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.

WeekNum - Returns a number that indicates where the week falls numerically within a year.

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

Xirr - Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic. To calculate the internal rate of return for a series of periodic cash flows, use the Irr function.

Xnpv - Returns the net present value for a schedule of cash flows that is not necessarily periodic.

Dim npv As Double 
npv = Application.Evaluate("=XNPV(.09,A2:A6,B2:B6)")

Xor - Returns a logical exclusive OR of all arguments.

YearFrac - Calculates the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date). Use the YearFrac worksheet function to identify the proportion of a whole year's benefits or obligations to assign to a specific term.

YieldDisc - Returns the annual yield for a discounted security.

YieldMat - Returns the annual yield of a security that pays interest at maturity.

Z_Test - Returns the one-tailed probability-value of a z-test. For a given hypothesized population mean, Z_Test returns the probability that the sample mean would be greater than the average of observations in the data set (array); that is, the observed sample mean.

Properties

Parent returns the parent object for the specified object. Read-only.