VBA ME

What is ME Keyword in VBA Excel?

In VBA (Visual Basic for Applications) for Excel, the “Me” keyword refers to the object representing the form or module in which the code is currently running. It is often used to reference properties, methods, and controls within the current form or module. The “Me” keyword simplifies code and makes it more readable by eliminating the need to explicitly reference the form or module name.

You’ll need to type in the Worksheets to use the VBA Me.Filter.

The VBA Me.Range has been used, as shown in the subroutine below.

After running the subroutine, go back to the Worksheet.

As noticed, the ActiveCell is in cell “A10.” Similarly, you can use the “Me” object to declare values in a cell.

What is ME Keyword in VBA Excel?

“Me” is a keyword in VBA that refers to the current instance of an object or module.
It is particularly useful in UserForms and class modules for interacting with controls and handling events.
“Me” promotes code flexibility, reusability, and readability by allowing dynamic referencing of the current instance.
It is used solely in WorkSheets and UserForms. Using it in the VBA Modules will result in a compile error.
It is widely used in object-oriented programming to interact with the current instance of a class or form.
In UserForms, “Me” can be used to reference properties and methods of controls, making the code more intuitive.

Uses of VBA ME

When we use VBA Me, the main difference comes in the declaration of the code. See the example below:

Here, you’ve defined the Sheet name, the range and the value. There is no need to do so much if the “Me” variable is used and is written directly into the sheets.

Similarly, in this case, by directly typing into the sheet module, we do not need to specify the WorksheetName and select the active cells in Excel.

Here, it will print “Welcome to” in cell “A1”.

In the subroutine, “Excel” is printed using the worksheet name. The result is as shown below:

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Start Learning Now

How to Use ME in Excel VBA?

To work with VBA Me, follow the steps below.

In the Excel Workbook, go to “Developer” and select “Visual Basic.”

In the VBA Module, select the sheet you want to work on.

Now, you can start working on coding in Excel VBA.
Given a table (on a set of values, press “Ctrl+T” to make it a table), define a subroutine to find the number of rows.
Define a ListObject variable to initialize the table.

The Me object refers to the sheet you’re working on.
Check if the object variable is not empty, then use the ListRows.Count function to find the number of rows in the table.
Define an Else function.

Code:

Sub Find_Range()
Dim tbl As ListObject
Set tbl = Me.ListObjects(“Table1”)
If Not tbl Is Nothing Then
MsgBox “The table has ” & tbl.ListRows.Count & ” rows.”
Else
MsgBox “Table not found.”
End If
End Sub
Click the “Run” button or “F5” button.

This will open a Message Box with the number of rows.

Knowing how to use the “Me” object, let’s go through the other ways the VBA Me.Controls are used, mainly in a UserForm.

Examples

Let us look at the different ways the “Me” object can be used in Excel VBA.

Example #1

You can learn how to use VBA Me.Range effectively in the “ThisWorkBook” module. Given a table of binary values for three columns, find the OR gate function using VBA Or, and to save time, use the VBA Me function along the way.

Print TRUE or FALSE color-coded to green and red, respectively, using VBA Font Color functions.

Step 1: In Microsoft Excel Objects, select the Object. Here, you will see an example of VBA Me in the whole Excel WorkBook.

When you use the Me object, it refers to the whole module. Here, the “Me” object is used to refer to the WorkBook as a whole. So, instead of using the “ThisWorkBook” object, you can substitute it with the Me Object.

For example, instead of writing ThisWorkBook.Sheets(“Sheet1”) you can instead write Me.Sheets(“Sheet1”) while editing in the ThisWorkBook module.

Step 2: Define a subroutine to Find the Result for the OR gate function using the VBA Me.Range object.

Step 3: For example, print the Excel Sheet that is currently active.

Step 4: Find the size of the table.

Here, using the “Me” object the sheet where the table above is present is referenced. To make it dynamic, take any row, count it and end it only at the end of the last non-empty cell found using the xlUp function, which finds the last non-empty cell and then stops.

Step 5: Initialize a For-Loop running till the size of the “Table.”

Step 6: Using the VBA OR function, check whether any of the values in the columns are equal to 1.

If it is true, change the Font color to green and print “True” in the respective “D” column.

Step 7: Define an Else condition, which is executed if the If condition(s) fails.

