Use any type: Worksheet Functions in VBA

April 14, 2021pass through Lakshmi Ramakrishnaexists Excel

content

Formula u EXCEL-u

Microsoft Excel has a lot built infunctions that can be used as typesin an Excel worksheet. It shows the function categories available in the Insert Function window, as shown in the figure below:

Use any type: Worksheet Functions in VBA - VBA and VB.Net Tutorial, Training and Programming Services (1)

Use formulas in Excel worksheets

Just like math formulas help us in simple and quick math calculations, the formulas provided by Ms. Excel help us maintain a good database and formulas that can be automatically calculated and saved for easy use and reference.

An example Excel formula:

Use any type: Worksheet Functions in VBA - VBA and VB.Net Tutorial, Training and Programming Services (2)

after the introduction"I AM" formula.In the formula bar or in any cell (whatever you type in a cell that starts with an equal sign (=) will appear in the formula bar), if we press Enter, click the check mark in the formula bar, or select another cell, we can seeAutomatic calculationanswer.

Use any type: Worksheet Functions in VBA - VBA and VB.Net Tutorial, Training and Programming Services (3)

Checkmarks and crosses in the formula bar are disabled when the formula bar or cell is not in edit mode.

Use formulas as functions in VBA

Excel VBA provides most of these types as built-in functions that can easily be used in our code to achieve the same results. these are calledWorksheet function.

What are worksheet functions?

The MS Excel application object has a method called "WorksheetFunction". It provides access to most of the standard built-in functions available as "forms" in Excel.

application.It can be used immediately to access these functions. However, usingApplication.worksheet function.It can help the developer to use Intellisense to prompt the developer to input the parameters to use to complete the function input.

List of Worksheet Functions - VBA

Listed below are some commonly used worksheet functions.

List of logical functions

serial number	Mode	describe	pension	example	Exit
1	I	Make sure all requirements are met. Possible output results can be TRUE or FALSE.	Worksheet function. And (logical1, logical2)	Worksheet function. And (5>3,4<6)	real
2	I	If the condition is met, do something (second parameter), if not, do something else (third parameter).	IF(,,)	IF(2<6;"2 less","6 less")	"2 now"
3	error	This is a combination of IF and ISERROR.	IF(ISERROR(), 0,)	if(error(2/0),0,2/0)	0
4	the	Check if any of the conditions are met. Possible output results can be TRUE or FALSE.	Worksheet function. if (logical1, logical2)	Worksheet function.If(5>3,14<6)	real

Lists of finder and reference functions

serial number	Mode	describe	pension
1	choose	Selects a value from a list based on index number and the index number specified in the argument.	select (, value 1, value 2)
2	Ask around	Finds the value in the first row of the array and returns the value.	hlookup(,,,)
3	index	Returns a value based on the column and row number.	INDEX (string,, <[column number]>, <[range number]>)
4	look at	Find values horizontally (by row) or vertically (by column).	Search (,,)
5	answer	Searches for a value in a list and returns its position.	RACE (,, <[match type]>)
6	transfer	Change the orientation of a range of cells. Vertical to horizontal and vice versa.	Transport ()
7	VLOOKUP	Looks up the value in the first column of the array and returns the value.	Vlookup(,,,)

date and time functions

Valid date here meansIsDate()RETURNBoolean value"real"

