I got the wrong output in VBA - is my code wrong?

Question

I wrote this code and the result is shown in the blue column. `

Sub ematest()
'Calculate alpha for each periods
Dim alphas As Integer 'smoothing factor short moving average
Dim alphal As Integer 'smoothing factor long moving average

alphas = 2 / (Cells(3, 13).Value + 1)
alphal = 2 / (Cells(3, 14).Value + 1)

'Calculate 50 days Exponential MA

'calculate sema
For m = 53 To 6102
    Cells(m, 13) = (Cells(m, 5) * alphas) + ((1 - alphas) * Cells(m, 9)) 'for Column M
    
Next m

'calculate lema
For n = 203 To 6102
    Cells(n, 14) = (Cells(n, 5) * alphal) + ((1 - alphal) * Cells(n, 10)) 'for Column N
    
Next n

End Sub

`

I expect the result as shown in the yellow column where it is calculated by excel function for checking.

Am I missing something or have I make mistake?

Answer 1

The data type being used is 'truncating' the results of your calculation.
Try using a data type of Double instead of Integer.

Run this code example to see the differences caused by the data type.

Sub ematestint()

Dim alphas As Integer 
Dim alphal As Integer 

alphas = (Worksheets("Sheet1").Cells(3, 13).Value)
alpha1 = (Worksheets("Sheet1").Cells(3, 14).Value)

alphas = alphas + 1
alpha1 = alpha1 + 1

alphas = 2 / alphas
alpha1 = 2 / alpha1

MsgBox "alphas = " & alphas & vbCrLf & "alpha1 =" & alpha1

End Sub


Sub ematestdbl()

Dim alphas As Double 
Dim alpha1 As Double 

alphas = (Worksheets("Sheet1").Cells(3, 13).Value)
alpha1 = (Worksheets("Sheet1").Cells(3, 14).Value)

alphas = alphas + 1
alpha1 = alpha1 + 1

alphas = 2 / alphas
alpha1 = 2 / alpha1


MsgBox "alphas = " & alphas & vbCrLf & "alpha1 =" & alpha1


End Sub