Excel VBA: Is it possible create custom functions with custom methods?

Question

What I'm referring to is, for example, how you can do:

Range().Select

Where "Range()" is the function, and "Select" is the method.

For example, if I had a function that I wanted to take three doubles that represent the side lengths of a triangle, and spit out the largest angle in either radians of degrees.

Public Function getAngle(a as Double, b as Double, c as Double)

    .degrees = 'some equation to determine degrees as a double
    .rads = 'some equation to determine radians as a string

End Function

Therefore, you would get the following results:

getAngle(3, 4, 5).degrees : 90.0

getAngle(3, 4, 5).rads : "0.5π"

You can use a custom Type or a class and return that from your function. — Tim Williams
– Tim Williams, Commented Aug 26, 2016 at 17:13

cyboashu · Accepted Answer · 2016-08-26 17:15:44Z

Create a class, for this example name it clsTrig.

Option Explicit

'/ Class  Name : clsTrig

Private m_ddegrees   As Double
Private m_drads  As Double

Public Property Get degrees() As Double
    degrees = m_ddegrees
End Property

Public Property Let degrees(val As Double)
     m_ddegrees = val
End Property

Public Property Get rads() As Double
    rads = m_drads
End Property

Public Property Let rads(val As Double)
     m_drads = val
End Property


Public Function doCalc(a1 As Double, a2 As Double) As Double

    '/ You do the math here. This is just a sample and not actual trigonometery

    m_ddegrees = a1 + a2
    m_drads = a1 - a2


End Function

Then in the standard module, you get your desired behavior like this:

Public Function getAngle(a As Double, b As Double) As clsTrig
    Dim oTrig As New clsTrig

    Call oTrig.doCalc(a, b)
    Set getAngle = oTrig
End Function

Sub test()
    MsgBox getAngle(30, 20).degrees
    MsgBox getAngle(30, 20).rads
End Sub

Tim Williams · Accepted Answer · 2016-08-26 17:27:30Z

1

Using a Type:

Option Explicit

Type cType
    Degrees As Double
    rads As Double
End Type


Sub tester()

    Dim a As cType

    a = getAngle(1, 2, 3)

    Debug.Print a.Degrees, a.rads

End Sub


Public Function getAngle(a As Double, b As Double, c As Double) As cType
Dim rv As cType

    rv.Degrees = 88  'for example
    rv.rads = 99     'for example

    getAngle = rv

End Function

edited Aug 26, 2016 at 17:27

answered Aug 26, 2016 at 17:17

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Collectives™ on Stack Overflow

Excel VBA: Is it possible create custom functions with custom methods?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related