serial number	Mode	describe	pension
1	datum	Returns a date from a combination of year, month, and day.	DATE (year, month, day)
2	date value	Converts a date stored as text to a valid date (in an acceptable data format).	date value ()
3	the	Returns the date from ValidDate as a number (1-31).	dana ()
4	360 dana	Returns the difference in days between 2 valid dates in a 360-day year.	DAYS360(,,
5	processing	Returns the valid date, "n" months from the valid start date.	EDATE(,)
6	Menstruation	Returns the last day of the month, 'n' months from the current date.	month (,)
7	Sat	Returns the time (0-23) from the current time.	Sati ()
8	minute, thin	Returns the minutes since the current time as a number (0-59).	minute, thin ()
9	month	Returns the month from the current date as a number (1-12).	month ()
10	cyber and	Returns the number of business days between 2 valid dates.	network days (,, [])
11	NETWORKDAYS.INTL	Returns the number of business days between 2 valid dates. Weekends are excluded by default and there is an option to add a list of holidays that should be excluded from the count.	networkdays.intl(,, [], [])
12	Now	Returns the current system date and time.	Now()
13	but	Returns the seconds (0-59) of the current time.	Others ()
14	year	Returns the time from a valid combination of hours, minutes, and seconds.	time (,,)
15	time value	Convert time stored as text to valid time.	time value ()
16	working day	Displays the day of the week as an integer (1-7).	working day (, [])
17	week number	Returns the week number (1-52) of the year.	week (, [])
18	working day	Returns a date n business days from the current date.	Day of the week (, Number of days, [])
19	Year	Returns the year since the current date.	year ()
20	YearFRAC	Returns the fraction of a year between 2 valid dates.	yearfrac(, [])

Mechanical operation

serial number	Mode	describe	pension
1	to change	Converts a number or value from one unit to another. For example: centimeters to inches, feet to centimeters, meters to centimeters, kilograms to grams.	convert (,,)

financial function

serial number	Mode	describe	pension
1	FV	Calculates and returns the future value of an investment.	fv(,,, [pv], [])
2	Photovoltaics	Calculates and returns the present value of an investment.	fv(,,, [fv], [])
3	National Economic Institute	Calculates and returns the total number of pay periods for an investment.	For (,, [], [kind])
4	photomultiplier tube	Calculates and returns the payment amount to the investment.	pmt(,,, [], [])
5	speed up	It calculates and returns the interest rate of the investment.	price(,,, [], [], [])
6	net present value	Calculates and returns the net present value of an investment.	npv(,, [], [],….., [])
7	Internal rate of return	Calculates and returns the internal rate of return for a set of CF journals.	irr(, [] )
8	XIRR	Calculates and returns the internal rate of return for a set of aperiodic CFs.	whistling,, [] )
9	price	Calculates and returns the value of the bond.	price(,,,,,, [])
10	antagonist	Returns the interest rate of the fully invested security.	inputs (,,,,, [])

information function

serial number	Mode	describe	pension
1	error	Checks for an error in the cell value, ignoring #N/A. Returns boolean TRUE/FALSE	iserr()
2	error	Checks if the cell value is false. Returns a Boolean TRUE/FALSE value	error()
3	even	Checks if the cell value is even. Returns a Boolean TRUE/FALSE value	seven ()
4	unreasonable	Checks if the cell is logical (TRUE or FALSE). Returns a Boolean TRUE/FALSE value	logically ()
5	ISNA	Checks if the cell value is #N/A. Returns a Boolean TRUE/FALSE value	also)
6	don't send message	Checks if a cell is text (empty cells are not text). Returns a Boolean TRUE/FALSE value	not text()
7	serial number	Checks if the cell is a number. Returns a Boolean TRUE/FALSE value	is a number ()
8	uproot	Checks if the cell value is odd. Returns a Boolean TRUE/FALSE value	clogging ()
9	text	Checks if a cell is text. Returns a Boolean TRUE/FALSE value	istext()
10	suggestion	Returns the type of the value stored in the cell.	items ()

mathematical function

