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

Use any type: Worksheet Functions in VBA

pass through Lakshmi Ramakrishnaexists Excel

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 numberModedescribepensionexampleExit
1IMake 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
2IIf 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"
3errorThis is a combination of IF and ISERROR.IF(ISERROR(), 0,)if(error(2/0),0,2/0)0
4theCheck 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 numberModedescribepension
1chooseSelects a value from a list based on index number and the index number specified in the (, value 1, value 2)
2Ask aroundFinds the value in the first row of the array and returns the value.hlookup(,,,)
3indexReturns a value based on the column and row number.INDEX (string,, <[column number]>, <[range number]>)
4look atFind values ​​horizontally (by row) or vertically (by column).Search (,,)
5answerSearches for a value in a list and returns its position.RACE (,, <[match type]>)
6transferChange the orientation of a range of cells. Vertical to horizontal and vice versa.Transport ()
7VLOOKUPLooks 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 numberModedescribepension
1datumReturns a date from a combination of year, month, and day.DATE (year, month, day)
2date valueConverts a date stored as text to a valid date (in an acceptable data format).date value ()
3theReturns the date from ValidDate as a number (1-31).dana ()
4360 danaReturns the difference in days between 2 valid dates in a 360-day year.DAYS360(,,
5processingReturns the valid date, "n" months from the valid start date.EDATE(,)
6MenstruationReturns the last day of the month, 'n' months from the current date.month (,)
7SatReturns the time (0-23) from the current time.Sati ()
8minute, thinReturns the minutes since the current time as a number (0-59).minute, thin ()
9monthReturns the month from the current date as a number (1-12).month ()
10cyber andReturns the number of business days between 2 valid days (,, [])
11NETWORKDAYS.INTLReturns 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(,, [], [])
12NowReturns the current system date and time.Now()
13butReturns the seconds (0-59) of the current time.Others ()
14yearReturns the time from a valid combination of hours, minutes, and seconds.time (,,)
15time valueConvert time stored as text to valid time.time value ()
16working dayDisplays the day of the week as an integer (1-7).working day (, [])
17week numberReturns the week number (1-52) of the year.week (, [])
18working dayReturns a date n business days from the current date.Day of the week (, Number of days, [])
19YearReturns the year since the current date.year ()
20YearFRACReturns the fraction of a year between 2 valid dates.yearfrac(, [])

Mechanical operation

serial numberModedescribepension
1to changeConverts 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 numberModedescribepension
1FVCalculates and returns the future value of an investment.fv(,,, [pv], [])
2PhotovoltaicsCalculates and returns the present value of an investment.fv(,,, [fv], [])
3National Economic InstituteCalculates and returns the total number of pay periods for an investment.For (,, [], [kind])
4photomultiplier tubeCalculates and returns the payment amount to the investment.pmt(,,, [], [])
5speed upIt calculates and returns the interest rate of the investment.price(,,, [], [], [])
6net present valueCalculates and returns the net present value of an investment.npv(,, [], [],….., [])
7Internal rate of returnCalculates and returns the internal rate of return for a set of CF journals.irr(, [] )
8XIRRCalculates and returns the internal rate of return for a set of aperiodic CFs.whistling,, [] )
9priceCalculates and returns the value of the bond.price(,,,,,, [])
10antagonistReturns the interest rate of the fully invested security.inputs (,,,,, [])

information function

serial numberModedescribepension
1errorChecks for an error in the cell value, ignoring #N/A. Returns boolean TRUE/FALSEiserr()
2errorChecks if the cell value is false. Returns a Boolean TRUE/FALSE valueerror()
3evenChecks if the cell value is even. Returns a Boolean TRUE/FALSE valueseven ()
4unreasonableChecks if the cell is logical (TRUE or FALSE). Returns a Boolean TRUE/FALSE valuelogically ()
5ISNAChecks if the cell value is #N/A. Returns a Boolean TRUE/FALSE valuealso)
6don't send messageChecks if a cell is text (empty cells are not text). Returns a Boolean TRUE/FALSE valuenot text()
7serial numberChecks if the cell is a number. Returns a Boolean TRUE/FALSE valueis a number ()
8uprootChecks if the cell value is odd. Returns a Boolean TRUE/FALSE valueclogging ()
9textChecks if a cell is text. Returns a Boolean TRUE/FALSE valueistext()
10suggestionReturns the type of the value stored in the cell.items ()

mathematical function

serial numberModedescribepension
1ABSComputes and returns the absolute value of a number.absolute value ()
2In totalDefines and performs calculations on databases or lists.TOTAL(,,, [k]>
3sharpenRounds a number to the nearest specified multiple and returns that value.strop (,)
4COSReturns the cosine of an angle.cosine ()
5Bachelor of ScienceConverts radians to degrees and returns a value.grades ()
6DSUMSummarizes database records that meet certain criteria.dsum(,,)
7evenRounds to the nearest even number and returns a value.parents ()
8ExperienceComputes the exponential value of the given number and returns that value.exp()
9factReturns the factorial value.fact ()
10tloRounds the number down to the nearest specified,)
11GCDReturns the greatest common divisor.gcd(, [], [],...[])
12information OfficeRounds a number down to the nearest whole number.whole number ()
13LCD unitReturns the least common multiple.lcm(, [], [],...[])
14logical nodeReturns the natural logarithm of a number.LN()
15logReturns the logarithm of a number to the specified base.log(, [])
16minutes 10Returns the logarithm of a base 10 number.log10()
17krugRounds the number to the specified multiple.mround(,)
18StrangeRound to the nearest odd number.Nepal ()
19piThe price of Fr.PI()
20powerCalculates the power of a number.power(,)
21productMultiply a series of numbers.product(, [], [],……[])
22businessReturns the integer result of the division.quotient (,)
23radianConvert degrees to radians.rays ()
24between the edgesComputes a random number between two numbers.edge between (,)
25krugRounds the number to the specified number of digits (specified in the argument).krug (,)
26onRounds a number down (towards zero).ROUND DOWN (,)
27busy ifRounds a number up (from zero).BUSY IF(,)
28crimeReturns the sine of the angle.sin ()
29SubtotalReturns a statistical summary of a data series.SUBTOTAL (,[],[],…..])
30IAdds the numbers and returns the total.sum (, [], [],...[])
31SumifSums the numbers that match the condition.sumif(,, [])
32SUMIFSAdds numbers that meet multiple conditions.soumifi (,,, [,],[,],…[,])
33sum of productsMultiplies sequences of numbers and adds the resulting sequences.cumulative product (, [], [], [],….[])
34tanned skinReturns the tangent of an angle.tan()

