Trending December 2023 # How To Use The Clng Function In Vba? # Suggested January 2024 # Top 19 Popular

You are reading the article How To Use The Clng Function In Vba? updated in December 2023 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 January 2024 How To Use The Clng Function In Vba?

Excel VBA CLng

Excel VBA CLng function is used to convert an expression or a variable’s value to a long integer. Every variable which is used in any programming language has a certain data type. Each data type has some range limited to it which means up to how much values a variable can store. For an example a long data type can store from -2,147,483,648 and 2,147,483,647. This is the limit of the Long data type. There are many instances when numerical data or value is assigned to a non-numerical data type or variable. In such scenarios we encounter errors. But in Excel VBA CLng is one such function that allows us to convert or change these values to the desired data type.

VBA CLng Function Syntax:

Watch our Demo Courses and Videos

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

Now this expression can be a value or a variable or it can be any expression. The use of this function is to convert that expression or value to long integer but there are certain scenarios where we may encounter errors while using this function. Let us discuss how we use this function in examples and then we will discuss the possible errors we might encounter while using this function.

How to Use the CLng Function in VBA?

We will learn how to use the CLng function using the VBA code in Excel.

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

Example #1

Let us begin with the first example for the CLng function. Let us start with the basics first and see how this function works. Follow the steps below to see how exactly the VBA CLng function works.

Step 2: Insert a new subprocedure.

Sub

Example1()

End Sub

Step 3: Let us just declare some variable as long.

Code:

Sub

Example1()

Dim

value

As Long

End Sub

Step 4: We can assign this variable a value using the CLng function.

Code:

Sub

Example1()

Dim

value

As Long

value =

CLng(

35150.45

)

End Sub

Step 5: So now we can use the Msgbox function to display the result.

Code:

Sub

Example1()

Dim

value

As Long

value =

CLng(

35150.45

)

MsgBox value

End Sub

Step 6: Run this code by hitting the F5 or Run button which is placed on the topmost ribbon of VBE.

Example #2

Earlier we talked about how we sometimes declare a variable with non-numeric data type but assign a numeric value. Let us try it in this example. Follow the steps below.

Step 1: Let us declare our second example subprocedure.

Code:

Sub

Example2()

End Sub

Step 2: Declare two variables, one as a string and another as long.

Code:

Sub

Example2()

Dim

num

As String

Dim

newnum

As Long

End Sub

Step 3: We can see that we have a variable as a string and let us assign a numeric value.

Code:

Sub

Example2()

Dim

num

As String

Dim

newnum

As Long

num = "123456789"

End Sub

Step 4: Now we can CLng function to convert it to long.

Code:

Sub

Example2()

Dim

num

As String

Dim

newnum

As Long

num = "123456789" newnum =

CLng(

num

)

End Sub

Step 5: We can use the Msgbox function.

Sub

Example2()

Dim

num

As String

Dim

newnum

As Long

num = "123456789" newnum =

CLng(

num

)

MsgBox newnum

End Sub

Step 6: Let us execute the code by hitting the F5 or Run button.

Example #3

We also discussed above that we might encounter some errors while using this function. Let us go through a couple of examples on how we can encounter some possible errors.

Step 1: In the same module let us declare another subprocedure.

Code:

Sub

Example3()

End Sub

Step 2: Let us again declare two variables one as a string and another as long.

Code:

Sub

Example3()

Dim

num

As String

Dim

newnum

As Long

End Sub

Step 3: Now let us assign a string value to the string variable and then try to convert it to long using the CLNG function.

Code:

Sub

Example3()

Dim

num

As String

Dim

newnum

As Long

num = "25645890003" newnum =

CLng(

num

)

End Sub

Step 4: We can use mgsbox function to check if we get any result.

Code:

Sub

Example3()

Dim

num

As String

Dim

newnum

As Long

num = "25645890003" newnum =

CLng(

num

)

MsgBox newnum

End Sub

Step 5: Run this code by hitting the F5 or Run button which is placed on the topmost ribbon of VBE.

We received type mismatch because we tried to convert a string value to long.

Example #4

Step 1: In the same module let us declare another subprocedure.

Code:

Sub

Example4()

End Sub

Step 2: Let us again declare two variables one as a string and another as long.

Code:

Sub

Example4()

Dim

num

As String

Dim

newnum

As Long

End Sub

Step 3: Now let us assign a value greater than long can hold to the string variable and then try to convert it to long using the CLng function.

Code:

Sub

Example4()

Dim

num

As String

Dim

newnum

As Long

num = "25645890003" newnum =

CLng(

num

)

End Sub

Step 4: We can use Mgsbox function to check if we get any result.

Code:

Sub

Example4()

Dim

num

As String

Dim

newnum

As Long

num = "25645890003" newnum =

CLng(

num

)

MsgBox newnum

End Sub

Step 5: When we execute the above code by hitting the F5 or Run button we will encounter the following error.

We have encountered this error because the value was greater than the long data type can hold.

Explanation of CLng in VBA

