Use Any Formula: Worksheetfunction in VBA

Formulasin EXCEL

Microsoft Excel provides several built-in functions that can be used as formulas in Excel worksheets. It shows the function categories available in the Insert Function window, as shown in the following figure:

Using a formula in an Excel worksheet

Just as mathematical formulas help us solve simple and quick mathematical problems, so do Mrs. B.’s. Excel provides formulas for keeping good databases and formulas that can be automatically calculated and saved for easy use and reference.

Example of an Excel formula:

After we enter the SUM formula in the formula bar or in any cell (what you enter in a cell that starts with an equal sign (=) is displayed in the formula bar), if we press Enter, click a check mark in the formula bar, or select another cell, the automatically calculated answer is displayed.

If a row or formula cell is not in edit mode, the checkboxes and crosses in the formula row are disabled.

Using formulas as functions in VBA

Excel VBA provides most of these formulas as built-in functions that can easily be used in our code to achieve the same result. These are called spreadsheet functions.

What is the spreadsheet function?

The MS Excel application object has a method called WorksheetFunction. It has access to most standard built-in functions that are available as formulas in Excel.

Application. can be used immediately to access the functions. However, using Application.worksheetfunction. can help a programmer who can use Intellisense to browse through the parameters that should be used to complete the feature set.

List of worksheet functions – VBA

The following is a list of some commonly used spreadsheet functions.

List of logical functions

no	Function	Description	Syntax	Example	Output
1	И	Check that all conditions are met. The possible result of the output can be TRUE or FALSE.	Function of the worktop. And(Logic1,Logic2)	Function of the worktop. И(5>3,4<6)	TRUE
2	IF	If the conditions are met, do something (second parameter), otherwise do something else (third parameter).	IF	IF(2<6, 2 min, 6 min)	2 means less
3	IFERROR	It is a combination of IF and ISERROR.	IF(ISERROR( <calculation>), 0, <result of calculation>)	IF(ISERROR(2/0),0,2/0)	0
4	OR	Verifies that one of the conditions is met. The possible result of the output can be TRUE or FALSE.	Function of the worktop. Si(Logic1,Logic2)	Function of the worktop. If(5>3,14<6)	TRUE

List of search and reference functions

no	Function	Description	Syntax
1	SELECT	Selects a value from a list based on the index number and index number specified in the parameter.	select( , value1, value2)
2	HLOOKUP	Looks for a value in the first row of the array and returns it.	Hlookup( , , , )
3	INDEX	Returns a value based on the column number and the row number.	INDEX (matrix, , <[col num]>, <[area num]>)
4	LOOKUP	Search for values in horizontal (rows) or vertical (columns) direction.	Lookup( <search value>, , )
5	MATCH	Searches for a value in the list and returns its position.	MATCH (,, <[type of match]>)
6	TRANSPORT	Changes the alignment of some cells. From vertical to horizontal and vice versa.	Transpose()
7	VLOOKUP	Searches for and returns a value in the first column of the array.	Vlookup( , , , )

Date and time functions

A valid date here means that IsDate() returns the boolean value TRUE.