End the If condition after declaring the If-Else statements.

Step 8: Continue the For loop.

VBA Code:

Sub WorkBook_Examples()
Debug.Print Me.ActiveSheet.Name
Dim lastRow As Integer
lastRow = Me.Sheets(“Sheet3”).Range(“A” & Rows.Count).End(xlUp).Row
For i = 1 To lastRow
If Me.Sheets(“Sheet3”).Range(“A” & i).Value = 1 _
Or Me.Sheets(“Sheet3”).Range(“B” & i).Value = 1 _
Or Me.Sheets(“Sheet3”).Range(“C” & i).Value = 1 Then
Me.Sheets(“Sheet3”).Range(“D” & i).Font.ColorIndex = 4
Me.Sheets(“Sheet3”).Range(“D” & i).Value = True
Else
Me.Sheets(“Sheet3”).Range(“D” & i).Font.ColorIndex = 3
Me.Sheets(“Sheet3”).Range(“D” & i).Value = False
End If
Next i
End Sub

Step 9: Run the “ThisWorkBook” module. The current active sheet will be printed in the “Immediate” tab.

Then, check on the table mentioned earlier.

Thus, with the help of the VBA Me object, the VBA OR function, the OR gate has been implemented successfully.

Example #2 – VBA ME with UserForm

In this example, similar to changing the background color of the UserForm, you can also randomly change the color of the tabs in Excel.

Step 1: Declare a subroutine to change the color of the Tab.

This subroutine takes an Object datatype as its argument.

Step 2: Define the current sheet this program will be working on.

Step 3: Change the WorkSheet color of the workbook with the VBA Rnd function used in conjunction to multiply it with 256 to get RGB values.

Step 4: Select “Insert” and select “UserForm.”

This creates a brand new UserForm where no changes are made.

Step 5: Add a command button to the UserForm.

Step 6: Double-click on the command button to make it redirect to its private subroutine where you can make your changes.

Step 7: Call the Subroutine defined earlier to the current workbook using the Me.Filter function.

Code:

Sub SelectWorkSheet(FormName As Object)
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(ActiveSheet.Name)
ws.Tab.Color = RGB(Rnd * 256, Rnd * 256, Rnd * 256)
End Sub

In the UserForm:

Private Sub CommandButton1_Click()
SelectWorkSheet Me
End Sub

Step 8: Run the UserForm and view the output below.

After clicking on the Command Button, the tab colors change.

Similar to Sheet1, you can change the ActiveSheet to “Sheet2” and change the Tab color to your preference.

Important Things to Note

The Object “Me” offers flexibility for writing more versatile and reusable code by referencing the current instance. It enhances code readability by making it more concise and easier to understand.
It is commonly used in UserForms and class modules in VBA for control and event handling.
Be mindful of the context when using “Me” to ensure it refers to the intended object or form.
“Me” helps prevent naming conflicts by ensuring that properties and methods are applied to the correct instance, avoiding unintended side effects.
The use of “Me” promotes self-referential code, making it more self-contained and modular, particularly valuable in larger software projects.

Frequently Asked Questions (FAQs)

1. What are some practical use cases for “Me” in VBA?

• In UserForms, use “Me” to reference controls within the current UserForm.
• In class modules, “Me” is used to interact with properties and methods of the current class instance.
• In form modules like worksheets, “Me” refers to the current form or worksheet.
• “Me” promotes reusability by allowing code to work with the current instance without specifying specific names.

2. How is “Me” different from referencing a form or object by name in VBA?

“Me” refers to the current instance of a form or class, making the code more flexible and reusable.
Referencing by name directly specifies a particular form or object and can lead to less flexible and more static code.

3. Is “Me” case-sensitive in VBA?

“Me” is not case-sensitive in VBA; it works regardless of the capitalization used.

4. How to use “Me” in VBA to refer to controls on a form?

To use VBE Me.Controls in a UserForm:
• In a form’s code module, type “Me” to reference the current form.
• Add a dot (.) after “Me” and specify the control name to interact with it.
• For example, “Me.TextBox1.Value” refers to the value of “TextBox1” in the current form.

Download Template

This article must be helpful to understand the VBA ME, with its formula and examples. You can download the template here to use it instantly.

VBA ME Download

VBA ME