# Trending November 2023 # Different Examples Of Contents In Excel Vba # Suggested December 2023 # Top 14 Popular

You are reading the article Different Examples Of Contents In Excel Vba updated in November 2023 on the website Hatcungthantuong.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 December 2023 Different Examples Of Contents In Excel Vba

Excel VBA Constants

VBA Constant, which means a value that doesn’t change by any mean. This we have seen a lot of time in mathematics. But the Constant can also be used in VBA coding as well with the same concept when we used in solving regular mathematical problems. In VBA Constant, we fix the value of any variable as per our need and we can use this predefined Constant later whenever we feel to use it.

If we define any value under VBA Constant, it will hold and store that value somewhere which will not be changed. If a person tries to change that value then it will show up the error.

Watch our Demo Courses and Videos

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

Examples of Constants in Excel VBA

Below are the different examples of contents in Excel VBA.

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

Excel VBA Constants – Example #1

VBA Constants is like defining a variable with DIM. First, we will see an example where we will execute a simple mathematical code as our regular process.

Follow the below steps to use Excel VBA Constants:

Step 1: Go to VBA Insert menu and open a Module first as shown below.

Code:

Sub

VBA_Constants()

End Sub

Step 3: Define a variable A as Integer first. This will allow us to consider all whole numbers in it.

Code:

Sub

VBA_Constants()

Dim

A

As Integer

End Sub

Step 4: Now assign any value in variable A. Let’s say it as 123. By this, we will store this value under variable A.

Code:

Sub

VBA_Constants()

Dim

A

As Integer

A = 123

End Sub

Step 5: Now again define a new variable C as Integer.

Code:

Sub

VBA_Constants()

Dim

A

As Integer

A = 123

Dim

C

As Integer

End Sub

Step 6: Now in a simple mathematical multiplication problem, let’s multiply variable A with 4 and get the output in variable C as shown below.

Code:

Sub

VBA_Constants()

Dim

A

As Integer

A = 123

Dim

C

As Integer

C = A * 4

End Sub

Step 7: Now to print the output, we will use MsgBox as shown below.

Code:

Sub

VBA_Constants()

Dim

A

As Integer

A = 123

Dim

C

As Integer

C = A * 4 MsgBox C

End Sub

Now we may end up in a situation where we have to change the value stored in variable A multiple times by keeping the constant value of multiplier as 4. So, if we create a constant where if we fix the value of multiplier which is 4 as we have for other variables then it will reduce our frequent activities.

Step 9: For this, use Const as in Constant with B and give it a variable Double.

Code:

Sub

VBA_Constants()

Dim

A

As Integer

A = 123

Const

B

As Double

Dim

C

As Integer

C = A * 4 MsgBox C

End Sub

Step 10: And assign the multiplier 4 to variable B as constant.

Code:

Sub

VBA_Constants()

Dim

A

As Integer

A = 123

Const

B

As Double

= 4

Dim

C

As Integer

C = A * 4 MsgBox C

End Sub

Step 11: Now change the same variable mathematically, multiply formula with 4 as shown below.

Code:

Sub

VBA_Constants()

Dim

A

As Integer

A = 123

Const

B

As Double

= 4

Dim

C

As Integer

C = A * B MsgBox C

End Sub

Step 12: Now again compile the code and run it.

We have got the same result in variable C as 492 which is the multiplication output of 123 and 4.

Step 13: For more test, let’s change the value stored in variable A from 123 to let’s say 321 as shown below.

Code:

Sub

VBA_Constants()

Dim

A

As Integer

A = 321

Const

B

As Double

= 4

Dim

C

As Integer

C = A * B MsgBox C

End Sub

Step 14: Now if we run the code we should be getting the multiplication of 321 and 4 in a message box.

We will see, the message box with the output as 1284, which is the actual multiplication of 321 and 4. This means that value stored in Const variable B is still constant as both the time it has multiplied the variable A with 4.

Excel VBA Constants – Example #2

In another example of VBA Constant how fixing all the variables as Constant works. Follow the below steps to use Excel VBA Constants.

Code:

Sub

VBA_Constants2()

End Sub

Step 2: Now define a Constant A as String and give it any text as per your choice. Let’s say that text in Constant as shown below.

Code:

Sub

VBA_Constants2()

Const

A

As String

= "Constant"

End Sub

Step 3: Now in the second line, again define another Constant B as Integer and give it any number as shown below. Let’s say that number is 10.

Code:

Sub

VBA_Constants2()

Const

A

As String

= "Constant"

Const

B

As Integer

= 10

End Sub

Step 4: In a simple way, let’s print a text as “The real constant is 10” with the help of MsgBox as shown below.

Code:

Sub

VBA_Constants2()

Const

A

As String

= "Constant"

Const

B

As Integer

= 10 MsgBox "The real " & A & " is " & B

End Sub

The text which we have written above can be anything.

Step 5: Now compile the code and run it, if found no error. We will get the message box as “The real Constant is 10” which we set above.

As our values are constant for A and B, so we can use these anywhere and anytime. And each time when we would call them values of Constant A and B, we will get the same values stored in this subcategory.

Pros of Excel VBA Constants

This saves a huge amount of time for fixing one or more variables as Constant.

The number of lines of code gets reduced.

We just need to enter the values in defined Constants once, and then whenever we will call that constant, the value stored in it will come up.

Cons of Excel VBA Constants

It is not always used as sometimes we need to come back multiple times to change the values stored in Constants if we are using these defined constants in different Subcategories or Class.

Things to Remember

Results obtained from Constants and Variables are the same. The difference is once Constants are defined, it can be used anywhere multiple times. But Variables are defined for each subcategory.

If there is a change in values which we call and stored constants then we may end up getting an error. So, it is better to check the values first which are fixed as constant.

Saving the file as a macro-enabled excel format helps us to retain the code for the future.

It is always recommended to use Constant when we are working on creating Class objects. Constant is shorter as compared to Variables, so it is a huge set of codes it will take lesser space.

Recommended Articles

This is a guide to VBA Constants. Here we discuss the different examples of Constants in Excel VBA along with some practical examples and downloadable excel template. You can also go through our other suggested articles –

You're reading Different Examples Of Contents In Excel Vba

## Different Examples Of Filter In Excel Vba

VBA Filter in Excel

It is very easy to apply the filter through just by pressing Alt + D + F + F simultaneously or Shift + Ctrl + L together. We can even go to the Data menu tab and select the Filter option there. But what if I say there is a much cooler way to use Filter using VBA Codes. Although applying the filter in any data is very easy manually but if we have a huge set of data where we need to apply the filter. Doing manually will take huge time to execute but doing this using Filter in Excel VBA code, we can make it much easier.