no	Function	Description	Syntax
1	DATE	Returns the date from a combination of year, month and day.	Date (year, month, day)
2	DATEVAL	Converts the date stored as text to a valid date (in an acceptable data format).	date value(<text-date> )
3	DAY	Returns the day as a number (1-31) from the current date.	Tag()
4	DAY360	Returns the difference (in days) between two valid dates in a 360-day year.	DAYS360( <starting date> , ,
5	EDATE	Returns the current date, n months from the start date.	DATE(, )
6	EOMONTH	Returns the last day of the month, n months from the current date.	Month
7	CHARLIE	Returns the time as a number (0-23) from the current time.	Time(<serial number>)
8	MINUTE	Returns the minute as a number (0-59) from the current time.	Minute()
9	MONTHS	Returns the month as a number (1-12) of a valid date.	Month()
10	POLICY NETWORK	Returns the number of working days between 2 valid dates.	network days(, , [])
11	NETWORK DAYS.INTL	Returns the number of working days between 2 valid dates. By default, weekends are excluded and it is possible to add a list of holidays to be excluded from the count.	netzwerktage.intl( , , [], [])
12	NOW	Returns the current date and time as they appear in the system.	now()
13	SECOND REPORT	Returns the second as a number (0-59) from the current time.	Second()
14	TIME	Returns the time from a valid combination of hours, minutes and seconds.	Time (, , )
15	TIME	Converts time stored in text form to real time.	Time value()
16	WEEKLY	Returns the day of the week as an integer (1-7).	Day of the week(, [])
17	WEEKLY NUMBER	Returns the week number of the year (1-52).	week number(, [])
18	WORK DAY	Returns the date of n working days from the valid date.	Working day (, days, [ ])
19	BIRTHDAY	Returns the year of a valid date.	Year(<serial number>)
20	YEARFRAC	Returns the fraction of a year between two valid dates.	yearfrac( , [])

Technical functions

no	Function	Description	Syntax
1	CONVERT	Converts a number or value from one unit to another. E.G.: Cm in inches, ft in centimeters, m in centimeters, kg in g.	convert(, <from unit> , <to unit> )

Financial functions

no	Function	Description	Syntax
1	FV	Calculates and provides the future value of the investment.	fv(, , [pv], [])
2	PV	Calculates and provides the current value of the investment.	fv(, , [], [])
3	NPER	Calculates and gives the total number of pay periods for the investment.	nper(<Rate, , [], [type])
4	PMT	Calculates and reimburses the payment amount when making an investment.	pmt(, , [], [])
5	TARIFFS	Calculates and returns the interest rate of the investment.	Quotes (, ,, [], [], [])
6	CASH VALUE	Calculates and returns the net present value of the investment.	npv(, , [], [], ….., [])
7	IRR	Calculates and reports internal profitability for a range of periodic CFs.	irr( , [] )
8	XIRR	Calculates and returns the internal rate of return for a set of non-periodic CFs.	xirr(, , [] )
9	PRICE	Calculates and returns the bond price.	Price (, , , [])
10	INTRATE	Returns the interest rate of a fully invested security.	within(, , , [])

Information functions

no	Function	Description	Syntax
1	ISERR	Tests if the cell value is an error, ignores #N/A. Returns a boolean value TRUE/FALSE	iserr( < value > )
2	ISERROR	Checks if the cell value is an error. Returns the boolean value TRUE/FALSE	iserror( <value>)
3	ISEVEN	Checks whether the cell value is uniform. Returns the boolean value TRUE/FALSE	iseven( <a number>)
4	ISLOGICAL	Checks whether the cell is logical (TRUE or FALSE). Returns the boolean value TRUE/FALSE	islogic( < value > )
5	ISNA	Test if the cell value is #N/A. Returns a boolean value TRUE/FALSE.	isna( < value > )
6	ISNONTEXT	Checks if the cell is text (empty cells are not text). Returns the boolean value TRUE/FALSE	isnontext( < value > )
7	IDENTIFIER	Checks if the mobile number is a number. Returns the boolean value TRUE/FALSE	isnumber( < value > )
8	ISODD	Checks if the value of the cell is odd. Returns the boolean value TRUE/FALSE	isodd( <a number>)
9	SOURCE	Checks if the cell is text. Returns the boolean value TRUE/FALSE	istext( < value > )
10	TYPE	Returns the type of the value stored in the cell.	Type( < value > )

Mathematical functions

no	Function	Description	Syntax
1	ABS	Calculates and returns the absolute value of the number.	abs( <a number>)
2	AGGREGAT	It defines and performs calculations for a database or a list.	AGGREGATE(, , [k] >
3	COVER	Rounds the number to the nearest specified multiple and returns the value.	Ceiling( <a number> , )
4	COS	Returns the cosine value of the angle.	cos( <a number>)
5	REVIEWS	Converts radians to degrees and returns the value.	Degree( <angle>)
6	TSUM	provides a summary of the records in the database that meet certain criteria.	dsum( , , )
7	EVEN	Round to the nearest even number and return the value.	even( <a number>)
8	EXP	Calculates an exponential value for a given number and returns the value.	exp( <a number> )
9	DONE	Returns the factorial value.	Done()
10	DISCUSSION	Round the number to the nearest indicated multiple.	soil ( , )
11	GCD	Returns the greatest common divisor.	gcd( <number 1> , [], [],……[])
12	INT	Round the number to the nearest whole number.	int( < number > )
13	LCM	Returns the least common multiple.	lcm( <number 1> , [], [],……[])
14	LN	Returns the natural logarithm of the number.	LN( <a number> )
15	LOG	Returns the logarithm of the number with the specified base.	log( , [])
16	LOG10	Returns the logarithm of the number in base 10.	log10( <a number> )
17	MROUND	Rounds a number to the specified plural.	mround( < number > , )
18	ODD	Round to the nearest odd number.	odd( <a number > )
19	PI	Meaning of PI.	PI()
20	POWER	Calculate the estimated number.	Power( <a number> , )
21	PRODUCT	Multiplies a series of numbers.	product( <number 1> , [], [],……[]).
22	QUOTIENT	Returns the integer of the division.	Quotient( <counter> , )
23	RADIANS	Converts the angle to radians.	radians( <angle> )
24	RANDBETWEEN	Calculates a random number between two numbers.	borderbetween( <Bottom> , )
25	ROUND	Rounds the number to the specified number of digits (specified in the parameter).	around( <a number> , )
26	ROUNDDOWN	Round the number down (to zero).	ROUNDDOWN( , )
27	ROUNDUP	Round the number up (away from zero).	ROUNDUP( , )
28	SIN	Returns the value of the sine of the angle.	sin( <a number>)
29	SYNOPSIS	Returns a statistical summary of a set of data.	subtotal( <function num>, , [], [],…..[]).
30	SYNOPSIS	Add up the numbers and return the sum.	Sum( <number 1> , [], [],……[])
31	SUMIF	The sums of the numbers that meet the criteria.	somif( , , [])
32	SUMIFS	Sum of numbers satisfying different criteria.	sumifs(<sum range>, , <criterion 1> , [, ],[, ],….[, ]])
33	SYNOPSIS	multilayer arrays of numbers and folds the resulting array.	sumproduct( <table 1> , [], [], [],….[])
34	TAN	Returns the value of the tangent of the angle.	tan( <a number > )

Statistical functions

no	Function	Description	Syntax
1	AVERAGE	Average of the given numbers.	Average( <number 1> , [], [],……[])
2	AVERAGE	A set of average numbers that meet the criteria.	averageif( , , [])
3	AVERAGEIFS	Average value of numbers that meet different criteria.	averageifs(<average range>, , <criterion 1> , [, ],[, ],….[, ]])
4	CORREL	Calculates and gives the correlation of two series.	correlate( , )
5	COUNT	Counts the number of cells that contain a specified value and returns this number.	count( , [], [], [<value 4>]….[] )
6	COUNTRY	Counts the number of empty cells and returns them.	COUNTA( , [], [], [<value 4>]….[] )
7	COUNTBLANK	Counts the number of empty cells and returns them.	countblank( <range> )
8	COUNTIF	Counts and returns the number of cells that meet the criteria.	counttif( , )
9	CONFIGURATIONS	Counts and returns the number of cells that meet various criteria.	counttifs( , <criterion 1> , [, ], [, ],….[, ])
10	PREDICTION	Predict future y-values from the linear trend line.	Prediction ( , )
11	FREQUENCY	Counts the values that fall within the specified ranges.	frequency(<data table> , )
12	GROW	Calculates Y values based on exponential growth.	Growth( , [], [], [], [const])
13	INTERCEPT	Calculates the intercept of the line with the best match.	interception(, )
14	MORE	Returns the kth largest value.	large(, k)
15	LINES	Returns a trendline statistic.	linest( <known ys> , [], [] , [] )
16	MAX	Returns the largest number in the specified row.	max( <number 1> , [], [],……[])
17	MEDIAN	Returns the median value of the specified range.	Median ( <number 1> , [], [],……[])
18	MIN	Returns the smallest number in the specified row.	min( <number 1> , [], [],……[])
19	MODE	Returns the most frequent number in the specified row.	mode( <number 1> , [], [],……[])
20	PERCENTAGE CRANE	Returns the 10th percentile.	Percentile( , )
21	PERCENTAGE CRANE	Returns the k percentile, where k is included.	Percentile.
22	PERCENTAGE CRANE	Returns the k percentile, where k is exclusive.	Percentile.
23	QUARTIL	Returns the value of the specified quartile.	quartile( <table> , )
24	NEIGHBORHOOD	Returns the value of the specified quartile (inclusive).	quartile.
25	OVERVIEW OF THE APARTMENT	Returns the value of the specified quartile (exclusive).	quartile.exc( , )
26	RANK	Returns the rank of the number in the row.	rank(<number> , , []))
27	RANK.AVG.	Returns the rank of the number in the row. (Average).	rank.avg (<a number> , , [])
28	RANK.E.M.T.	Returns the rank of the number in the row. (Front row).	rank.eq(<number> , , [])
29	SLOP	Calculates and returns the value of the slope of a linear regression.	Slope( < known ys> , )
30	LITTLE	Returns the smallest value in the number sequence.	small( <table>, )
31	STDEV	Calculates and returns the standard deviation.	stdev( <number 1> , [], [],……[])
32	STDEV.P.	Calculates and gives the standard deviation of the entire population.	stdev.p( <number 1> , [], [],……[])
33	STDEV.S.	Calculates and gives the standard deviation of the sample.	stdev.s( <number 1> , [], [],……[])
34	STDEVP	Calculates and gives the standard deviation of the whole population	stdevp( <number 1> , [], [],……[])
35	TREND	Calculates and returns Y values based on the trend line.	trend( , [], [], [], [const])

