Trending December 2023 # How To Use Offset Function In Excel Vba With Example? # Suggested January 2024 # Top 14 Popular

You are reading the article How To Use Offset Function In Excel Vba With Example? 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 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.

Recommended Articles

This is a guide to VBA OFFSET. Here we discuss how to use OFFSET function in Excel using VBA code along with practical examples and downloadable excel template. You may also look at the following articles to learn more –

You're reading How To Use Offset Function In Excel Vba With Example?

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 –

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.

Recommended Articles

This is a guide to the VBA CLng. Here we discuss how to Use the CLng Function in Excel VBA along with practical examples and 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

How To Create Hyperlink In Excel Vba With Examples?

Definition of VBA Hyperlink

The hyperlink is commonly used with websites for navigating from one page to another or one website to another on the internet. In a similar way, we can control the movements within excel worksheet too. The different operations that can be performed in Excel are:

Moving to a specific location within the current workbook.

Opening different documents and select a mentioned area within the document.

Navigating to webpages from the worksheet.

Sending email to a defined address.

The hyperlink is easy to recognize because of its color change, mostly in blue. There exist different methods to create a hyperlink in excel and let using VBA.

Watch our Demo Courses and Videos

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

How to Create a Hyperlink in Excel Using VBA Code?

You can add a hyperlink to a text or one sheet to another worksheet within excel using hyperlink add property. The format needs to be followed by specifying where the hyperlink should be created and navigation URL etc.

Format for VBA Hyperlink Add

The format shows the parameters need to be provided to add a hyperlink to a worksheet.

Anchor: Defines the cell you want to create the hyperlink.

Address: The URL to which the navigation should move.

[SubAddress]: Subaddress of the URL.

[ScreenTip]: The mouse pointer value to be showed while placing a mouse pointer.

[Text to Display]: The text needs to be displayed on the cell.

Use the Active cell property to add a hyperlink.

Examples to Create Hyperlinks in Excel VBA

Below are the different examples to create hyperlinks in excel using VBA code.

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

Example #1 – Creating a hyperlink from the Worksheet to a website

We want to create a hyperlink from worksheet named sub to a website using VBA code in excel.

Below are the steps to create a hyperlink in Excel VBA:

Step 1: Create a function named hyper to add the hyperlink.

Code:

Private Sub

hyper()

End Sub

Step 2: Use the Active cell object to get open the hyperlink add method.

Code:

Private Sub

hyper() ActiveCell.Hyperlinks.Add(

End Sub

Step 3: Provide the parameter values to the hyperlink add method.

Code:

Private Sub

hyper()

End Sub

Anchor: name of the worksheet

Address: Hyperlink to where the control to be navigated, given the website address

ScreenTip: The mouse pointer text

TextToDisplay: To which text the hyperlink is to be assigned

Step 4: Hit F5 or Run button under VBE to run this code and see the output.

Example #2 – Hyperlink to Connect Two Worksheets

We have two worksheets named Home and sub. Let’s try to create a hyperlink from sub to home using VBA code.

Follow the below steps to create a hyperlink from one worksheet to another within the same workbook using the VBA code.

Step 1: Create a function, where we will write all codes to perform the action. Write code to select the worksheet ‘sub’ using the selection method of the worksheet.

Code:

Private Sub

hyper1() Worksheets("sub").Select

End Sub

Since the control moves within the sheet, it is necessary to select the worksheet in which you are creating the hyperlink.

Step 2: Select the cell range within the sheet where the hyperlink is want to create.

Code:

Private Sub

hyper1() Worksheets("sub").Select Range("A1").Select

End Sub

Step 3: Now let’s add the hyperlink using the active cell property.

Code:

Private Sub

hyper1() Worksheets("sub").Select Range("A1").Select

End Sub

Since the worksheet is already selected, Anchor is given as ‘Selection’. The hyperlink is specified as ‘Home’ sheet and range A1.

Step 4: Run the code and sheet sub will be shown the hyperlink as below.

Example #3 – Hyperlink with Multiple Worksheets

If you want to create hyperlink across multiple worksheets it is also possible. In this example, we have multiple sheets within the same workbook. Different type of excel functions exists so from the main worksheet ‘Functions’. Let’s try to create a hyperlink to the different worksheet named with different functions using VBA code:

The multiple worksheets are named as below with different excel function names

Since we want to create a hyperlink to each worksheet it’s difficult to repeat the code. Follow the below steps to create a hyperlink using VBA Code in Excel:

Step 1: Create a variable to deal with worksheet easily.

Code:

Private Sub

hyper2()

Dim

ws

As Worksheet

End Sub

Step 2: Now we want to select the main page which acts as an index page and select the cell range A1.

Code:

Private Sub

hyper2()

Dim

ws

As Worksheet

Worksheets("Functions").Select Range("A1").Select

End Sub

Code:

Private Sub

hyper2()

Dim

ws

As Worksheet

Worksheets("Functions").Select Range("A1").Select

For Each

ws

In

ActiveWorkbook.Worksheets ActiveCell.Hyperlinks.Add Anchor:=ActiveCell

Next

ws

End Sub

Step 4: Provide the parameter values to create a hyperlink for each worksheet. Since hyperlink starts from active cell anchor=Active cell, the address is given as ” “.

Code:

Private Sub

hyper2()

Dim

ws

As Worksheet

Worksheets("Functions").Select Range("A1").Select

For Each

ws

In

ActiveWorkbook.Worksheets ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=""

Next

ws

End Sub

Step 5: The hyperlink is looped through worksheet so we should give subaddress as sheet names. To get the sheet names we can use the variable ws and cell range as A1. The sheet name will have referred with a single quotation. Sheet name and range will be specified and also closed with a single quotation.

Code:

Private Sub

hyper2()

Dim

ws

As Worksheet

Worksheets("Functions").Select Range("A1").Select

For Each

ws

In

ActiveWorkbook.Worksheets ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:="", SubAddress:="" & chúng tôi & "!A1" & ""

Next

ws

End Sub

Step 6: To get the hyperlink with sheet name gives TextToDisplay as ws.Name

Code:

Private Sub

hyper2()

Dim

ws

As Worksheet

Worksheets("Functions").Select Range("A1").Select

For Each

ws

In

ActiveWorkbook.Worksheets ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:="", SubAddress:="" & chúng tôi & "!A1" & "", TextToDisplay:=ws.Name

Next

ws

End Sub

This code will store hyperlink for each worksheet in the same cell A1.

Step 7: To change this each sheet to different cell down one cell from the active cell.

Code:

Private Sub

hyper2()

Dim

ws

As Worksheet

Worksheets("Functions").Select Range("A1").Select

For Each

ws

In

ActiveWorkbook.Worksheets ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:="", SubAddress:="" & chúng tôi & "!A1" & "", TextToDisplay:=ws.Name ActiveCell.Offset(1, 0).Select

Next

ws

End Sub

Things to Remember

Hyperlink property of active cell used to create hyperlinks in VBA.

Hyperlink help to move within the workbook easily.

Recommended Articles

This is a guide to VBA Hyperlinks. Here we learn how to create hyperlinks in Worksheet Using VBA Code to quickly move from one sheet to another sheet along with some practical examples and downloadable excel template. You can also go through our other suggested articles –

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.

Update the detailed information about How To Use Offset Function In Excel Vba With Example? 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!