Watch our Demo Courses and Videos

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

Syntax of VBA Filter:

Where, Range = This is the range of cells where we need to apply Auto Filter. This can be a single cell or range of cells in a row.

Field: This is a sequence number of the column number from there our range is getting started.

Criteria: Here we can quote our criteria which we want to filter from the selected field.

Operator: This is optional if we have only one criteria. But for Criteria2 we use xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlTop10Percent, xlBottom10Percent such keys to filter the data.

How to Apply Filter using VBA (Examples)

Below are the different examples of Filter in Excel VBA:

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

Example #1

We have some sales data in Sheet1 as shown below. As we can see, we have the data in multiple columns which is perfect to show how VBA Filter works. For this, follow the below steps:

Step 2: Now write the subprocedure for VBA Filter.

Code:

Sub VBA_Filter()

Step 3: Select the worksheet which has the data. Here our data is in Sheet1.

Code:

Sub VBA_Filter()

Worksheets(“Sheet1”).

End Sub

Step 4: And after select the column as Range which we want to filer followed by AutoFilter function.

Code:

Sub VBA_Filter()

Worksheets(“Sheet1”).Range(“G1”).AutoFilter

End Sub

Step 5: Now run the code. We will see the complete row got the filter dropdown. Even if we select a cell, auto filter will be applied to the complete row.

Example #2

If we use the proper syntax of VBA Filter, we can filter the data as we do manually. Let’s filter the data with the Owner’s name as Ben and see what we get. For this, follow the below steps:

Step 1: Defining the subprocedure for VBA Filter.

Code:

Sub VBA_Filter2()

End Sub

Step 2: Select the worksheet which has the data. Here again, the sheet is Sheet1. And then select the column name as Range which we want to filter. Here the Owner name column is at G1 position.

Code:

Sub VBA_Filter2()

Worksheets(“Sheet1”).Range(“G1”).

End Sub

Step 3: Now we will use the AutoFilter command to apply the filter. Then select the Field number which is at 7th position and Criteria as Ben.

Code:

Sub VBA_Filter2()

Worksheets(“Sheet1”).Range(“G1″).AutoFilter Field:=7, Criteria1:=”Ben”

End Sub

Step 4: Compile the code by hitting F5 or the Run button and run it. We will see, the filer is now applied to Row1 at cell G1. And as we can see, the dot in the G1 cell filter shows the data is filtered.

Code:

Sub VBA_Filter2()

Worksheets(“Sheet1”).Range(“A1:J1″).AutoFilter Field:=7, Criteria1:=”Ben”

End Sub

Step 7: What if we choose an Operator here to apply multiple filters in the same selected field? For this, in the same line of code, add Operator xlOR. This will help us to apply more than one criteria.

Code:

Sub VBA_Filter2()

Worksheets(“Sheet1”).Range(“A1:J1″).AutoFilter Field:=7, Criteria1:=”Ben”, Operator:=xlOr,

End Sub

Step 8: Now, at last, select another criterion which is Criteria2. Let’s say that criteria be John.

Code:

Sub VBA_Filter2()

Worksheets(“Sheet1”).Range(“A1:J1″).AutoFilter Field:=7, Criteria1:=”Ben”, Operator:=xlOr, Criteria2:=”John”

End Sub

Step 9: Now run the code again. We will see, in the drop-down option at cell G1, both the owner’s name are filtered. One is BEN and the other is JOHN.

Example #3

There is another way to filter the data with more than 1 criteria in different columns. We will use With-End With Loop to execute this. For this, follow the below steps:

Step 1: Write the subprocedure.

Code:

Sub VBA_Filter3()

End Sub

Step 2: Select the Range where we need to apply filter. Here our range is from cell A1:J1.

Sub VBA_Filter3()

With Range(“A1:J1”)

End Sub

Step 3: In the next line of code, use AutoFilter function and select the Fields and Criteria as required. Here Field will be 7 and Criteria will be BEN.

Code:

Sub VBA_Filter3()

.AutoFilter Field:=7, Criteria1:=”Ben”

End Sub

Step 4: In the second line of code, we will select another cell of headers to be filtered. Let’s filter the Quantity column with the values greater than 50 for the Owner’s name BEN.

Code:

Sub VBA_Filter3()

End Sub

Step 5: End the loop with End With.

Code:

Sub VBA_Filter3()

End With

End Sub

Step 6: Run the Code by hitting F5 or the Run button. we will see field number 7 and 9 both got the filtered.

Step 7: And if we check in Column I of header Quantity, we will see, the quantities filtered are greater than 50.

Pros of VBA Filter

It is very easy to apply.

We can filter as many headers as we want.

File with huge data set can easily be filtered using VBA Filter.

VBA Autofilter can speed things up and save time.

Things to Remember

We can select one cell or a line for Range. But the filter will be applied to the complete range of header which has data.

Use with operation, if you want to filter the data with more than 1 column.

The field section in the syntax of VBA Filter can only contain the number which is the sequence of the required column.

Always mention the names into inverted quotes.

Save the file in Macro Enabled Excel format to preserve the applied code.

Recommended Articles

This is a guide to VBA Filter. Here we discuss some useful examples of VBA Filter code in Excel along with a downloadable excel template. You can also go through our other suggested articles –

## Various Examples Of Hlookup In Excel

Examples of HLOOKUP

Hlookup is used for mapping and looking up the values that are horizontally filled. Similar to Vlookup, Hlookup is also having the same syntax. Below are the different Hlookup examples in Excel.

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

HLOOKUP Example in Excel – Illustration #1

Here we have sample data of some customers who bought some products on different dates of a month, and the data is horizontal.

Now we will test Hlookup for the data shown in the above table, and the location of the output cell will be B7 which is marked below with the heading Quantity.

Before we apply Hlookup, first, let’s see the syntax of Hlookup as shown below.

Lookup_value: It is the fixed reference lookup cell for which we need to find the value.

Table_array: It is that table from which we need to find the value.

Row_index_num: Exact sequence of a row for looking up the value.

[Range_lookup]: Here, we will fix an exact or approximate match as per data type.

Now let’s use Hlookup in excel. For this, go to that cell, where we need to find the value. Here we are choosing cell B7.

Type “=” (Equal) sign. By this, all the inbuilt functions in excel will get enabled. Now select Hlookup.

Lookup_Value = Select the reference cell A7.

Table_array = Select table array B1:K3, which covers the complete table.

Row_index_num = Select row 2, by typing 2.

[Range_lookup] = Select FALSE or “0” for an exact match as our data is quite simple to understand.

