Methods with Excel VBA

Class methods usually contain code that analyzes the state of an object and modifies it. For example, classes often have certain functions that are not limited to simply reading or assigning values to some parameters, but require specific computations.

In such cases, methods come into play. Essentially, methods are procedures. If they return or assign values, it is often more convenient to implement them as functions.

A method call is an operation performed on an object by referencing it, followed by a dot, then the method name, and finally a list of actual parameters in parentheses:

Object.Method(parameterList)

In the code , the functionality of the Point class is extended by adding three methods:

• Move() moves the point in the direction specified by another point.
• Length() returns the vector length, i.e., the distance from the origin to the point.
• ToString() accumulates information about the class instance into a string.

The first of these methods is implemented as a Sub procedure, while the second and third are implemented as functions.

Methods. Class module Point

Public x As Integer
Public y As Integer

Public Sub Move(ByVal pt As Point)
    x = x + pt.x
    y = y + pt.y
End Sub

Public Function Length() As Double
    Length = Sqr(x ^ 2 + y ^ 2)
End Function

Public Function ToString() As String
    ToString = "(" & x & "," & y & ")"
End Function

Methods. Standard module

Sub TestPointWithMethods()
    Dim p1 As New Point
    p1.x = 1 : p1.y = 1
    Debug.Print p1.ToString
    Debug.Print p1.Length
    Dim p2 As New Point
    p2.x = 2 : p2.y = 2
    Debug.Print p2.ToString
    p1.Move p2
    Debug.Print p1.ToString
    Debug.Print p1.Length
End Sub