Trending February 2024 # Large Function In Excel (Formula, Examples) # Suggested March 2024 # Top 6 Popular

You are reading the article Large Function In Excel (Formula, Examples) updated in February 2024 on the website Katfastfood.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested March 2024 Large Function In Excel (Formula, Examples)

LARGE Function in Excel

A large function in excel is an inbuilt statistical function that returns the nth position or Kth position from the selected numerical array. If the Kth position is greater or larger than the values, there is an array, or if we keep the Kth position blank, it will return #Num! As error. This means while putting the Kth value in the syntax, we need to put the value which is the lowest number from the selected array or any lowest number, but it should not be in an array.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

This function can sort the information provided and find the maximum value.

LARGE Formula in Excel

Below is the LARGE Formula:

Array–the array or range of data from which we want to select the largest value.

K – An integer value specifies the position from the largest value.

As the LARGE function falls under the Statistical function category, and it is a built-in function in Excel, i.e. this function can be found under the FORMULAS tab in Excel. The steps are as follows:

Select the Statistical functions category. It will open the drop-down list of functions.

After selecting the LARGE function, a Function arguments box will pop up.

Enter the Array field with an array or range of data you want to find the nth largest value.

Enter the K field; it is the position from the largest value in the array of the value to return.

How to use the LARGE Function in Excel?

LARGE Function is very simple to use. Let us now see how to use a LARGE function with the help of some examples.

You can download this LARGE Function Excel Template here – LARGE Function Excel Template

Example #1

Suppose we have company employee data, and the company distributes incentives to their employees based on their performance. We need to find out the name of the employees who are in the top 3 on the list and received the highest incentives.

To find the employee name who has achieved the largest incentive, will use the below formula:

=LARGE(C4: C13,1)

The Result is:

To find the employee name who has achieved the second-largest incentive, will use the below formula:

=LARGE(C4: C13,2)

To find out the employee name who has achieved the 3rd largest incentive, will use the below formula:

=LARGE(C4: C13,3)

The Result is:

The final results are:

Example #2

Let’s consider the below example with some values.

We need to arrange the above data in the order of Largest to smallest. With the help of the LARGE function, we can do this very easily.

=LARGE(A25:A31,1)

Similarly, we find other values

Example #3

Let’s assume Sales data is given, and we want to see the total sales from the top 5 performers.

We want to see the sales done by the top 5 sales employee:

We will apply the LARGE function to select the top 5 sales performers by passing the positions from 1 to 5 as an array as a second argument (k) position and summing those values.

The formula used for solving this problem is:

=SUM (LARGE(B37:B51,{1,2,3,4,5}))

 The final result is:

Things to Remember

If the LARGE function returns the error value #NUM!, it means

The second argument value (k) is less than 1 or greater than the number of values in the given array.

The given array is empty.

Suppose the function returns the error value #VALUE! – means the second argument (k) is non-numeric.

The LARGE function is used to sort the data.

This has been a guide to Excel LARGE Function. Here we discuss the LARGE Formula and how to use the LARGE function, along with practical examples and a downloadable Excel template. You can also go through our other suggested articles –

You're reading Large Function In Excel (Formula, Examples)

Correl In Excel (Formula, Examples)

CORREL in Excel

The correl function in Excel is used for calculating the Correlation Coefficient, whose value ranges from -1 to +1 only, and it also shows how strongly any 2 values are related. The range for the correlation coefficient is only -1 to +1, which is quite small, and the value falling under this range will be less compared to any other number. As per the syntax, we just need to select the 2 arrays of numbers for which we need to find the Correlation Coefficient.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Correlation coefficient

It’s a statistical measure of how strong a relationship is between two variables, i.e., for a positively correlated variable, the Correlation coefficient value increases. In contrast, for a negatively correlated variable, the Correlation coefficient value decreases.

Correlation coefficients express values between +1 and -1.

I have two variables (one plotted on the X-axis, one on the Y-axis)