As we discussed above how we use CLng function to convert an expression or a value to Long data type value. However, we also saw that in the process we encounter some errors when we provide data that is non-numeric or the data or value is out of the range of long data type.

Things to Remember

There are few things which we need to remember about CLng in VBA and they are as follows:

CLng is a function in VBA which is used to convert a value to a long data type.

This function has a single argument as an input.

While using this function we should consider in mind the range of long data type which is -2,147,483,648 and 2,147,483,647.

This function is used as an expression.

How To Use Offset Function In Excel Vba With Example?

Excel VBA OFFSET Function

As there are two things in this word, one is VBA and other is OFFSET. In this, I’ll be explaining how to use OFFSET function using VBA (Visual Basic for Applications).

VBA – It is a programming language for those who work in Excel and other Office programs, so one can automate tasks in Excel by writing Macros.

Watch our Demo Courses and Videos

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

OFFSET – It is a reference function in Excel. The OFFSET function returns a reference to a range that is a specific number of rows and columns from another range or cell. It is one of the most important notions in Excel.

Let’s consider we have a dataset which consists of columns Customer Name, Product, sales, Quantity, Discount.

Suppose on the chance that we need to move down from a particular cell to the particular number of rows and to choose that cell at that point of time OFFSET function is very useful. For example, from cell B1 we want to move down 5 cells and want to select 5th cell i.e. B6. Suppose, if you want to move down from B1 cell 2 rows and goes 2 columns to the right and select that cell i.e. cell D3.

To use OFFSET function in VBA, we have to use VBA RANGE object because OFFSET refers cells and from that RANGE object we can use OFFSET function. In Excel, RANGE refers to the range of cells.

Let’s take a look at how OFFSET is used with RANGE.

Range(“A1”).offset(5).select

How to Use the OFFSET Function in Excel VBA?

Below are the different examples to use OFFSET Function in Excel using VBA Code.

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

VBA OFFSET – Example #1

Step 2: Drag the arrow at any cell to create a Command Button.

Code:

End Sub

Step 4: Inside this function, we have to write our code of OFFSET for selecting cells. As mentioned in the previously we have to use OFFSET function with RANGE in VBA.

Range(

End Sub

Step 5: In this code, we have to select the 5th cell of column Product i.e. B6. Cell1 in Range is B1 because we have to move down 5 cells from cell B1 to B6 i.e 5 cells down.

Code:

Range(“B1”).Offset(

End Sub

OFFSET function has two arguments:

RowOffset: How many rows we want to move from the selected row. We have to pass the number as an argument.

ColumnOffset: How many columns we want to move from the selected row.

Step 6: Now I want to select cell B6 i.e I have to move down 5 cells. So, we have to enter 5 as the parameter for Row Offset.

Code:

Range(“B1”).Offset(5)

End Sub

Step 7: After closing the bracket we have to put a (.) dot and write the Select method.

Code:

Range(“B1”).Offset(5).Select

End Sub

VBA OFFSET – Example #2

In this example, we will see how to use Column OFFSET argument. We will be working on the same data. All the above steps will be the same but we need to make a change in code.

Since I want to move down 5 cells and take the right 3 columns to reach the cell E6.

Code:

Range(“B1”).Offset(5, 3).Select

End Sub

Things to Remember

It is a reference function in Excel. The OFFSET function returns a reference to a range that is a specific number of rows and columns from another range or cell.

VBA OFFSET is used with RANGE object in VBA.

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.

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

How To Write, Build, And Use Vlookup Function In Excel

The VLOOKUP function in Microsoft Excel literally means vertical lookup. It’s a search function for querying values in the cell of a column. This function searches for the data relative to the entries in the first column from the left.

A vertical data search is most vital when dealing with tables with numerous columns and rows. Instead of scrolling through and analyzing hundreds of cells, Excel’s VLOOKUP function helps you find the data you’re looking for by looking up the values from top to bottom.

Create, build & use Excel’s VLOOKUP function

In our example, we’ll work with a VLOOKUP function that searches for information about seven employees’ salaries. This section shows you how to use the VLOOKUP function in the following ways:

Write the Excel VLOOKUP function.

Build a VLOOKUP function in Excel.

Without further ado, let’s get to it. In the first method, we’ll create the function manually. Next, we’ll use it from Excel’s inbuilt Functions Arguments wizard.

1] Write the Excel VLOOKUP function

Launch Microsoft Excel and make a column for the values that act as unique identifiers. We’ll call this the reference column.

Add some more columns to the right-hand side of the first one you created in the first step and insert values for the cells in these columns.

=VLOOKUP()

On entering the above formula, Excel suggests the VLOOKUP syntax:

=VLOOKUP(vlookup_value,table_array,col_index_num,range_lookup) Arguments or parameters

Here are what the above arguments define in the syntax:

lookup_value: the cell with the product identifier from the reference column.

table_array: the data range from with to search. It must contain the reference column and the column containing the value you’re looking up. In most cases, you can use the entire worksheet. You can drag your mouse over the values of the table to select a data range.

col_index_num: the number of the column from which to look up a value. You put this in from left to right.

range_lookup: TRUE for an approximate match, FALSE for an exact match. The value is TRUE by default, but you generally use FALSE.

With this information, we’ll now replace the parameters in the parenthesis with the information we wish to look up. For example, to return Wayne Creed‘s salary, enter the following formula:

=VLOOKUP(C14,B5:E11,6,FALSE)

On navigating away from the cell with the VLOOKUP formula, it returns the value for which you queried. If you get a #N/A error, read this Microsoft guide to learn how to correct it.

2] Build a VLOOKUP function in Excel

