Trending December 2023 # How To Create Hyperlink In Excel Vba With Examples? # Suggested January 2024 # Top 17 Popular

You are reading the article How To Create Hyperlink In Excel Vba With Examples? 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 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

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

As Worksheet

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

End Sub

Code:

Private Sub

hyper2()

Dim

As Worksheet

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

For Each

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

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

As Worksheet

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

For Each

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

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

As Worksheet

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

For Each

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

End Sub

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

Code:

Private Sub

hyper2()

Dim

As Worksheet

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

For Each

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

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

As Worksheet

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

For Each

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

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 –

You're reading How To Create Hyperlink In Excel Vba With Examples?

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 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 –

How To View Vba Code In Excel: A Step

In this article, you’ll learn how to access VBA code in Excel as well as get an understanding of the tools and techniques needed to get started. By getting familiar with the VBA editor, you‘ll open up a whole new world of possibilities for improving your workbook processes and simplifying tricky tasks.

Let’s dive in!

In this section, you’ll learn how to access the VBA environment within Excel by enabling the Developer tab, using a keyboard shortcut, and locating existing macros. This guide will help you navigate through the VBA code and make customizations to your Excel workbooks.

To access the VBA environment, you first need to enable the Developer tab in the Ribbon. Follow these steps to do so:

Select Options to open the Excel Options Window.

In the right panel, under the Main Tabs section, check the box next to Developer in the dropdown list.

The Developer tab should now be visible in the Excel Ribbon.

You can access the VBA environment using a keyboard shortcut as well. Press ALT + F11 to open the Excel Visual Basic Editor.

To view and manage the code for your Excel macros, go through the following steps:

In the Macro dialog box, you will see a list of available macros in your workbook.

The Visual Basic Editor will open, allowing you to view and modify the code for the selected macro.

The Visual Basic for Applications (VBA) Editor is an integral part of Excel that allows you to access and modify code embedded in your workbooks.

In this section, we’ll dive into the VB Editor and explore its essential components: the Project Explorer, Code Window, and Properties Window.

You can also simply press Ctrl+R to access the Project Explorer.

You’ll see a list of your Excel workbook files, each with a “+” icon that you can expand to reveal the objects and modules within.

The Code Window is the main area where you’ll write, edit, and view your code. When you select a module or object in Project Explorer, the corresponding code will be displayed in the Code Window.

This will add a new module to store your code.

When writing code, you can make use of the VBA Editor’s features like code completion and syntax highlighting, which help to make your code more readable and easy to understand.

The Properties Window allows you to view all the properties and modify the properties of the selected objects in your VBA project.

The Properties Window will display a list of properties connected with the currently selected object in the Project Explorer, such as its name or visibility.

With a solid understanding of the VBA Editor’s key components – Project Explorer, Code Window, and Properties Window — you’ll be able to use and change Visual Basic code in your Excel workbooks.

Knowing VBA modules is super helpful because they let you automate stuff, customize things, and handle data like a boss in Microsoft Office programs. You can make tasks go smoother and come up with personalized solutions. When you understand VBA modules, you can boost your productivity and get things done faster and better.

In this section, you’ll learn how to work with VBA modules in Excel. VBA modules include procedures that define the actions your code performs. We’ll discuss inserting a module, removing a module, and managing module properties.

After you open Visual Basic Editor, go to the Project Explorer window.

In Project Explorer, locate the module you want to remove.

In the Properties Window, you can modify the module’s name under the (Name) property (the first property). It’s good practice to give the module a descriptive name related to its functionality.

By following these steps, you’ll efficiently manage your VBA modules, helping you keep your code organized and easy to maintain.

Now that we’ve gone over the various ways you can work with VBA modules, let’s discuss how to navigate your code in the next section!

So, you want to dive into the world of VBA code? Well, buckle up, because navigating Visual Basic code is like exploring a secret maze of commands and functions. It’s all about understanding the language and finding your way around to create amazing automation and customization. Let’s get started!

This technique can be helpful to explain the purpose of specific lines or blocks of code, making it easier for you (and any other users) to comprehend the logic behind the code.

You may sometimes get errors when handling Visual Basic code. Debugging tools included in the VBE can help you identify and fix these problems. Some valuable debug features are:

Step Into (F8): Executes the code line by line, allowing you to follow the code flow and monitor variable values at each step.

Immediate Window (CTRL+G): Allows you to execute single lines of code and display the results. This feature can help test and auto-syntax check various parts of your code without running the entire script.

By utilizing these tools and techniques, you can efficiently navigate, understand, and debug your Visual Basic code in Excel.

If you want to make Microsoft Office programs bend to your will, Visual Basic code customization is the way to go.

With a bit of coding know-how, you can tweak those apps to match your exact preferences. Get ready to unleash your creativity and personalize your work experience!

With VBA, you can create custom functions (also known as User Defined Functions or UDFs) to enhance your productivity and optimize your workflow in Excel. To create a custom function, open the VBE by pressing Alt + F11.

For example, to create a custom function that adds two cells and multiplies the result by a specified factor, you can use the following code:

Function MultiplySum(cell1 As Range, cell2 As Range, factor As Double) As Double MultiplySum = (cell1.Value + cell2.Value) * factor End Function

To use your new custom function in an Excel worksheet, simply type =MultiplySum(A1, B1, 2) into a cell, replacing the cell references and factor as needed.

VBA provides a range of options for customizing the appearance of cells in your Excel worksheets. One way to apply custom styles is through the Cells object. You can modify the font, background color, number format, and other properties of individual cells or whole ranges.