As we can see in the above screenshot, how our applied Hlookup final formula will look like. The red color table is our table array, the Blue color cell is our lookup value, and 2 is row 2 from the selected table array.

As we can see in the above screenshot, how our applied Hlookup final formula will look like. The red color table is our table array, the Blue color cell is our lookup value, and 2 is row 2 from the selected table array.

Once we press Enter, we will get the applied formula as shown below.

#N/A in the above screenshot can be seen because no reference value is filled in cell A7. But that doesn’t mean that the applied formula is incorrect. The correctness of the formula can be measured once we enter some value from the table array to cell A7. Now fill cell A7 with any of the table_array value of the same header name Customer Name. Let’s enter Customer 4 here and cell the result.

As we can see in the above screenshot, after entering Customer 4 in A7, applied Hlookup mapped the value from the selected table array of the reference cell to give the value like 23 in B7. This is how Hlookup works.

HLOOKUP Example in Excel – Illustration #2

Now to test if the formula is applied or not, enter any customer name in cell A7. For testing, let’s enter Customer 5 in cell A7. Once we do this, the value of Customer 5 from the table array will get looked up with the same header Customer Name as 55.

As we got the looked-up value like 55 in cell B7, which means that our applied formula is correct.

Things to Remember

If the Lookup Value is not the exact value in the data table, we will get a # N/A error.

The structure of the data table is very important. If the data table is horizontal, HLOOKUP should be applied, and VLOOKUP should be applied if the table is vertical.

Like VLOOKUP, HLOOKUP also limits the collection of data from top to bottom and not from bottom to top.

Recommended Articles

This has been a guide to HLOOKUP Examples in excel. Here we discuss various HLOOKUP Examples in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

## How To Use Vba On Error Statement In Excel?

VBA On Error Statements

VBA On Error is an easy method for handling unexpected exceptions in Excel chúng tôi is known that we cannot write code without any error. Sometimes writing big code may give us an error even at the time of compiling. To avoid this kind of situation, we add an Error Message which, instead of giving us the right answer or error code it will show us the message with the error code. That would look like we got the output of our calculation, but it is the error code that will get imprinted.

Watch our Demo Courses and Videos

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

How to Use Excel VBA On Error Statements in Excel?

There are 3 ways of Error in VBA. Let’s understand different ways with some examples.

Example #1

The first error type is a Code compilation error which comes when a code is undeclared or impossible variables. To understand more, we will use a simple mathematical expression of the divide. For this, go to the Insert menu of VBA and select Module as shown below.

Now open Subcategory and add any name as we are using On Error, so we have named it as same.

Sub

OnError()

End Sub

Now define any 2 or 3 Integers. Here we have taking X and Y as Integers.

Sub

OnError()

Dim

X

As Integer

, Y

As Integer

, Z

As Integer

End Sub

Now, as discussed above, we will calculate division mathematical expression. For X, we will put a character in Numerator and divide it 0. And Y will be 20/2, which is complete numbers.

Sub

OnError()

Dim

X

As Integer

, Y

As Integer

X = Test / 0 Y = 20 / 2

End Sub

Now to overrule this error, we will add one line On Error Resume Next before we write the mathematical code. It will jump the error code, but we will not able to see the outcome of the second mathematical code. This only hides the error message of various codes lines, as shown below. Now try to run the code as well.

Sub

OnError()

Dim

X

As Integer

, Y

As Integer

On Error Resume Next

X = Test / 0 Y = 20 / 2 MsgBox X MsgBox Y

End Sub

Now to overrule this error, we will add one line On Error Resume Next before we write the mathematical code. It will jump the error code, but we will not able to see the outcome of the second mathematical code. This only hides the error message of various codes lines, as shown below. Now try to run the code as well.

Example #2

In this example, we will consider that mathematical division which gives infinite result, but in coding, it will #DIV/0 result. To demonstrate this, we will consider one more integer Z along with X and Y in a subcategory, as shown below.

Sub

OnError()

Dim

X

As Integer

, Y

As Integer

, Z

As Integer

End Sub

Now frame all the integers X, Y, and Z with a mathematical expression of divide and to print it use MsgBox function in VBA of each integer’s result.

Below for Integer X, we have divided 10 by 0, 20 by 2 and 30 by 4.

Sub

OnError()

Dim

X

As Integer

, Y

As Integer

, Z

As Integer

X = 10 / 0 Y = 20 / 2 Z = 30 / 4 MsgBox X MsgBox Y MsgBox Z

End Sub

Now run the code using the F5 key or manually, as shown below.

As we can see in the above screenshot, Run-time error 11, which means the error is related to the number. Now to overcome this, add one line On Error Resume Next before mathematical expression as shown below.

Sub

OnError()

Dim

X

As Integer

, Y

As Integer

, Z

As Integer

On Error Resume Next

X = 10 / 0 Y = 20 / 2 Z = 30 / 4 MsgBox X MsgBox Y MsgBox Z

End Sub

Example #3

Sub

OnError()

Dim

X

As Integer

, Y

As Integer

, Z

As Integer

End Sub

Now also consider the same mathematical division which we have seen in the above example.

Sub

OnError()

Dim

X

As Integer

, Y

As Integer

, Z

As Integer

X = 10 / 0 Y = 20 / 2 Z = 30 / 4 MsgBox X MsgBox Y MsgBox Z

End Sub

If we run the code, we will get the same error message of Run-time error 11.

Now to overrule this error, use text On Error GoTo with the word “ “Result to skip the error message and get the output which works fine, as shown below.

Sub

OnError()

Dim

X

As Integer

, Y

As Integer

, Z

As Integer

On Error GoTo

ZResult: X = 10 / 0 Y = 20 / 2 ZResult: Z = 30 / 4 MsgBox X MsgBox Y MsgBox Z

End Sub

Now run the code again. We will get the same result as the previous example.

On Error GoTo ZResult helps us to directly jump of mentioned result point integer as we did for integer Z.

Example #4

In the third type of error, when we run the code and VBA is not able to understand the line of code. This can be done with the help of code On Error Resume Next along with MsgBox Err.Number. Consider the same data as used in the above examples. We will again see the same 3 integers X, Y, and Z, as shown below.

Sub

OnError()

Dim

X

As Integer

, Y

As Integer

, Z

As Integer

End Sub

Sub

OnError()

Dim

X

As Integer

, Y

As Integer

, Z

As Integer

X = 10 / 0 Y = 20 / 2 Z = 30 / 4 MsgBox X MsgBox Y MsgBox Z

End Sub

Now, if we run the complete code, then we will get an error message of mathematical error Run time error 11.

Now to overrule this error, we will use On Error Resume Next.

Sub