If the value is 1, then it means a strong positive correlation. In this case, y increases when x increases (Positive linear relationship)

If the value is 0: means that there is no relationship between the two variables (x and y)

If the value is -1, then it means a negative correlation: In this case, y decreases when x increases (Negative linear relationship)

Definition

CORREL Formula in Excel

Below is the CORREL Formula:

Where

Array1– It is an independent variable. It is entered as a cell reference or range of values.

Array2– It is a dependent variable. It is entered as a second cell reference or range of values.

Correlation coefficients are expressed as values between +1 and -1.

A coefficient of zero indicates, No discernable relationship between fluctuations of the variables.

It is most commonly used to Calculate the correlation coefficient for two sets of values or variables, i.e., the Correlation between a particular stock or share price and the market index value.

To Calculate the correlation coefficient for refrigerators & air conditioner sales in the summer & winter seasons and Car models, their year of launch & price difference.

Excel Correl function is similar to Pearson Function

CORREL function is used as a worksheet function & also in Excel VBA.

CORREL function in Excel is easy to use & is a very simple function with few arguments

How to use CORREL Function in Excel?

CORREL Function is very simple to use. Let us now see how to use the CORREL function in Excel with the help of some examples.

You can download this CORREL Function Excel Template here – CORREL Function Excel Template

Example #1

For a Set of Positive Variables or Dataset

With the help of the Correl function, I need to find the correlation coefficient between two datasets or variables.

In the below-mentioned example, the table contains two variables, one in column X & the other in column Y. where both the datasets contain positive values.

Let’s apply the Correl function in cell “C14”. Select the cell “C14,” where the Correl function needs to be applied.

A dialog box appears where arguments for the CORREL function need to be filled or entered, i.e., =CORREL(array1, array2)

i.e. =CORREL(B8:B12,C8:C12) will appear in cell C14

i.e. =CORREL(B8:B12,C8:C12) returns 0.988104929 as the result.  The Correlation coefficient between the two datasets or variables is 0.98

To use a line chart for graphical representation, choose the chart option for “line chart”. It will allow you to represent data using a line chart visually.

I have two variables, X & Y, where one is plotted on the X-axis and the other one on the Y-axis.

Select the table range excluding header X & Y, i.e., B8 TO C12

It will result in a chart,

Chart elements such as legend series (X, Y) axis title (X & Y axis), chart title (POSITIVE CORRELATION) & data label (Values) need to be updated in the chart.

You can see a strong positive correlation, i.e., Variables X & Y values are positively correlated (Positive linear relationship)

Example #2

For a Dataset Containing Positive & Negative Values

With the help of the Correl function, I need to find out the correlation coefficient between two datasets or variables

In the below-mentioned example, I have two variables, one in column x & the other in column Y. where column X datasets contain positive values & column Y datasets contain negative values

Let’s apply the Correl function in cell “C29”. Select the cell “C29,” where the Correl function needs to be applied.

A dialog box appears where arguments for the CORREL function need to be filled or entered, i.e., =CORREL(array1, array2)

i.e. =CORREL(B23:B27,C23:C27) will appear in the cell C29

i.e. =CORREL(B23:B27,C23:C27) returns -0.988104929 as the result.  The Correlation coefficient between two datasets or variables is -0.98

Similar to the above example, it graphically represents using a line chart under chart options

I have two variables, X & Y, where one plot on the X-axis, the other one on the Y-axis

You can see the negative correlation, i.e., Variables X & Y values are negatively correlated (Negative linear relationship). In this case, y decreases when x increases.

Things to Remember

Suppose Array1 and Array2 have a different number of data points or if the supplied arrays are of different lengths. CORREL results in or returns the #N/A error value.

The function will return the #DIV/0! Error value if the argument array1 or array2 contains non-numeric data (text, logical values, or blank cells).

It’s an inbuild Analysis Toolpak Add-in which is present in application add-ins.

Recommended Articles