serial number	Mode	describe	pension
1	ABS	Computes and returns the absolute value of a number.	absolute value ()
2	In total	Defines and performs calculations on databases or lists.	TOTAL(,,, [k]>
3	sharpen	Rounds a number to the nearest specified multiple and returns that value.	strop (,)
4	COS	Returns the cosine of an angle.	cosine ()
5	Bachelor of Science	Converts radians to degrees and returns a value.	grades ()
6	DSUM	Summarizes database records that meet certain criteria.	dsum(,,)
7	even	Rounds to the nearest even number and returns a value.	parents ()
8	Experience	Computes the exponential value of the given number and returns that value.	exp()
9	fact	Returns the factorial value.	fact ()
10	tlo	Rounds the number down to the nearest specified multiple.	by,)
11	GCD	Returns the greatest common divisor.	gcd(, [], [],...[])
12	information Office	Rounds a number down to the nearest whole number.	whole number ()
13	LCD unit	Returns the least common multiple.	lcm(, [], [],...[])
14	logical node	Returns the natural logarithm of a number.	LN()
15	log	Returns the logarithm of a number to the specified base.	log(, [])
16	minutes 10	Returns the logarithm of a base 10 number.	log10()
17	krug	Rounds the number to the specified multiple.	mround(,)
18	Strange	Round to the nearest odd number.	Nepal ()
19	pi	The price of Fr.	PI()
20	power	Calculates the power of a number.	power(,)
21	product	Multiply a series of numbers.	product(, [], [],……[])
22	business	Returns the integer result of the division.	quotient (,)
23	radian	Convert degrees to radians.	rays ()
24	between the edges	Computes a random number between two numbers.	edge between (,)
25	krug	Rounds the number to the specified number of digits (specified in the argument).	krug (,)
26	on	Rounds a number down (towards zero).	ROUND DOWN (,)
27	busy if	Rounds a number up (from zero).	BUSY IF(,)
28	crime	Returns the sine of the angle.	sin ()
29	Subtotal	Returns a statistical summary of a data series.	SUBTOTAL (,[],[],…..])
30	I	Adds the numbers and returns the total.	sum (, [], [],...[])
31	Sumif	Sums the numbers that match the condition.	sumif(,, [])
32	SUMIFS	Adds numbers that meet multiple conditions.	soumifi (,,, [,],[,],…[,])
33	sum of products	Multiplies sequences of numbers and adds the resulting sequences.	cumulative product (, [], [], [],….[])
34	tanned skin	Returns the tangent of an angle.	tan()

statistical function

serial number	Mode	describe	pension
1	Average	Average of given numbers.	average(, [], [],……[])
2	Average AN	Calculates the average of the given numbers that meet the criteria.	projectif(,, [])
3	average value	Calculates the average of a given number that satisfies multiple conditions.	project code (,,, [,],[,],….[,])
4	fellow	Computes and returns the correlation of two strings.	correlation(,)
5	count	Counts and returns the number of cells that contain a specified value.	number(, [], [], []….[])
6	count	Counts and returns the number of cells that are not empty.	COUNTA(, [], [], []….[])
7	they count empty	Counts and returns the number of empty cells.	measurement gap ()
8	COUNTIF	Counts and returns the number of cells that satisfy a condition.	countif(,)
9	count	Counts and returns the number of cells that meet multiple criteria.	countifs (,, [,],[,],….[,])
10	forecast	Predicting future y values from a linear trend line.	forecast(,,)
11	frequency	Calculates a value within a specified range.	frequency(,)
12	to find	Calculates Y values based on exponential growth.	increase ([],[], [const])
13	restrain	Calculates the Y-intercept of the "best fit" line.	wiretapping (,)
14	large	Returns the kth largest value.	a large(, K)
15	line	Returns trendline statistics.	line (, [], [] , [] )
16	upper limit	Returns the largest number in the given array.	Maximum(, [], [],...[])
17	the median	Returns the median of the given array.	median (, [], [],...[])
18	minimum price	Returns the smallest number in the given array.	min(, [], [],...[])
19	model	Returns the most common number in the given sequence.	mode (, [], [],……[])
20	rate	Returns the kth percentile.	percentile (,)
21	rate	Displays the kth percentile, up to and including k.	percentil.inc(,)
22	Percentage.EXC	Returns the kth percentile, where k is unique.	percentil.exc(,)
23	quadrant	Returns the specified quartile value.	quadrant (,)
24	quarter of the company	Returns the specified quartile value (inclusive).	kvartil.inc(,)
25	quadrant.EXC	Returns the specified quartile value (exclusive).	quartile.exc(,)
26	knocked	Returns the rank of the numbers in the sequence.	knocked (,, [])
27	Ranking M.S	Returns the rank of the numbers in the sequence. (Average value).	ranking.prosj. (,, [])
28	level equalizer	Returns the rank of the numbers in the sequence. (wickedness).	rank.eq (,, [])
29	prejudice	Calculates and returns the value of the slope of a linear regression.	prejudice(,)
30	Yes	Returns the smallest value in an array of numbers.	Yes (,)
31	standard deviation	Calculates and returns the standard deviation.	stdev(, [], [],...[])
32	standard development process	Calculates and returns the standard deviation for the entire population.	stdev.p(, [], [],...[])
33	standard development tools	Calculates and returns the sample standard deviation.	stdev.s(, [], [],...[])
34	Vice President of Standards Development	Calculates and returns the standard deviation for the entire population	stdevp(, [], [],...[])
35	trend	Calculates and returns the Y value from the trend line.	trend(, [], [], [art])