OnError()

Dim

X

As Integer

, Y

As Integer

, Z

As Integer

On Error Resume Next

X = 10 / 0 Y = 20 / 2 Z = 30 / 4 MsgBox X MsgBox Y MsgBox Z

End Sub

And run the code. This will give a use result on a valid mathematical line, as shown below.

Now further add ZResult code line before Z integer division mathematical expression and add MsgBox Err.Number code line at the end of code as shown below.

Sub

OnError()

Dim

X

As Integer

, Y

As Integer

, Z

As Integer

On Error Resume Next

X = 10 / 0 Y = 20 / 2 ZResult: Z = 30 / 4 MsgBox X MsgBox Y MsgBox Z MsgBox Err.Number

End Sub

Now run the code by using the F5 key or by pressing the play button, as shown below.

As we can see in the above screenshots. The first message box has 0, which is overruling of incorrect mathematical expression. 2nd and 3rd have a division result of Y and Z integers. And last message box has run time error code 11, which is probably the error code of X integer’s division expressions.

Pros of VBA On Error

We can calculate any mathematical formula even if it is incorrect.

For bigger coding structures where there are chances of having an error, using these methods may give correct result even among the line of codes.

This gives a better result as compared to the result obtained from normal excel calculations.

Things to Remember

Always save the file in a Macro-Enabled Excel file so that we can use created VBA code many and multiple times.

Always compile the written code before implementing with any excel requirement.

You can download this VBA On Error Excel Template here – VBA On Error Excel Template.

Recommended Articles

This has been a guide to Excel VBA On Error. Here we discussed how to use VBA On Error Statement along with some practical examples and a downloadable excel template. You can also go through our other suggested articles–

## Rank In Excel (Formula, Examples)

RANK in Excel

The rank function in Excel is used for finding the best sequence position of any selected cell from the given hierarchy or range, which only applies to numbers. And it is because Rank can only be measured in numbers. If we have 5 numbers and want to find the rank (or position) of any number, we simply need to select the range and then choose the order in which rank we want.

RANK Formula in Excel:

Below is the RANK Formula in Excel:

Explanation of RANK Function in Excel

RANK Formula in Excel includes two mandatory arguments and one optional argument.

Number: This is the value or number we want to find the rank.

Ref: This is the list of numbers in a range or in an array you want your “Number” compared to.

[Order]: Whether you want your ranking in Ascending or Descending order. Type 0 for descending and type 1 for ascending order.

Ranking products, people, or services can help you to compare one against another. The best thing is we can see which is at the top, at the average level, and at the bottom.

We can analyze each one of them based on the rank given. If the product or service is at the bottom level, we can study that particular product or service, find the root cause for its poor performance, and take necessary action against it.

Three Different Types of RANK Functions in Excel

If you start typing the RANK function in Excel, it will show you 3 types of RANK functions.

chúng tôi

chúng tôi

RANK

In Excel 2007 and earlier versions, only the RANK Function was available. However, later on, a RANK Function has been replaced by a chúng tôi and chúng tôi functions.

Though the RANK Function still works in recent versions, it may not be available in future versions.

How to Use the RANK Function in Excel?

The RANK Function in Excel is very simple and easy to use. Let us understand the working of the RANK Function in Excel by a RANK Formula example.

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

Example #1

I have 12 teams that recently participated in the Kabaddi tournament and have team names and their total points in the first two columns.

I had to rank each team when I compared it to other teams on the list.

Since RANK works only for compatibility with earlier versions, I am using chúng tôi function instead of a RANK Function here.

Note: Both work the same way.

Apply the chúng tôi function in cell C2.

So the output will be :

Note: I have not mentioned the order reference. Therefore, excel, by default, ranks in descending order.

=RANK.EQ (B2, \$B\$2:\$B\$13) returned a number (rank) of 12. In this list, I have a total of 12 teams. This team scored 12 points, the lowest among all the 12 teams we have considered. Therefore, the formula ranked it as 12, i.e. the last rank.

=RANK.EQ (B3, \$B\$2:\$B\$13) returned a number (rank) of 1. This team scored 105 points, the highest among all the 12 teams we have considered. Therefore, the formula ranked it as 1, i.e. first rank.

This is how RANK or chúng tôi function helps us find out each team’s rank when we compare against each other in the same group.

Example #2

One common problem with chúng tôi function is if there are two same values, then it gives the same ranking to both the values.

Consider the below data for this example. I have a batsmen’s name and their career average data.

Apply the chúng tôi function in cell C2, and the formula should be like the below one.

=RANK.EQ(B2,\$B\$2:\$B\$6)

So the output will be :

If I apply a RANK formula to this data, Sachin and Dravid get the rank 1.

If the formula finds two duplicate values, it must show 1 for the first-ever value found and the next for the other number.

There are many ways we can find the unique ranks in these cases. In this example, I am using chúng tôi with COUNTIF function.

So the output will be :

The formula I have used here is

=RANK.EQ (B2, \$B\$2:\$B\$6) will find the rank for this set.

COUNTIF (\$B\$2:B2, B2) – 1. The COUNTIF formula will do the magic here. For the first cell, I have mentioned \$B\$2:B2 means at this range, what is the total count of the B2 value, then deduct that value from 1.

The first RANK returns 1, and COUNTIF returns 1, but since we mentioned -1, it becomes zero; therefore, 1+0 = 1. For Sachin, RANK remains 1.

For Dravid, we got the Rank of 2. Here RANK returns 1, but COUNTIF returns 2, but since we mentioned -1, it becomes 1; therefore, 1 + 1 = 2. The rank for Dravid is 2, not 1.

This is how we can get unique ranks in case of duplicate values.

Things to Remember

RANK replaces a RANK chúng tôi in 2010 and later versions.

A RANK Function in Excel can accept only numerical values. Anything other than numerical values, we will get an error as #VALUE!

If the number you are testing is not present in the list of numbers, we will get #N/A! Error.

The RANK function in Excel gives the same ranking in the case of duplicate values. Anyhow, we can get unique ranks by using the COUNTIF function.

Data need not sort in ascending or descending order to get the results.

Recommended Articles

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

## Excel Vba Events – An Easy (And Complete) Guide

When you create or record a macro in Excel, you need to run the macro to execute the steps in the code.

A few ways of running a macro includes using the macro dialog box, assigning the macro to a button, using a shortcut, etc.

Apart from these user-initiated macro executions, you can also use VBA events to run the macro.

Let me first explain what is an event in VBA.

An event is an action that can trigger the execution of the specified macro.

There are many such events in VBA, and you can create codes for these events. This means that as soon as an event occurs, and if you have specified a code for that event, that code would instantly be executed.