Although this has been a guide to the Excel CORREL function. Therefore, here we discuss the CORREL Formula and how to use the CORREL function in Excel, along with practical examples and a downloadable Excel template. Thus, you can also go through our other suggested articles –

Networkdays In Excel (Formula, Examples)

NETWORKDAYS in Excel

Networkdays function simply returns the number of working days between two dates and also considers the holidays if there are any (Optional). By this, we get to know the number of working days as well, and also, it is quite helpful for counting the number of working days spent on any activity or project to have an idea track or can be used for any other purpose.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Uses of NETWORKDAYS Function in Excel

The NETWORKDAYS function calculates the working days between two dates in Excel. This function automatically excludes weekends (Saturday and Sunday) and holidays. This function also excludes the specified holidays from the working day calculation.

NETWORKDAYS Formula in Excel:

Below is the NETWORKDAYS Formula in Excel :

Where the supplied arguments are as follows:

Start_date – The start_date from where you want to start calculating work days.

End_date – The end_date up to which you want to calculate working days.

Holidays – This is an optional argument. We want to exclude the list of holidays (one or more dates) from the working day’s calculation. This list can be entered as a cell, a range of cells, or an array containing dates.

As a worksheet function, the NETWORKDAYS Function can be entered in Excel as per the below screenshot:

The NETWORKDAYS function is a built-in function in Excel; thus, it can be found under the FORMULAS tab. Please follow the below steps:

Select the Date & Time functions category.

It will open up a drop-down list of functions. Select the NETWORKDAYS Functions from the drop-down list.

It will open a dialog box of Function arguments.

Fill in the start_date field; it can be entered as serial data, which calculates the working days.

Fill the end_date field; it can be entered as the serial date and used in the calculation.

Enter the holiday field, which is an optional argument. It is the set of one or more dates that we want to exclude from the working day’s calculation.

How to Use the NETWORKDAYS Function in Excel?

You can download this NETWORKDAYS Function Excel Template here – NETWORKDAYS Function Excel Template

Example #1

Let’s take some dates as start date and end date.

Below dates are the holidays in between:

Now for calculating the number of working days, the start date, end date, and holidays date have been entered as serial dates.

The result will be :

Drag & drop this formula for the rest values, and the final result is as shown below:

Example #2

It depends on the solution, that does we want to exclude only weekends or do we want to also exclude the holiday dates too from the working day’s calculation.

We have given the below dates, and we will do both types of calculations:

Apply NETWORKDAYS Function to the cell E26.

The Result will be :

The final result is shown below:

Things to Remember

NETWORKDAYS Function automatically excludes both Saturday and Sunday (takes both as weekends). If you want to use different days, like weekends, you need to use a different function for this, i.e. chúng tôi function.

If you pass a range of cells for the holidays, make the range an absolute value. So that the range will not change if you drag this formula to other cells.

The function returns the #VALUE! error value if any date passed as an argument is invalid.

If the start date is later than the End Date, the NETWORKDAYS Function will return a Negative value.

Recommended Articles

This has been a guide to NETWORKDAYS Function in Excel. Here we discuss the NETWORKDAYS Formula in Excel and How to Use NETWORKDAYS Function in Excel, along with practical examples and a downloadable Excel template. You can also go through our other suggested articles –

Vlookup In Excel (Formula, Examples)

VLOOKUP in Excel (Table of contents)

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Introduction to VLOOKUP Function in Excel

VLOOKUP in Excel is used to lookup the value with a reference cell and fetch the value from the selected lookup table array and is quite useful and one of the most widely used excel functions We can use a table or single column to lookup the value. And all the lookup can be done in a vertical zone or with columns only.

The vlookup function uses of the arguments:-

There are four arguments in vlookup function which is below mention:

Lookup value (required argument) – it is the value that we want to look up for in the column of a table. Where you want or get the value from another table.

Table array (required argument) – it is the data array that is to be searched. The vlookup function searches in the left-most column of this array. We can say that this is a matching table.

