I was trying to make sense of ByVal and ByRef and passing arguments from long to double using the ByVal keyword.
I noticed that VBA gave me the incorrect answer for the value of y squared. It does work when y (i in my sub) is a whole number.
In the below example I had i = 22.5.
The spreadsheet gave me 506.25.
My function gave me 484.
I thought both long and double support decimals.
Sub automation_test()
Dim i As Long
Dim j As Long
Dim x As Long
Dim ans As Long
i = Range("B1")
j = Range("B2")
x = Range("B3")
ans = my_model(i, j, x)
Range("B4").Value = ans
End Sub
Function my_model(ByVal y As Double, ByVal m As Double, ByVal q As Double) As Double
' my_model = (y ^ 2) * (m ^ 3) * (q ^ 1 / 2)
my_model = y ^ 2
End Function
You must declare all used variables As Double (or As Single, depending on the maximum value to be used).
Long variables do not accept decimals.
The difference is exactly the one coming from rounding (down):
22.5^2 = 506.25
22^2 = 484