The first part showed you how to create a VLOOKUP function manually. If you thought the above method was easy, wait till you read this. Here, you’ll learn how to build a VLOOKUP function quickly using the user-friendly Functions Arguments wizard.

Open Microsoft Excel first, and create a reference column that will contain unique identifiers.

Next, create some more columns on the right-hand side of the reference column. Here, we’ll insert the relevant values for the items on the reference column.

Select an empty cell and type in a value from the reference cell. This is the value whose properties we’ll lookup.

Select the Lookup & Reference tool from the Functions Library and choose VLOOKUP from the dropdown menu. This opens the Functions Arguments wizard.

Fill in the Lookup_value, Table_array, Col_index_num, and Range_lookup fields in the Functions Arguments wizard specified in the first method.

Hit the OK button when you’re done, and the VLOOKUP function will return the results from the arguments you entered.

This guide will help you if the Excel formula fails to update automatically.

Both methods will successfully query the data you need in reference to the first column. The Formulas Argument wizard makes it easy to input the variables to make the VLOOKUP function work.

However, the VLOOKUP function also works on the web version of Excel. You also get to use the Functions Argument wizard or create the VLOOKUP function manually on the web version.

Let us take a look at the HLOOKUP function in Excel now.

How To Round Numbers In Excel Using The Round Function

There are times where rounding values to the nearest decimal or whole number should be used to improve the look and clarity of your Excel presentation. When you round numbers, you remove the least significant digits. This results in more presentable values with your preferred level of accuracy.

Rounding is often used to give estimates and to make numbers easier to work with. For example, if I want to calculate the percentage discount and I get a number such as 17.3587563, rounding it to one decimal place will give me 17.4%, which is more presentable. In this tutorial we’ll show you how to round numbers using the ROUND function.

Excel ROUND Function

The ROUND function is the most popular and the most commonly used Excel function for rounding numbers. This function rounds numbers to the nearest decimal based on your specified number of digits. If the next digit to the right is 0, 1, 2, 3, or 4, it rounds down. If the next digit to the right is 5, 6, 7, 8, or 9, it rounds up. So “7.82564” rounded to two decimal places would become “7.83”. Here’s how to use the Excel ROUND function.

1. Open an existing Excel workbook with raw numbers or create a new worksheet. Then create a new column just next to the values you want to round up or dowm and give it a name.

The Formulas dropdown menu will open. Select the “ROUND” function in the menu options.

5. This will open up the function’s argument window where you’ll configure the function to your desired results. Enter the number you want to round in the “Number” field. The easiest way to do this is to enter the cell number to which you are referencing. In our case we’ll use B4 to specify the top cell in our values column.

Enter the number of digits to which the number should be rounded in the “Num_digits” field. This specifies the number of digits you want the resulting figure to have. For example, you can enter 2 to signify two decimal places.

If you want to round numbers to the nearest 10 or 100, insert a negative number in the “Number” field. Doing so will round the number to the right side of the decimal. For example, if you insert “-1” in the number field and you’re rounding a number, such as 427.13, the resulting figure would be 430.

That’s it. You’ve just rounded all the numbers to your desired number of digits.

Using the ROUND syntax

Alternatively, you can use a function’s syntax which is a lot easier compared to the method above.

A function’s syntax refers to the formula breakdown of the function and includes the function’s name, bracket, and arguments. The arguments are the “Number” and the “Num_digits.”

The Syntax for the ROUND formula is:

ROUND

(number, num_digits)

where “Number” is the value to be rounded and “Num_digits” is the number of digits to which the number will be rounded.

To use the ROUND syntax formula, simply follow the steps below.

1. Select the cell where you want the results to go.

3. Type in the syntax for the ROUND formula. Here’s how it would appear in our case.

4. Press Enter and the rounded value will appear in the results column. You can then drag down the rounded figure for the formula to apply to the rest of the cells.

Wrapping Up

Rounding numbers is a great way to make your Excel worksheet neat and presentable. And since the Excel ROUND function follows the mathematical rules for rounding numbers, you get highly accurate results that are a better representation of the actual figures.

Kenneth Kimari

Kenn is a tech enthusiast by passion, Windows blogger by choice, and a massive coffee imbiber. He likes watching sci-fi movies in his free time and tearing gadgets apart so he can fix them.

Subscribe to our newsletter!

Our latest tutorials delivered straight to your inbox

By signing up, you agree to our Privacy Policy and European users agree to the data transfer policy. We will not share your data and you can unsubscribe at any time.

Update the detailed information about How To Use The Clng Function In Vba? 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!