String (or) text functions

no	Function	Description	Syntax
1	NUMBER	Deletes all characters that cannot be printed.	pure
2	DOLLAR	Adds the currency format and decimals to the number.	dollar( <a number> , [])
3	FIND IT	Indicates the position of the search text in the cell. Upper and lower case letters are case sensitive.	find( < search text > , , [] )
4	LEFT	Returns the specified number of characters to the left of the string.	LEFT(, <no characters>))
5	LEN	Counts the number of characters in a string. Fields are also included.	len( <string value> )
6	MID	Extracts text from the middle of a line from a specified position.	mid( <string value> , <start position>, <numeric character>)
7	OWN	Converts the text to the desired capital letter.	actual( <line value>)
8	REPLACEMENT	Replaces part of the text with other text on the line.	replace( <string value>)), <string to find>, <string to replace> )
9	ANSWER:	Repeat the text several times on one line.	rept( <row value> , <a number of times>))
10	RULES	Returns the specified number of characters to the right of the string.	right( <line value>, <number of characters>))
11	SEARCH	The search is not case sensitive.	search( <search text> , <position to start search>) )
12	SUBSCRIBE	Search and replace text. Search and replace is case sensitive.	replace( <string> , <old text> , <new text> , [ ] )
13	TEXT	Converts the value to a text data type with a specified numeric format.	text( <string value> , )
14	TRIM	Removes all spaces before ( master ) and at the end ( trailing ) of a text/line expression.	trim( < string value > )

