Using Class in Excel VBA

Question

I am trying to calculate something in Excel VBA using a class definion. The main goal is to calculate in an class within the sub Calc1 a simple ratio. When executing the module I receive the following error:

function or variable expected

in the line of the module

MsgBox myCalc1.Calc1

The code is below

Class is defined as the following:

' These are properties
Public dat_date1 As Date
Public dat_date2 As Date
Public str_Option As String
Dim ratio As Double
Dim nominator As Double
Dim denominator As Double

' These are methods
Sub Calc1()
    ' Code for day count conventions
    If str_Option= "o1" Then
        nominator = (dat_date2-dat_date1)
        denominator = 2
    ElseIf str_Option = "o2" Then
        nominator = (dat_date2-dat_date1)
        denominator = 3
    Else
        MsgBox "No function defined"
    End If

    ratio = nominator / denominator
End Sub

My module is defined as the following:

Sub ModuleCalc()

    Dim myCalc1 As clsCalc

    Set myCalc1 = New clsCalc
    With myCalc1 
        .dat_date1 = "01/02/2015"
        .dat_date2 = "28/02/2015"
        .str_Option= "o1"
    End With

    MsgBox myCalc1.Calc1
End Sub

Unless you specifically need a string an enum is a better choice for o1 — Alex K.
– Alex K., Commented Jun 13, 2017 at 12:55
@AlexK., thanks for the quick reply. was a typo but still get the same error message — MCM
– MCM, Commented Jun 13, 2017 at 12:56

SierraOscar · Accepted Answer · 2017-06-13 13:05:36Z

2

Your actual error is because Calc1 isn't a function, so it doesn't return a value and therefore you can't pass it as an argument to a message box object.

I suspect your function should look like this:

Function Calc1() As Double
    ' Code for day count conventions
    If str_Option= "o1" Then
        nominator = (dat_date2-dat_date1)
        denominator = 2
    ElseIf str_Option = "o2" Then
        nominator = (dat_date2-dat_date1)
        denominator = 3
    Else
        MsgBox "No function defined"
    End If

    Calc1 = CDbl(nominator / denominator)
End Sub

Now you can use it to pass the value back to a message box.

answered Jun 13, 2017 at 13:05

SierraOscar

17.7k6 gold badges44 silver badges71 bronze badges

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

1 Comment

MCM Over a year ago

Thanks a lot. It works. Missed it using a function instead of Sub :)

Collectives™ on Stack Overflow

Using Class in Excel VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related