Excel automatically does this as soon as it notices that an event has taken place. So you only need to write the code and place it in the correct event subroutine (this is covered later in this article).

For example, if you insert a new worksheet and you want it to have a year prefix, you can write the code for it.

Now, whenever anyone inserts a new worksheet, this code would automatically be executed and add the year prefix to the worksheet’s name.

Similarly, you can create VBA codes for many such events (as we will see later in this article).

While the above example is a useless event, I hope it helps you understand what events really are.

There are different objects in Excel – such as Excel itself (to which we often refer to as the application), workbooks, worksheets, charts, etc.

Each of these objects can have various events associated with it. For example:

If you create a new workbook, it’s an application level event.

If you add a new worksheet, it’s a workbook level event.

If you change the value in a cell in a sheet, it’s a worksheet level event.

Below are the different types of Events that exist in Excel:

Workbook Level Events: These events would be triggered based on the actions at the workbook level. Examples of these events include adding a new worksheet, saving the workbook, opening the workbook, printing a part or the entire workbook, etc.

Application Level Events: These are the events that occur in the Excel application. Example of these would include closing any of the open workbooks or opening a new workbook.

Chart Events: These are events related to the chart sheet. A chart sheet is different than a worksheet (which is where most of us are used to work in Excel). A chart sheets purpose is to hold a chart. Examples of such events would include changing the series of the chart or resizing the chart.

OnTime and OnKey Events: These are two events that don’t fit in any of the above categories. So I have listed these separately. ‘OnTime’ event allows you to execute a code at a specific time or after a specific time has elapsed. ‘OnKey’ event allows you to execute a code when a specific keystroke (or a combination of keystrokes) is used.

In the above section, I covered the different types of events.

Based on the type of event, you need to put the code in the relevant object.

For example, if it’s a worksheet related event, it should go in the code window of the worksheet object. If it’s workbook related, it should go in the code window for a workbook object.

In VBA, different objects – such as Worksheets, Workbooks, Chart Sheets, UserForms, etc., have their own code windows. You need to put the event code in the relevant object’s code window. For example – if it’s a workbook level event, then you need to have the event code in the Workbook code window.

The following sections cover the places where you can put the event code:

When you open the VB Editor (using keyboard shortcut ALT + F11), you would notice the worksheets object in the Project Explorer. For each worksheet in the workbook, you will see one object.

While you can start writing the code from scratch, it’s much better to select the event from a list of options and let VBA automatically insert the relevant code for the selected event.

To do this, you need to first select worksheet from the drop down at the top-left of the code window.

After selecting worksheet from the drop down, you get a list of all the events related to the worksheet. You can select the one you want to use from the drop-down at the top right of the code window.

As soon as you select the event, it would automatically enter the first and last line of the code for the selected event. Now you can add your code in between the two lines.

Note: As soon as you select Worksheet from the drop-down, you would notice two lines of code appear in the code window. Once you have selected the event for which you want the code, you can delete the lines that appeared by default.

Note that each worksheet has a code window of its own. When you put the code for Sheet1, it will only work if the event happens in Sheet1.

Just like worksheets, if you have a workbook level event code, you can place it in ThisWorkbook code window.

You need to select Workbook from the drop-down at the top-left of the code window.

After selecting Workbook from the drop down, you get a list of all the events related to the Workbook. You can select the one you want to use from the drop-down at the top right of the code window.

As soon as you select the event, it would automatically enter the first and last line of the code for the selected event. Now you can add your code in between the two lines.

Note: As soon as you select Workbook from the drop-down, you would notice two lines of code appear in the code window. Once you have selected the event for which you want the code, you can delete the lines that appeared by default.

While the Sheet objects and ThisWorkbook objects are already available when you open the VB Editor, UserForm is something you need to create first.

This would insert a UserForm object in the workbook.

Now just like worksheets or ThisWorkbook, you can select the event and it will insert the first and the last line for that event. And then you can add the code in the middle of it.

In Excel, you can also insert Chart sheets (which are different then worksheets). A chart sheet is meant to contain charts only.

When you have inserted a chart sheet, you will be able to see the Chart sheet object in the VB Editor.

You can add the event code to the chart sheet code window just like we did in the worksheet.

Now, you need to select Chart from the drop-down at the top-left of the code window.

After selecting Chart from the drop-down, you get a list of all the events related to the Chart sheet. You can select the one you want to use from the drop-down at the top right of the code window.

Note: As soon as you select Chart from the drop-down, you would notice two lines of code appear in the code window. Once you have selected the event for which you want the code, you can delete the lines that appeared by default.

Class Modules need to be inserted just like UserForms.

A class module can hold code related to the application – which would be Excel itself, and the embedded charts.

I will cover the class module as a separate tutorial in the coming weeks.

Note that apart from OnTime and OnKey events, none of the above events can be stored in the regular VBA module.

When you trigger an event, it doesn’t happen in isolation. It may also lead to a sequence of multiple triggers.

For example, when you insert a new worksheet, the following things happen:

The previous worksheet gets deactivated

The new worksheet gets activated

While in most cases, you may not need to worry about the sequence, if you’re creating complex codes that rely on events, it’s better to know the sequence to avoid unexpected results.

Before we jump to Event examples and the awesome things you can do with it, there is one important concept I need to cover.

In VBA events, there would be two types of codes:

Without any arguments

With arguments

And in this section, I want to quickly cover the role of arguments.

Below is a code that has no argument in it (the parenthesis are empty):

Private Sub Workbook_Open() MsgBox "Remember to Fill the Timesheet" End Sub

With the above code, when you open a workbook, it simply shows a message box with the message – “Remember to fill the Timesheet”.

Now let’s have a look at a code that has an argument.

Private Sub Workbook_NewSheet(ByVal Sh As Object) Sh.Range("A1") = Sh.Name End Sub

The above code uses the Sh argument which is defined as an object type. The Sh argument could be a worksheet or a chart sheet, as the above event is triggered when a new sheet is added.

By assigning the new sheet that is added to the workbook to the object variable Sh, VBA has enabled us to use it in the code. So to refer to the new sheet name, I can use Sh.Name.

The concept of arguments will be useful when you go through the VBA events examples in the next sections.

Following are the most commonly used events in a workbook.

EVENT NAME WHAT TRIGGERS THE EVENT

Activate When a workbook is activated

AfterSave When a workbook is installed as an add-in

BeforeSave When a workbook is saved

BeforeClose When a workbook is closed

BeforePrint When a workbook is printed

Deactivate When a workbook is deactivated

NewSheet When a new sheet is added

Open When a workbook is opened