Column index number (required argument) – an integer, specifying the column number of the supplied table array, that you want to return a value from. If you only using it for data matching then you can put 1 but if you want to get a value from another column behave on matching the lookup value then you need to put the column no from matching column no.

Range lookup (optional argument) – it defines what this function should return in the event that it does not find an exact match to the lookup value. The argument can be set to true or false, which means:

True – approximate match, that is, if an exact match is not found, use the closest match below the lookup value.

False – exact match, that is, if an exact match not found then it will return an error.

We can also use 0 for false and 1 for true matching.

Steps for Using VLOOKUP Function

We get a new function window showing in the below mention pictures.

Then we have to enter the details as shown in the picture.

Put the lookup value where you want to match from one table to another table value.

You need to put the table array which is another table value.

Put the col index number for another table vertical value which is a need.

Rage lookup false for exact match and true for an approximate match.

You can also use 0 for an exact match and 1 for an approximate match.

Shortcut for using the Formula

Explanation for VLOOKUP Function:

We can also use one sheet to another sheet and one workbook to another workbook also

How to Use VLOOKUP in Excel?

Vlookup function is very simple and easy to use. Let us understand the working of vlookup. Below mention are the details using the formulas.

You can download this VLOOKUP Function Template here – VLOOKUP Function Template

Example #1 – Exact Match

To search for an exact match, you put false in the last argument.

As above mention in table b there is all information of the employee like department, employee id, address mobile no, etc. You can suppose as array table or master table data. And you have another table where required only contact no of the employee. So as the employee name is a unique column then the employee name a lookup value in a table where you want to get a result.

Master data table b as above showing is a table array, and in master data, you can see that mobile no column is on the 5 number column index. Then we need to put the 0 for exact matching or 1 for false matching.

You can see the result here:

Formula: – “=vlookup(a21,a1:e12,5,0)”

We can see that in a table all values are matching the exact value.

Example #2 – Approximate Match

As above showing in the picture in the H column, there is employee age mention and column I employee name. You can take this as an array or master table. Now I want to put the age value in k2 then we get the employee name which is approximate age as given in k2 age value as showing below mention pictures.

So lets we check the formula now.

“=vlookup(k2,h2:i12,2,true)”

Then we can get the approximate matching value.

As you see, the formula returns Mr. Puneet Sharma whose age is 43, while we also have Mr. Manish patial that age 48 but 43 is much closer to 44 than 48. So, why does it return Mr. Puneet? Because vlookup with approximate match retrieves the closest value that is less than the lookup value.

Things to Remember

The vlookup function returns result in any data type such as a string, numeric, date, etc.

If you specify false for the approximate match parameter and no exact match is found, then the vlookup function will return #n/a.

If you specify true for the approximate match parameter and no exact match is found, then the next smaller value is returned.

If the index number is greater than the number of columns in the table, the vlookup function will return #ref!

Recommended Articles

This has been a guide to VLOOKUP Function. Here we discuss the VLOOKUP Formula and how to use the VLOOKUP function along with an excel example and downloadable excel templates. You may also look at these useful functions in excel-

How To Use Excel Vba Sleep Function With Examples?

VBA Sleep Function

The sleep function in VBA is a Windows function. It is similar to the wait function in VBA. It is used to slow down or pause or we can say halt the running of a specific code by some specified time. Sleep function needs to be called in VBA while declaring it in the code. How we do that is what we will learn in today’s topic.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

As explained above, VBA Sleep is a Windows function and is present in the kernel database of Windows. The method of declaring and calling sleep functions in VBA differs for both 32-bit and 64-bit operating systems. It is a Windows API function.

The syntax to use the VBA Sleep function is as follows:

Sleep (Time in Mili Seconds)

So if we need to slow down or halt the code for 1 sec, we need to write the code as:

Sleep 1000

1000 is the mili seconds equal to 1 second and will slow down the code for 1 sec. If we want to slow down the code for 5 seconds, the code will be:

Sleep 5000

The declaration for sleep function is as follows:

#If VBA7 Then ' Excel 2010 or later Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As LongPtr) #Else ' Excel 2007 or earlier Public Declare Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long) #End If

