Me Keyword in VBA Excel
I am sure you must have had this question “What is “ME” in VBA? Yes, even I had this question when I was new to VBA. After spending a considerable amount of time I have experienced ME keyword in excel VBA Coding. It is a bit advanced level if you are the starter in VBA. Anyway, you will get a hang of it slowly. In this article, we will show you how to use “Me” keyword in Excel VBA coding.
“ME” is the object in VBA which is specially designed which is built-in to excel. It points to the object which resides in and we can call that object by “ME” keyword. “ME” represents the parent object from where the code resides in.
If you are not understanding technically anything, don’t worry because when the examples come you will get to know this better. Before that let me give some background in VBA.
When we write excel macros we write in “Modules” and in modules, we have two sets of modules. First one is “Standard Modules” and the second one is “Class Modules”.
Further in VBA Class Modules, we have two subcategories i.e. Module with user Interface Element and Module without Interface Element. But for this example purpose, we will take into consideration only “Module with User Interface Element”.
Some of the example for these modules are ThisWorkbook, Sheet1, UserForm1 and so on.
This is the general overview of the ME keyword in VBA. Now we will see practical examples of ME word.
How to Use ME in Excel VBA?
For an example look at the below code in Module 1.
Remember this is the code I had written in Module 1. This will insert the word “Hello Friends” to the worksheet named as “Data Sheet”.
In order to refer to the sheet “Data Sheet”, we have used worksheets object and entered the worksheet by its name.
Now I will double click on “Data Sheet”.
As soon as we double click we can see the blank coding window on the right-hand side.
Now start the VBA subprocedure.
Sub Me_Example() End Sub
Now since I am writing the code in this sheet only, instead of mentioning the worksheet name I can just call this as “ME”.
We can see IntelliSense list with ME word. Now the word “ME” works like an implicitly declared object variable.
Now using ‘VBA Me’ lets access Range object in VBA and insert the same word as above in the sheet.
Sub Me_Example() Me.Range("A1").Value = "Hello Friends" End Sub
This will insert the same word as our previous macro in the excel sheet.
Here one thing we have noticed is we can see the word “ME” only in the specific objects and that word represents the object where the code writing is going on.
In this example, Me keyword represents the worksheet “Data Sheet”.
Below are some of the example codes of ME word.
Sub Me_Example() Me.Range("A1").Value = "Hello Friends" 'This will insert Hello Friends to the cell A1 in a Data Sheet. Me.Name = "New Sheet" 'This will change the sheet name from the Data Sheet to New Sheet. Me.Select 'This will select the sheet. End Sub
When we run the above code using the F5 key, in A1 cell we’ll get Hello Friends, sheets name will change from Data Sheet to New Sheet and this sheet will get selected.
Example #2 – VBA ME with User Form
“ME” is the keyword often used with User forms in VBA. For an example look at the below image of the newly inserted user form in VBA.
This user form name is “UserForm1”.
Whenever we want to address this user form from another module we can call this by this name i.e. “UserForm1”.
But when we work within this user form we don’t need to rely on the name of the user form rather we can just use the word “Me”.
Let’s draw one simple text box on the user form.
Double click on User Form to see the below macro.
Now to insert text to the newly inserted text box we can use two methods.
#1 – First, we can address the User Form by its name and text box by its name.
Private Sub TextBox1_Change() UserForm1.TextBox1.Text = "Welcome to VBA!!!" End Sub
UserForm1 is the name of the user form. TextBox1 is the name of the text box. So we have inserted the text “Welcome to VBA!!!”.
#2 – Since we are writing the code in the same user form we can call this by “ME”.
Private Sub TextBox1_Change() Me.TextBox1.Text = "Welcome to VBA!!!" End Sub
This will also do the same thing as the above code.
Like this, in VBA we can reference the object with the word “ME” when we specifically write the code in those objects.
This has been a guide to VBA Me. Here we discuss how to use the ME keyword in VBA excel along with the examples and downloadable excel template. You can learn more about VBA Excel from the following articles –