SheetActivate When any sheet in the workbook is activated

SheetBeforeDelete When any sheet is deleted

SheetCalculate When any sheet is calculated or recalculated

SheetDeactivate When a workbook is deactivated

SheetPivotTableUpdate When a workbook is updated

SheetSelectionChange When a workbook is changed

WindowActivate When a workbook is activated

WindowDeactivate When a workbook is deactivated

Note that this is not a complete list. You can find the complete list here.

Remember that the code for Workbook event is stored in the ThisWorkbook objects code window.

Now let’s have a look at some useful workbook events and see how these can be used in your day-to-day work.

Let’s say that you want to show the user a friendly reminder to fill their timesheets whenever they open a specific workbook.

You can use the below code to do this:

Private Sub Workbook_Open() MsgBox "Remember to Fill the Timesheet" End Sub

Now as soon as you open the workbook that has this code, it will show you a message box with the specified message.

There are a few things to know when working with this code (or Workbook Event codes in general):

If a workbook has a macro and you want to save it, you need to save it in the .XLSM format. Else the macro code would be lost.

In the above example, the event code would be executed only when the macros are enabled. You may see a yellow bar asking for permission to enable macros. Until that is enabled, the event code is not executed.

The Workbook event code is placed in the code window of ThisWorkbook object.

You can further refine this code and show the message only of Friday.

The below code would do this:

Private Sub Workbook_Open() wkday = Weekday(Date) If wkday = 6 Then MsgBox "Remember to Fill the Timesheet" End Sub

Note that in the Weekday function, Sunday is assigned the value 1, Monday is 2 and so on.

Hence for Friday, I have used 6.

Workbook Open event can be useful in many situations, such as:

When you want to show a welcome message to the person when a workbook is opened.

When you want to display a reminder when the workbook is opened.

When you want to always activate one specific worksheet in the workbook when it’s opened.

When you want to open related files along with the workbook.

When you want to capture the date and time stamp every time the workbook is opened.

NewSheet event is triggered when you insert a new sheet in the workbook.

Let’s say that you want to enter the date and time value in cell A1 of the newly inserted sheet. You can use the below code to do this:

Private Sub Workbook_NewSheet(ByVal Sh As Object) On Error Resume Next Sh.Range("A1") = Format(Now, "dd-mmm-yyyy hh:mm:ss") End Sub

The above code uses ‘On Error Resume Next’ to handle cases where someone inserts a chart sheet and not a worksheet. Since chart sheet doesn’t have cell A1, it would show an error if ‘On Error Resume Next’ is not used.

Another example could be when you want to apply some basic setting or formatting to a new sheet as soon as it is added. For example, if you want to add a new sheet and want it to automatically get a serial number (up to 100), then you can use the code below.

Private Sub Workbook_NewSheet(ByVal Sh As Object) On Error Resume Next With Sh.Range("A1") .Value = "S. No." .Interior.Color = vbBlue .Font.Color = vbWhite End With For i = 1 To 100 Sh.Range("A1").Offset(i, 0).Value = i Next i Sh.Range("A1", Range("A1").End(xlDown)).Borders.LineStyle = xlContinuous End Sub

The above code also does a bit of formatting. It gives the header cell a blue color and makes the font white. It also applies a border to all the filled cells.

The above code is an example of how a short VBA code can help you steal a few seconds every time you insert a new worksheet (in case this is something that you have to do every time).

Before Save event is triggered when you save a workbook. Note that the event is triggered first and then the workbook is saved.

When saving an Excel workbook, there could be two possible scenarios:

You’re saving it for the first time and it will show the Save As dialog box.

You’ve already saved it earlier and it will simply save and overwrite the changes in the already saved version.

Now let’s have a look at a few examples where you can use the BeforeSave event.

Suppose you have a new workbook that you’re saving for the first time, and you want to remind the user to save it in the K drive, then you can use the below code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If SaveAsUI Then MsgBox "Save this File in the K Drive" End Sub

In the above code, if the file has never been saved, SaveAsUI is True and brings up the Save As dialog box. The above code would display the message before the Save As dialog box appear.

Another example could be to update the date and time when the file is saved in a specific cell.

The below code would insert the date & time stamp in cell A1 of Sheet1 whenever the file is saved.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Worksheets("Sheet1").Range("A1") = Format(Now, "dd-mmm-yyyy hh:mm:ss") End Sub

Note that this code is executed as soon as the user saves the workbook. If the workbook is being saved for the first time, it will show a Save As dialog box. But the code is already executed by the time you see the Save As dialog box. At this point, if you decide to cancel and not save the workbook, the date and time would already be entered in the cell.

Before Close event happens right before the workbook is closed.

The below code protects all the worksheets before the workbook is closed.

Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim sh As Worksheet For Each sh In ThisWorkbook.Worksheets sh.Protect Next sh End Sub

Remember that the event code is triggered as soon as you close the workbook.

One important thing to know about this event is that it doesn’t care whether the workbook is actually closed or not.

When you give the print command (or Print Preview command), the Before Print event is triggered.

The below code would recalculate all the worksheets before your workbook is printed.

Private

Sub

Workbook_BeforePrint(Cancel

As

Boolean

)

For

Each

ws

in

Worksheets ws.Calculate

Next

ws

End

Sub

When the user is printing the workbook, the event would be fired whether he/she is printing the entire workbook or only a part of it.

Another example below is of the code that would add the date and time to the footer when the workbook is printed.

Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.PageSetup.LeftFooter = "Printed On - " & Format(Now, "dd-mmm-yyyy hh:mm") Next ws End Sub

Worksheet events take place based on the triggers in the worksheet.

Following are the most commonly used events in a worksheet.

Event Name What triggers the event

Activate When the worksheet is activated

BeforeDelete Before the worksheet is deleted

Calculate Before the worksheet is calculated or recalculated

Change When the cells in the worksheet are changed

Deactivate When the worksheet is deactivated

PivotTableUpdate When the Pivot Table in the worksheet is updated

SelectionChange When the selection on the worksheet is changed

Note that this is not a complete list. You can find the complete list here.

Remember that the code for Worksheet event is stored in the worksheet object code window (in the one in which you want the event to be triggered). There can be multiple worksheets in a workbook, and your code would be fired only when the event takes place in the worksheet in which it is placed.

Now let’s have a look at some useful worksheet events and see how these can be used in your day-to-day work.

This event is fired when you activate a worksheet.

The below code unprotects a sheet as soon as it is activated.

Private Sub Worksheet_Activate() ActiveSheet.Unprotect End Sub

You can also use this event to make sure a specific cell or a range of cells (or a named range) is selected as soon as you activate the worksheet. The below code would select cell D1 as soon as you activate the sheet.