Note: Before using the sleep function, there is one thing we need to keep in mind. We need to use this function in modules, not in the objects of Excel. To use VBA to ensure that the developer’s tab is turned on from the files tab in the options section.

How to Use Excel VBA Sleep Function?

We will learn how to use a VBA Sleep function with a few examples in Excel.

You can download this VBA Sleep Excel Template here – VBA Sleep Excel Template

VBA Sleep Function – Example #1

What we are going to do in this example is we will pop up a message to the user that the macro will stop for five seconds. And exactly after five seconds, we want a second message to pop up which says macro resumed.

Follow the below steps to use Sleep Function in Excel VBA:

Step 3: Use the declaration statement to use the sleep function. As I am using Windows 64-bit operating system, I will use the declaration statement for the same.

Code:

Public Declare PtrSafe Sub

Sleep

Lib

"kernel32" (

ByVal

dwMilliseconds

As LongPtr

)

Step 4: Now declare the sub-function to start writing the code.

Code:

Public Declare PtrSafe Sub

Sleep

Lib

"kernel32" (

ByVal

dwMilliseconds

As LongPtr

)

Sub

Sample()

End Sub

Step 5: Use the Mgsbox function to display the message that the macro will be paused for five seconds.

Code:

Public Declare PtrSafe Sub

Sleep

Lib

"kernel32" (

ByVal

dwMilliseconds

As LongPtr

)

Sub

Sample() MsgBox "MAcro going to be paused for five seconds"

End Sub

Step 6: Use the Sleep function to pause the macro for five seconds.

Code:

Public Declare PtrSafe Sub

Sleep

Lib

"kernel32" (

ByVal

dwMilliseconds

As LongPtr

)

Sub

Sample() MsgBox "MAcro going to be paused for five seconds" Sleep 5000

End Sub

Step 7: Now, use the msgbox function to display the message that the macro has been resumed.

Code:

Public Declare PtrSafe Sub

Sleep

Lib

"kernel32" (

ByVal

dwMilliseconds

As LongPtr

)

Sub

Sample() MsgBox "MAcro going to be paused for five seconds" Sleep 5000 MsgBox "Macro has been resumed"

End Sub

Step 8: Run the code from the run button provided or press F5 to see the result. We see the first message is displayed.

There was a pause for five seconds between both messages.

VBA Sleep Function – Example #2

Now what we are going to do in another example is that I have four variables A, B, C, and D. First, I want to add the value of A and B and display it, and after 5 seconds, I want to display the value of the addition of A, B, C, and D.

Follow the below steps to use Sleep Function in Excel VBA:

Step 3: Now, use the declaration statement to use the sleep function. As I am using Windows 64-bit operating system, I will use the declaration statement for the same.

Code:

Public Declare PtrSafe Sub

Sleep

Lib

"kernel32" (

ByVal

dwMilliseconds

As LongPtr

)

Step 4: Now declare the sub-function to start writing the code.

Code:

Public Declare PtrSafe Sub

Sleep

Lib

"kernel32" (

ByVal

dwMilliseconds

As LongPtr

)

Sub

Sample1()

End Sub

Step 5: Declare six variables A, B, C, D, X, and Y to store values.

Public Declare PtrSafe Sub

Sleep

Lib

"kernel32" (

ByVal

dwMilliseconds

As LongPtr

)

Sub

Sample1()

Dim

A, B, C, D, X, Y

As Integer

End Sub

Step 6: Give Random Values to A, B, C, and D.

Code:

Public Declare PtrSafe Sub

Sleep

Lib

"kernel32" (

ByVal

dwMilliseconds

As LongPtr

)

Sub

Sample1()

Dim

A, B, C, D, X, Y

As Integer

A = 10 B = 15 C = 20 D = 25

End Sub

Step 7: Store the value of A + B in X.

Code:

Public Declare PtrSafe Sub

Sleep

Lib

