Excel VBA Me Function
Just by seeing the name VBA Me, people must be wondering what kind of thing is it in VBA called VBA Me? This came to my mind as well when I heard this first time. VBA has an object called ME which is used in place of parent objects. This seems some typical function with complex syntax. But trust me, the syntax of VBA Me might have seen and used as well. Although, VBA Me is used in advanced limit and is not seen in regular commands.
I know, by reading so far, people still may have the question, What is VBA Me? Let’s understand this on a very ground level language. Consider yourself in this case. We all have been given different kind of nicknames since our childhood which can be 2 or more. But all have one official and real name by which we are called by the rest of the world. By which, we introduce us to the rest of the people. So, that real name will be ME in VBA Me object which can be used by replacing similar other identities. This example can be understood in the opposite manner as well.
Frankly, VBA Me doesn’t have any defined syntax. This is used as a replacement for parent objects.
How to Use Excel VBA Me Function?
Below are the different examples to use Me Function in Excel VBA.
VBA Me – Example #1
VBA ME can be implemented in Module and Sheet both. But we again need to know when should we use what. We can use VBA Me anywhere. It can be used in Class or Module. And Class has 2 modules i.e. Module with User Interface and Module without User Interface. Here, we will see the Module with User Interface. As said, we will execute this into a Module.
Below are the different steps to use VBA Me function.
Step 1: Insert a new module in Visual Basic Editor (VBE). Click on Insert tab > select Module.
Step 2: Write subprocedure for VBA ME as shown below.
Code:
Sub VBA_Me() End Sub
Step 3: Select Worksheet first. Here, we will be selecting the worksheet which is named as Sheet1.
Code:
Sub VBA_Me() Worksheets("Sheet1") End Sub
Step 4: Now select the range cell where we will be seeing the output value. Let that cell be B2 and the value be “VBA Me” as shown below.
Code:
Sub VBA_Me() Worksheets("Sheet1").Range("B2").Value = "VBA Me" End Sub
Step 5: Now run the code by pressing the F5 key or by clicking on Play Button. We will see the value is printed in cell B2.
Here, the ME Object will be the name of Sheet which we have selected with the help of Worksheets function. So now we will replace that with ME. But again this won’t work under Module.
Step 6: Go to the sheet which we have selected, i.e. Sheet1.
Step 7: And place the same code as we have seen in Module and replace worksheet function with ME as discussed. Here, for change, we have selected the Range as A2.
Code:
Sub VBA_Me() Me.Range("A2").Value = "VBA Me" End Sub
Step 8: Again run the code by pressing the F5 key or by clicking on Play Button.
We will see, VBA ME has returned the text value in cell A2 as well. This is how we can interchange parent object with VBA ME.
VBA Me – Example #2
Now the same VBA ME we will see in User Form. For this, follow the below steps:
Step 1: Open a User Form from Insert menu tab as shown below.
This will look like this.
Step 2: In this toolbox select TextBox.
Step 3: Drag this TextBox on the user form.
Here we are selecting Text box so that we would see the output what we will feed in.
Step 4: Double click on the created text box which we will direct us to edit code mode.
Code:
Option Explicit Private Sub TextBox1_Change() End Sub
Step 5: Write the code to select the User Form and Text box which we have created.
Code:
Option Explicit Private Sub TextBox1_Change() UserForm1.TextBox1.Value End Sub
Step 6: Now select the text which you want to see in the Text box of User Form. Let that text be “Testing Ok!”.
Code:
Option Explicit Private Sub TextBox1_Change() UserForm1.TextBox1.Value = "Testing Ok!" End Sub
Step 7: Now run the code by pressing the F5 key or by clicking on Play Button. We will get a User Form with a Text box in it as shown below. Now if we try to enter anything in that text box, we will see the output message we have set just above.
Here, our ME object will be the name of User Form which is the parent object.
Step 8: So we can replace that with ME to execute VBA ME.
Code:
Option Explicit Private Sub TextBox1_Change() Me.TextBox1.Value = "Testing Ok!" End Sub
Step 9: Again run the code.
Step 10: And try to type anything in the Text box. We will see the same output message in the text box which we have received before.
This is how we can implement VBA ME anywhere by replacing Parent object with ME.
VBA Me – Example #3
There is another simple example to see how VBA Me works. We have created a sheet and named it as Test.
Step 1: Open that sheet in VBA and write the code where we will the name the newly created sheet in the message box.
Code:
Sub VBA_Me2() MsgBox Sheet2.Name End Sub
Step 2: Now run the code. We will get the name of sheet as Test.
Step 3: Here, Sheet2 has become our object which can be replaced. So, we can replace with ME as shown below.
Code:
Sub VBA_Me2() MsgBox Me.Name End Sub
Step 4: And the run the code again. We will see, the name of Sheet Test will be seen in message box.
Pros & Cons of Excel VBA Me
- It seems complex but it is very easy to implement.
- We can replace any object with VBA Me.
- This also helps in reducing the size of code by removing unwanted, mistakable object functions.
- ME also provides the reference to select a specific instance of Class of the same code.
- It cannot be implemented in Module.
Things to Remember
- We need to replace the same object function of parent object which is there in the same Sheet or User Form of the code.
- It automatically becomes available to every subcategory in the same Class or Form.
- VBA Me can be used with any type of code where we see the replacement of the parent object.
- VBA Me can directly be used where we can just use Debug Print or message box where instead of selecting the debugging function or variable, we can replace that with VBA ME.
Recommended Articles
This is a guide to VBA Me Function. Here we discuss how to use Excel VBA with Me Function along with practical examples and downloadable excel template. You can also go through our other suggested articles –