Private Sub Worksheet_Activate() ActiveSheet.Range("D1").Select End Sub

A change event is fired whenever you make a change in the worksheet.

Well.. not always.

There are some changes that trigger the event, and some that don’t. Here is a list of some changes that won’t trigger the event:

When you change the formatting of the cell (font size, color, border, etc.).

When you merge cells. This is surprising as sometimes, merging cells also removes content from all the cells except the top-left one.

When you sort a range of cells.

When you use Goal Seek.

The following changes would trigger the event (even though you may think it shouldn’t):

Copy and pasting formatting would trigger the event.

Clearing formatting would trigger the event.

Running a spell check would trigger the event.

Below is a code would show a message box with the address of the cell that has been changed.

Private Sub Worksheet_Change(ByVal Target As Range) MsgBox "You just changed " & Target.Address End Sub

While this is a useless macro, it does show you how to use the Target argument to find out what cells have been changed.

Now let’s see a couple of more useful examples.

Suppose you have a range of cells (let’s say A1:D10) and you want to show a prompt and ask the user if they really wanted to change a cell in this range or not, you can use the below code.

It shows a prompt with two buttons – Yes and No. If the user selects ‘Yes’, the change is done, else it is reversed.

Private Sub Worksheet_Change(ByVal Target As Range) If chúng tôi <= 10 And Target.Column <= 4 Then Ans = MsgBox("You are making a change in cells in A1:D10. Are you sure you want it?", vbYesNo) End If If Ans = vbNo Then Application.EnableEvents = False Application.Undo Application.EnableEvents = True End If End Sub

In the above code, we check whether the Target cell is in first 4 columns and the first 10 rows. If that’s the case, the message box is shown. Also, if the user selected No in the message box, the change is reversed (by the chúng tôi command).

Note that I have used Application.EnableEvents = False before the Application.Undo line. And then I reversed it by using Application.EnableEvent = True in the next line.

This is needed as when the undo happens, it also triggers the change event. If I don’t set the EnableEvent to False, it will keep on triggering the change event.

You can also monitor the changes to a named range using the change event. For example, if you have a named range called “DataRange” and you want to show a prompt in case user makes a change in this named range, you can use the code below:

Private Sub Worksheet_Change(ByVal Target As Range) Dim DRange As Range Set DRange = Range("DataRange") If Not Intersect(Target, DRange) Is Nothing Then MsgBox "You just made a change to the Data Range" End If End Sub

The above code checks whether the cell/range where you have made the changes has any cells common to the Data Range. If it does, it shows the message box.

The selection change event is triggered whenever there is a selection change in the worksheet.

The below code would recalculate the sheet as soon as you change the selection.

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.Calculate End Sub

Another example of this event is when you want to highlight the active row and column of the selected cell.

Something as shown below:

The following code can do this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells.Interior.ColorIndex = xlNone With ActiveCell .EntireRow.Interior.Color = RGB(248, 203, 173) .EntireColumn.Interior.Color = RGB(180, 198, 231) End With End Sub

The code first removes the background color from all the cells and then apply the one mentioned in the code to the active row and column.

And that’s the problem with this code. That it removes color from all cells.

If you want to highlight the active row/column while keeping the color in other cells intact, use the technique shown in this tutorial.

This is one of my favorite worksheet events and you’ll see a lot of tutorials where I have used this (such as this one or this one).

Let me show you how awesome this is.

Cancel = True With Target .Interior.Color = vbBlue .Font.Color = vbWhite .Font.Bold = True End With End Sub

Note that in the above code, I have made the value of Cancel = True.

Here is another example.

Something as shown below:

Here is the code that will do this:

Cancel = True CurrFormat = Target.Font.Strikethrough If CurrFormat Then Target.Font.Strikethrough = False Else Target.Font.Strikethrough = True End If End Sub

The events that we have seen so far in this article were associated with one of the Excel objects, be it the workbook, worksheet, chart sheet, or UserForms, etc.

OnTime event is different than other events as it can be stored in the regular VBA module (while the others were to be placed in the code window of objects such as ThisWorkbook or Worksheets or UserForms).

Within the regular VBA module, it is used as a method of the application object.

The reason this is considered an event is that it can be triggered based on the time you specify. For example, if I want the sheet to recalculate every 5 minutes, I can use the OnTime event for it.

Or, if I want to show a message/reminder at a specific time of the day, I can use the OnTime event.

Below is a code that will show a message at 2 pm every day.

Sub MessageTime() Application.OnTime TimeValue("14:00:00"), "ShowMessage" End Sub Sub ShowMessage() MsgBox "It's Lunch Time" End Sub

Remember you need to place this code in the regular VBA module,

Also, while the OnTime event would be triggered at the specified time, you need to run the macro manually at any time.

Once you run the macro, it will wait till it’s 2 PM and then call the ‘ShowMessage’ macro.

The ShowMessage macro would then display the message.

The OnTime event takes four arguments:

Application.OnTime(EarliestTime, Procedure, LatestTime, Schedule)

EarliestTime: The time when you want to run the procedure.

Procedure: The name of the procedure that should be run.

LatestTime (Optional): In case another code is running and your specified code can’t be run at the specified time, you can specify the LatestTime for which it should wait. For example, it could be EarliestTime + 45 (which means it will wait for 45 seconds for the other procedure to get completed). If even after 45 seconds the procedure is not able to run, it gets abandoned. If you don’t specify this, Excel would wait until the code can be run, and then run it.

Schedule (Optional): If set to True, it schedules new time procedure. If False, then it cancels the previously set procedure. By default, this is True.

In the above example, we only used the first two arguments.

Let’s look at another example.

The below code would refresh the worksheet every 5 min.

Dim NextRefresh as Date Sub RefreshSheet() ThisWorkbook.Worksheets("Sheet1").Calculate NextRefresh = Now + TimeValue("00:05:00") Application.OnTime NextRefresh, "RefreshSheet" End Sub Sub StopRefresh() On Error Resume Next Application.OnTime NextRefresh, "RefreshSheet", , False End Sub

The above code would refresh the worksheet every 5 minutes.

It uses the Now function to determine the current time and then adds 5 minutes to the current time.

The OnTime event would continue to run until you stop it. If you close the workbook and Excel application is still running (other workbooks are open), the workbook that has the OnTime event running in it would reopen itself.

This is better handled by specifically stopping the OnTime event.

In the above code, I have the StopRefresh code, but you need to execute it to stop the OnTime event. You can do this manually, assign it to a button and do this by pressing the button or call it from the Workbook Close event.

