Me Keyword in Excel Macros & VBA

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.

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:

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

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

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

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

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.

Now, run the macro:

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

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:

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.