String (or) text functions

serial number	Mode	describe	pension
1	clean	Remove any non-printable characters.	CLEAN ()
2	Dollar	Add currency and decimal formatting to numbers.	USD(,[])
3	I am looking	Find the location of the search text in the cell. case sensitive.	Finding (,, [] )
4	left	Returns the specified number of characters from the left side of the string	LEFT(,)
5	Lun	Counts the number of characters in a string. Spaces are also included.	len()
6	the middle	Extracts the text from the middle of the string at the specified position.	in the middle (,,)
7	suitable	Convert text to fix uppercase and lowercase.	corresponding ()
8	replace	Replaces a piece of text with other text in a string.	replace (),,)
9	answer	Repeat text several times in a row.	rept(,)
10	rectify	Returns the specified number of characters from the right side of the string	correctly (,)
11	looking for	Place the text inside the table. This search is not case sensitive.	Search (,)
12	replace	Find and replace text. This search and replace is case sensitive.	replace (,,, [])
13	text	Converts a value to a text data type with a specified number format.	text(,)
14	circumcise	Removes all whitespace before (at the start) and at the end (at the end) of a text/string expression.	arrangement ()

Examples of using worksheet functions in VBA

Delete empty rows in a range

Sub row_del_demo()' declare variableDim range_new' Loop through rowsFor i = 1 To 60 ' Get the range of rows Define range_new = Worksheets("Wonders"). Rows(i & ":" & i) ' Find Count the number of empty columns in the range fill_cols = Application.WorksheetFunction.CountA(range_new) ' If all columns in it are empty, delete the entire row If fill_cols = 0 Then Sheets ("Wonders"). Rows(i). Delete End IfNextEnd Sub

Use any type: Worksheet Functions in VBA - VBA and VB.Net Tutorial, Training and Programming Services (4)

Add integers to a sequence using worksheet functions

Subworksh_demo()' declare variables and objectsDim a(10) As IntegerDim sum_10 As Integer Loop to assign integer values to all 10 array elements For i = 0 Do 9a(i) = i + 10 Debug. Print a(i) Next ' Use sheetfunctionsum_10 = Application.WorksheetFunction.Sum(a)' Print sumDebug.Print sum_10End Subnumerical sum to complex

Use the worksheet average and sum functions

Sub wksfun_Demo() ' Declare variables Dim tot, avg, res, sci, soc, mat, eng, lan ' Assign values sci = 67 soc = 78 eng = 90 mat = 89 lan = 45 ' Use the worksheet function to find the total sum tot = WorksheetFunction .Sum(sci, soc, eng, lan, mat) Debug.Print "The sum of all scores is " & tot 'Average score m.g = WorksheetFunction.Average(sci, soc, mat, eng, lan ) Debugging. Print "Student's average is" & final sub average

Use any type: Worksheet Functions in VBA - VBA and VB.Net Tutorial, Training and Programming Services (5)

Worksheet functions that use Excel worksheet data as arguments

Use any type: Worksheet Functions in VBA - VBA and VB.Net Tutorial, Training and Programming Services (6)

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

Exit:

528

in conclusion

The worksheet function object helps us write programs using the appropriate format when it suits us instead of using inline macros to write formulas. When using worksheet functions, we can use object variables in a procedure or cell values in a worksheet/any other Excel workbook as parameters. The preceding application object is optional. (Application worksheet function.)

Marked as: Excel,Excel formula,officially,Microsoft,Microsoft Excel,VBA,VBA for Excel,work sheet,worksheet function,work sheet

Use any type: Worksheet Functions in VBA - VBA and VB.Net Tutorial, Training and Programming Services (2023)