Examples of using worksheet functions in VBA

Delete the empty rows in the range.

Sub row_del_demo()

Declare variable
Dim range_new

Loop to go through the rows
For i = 1 To 60
‘ Get the range of the rows
Set range_new = Worksheets(Wonders).Rows(i & : & i)

Get the number of empty columns in this row
fill_cols = Application.WorksheetFunction.CountA(range_new)

‘ delete the entire row if all columns are empty
If fill_cols = 0, then
Sheets(Wonders).Rows(i).Delete
End If
Next

Sub-sub final

Use a spreadsheet function to find the sum of integrals in a table.

Under worksh_demo()

Declare variables and objects
Dim a(10) as integer
Dim sum_10 as integer

‘ loop to assign integer values to the 10 elements of the array
For i = 0 To 9
a(i) = i + 10
Debug.Print a(i)
Next

Calculate the sum of all the numbers in the table using worksheet function
sum_10 = Application.WorksheetFunction.Sum(a)

Print sum
Debug.Print sum_10

Sub-sub final

Using the Average and Summary functions in the worksheet

Sub wksfun_Demo()
‘ Declare variables
Dim tot, avg, res, sci, soc, mat, eng, lan

Value assignment
sci = 67
soc = 78
eng = 90
mat = 89
lan = 45

Get sum with worksheet function
to = WorksheetFunction.Sum(sci, soc, eng, lan, mat)
Debug.Print Sum of all scores & Sum of all scores is & total

Get average grades
avg = WorksheetFunction.Average(sci, soc, mat, eng, lan)
Debug.Print Average grades student & avg

Sub-sub final

Spreadsheet function, which uses the data from the Excel spreadsheet as parameter.

Sub wksh_demo()
maximum_value = WorksheetFunction.Max(range(D2:D9))
Debug.Print maximum_value
End Sub

Go away:

528

Supplement

The Worksheetfunction object helps us use the correct format to code our program, rather than using a macro written to code an arbitrary formula. We can use object variables from our procedure or cell values from worksheets/any other Excel workbook as parameters when we use the Worksheet function. The preceding object of application is optional. (Appendix. Worksheet function.)

Post Use any formula: The post VBA worksheet function appeared first on Software Solutions Online.

frequently asked questions

How do you use the worksheet function in VBA?

Blog

Can we use Vlookup in VBA?

vlookup-in-vba-excel

How do you use a variable in an Excel VBA formula?

Forum 86819-vba code…

Related Tags:

using excel formulas in vbaworksheetfunction.sum vbavba worksheetfunction vlookuphow to write excel formula in vba codeapplication.worksheetfunction vbaworksheetfunction.match vba,People also search for,Feedback,using excel formulas in vba,worksheetfunction.sum vba,vba worksheetfunction vlookup,how to write excel formula in vba code,application.worksheetfunction vba,worksheetfunction.match vba,vba worksheetfunction column,excel vba worksheet functions list