Private Sub Workbook_BeforeClose(Cancel As Boolean) Call StopRefresh End Sub

The above ‘BeforeClose’ event code goes in ThisWorkbook code window.

Also read: Make VBA Code Pause or Delay

When you’re working with Excel, it keeps monitoring the keystrokes you use. This allows us to use keystrokes as the trigger for an event.

With OnKey event, you can specify a keystroke (or a combination of keystrokes) and the code that should be executed when that keystroke is used. When these keystrokes are pressed, it will execute the code for it.

Just like OnTime event, you need to have a way to cancel the OnKey event. Also, when you set the OnKey event for a specific keystroke, it becomes available in all the open workbooks.

Before I show you an example of using the OnKey event, let me first share the key codes that are available to you in VBA.

KEY CODE

Backspace {BACKSPACE} or {BS}

Break {BREAK}

Caps Lock {CAPSLOCK}

Delete {DELETE} or {DEL}

Down Arrow {DOWN}

End {END}

Enter ~

Enter (on the nueric keypad) {ENTER}

Escape {ESCAPE} or {ESC}

Home {HOME}

Ins {INSERT}

Left Arrow {LEFT}

NumLock {NUMLOCK}

PageDown {PGDN}

PageUp {PGUP}

RightArrow {RIGHT}

Scroll Lock {SCROLLOCK}

Tab {TAB}

Up Arrow {UP}

F1 through F15 {F1} through {F15}

When you need to use any onkey event, you need to use the code for it.

The above table has the codes for single keystrokes.

You can also combine these with the following codes:

Shift: + (Plus Sign)

Control: ^ (Caret)

Alt: % (Percentage)

For Example, for Alt F4, you need to use the code: “%{F4}” – where % is for the ALT key and {F4} is for the F4 key.

Now let’s have a look at an example (remember the code for OnKey events are placed in the regular VBA module).

When you hit the PageUp or PageDown key, it jumps 29 rows above/below the active cell (at least that’s what it’s doing on my laptop).

If you want it to jump only 5 rows at a time, you can use the below code:

Sub PageUpDOwnKeys() Application.OnKey "{PgUp}", "PageUpMod" Application.OnKey "{PgDn}", "PageDownMod" End Sub Sub PageUpMod() On Error Resume Next ActiveCell.Offset(-5, 0).Activate End Sub Sub PageDownMod() On Error Resume Next ActiveCell.Offset(5, 0).Activate End Sub

When you run the first part of the code, it will run the OnKey events. Once this is executed, using the PageUp and the PageDown key would only make the cursor jump 5 rows at a time.

Note that we have used ‘On Error Resume Next’ to make sure errors are ignored. These errors can occur when you press the PageUp key even when you’re at the top of the worksheet. Since there are no more rows to jump, the code would show an error. But since we have used ‘On Error Resume Next’, it will be ignored.

To make sure these OnKey events are available, you need to run the first part of the code. In case you want this to be available as soon as you open the workbook, you can place this in the ThisWorkbook code window.

Private Sub Workbook_Open() Application.OnKey "{PgUp}", "PageUpMod" Application.OnKey "{PgDn}", "PageDownMod" End Sub

The below code will return the keys to their normal functionality.

Sub Cancel_PageUpDownKeysMod() Application.OnKey "{PgUp}" Application.OnKey "{PgDn}" End Sub

When you don’t specify the second argument in the OnKey method, it will return the keystroke to its regular functionality.

In case you want to cancel the functionality of a keystroke, so that Excel does nothing when that keystroke is used, you need to use a blank string as the second argument.

In the below code, Excel would do nothing when we use the PageUp or PageDown keys.

Sub Ignore_PageUpDownKeys() Application.OnKey "{PgUp}", "" Application.OnKey "{PgDn}", "" End Sub

Sometimes you may need to disable events to make your code work properly.

For example, suppose I have a range (A1:D10) and I want to show a message whenever a cell is changed in this range. So I show a message box and asks the user whether they are sure that they want to make the change. If the answer is Yes, the change is made, and if the answer is No, then VBA would undo it.

You can use the below code:

Private Sub Worksheet_Change(ByVal Target As Range) If chúng tôi <= 10 And Target.Column <= 4 Then Ans = MsgBox("You are making a change in cells in A1:D10. Are you sure you want it?", vbYesNo) End If If Ans = vbNo Then Application.Undo End If End Sub

The problem with this code is that when the user selects No in the message box, the action is reversed (as I have used Application.Undo).

When the undo happens and the value is changed back to the original one, the VBA change event is again triggered, and the user is again shown the same message box.

To avoid such cases, you need to disable events so that the change event (or any other event) is not triggered.

The following code would work well in this case:

Private Sub Worksheet_Change(ByVal Target As Range) If chúng tôi <= 10 And Target.Column <= 4 Then Ans = MsgBox("You are making a change in cells in A1:D10. Are you sure you want it?", vbYesNo) End If If Ans = vbNo Then Application.EnableEvents = False Application.Undo Application.EnableEvents = True End If End Sub

In the above code, right above the chúng tôi line, we have used – Application.EnableEvents = False.

Setting EnableEvents to False would not trigger any event (in the current or any open workbooks).

Once we have completed the undo operation, we can switch back the EnableEvents property to True.

Keep in mind that disabling events impacts all the workbooks that are currently opened (or opened while EnableEvents is set to False). For example, as a part of the code, if you open a new workbook, then the Workbook Open event would not work.

Let me first tell you what an Undo Stack is.

When you work in Excel, it keeps monitoring your actions. When you make a mistake, you can always use Control + Z to go back to the previous step (i.e., undo your current action).

If you press Control + Z twice, it will take you back two steps. These steps that you have performed are stored as a part of the Undo stack.

Any event that changes the worksheet destroys this Undo stack. This means that if I have done 5 things before I trigger an event, I will not be able to use Control + Z to go back to those previous steps. Triggering the event has destroyed that stack for me.

In the below code, I use VBA to enter the timestamp in cell A1 whenever there is a change in the worksheet.

Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Range("A1").Value = Format(Now, "dd-mmm-yyyy hh:mm:ss") Application.EnableEvents = True End Sub

Since I am making a change in the worksheet, this will destroy the undo stack.

Also, note that this is not limited to events only.

If you have a code that is stored in regular VBA module, and you make a change in the worksheet, it would also destroy the undo stack in Excel.

For example, the below code simply enter the text “Hello” in cell A1, but even running this would destroy the undo stack.

Sub TypeHello() Range("A1").Value = "Hello" End Sub

You May Also Like the Following Excel VBA Tutorials:

Update the detailed information about Different Examples Of Contents In Excel Vba on the Hatcungthantuong.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!