statistical function

serial numberModedescribepension
1AverageAverage of given numbers.average(, [], [],……[])
2Average ANCalculates the average of the given numbers that meet the criteria.projectif(,, [])
3average valueCalculates the average of a given number that satisfies multiple conditions.project code (,,, [,],[,],….[,])
4fellowComputes and returns the correlation of two strings.correlation(,)
5countCounts and returns the number of cells that contain a specified value.number(, [], [], []….[])
6countCounts and returns the number of cells that are not empty.COUNTA(, [], [], []….[])
7they count emptyCounts and returns the number of empty cells.measurement gap ()
8COUNTIFCounts and returns the number of cells that satisfy a condition.countif(,)
9countCounts and returns the number of cells that meet multiple criteria.countifs (,, [,],[,],….[,])
10forecastPredicting future y values ​​from a linear trend line.forecast(,,)
11frequencyCalculates a value within a specified range.frequency(,)
12to findCalculates Y values ​​based on exponential growth.increase ([],[], [const])
13restrainCalculates the Y-intercept of the "best fit" line.wiretapping (,)
14largeReturns the kth largest value.a large(, K)
15lineReturns trendline statistics.line (, [], [] , [] )
16upper limitReturns the largest number in the given array.Maximum(, [], [],...[])
17the medianReturns the median of the given array.median (, [], [],...[])
18minimum priceReturns the smallest number in the given array.min(, [], [],...[])
19modelReturns the most common number in the given sequence.mode (, [], [],……[])
20rateReturns the kth percentile.percentile (,)
21rateDisplays the kth percentile, up to and including,)
22Percentage.EXCReturns the kth percentile, where k is unique.percentil.exc(,)
23quadrantReturns the specified quartile value.quadrant (,)
24quarter of the companyReturns the specified quartile value (inclusive),)
25quadrant.EXCReturns the specified quartile value (exclusive).quartile.exc(,)
26knockedReturns the rank of the numbers in the sequence.knocked (,, [])
27Ranking M.SReturns the rank of the numbers in the sequence. (Average value).ranking.prosj. (,, [])
28level equalizerReturns the rank of the numbers in the sequence. (wickedness).rank.eq (,, [])
29prejudiceCalculates and returns the value of the slope of a linear regression.prejudice(,)
30YesReturns the smallest value in an array of numbers.Yes (,)
31standard deviationCalculates and returns the standard deviation.stdev(, [], [],...[])
32standard development processCalculates and returns the standard deviation for the entire population.stdev.p(, [], [],...[])
33standard development toolsCalculates and returns the sample standard deviation.stdev.s(, [], [],...[])
34Vice President of Standards DevelopmentCalculates and returns the standard deviation for the entire populationstdevp(, [], [],...[])
35trendCalculates and returns the Y value from the trend line.trend(, [], [], [art])

String (or) text functions

serial numberModedescribepension
1cleanRemove any non-printable characters.CLEAN ()
2DollarAdd currency and decimal formatting to numbers.USD(,[])
3I am lookingFind the location of the search text in the cell. case sensitive.Finding (,, [] )
4leftReturns the specified number of characters from the left side of the stringLEFT(,)
5LunCounts the number of characters in a string. Spaces are also included.len()
6the middleExtracts the text from the middle of the string at the specified the middle (,,)
7suitableConvert text to fix uppercase and lowercase.corresponding ()
8replaceReplaces a piece of text with other text in a string.replace (),,)
9answerRepeat text several times in a row.rept(,)
10rectifyReturns the specified number of characters from the right side of the stringcorrectly (,)
11looking forPlace the text inside the table. This search is not case sensitive.Search (,)
12replaceFind and replace text. This search and replace is case sensitive.replace (,,, [])
13textConverts a value to a text data type with a specified number format.text(,)
14circumciseRemoves 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



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


Top Articles
Latest Posts
Article information

Author: Van Hayes

Last Updated: 06/01/2023

Views: 5237

Rating: 4.6 / 5 (46 voted)

Reviews: 85% of readers found this page helpful

Author information

Name: Van Hayes

Birthday: 1994-06-07

Address: 2004 Kling Rapid, New Destiny, MT 64658-2367

Phone: +512425013758

Job: National Farming Director

Hobby: Reading, Polo, Genealogy, amateur radio, Scouting, Stand-up comedy, Cryptography

Introduction: My name is Van Hayes, I am a thankful, friendly, smiling, calm, powerful, fine, enthusiastic person who loves writing and wants to share my knowledge and understanding with you.