VBA methods in Excel

In the Write/Read Cell, Objects Chapter, we will see in detail about objects and its properties.

The properties just defines what objects are and does not utilize the object's functionalities.

Every object performs some actions and those actions are contained in its methods.

Thus, Methods are the actions that can be performed by the objects.

For example, a Range object can be used to Copy contents from one range to another or from one sheet to another.

This action of Copying and Pasting is an action and is performed by VBA METHODS called "Copy" and "Paste".

So, a VBA method makes the object meaningful. Without methods, objects are meaningless.

The fact that, VBA is not fully object oriented is seen from the fact that, objects are not instantiated in VBA, unlike in other OOPS languages, where instantiating objects is a must.

The following example illustrates the concepts discussed above. We named a new Sheet Q26.

  1. Sub Methods_example()
  2. Dim rng As Range
  3. Set rng = Sheets("Q26").Range("P15")
  4. rng.Value = "Hello"
  5. rng.Copy
  6. Sheets("Q26").Range("P26").Select
  7. ActiveSheet.Paste
  8. MsgBox "Copied from P15 and Pasted to P26"
  9. rng.Clear
  10. Sheets("Q26").Range("P26").Clear
  11. End Sub

The screenshot of the VBA editor is as shown below.

The rng.copy and the ActiveSheet.Paste are both VBA Methods.

excel vba methods

The above example Copies the Value in P15 and Pastes to P26.

And a message box is displayed for you to confirm, as shown below.

excel vba methods


You can find similar Excel Questions and Answer hereunder

1) How do you close, save, open files and do other operations in the workbook in VBA

2) How can I add a background image to a worksheet?

3) What are the main cell objects in VBA

4) How do I have proper (Capitalize the first letter in each word of a text) text in cells ?

5) How to hide and unhide rows and columns in Excel

6) How to avoid screen update in Excel VBA. Various application objects explained

7) What are the main sheet objects and what can the objects do in the sheet in VBA

8) How to enable or disable macros execution or loading in Excel

9) How can I get users to select a folder to save the output of my macro?

10) How can I hide all comments in my WorkSheet using VBA?


Here the previous and next chapter