Me Keyword in Excel Macros & VBA

Add to Favorites

The Me keyword in Excel VBA allows you to refer to either the current worksheet, workbook, or userform without having to use their name. This allows you to make more versatile macros that won't break when you rename something or copy them to another workbook or worksheet.

Sections:

Where to Use the Me Keyword

Worksheet Example

Workbook Example

UserForm Example

Notes

Where to Use the Me Keyword

You can use the Me keyword inside of a worksheet module, a workbook module, and a UserForm.

af5c76a8178a405d751e733347570b79.jpg

Note: You cannot use the Me keyword inside of a regular module!

Any code that is put into a worksheet module is specific to that worksheet; putting code into the workbook, via ThisWorkbook, is specific to the workbook in which it is placed; and putting Me inside the code for a UserForm refers to that UserForm.

The VBA code can still reference data from anywhere in Excel but the scope of the Me keyword is limited to where it is placed.

This may seem a little confusing, so let's get to some examples.

Worksheet Example

Using Me within a worksheet module allows you to access data related to that worksheet.

Go to the VBA Editor (Alt + F11) and double-click Sheet1 or any sheet. Then let's start with a very simple macro that will output a message using data from the Me keyword.

The instant you type Me. you will see a list of options that shows you the kinds of data that you can access:

7a76605ba21a7c3237bce7d9d23de844.png

All of this data is specific to the worksheet in which you are creating this macro; so, I created the macro in Sheet1 (also referred to as the worksheet module for Sheet1) and that means that all of the data from the Me keyword here will be for Sheet1.

Now, let's get the name of the sheet and output that in the message box:

Sub me_sheet()

MsgBox Me.Name

End Sub

ef5b2ac42b52ba701806b052d54b9f70.png

Me references the worksheet.

Name gets the name of the worksheet.

MsgBox simply outputs this in a pop-up window so we can see the result.

Run the Macro

Now, go back to Excel and hit Alt + F8 and choose the macro from the list to run it. Since the macro is in the worksheet module for Sheet1, its name will start with Sheet1

9d8f68e923ff7f4680dcee2fe984c112.png

Now, run the macro (you don't have to be on Sheet1 to run this macro).

7997f60cd861ece0d36989f0aa122fa5.jpg

No matter where you run this macro, the output will always be the name of Sheet1, which is "Sheet1"; this is because the code for the macro is placed within the worksheet module for Sheet1 and so it doesn't matter from where you call it.

The Me keyword also works the same way in your worksheet event macros.

Workbook Example

Using the Me keyword within the ThisWorkbook module allows you to access data related to the workbook.

Go to the VBA Editor (Alt + F11) and double-click where it says "ThisWorkbook" and in the window that opens, input your macro code.

You will notice that once you type Me. there will be a list of options that show you the data you can access.

Here, I'll just get the name of the workbook and output that in a message box.

Here is the code:

Sub me_book()

MsgBox Me.Name

End Sub

eeadfb0d4255fcb404fe7df4e7b80828.png

Me references the workbook.

Name gets the name of the workbook.

MsgBox just outputs the result in a pop-up window so we can see it.

Run the Macro

Now, go back to Excel and hit Alt + F8 and choose the macro from the list to run it. Since the macro is in the ThisWorkbook module, its name will start with ThisWorkbook.

03c8c354f4edd0fd477b7b54ace9184f.png

Now, run the macro:

320ba335036d2d9f606f5f181ffaa6f2.jpg

You can see that it returns the name of the current Excel workbook.

The Me keyword also works the same way in your workbook event macros.

UserForm Example

You use the Me keyword inside a UserForm in order to reference that form. This makes your code more versatile because you don't have to change anything if the name of the UserForm changes.

Go to the VBA Editor (Alt + F11), double-click the module for your form and right-click on the form and click View Code.

In the window that opens, I'll add a small piece of code that is attached to the only command button in this particular form:

Private Sub CommandButton1_Click()

Unload Me

End Sub

74bab33fd9f5187648b6bfb16d4e1734.png

Unload is what closes the UserForm.

Me refers to what should be closed, this UserForm.

Since the Me keyword is used within a UserForm here, it refers to that specific UserForm. This allows you to use Me throughout the UserForm code without having to worry about the actual name of the UserForm in order to reference it.

Here is the small UserForm that I made:

2fbbf7757dc3fabbb3b65e2c2babf854.jpg

Click the Close Me button and the form will close using the code I posted above.

You can use the Me keyword for a UserForm just like you would for a worksheet or workbook module; as in, you can type a period after it and get specific properties of the UserForm.

Notes

As you can see, the Me keyword is something that makes your life easier when making certain kinds of macros, those within a worksheet or workbook module, or those within a UserForm.

The Me keyword isn't often used for the simple fact that a lot of people don't know much about it, but it can be quite helpful.

Also, I provided a sample macro within a regular old Module that uses the Me keyword so you can see what happens when you use it where you aren't supposed to use it. Just run the macro me_module in the sample file in order to test that. Remember, the Me keyword does not work inside regular Modules.

There are more uses for the Me keyword but, in the context of Excel, this is the most important information.

Make sure to download the sample file attached to this tutorial to work with these examples in Excel.


Downloadable Files: Excel File

Similar Content on TeachExcel
VBA IF Statement in Excel Macros
Tutorial: IF statements allow you to make decisions in your Macros and VBA in Excel. An IF statement...
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
Macro: This free Excel macro illustrates all of the possible parameters and arguments that yo...
Logical Operators in Excel VBA Macros
Tutorial: Logical operators in VBA allow you to make decisions when certain conditions are met. They...
Print Entire Workbook in Excel
Macro: This free excel macro allows you to print the entire workbook in Excel. You can easil...
Filter Data in Excel to Display Results that Contain 1 of 2 Possible Values - AutoFilter
Macro: This Excel macro filters data in Excel to display results that contain 1 of 2 possible val...
Delete All Comments in a Worksheet in Excel Macro
Macro: Excel macro that will delete all of the comment contained within the active or current wor...