"kernel32" (

ByVal

dwMilliseconds

As LongPtr

)

Sub

Sample1()

Dim

A, B, C, D, X, Y

As Integer

A = 10 B = 15 C = 20 D = 25 X = A + B

End Sub

Step 8: Display the value of X.

Code:

Public Declare PtrSafe Sub

Sleep

Lib

"kernel32" (

ByVal

dwMilliseconds

As LongPtr

)

Sub

Sample1()

Dim

A, B, C, D, X, Y

As Integer

A = 10 B = 15 C = 20 D = 25 X = A + B MsgBox X

End Sub

Step 9: Now, use the sleep function to pause for five seconds.

Code:

Public Declare PtrSafe Sub

Sleep

Lib

"kernel32" (

ByVal

dwMilliseconds

As LongPtr

)

Sub

Sample1()

Dim

A, B, C, D, X, Y

As Integer

A = 10 B = 15 C = 20 D = 25 X = A + B MsgBox X Sleep 5000

End Sub

Step 10: Now, in variable Y, store the value of X +C + D and display it.

Code:

Public Declare PtrSafe Sub

Sleep

Lib

"kernel32" (

ByVal

dwMilliseconds

As LongPtr

)

Sub

Sample1()

Dim

A, B, C, D, X, Y

As Integer

A = 10 B = 15 C = 20 D = 25 X = A + B MsgBox X Sleep 5000 Y = X + C + D MsgBox Y

End Sub

Step 11: Run the above code from the provided run button or by pressing the F5 key to see the result. We see the first message is displayed as.

Step 12: Press OK and the macro waits for five seconds and displays the next result.

VBA Sleep Function – Example #3

In this example, we want to rename two worksheets, sheet 1 and sheet 2, as Anand and Aran, respectively. But the time duration between both should be five seconds. We want the macro to pause after renaming sheet 1 and then rename sheet 2. Currently, both sheets are named as follows:

Follow the below steps to use Sleep Function in Excel VBA:

Step 3: Now, use the declaration statement to use the sleep function. As I am using the Windows 64-bit operating system, I will use the declaration statement for the same.

Code:

Public Declare PtrSafe Sub

Sleep

Lib

"kernel32" (

ByVal

dwMilliseconds

As LongPtr

)

Step 4: Now declare the sub-function to start writing the code.

Code:

Public Declare PtrSafe Sub

Sleep

Lib

"kernel32" (

ByVal

dwMilliseconds

As LongPtr

)

Sub

Sample2()

End Sub

Step 5: Activate worksheet 1 and rename it by the following code:

Code:

Public Declare PtrSafe Sub

Sleep

Lib

"kernel32" (

ByVal

dwMilliseconds

As LongPtr

)

Sub

Sample2() Worksheets("Sheet1").Activate Worksheets("Sheet1").Name = "Anand" MsgBox "Sheet 1 renamed"

End Sub

Step 6: Use the sleep function to use delay for five seconds.

Code:

Public Declare PtrSafe Sub

Sleep

Lib

"kernel32" (

ByVal

dwMilliseconds

As LongPtr

)

Sub

Sample2() Worksheets("Sheet1").Activate Worksheets("Sheet1").Name = "Anand" MsgBox "Sheet 1 renamed" Sleep 5000

End Sub

Step 7: Now rename sheet 2 with the following code.

Code:

Public Declare PtrSafe Sub

Sleep

Lib

"kernel32" (

ByVal

dwMilliseconds

As LongPtr

)

Sub

Sample2() Worksheets("Sheet1").Activate Worksheets("Sheet1").Name = "Anand" MsgBox "Sheet 1 renamed" Sleep 5000 Worksheets("Sheet2").Activate Worksheets("Sheet2").Name = "Aran" MsgBox "Sheet 2 renamed"

End Sub

Step 8: Now run the code and see the first message displayed.

Also, we can check that sheet 1 is renamed.

Step 9: Press ok and wait five seconds for the next message and second sheet to be renamed.

The second sheet is also renamed.