For example, to apply bold text and specific background color to a cell range, use the following code:

With Range("A1:B10").Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = RGB(153, 204, 255) End With Range("A1:B10").Font.Bold = True

Remember to customize the cell range, font properties, and color values according to your needs.

Add-ins are a powerful way to extend the functionality of Excel, and you can use VBA to create your own or to implement existing ones. To create an add-in, save your VBA module with the custom functions or code in a new Excel workbook, and then save the workbook as an Excel Add-In (*.xlam) file.

By effectively using custom functions, styles, and add-ins, you can significantly enhance your productivity in Excel, tailor the software to your specific needs, and streamline your daily tasks.

By now, you should feel confident and knowledgeable about viewing Visual Basic code in Excel. The key steps to remember are:

Access the VBE by pressing Alt + F11 in Excel.

Use the Project Explorer within the VBE to navigate to the modules, forms, and objects that contain the code.

With these guidelines in mind, you can confidently navigate and work with VBA in Excel. Remember, expertise comes with practice and exploration, so continue learning and applying your skills to develop efficient and customized solutions for your Excel projects. Happy coding!

To learn more about how to use Excel and its formulas, check out the video below:

The shortcut for displaying the VBA code in Excel is Alt + F11. This opens the Visual Basic Editor where you can view, edit, or create new code and macros.

How To Create Table In Hive With Query Examples?

Introduction to Hive Table

In the hive, the tables consist of columns and rows and store the related data in the table format within the same database. The table is storing the records or data in tabular format. The tables are broadly classified into two parts, i.e., external table and internal table.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

The default storage location of the Table varies from the hive version. From HDP 3.0, we are using hive version 3.0 and more. The default Table location was changed from HDP 3.0 version / Hive version 3.0. The location for the external hive Table is “/warehouse/tablespace/external/hive/” and the location for the manage Table is “/warehouse/tablespace/managed/hive”.

In the older version of the hive, the default storage location of the hive Table is “/apps/hive/warehouse/”.

Syntax

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [ database name ] table name [ ROW FORMAT row format] [ STORED AS file format] How to create a Table in Hive?

Hive internal table

Hive external table

Note: We have the hive “hql” file concept with the help of “hql” files, we can directly write the entire internal or external table DDL and directly load the data in the respective table.

1. Internal Table

The internal table is also called a managed table and is owned by a “hive” only. Whenever we create the table without specifying the keyword “external” then the tables will create in the default location.

If we drop the internal or manage the table, the table DDL, metadata information, and table data will be lost. The table data is available on HDFS it will also lose. We should be very careful while dropping any internal or managing the table.

DDL Code for Internal Table

create table emp.customer ( idint, first_name string, last_name string, gender string, company_name string, job_title string ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' lines terminated by 'n' location "/emp/table1" tblproperties ("skip.header.line.count"="1");

Note: To load the data in hive internal or manage the table. We are using the “location” keyword in DDL Code. From the same location, we have kept the CSV file and load the CSV file data in the table.

Output:

2. External Table

The best practice is to create an external table. Many organizations are following the same practice to create tables. It does not manage the data of the external table, and the table is not created in the warehouse directory. We can store the external table data anywhere on the HDFS level.

The external tables have the facility to recover the data, i.e., if we delete/drop the external table. Still no impact on the external table data present on the HDFS. It will only drop the metadata associated with the table.

DDL Code for External Table

create external table emp.sales ( idint, first_name string, last_name string, gender string, email_id string, city string ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' lines terminated by 'n' location "/emp/sales" tblproperties ("skip.header.line.count"="1");

Note: we can directly store the external table data on the cloud or any other remote machine in the network. It will depend on the requirement.

Output:

How to modify/alter the Table?

Here we have the facility to alter or modify the existing attributes of the Table. With the help of the “alter” functionality, we can change the column name, add the column, drop the column, change the column name, and replace the column.

We can alter the below Table attributes.

1. Alter/ rename the tablename

Syntax:

ALTER TABLE [current table name] RENAME TO [new table name]

Query to Alter Table Name :

ALTER TABLE customer RENAME TO cust;

Output:

Before alter

After alter

2. Alter/ add column in the table

Syntax:

ALTER TABLE [current table name] ADD COLUMNS (column spec[, col_spec ...])

Query to add Column :

ALTER TABLE cust ADD COLUMNS (dept STRING COMMENT 'Department');

Output:

Sample view of the table

We are adding a new column in the table “department = dept”

3. Alter/change the column name

Syntax:

ALTER TABLE [current table name] CHANGE [column name][new name][new type]

Query to change column name :

ALTER TABLE cust CHANGE first_name name string;

Output:

Sample view of the customer table.

How to drop the Table?

Drop Internal or External Table

Syntax:

DROP TABLE [IF EXISTS] table name;

Drop Query:

drop table cust;

Output:

Before drop query run

After dropping the query, run on the “cust” table.

Conclusion

We have seen the uncut concept of “Hive Table” with the proper example, explanation, syntax, and SQL Query with different outputs. The table is useful for storing the structure data. The table data is helpful for various analysis purposes like BI, reporting, helpful/easy in data slicing and dicing, etc. The internal table is managed, and the hive does not manage the external table. We can choose the table type we need to create per the requirement.

Recommended Articles

We hope that this EDUCBA information on “Hive Table” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

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 –

Update the detailed information about How To Create Hyperlink In Excel Vba With 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!