Error calling class function in VBA

Question

I have this class module inside my Access database:

Option Compare Database

Public Event BeforeCalc()

Public Sub Calculate(ByVal i As Integer, ByVal y As Integer)
    RaiseEvent BeforeCalc
    Calculate = i + y
End Sub


Private Sub Class_Initialize()
    Debug.Print "Inside construcotr"
End Sub

Then, inside a custom form:

Option Compare Database

Private WithEvents math As MyMath

Private Sub btnCalculate_Click()
    Dim result As Integer
    Set result = math.Calculate(CInt(txtI.Text), CInt(txtY.Text))
End Sub

Private Sub Form_Load()
    Set math = New MyMath
End Sub

Private Sub math_BeforeCalc()
    MsgBox "About to Calc!", vbInformation
End Sub

When I click the form button btnCalculate I got this error at math.Calculate:

"Compile error. Expected function or variable."

What's wrong with my code?

Note that .Text is the wrong property to use (it's only valid when the control has the focus). Use .Value instead. — Andre
– Andre, Commented Apr 12, 2018 at 14:03

LatifaShi · Accepted Answer · 2018-04-12 14:02:08Z

2

Since it is a function so it should return something, in your case an integer and also you should specify the Function keyword.

replace the code :

Public Sub Calculate(ByVal i As Integer, ByVal y As Integer)
     RaiseEvent BeforeCalc
     Calculate = i + y
End Sub

with :

Public Function Calculate(ByVal i As Integer, ByVal y As Integer) as Integer
    RaiseEvent BeforeCalc
    Calculate = i + y
End Sub

answered Apr 12, 2018 at 14:02

LatifaShi

4401 gold badge3 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jeroen Langenaken · Accepted Answer · 2018-04-12 14:05:31Z

1

You have defined Calculate as a Sub, try defining it as a Function:

Public Function Calculate(ByVal i As Integer, ByVal y As Integer) as Integer
    RaiseEvent BeforeCalc
    Calculate = i + y
End Function

Also, don't set the result:

Private Sub btnCalculate_Click()
    Dim result As Integer
    result = math.Calculate(CInt(txtI.Text), CInt(txtY.Text))
End Sub

answered Apr 12, 2018 at 14:05

Jeroen Langenaken

1236 bronze badges

Collectives™ on Stack Overflow

Error calling class function in VBA

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