Things to Remember

VBA Sleep is a window function, so to use it, we need to use declaration statements.

There are different declaration statements for different types of operating systems.

Simply using VBA Sleep freezes the macro for the time duration provided.

The time parameter given to the VBA sleep function is in milliseconds.

Recommended Articles

This is a guide to VBA Sleep Function. Here we discuss using Excel VBA Sleep Function, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

Creating A User Defined Function (Udf) In Excel Vba

Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1) Next i GetNumeric = Result ‘ This function extracts the numeric part from the string Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1) Next i GetNumeric = Result WorkbookName = ThisWorkbook.Name End Function

The above code specifies the function’s result as a String data type (as the result we want is the file name – which is a string).

This function assigns the value of  ‘ThisWorkbook.Name’ to the function, which is returned when the function is used in the worksheet.

If the file has been saved, it returns the name with the file extension, else it simply gives the name.

The above has one issue though.

If the file name changes, it wouldn’t automatically update. Normally a function refreshes whenever there is a change in the input arguments. But since there are no arguments in this function, the function doesn’t recalculate (even if you change the name of the workbook, close it and then reopen again).

If you want, you can force a recalculation by using the keyboard shortcut – Control + Alt + F9.

To make the formula recalculate whenever there is a change in the worksheet, you need to a line of code to it.

The below code makes the function recalculate whenever there is a change in the worksheet (just like other similar worksheet functions such as TODAY or RAND function).

Function WorkbookName() As String Application.Volatile True WorkbookName = ThisWorkbook.Name ConvertToUpperCase = UCase(CellRef) Dim Result As String Dim DelimPosition As Integer DelimPosition = InStr(1, CellRef, Delim, vbBinaryCompare) - 1 Result = Left(CellRef, DelimPosition) GetDataBeforeDelimiter = Result End Function

When you need to use more than one argument in a user-defined function, you can have all the arguments in the parenthesis, separated by a comma.

Note that for each argument, you can specify a data type. In the above example, ‘CellRef’ has been declared as a range datatype and ‘Delim’ has been declared as a String data type. If you don’t specify any data type, VBA considers these are a variant data type.

When you use the above function in the worksheet, you need to give the cell reference that has the text as the first argument and the delimiter character(s) in double quotes as the second argument.

It then checks for the position of the delimiter using the INSTR function in VBA. This position is then used to extract all the characters before the delimiter (using the LEFT function).

Finally, it assigns the result to the function.

This formula is far from perfect. For example, if you enter a delimiter that is not found in the text, it would give an error. Now you can use the IFERROR function in the worksheet to get rid of the errors, or you can use the below code that returns the entire text when it can’t find the delimiter.

Function GetDataBeforeDelimiter(CellRef As Range, Delim As String) as String Dim Result As String Dim DelimPosition As Integer DelimPosition = InStr(1, CellRef, Delim, vbBinaryCompare) - 1 If DelimPosition < 0 Then DelimPosition = Len(CellRef) Result = Left(CellRef, DelimPosition) GetDataBeforeDelimiter = Result End Function

We can further optimize this function.

If you enter the text (from which you want to extract the part before the delimiter) directly in the function, it would give you an error. Go ahead.. try it!

This happens as we have specified the ‘CellRef’ as a range data type.

Or, if you want the delimiter to be in a cell and use the cell reference instead of hard coding it in the formula, you can’t do that with the above code. It’s because the Delim has been declared as a string datatype.

If you want the function to have the flexibility to accept direct text input or cell references from the user, you need to remove the data type declaration. This would end up making the argument as a variant data type, which can take any type of argument and process it.

The below code would do this:

Function GetDataBeforeDelimiter(CellRef, Delim) As String Dim Result As String Dim DelimPosition As Integer DelimPosition = InStr(1, CellRef, Delim, vbBinaryCompare) - 1 If DelimPosition < 0 Then DelimPosition = Len(CellRef) Result = Left(CellRef, DelimPosition) GetDataBeforeDelimiter = Result Dim Result If IsMissing(fmt) Then CurrDate = Format(Date, "dd-mm-yyyy") Else CurrDate = Format(Date, "dd mmmm, yyyy") End If End Function

Note that the above function uses ‘IsMissing’ to check whether the argument is missing or not. To use the IsMissing function, your optional argument must be of the variant data type.

The above function works no matter what you enter as the argument. In the code, we only check if the optional argument is supplied or not.

You can make this more robust by taking only specific values as arguments and showing an error in rest of the cases (as shown in the below code).

Function CurrDate(Optional fmt As Variant) Dim Result If IsMissing(fmt) Then CurrDate = Format(Date, "dd-mm-yyyy") ElseIf fmt = 1 Then CurrDate = Format(Date, "dd mmmm, yyyy") Else CurrDate = CVErr(xlErrValue) End If End Function

The above code creates a function that shows the date in the “dd-mm-yyyy” format if no argument is supplied, and in “dd mmmm,yyyy” format when the argument is 1. It gives an error in all other cases.

Function with Required as well as Optional Arguments

We have already seen a code that extracts the numeric part from a string.

Now let’s have a look at a similar example that takes both required as well as optional arguments.

The below code creates a function that extracts the text part from a string. If the optional argument is TRUE, it gives the result in uppercase, and if the optional argument is FALSE or is omitted, it gives the result as is.

Function GetText(CellRef As Range, Optional TextCase = False) As String Dim StringLength As Integer Dim Result As String StringLength = Len(CellRef) For i = 1 To StringLength If Not (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1) Next i If TextCase = True Then Result = UCase(Result) GetText = Result Dim Cell As Range For Each Cell In CellRef If IsNumeric(Cell.Value) Then If Cell.Value Mod 2 = 0 Then Result = Result + Cell.Value End If End If Next Cell AddEven = Result For Each arg In arglist AddArguments = AddArguments + arg Next arg For Each arg In arglist For Each Cell In arg AddArguments = AddArguments + Cell Next Cell Next arg Dim NumberValue(1 To 3) NumberValue(1) = 1 NumberValue(2) = 2 NumberValue(3) = 3 ThreeNumbers = NumberValue Dim MonthName(1 To 12) MonthName(1) = "January" MonthName(2) = "February" MonthName(3) = "March" MonthName(4) = "April" MonthName(5) = "May" MonthName(6) = "June" MonthName(7) = "July" MonthName(8) = "August" MonthName(9) = "September" MonthName(10) = "October" MonthName(11) = "November" MonthName(12) = "December" Months = MonthName Months = Array("January", "February", "March", "April", "May", "June", _ "July", "August", "September", "October", "November", "December") WorkbookName = ThisWorkbook.Name End Function

You can use this function in the subroutines and the procedures in the same modules, but can’t use it in other modules. This function would also not show up in the worksheet.

The below code would make this function Public. It will also show up in the worksheet.

Function WorkbookName() As String WorkbookName = ThisWorkbook.Name WorkbookName = chúng tôi End Function

The below procedure call the function and then display the name in a message box.

Sub ShowWorkbookName() MsgBox WorkbookName End Sub

You can also call a function from another function.

In the below codes, the first code returns the name of the workbook, and the second one returns the name in uppercase by calling the first function.

Function WorkbookName() As String WorkbookName = ThisWorkbook.Name End Function Function WorkbookNameinUpper() WorkbookNameinUpper = UCase(WorkbookName) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If J = 3 Then Exit Function If IsNumeric(Mid(CellRef, i, 1)) Then J = J + 1 Result = Result & Mid(CellRef, i, 1) GetNumericFirstThree = Result End If Next i Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If J = 3 Then Exit Function If IsNumeric(Mid(CellRef, i, 1)) Then J = J + 1 Result = Result & Mid(CellRef, i, 1) Debug.Print J, Result GetNumericFirstThree = Result End If Next i

Update the detailed information about Large Function In Excel (Formula, Examples) on the Katfastfood.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!