1

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
Community
  • 1
  • 1
NeilF
  • 13
  • 3
  • 2
    Long variable does not accept decimals! Since you do not use the variables used like arguments, after calling the function,`ByVal` does not make any difference against `ByRef`... Try declaring all variables 'As Double`, please. And it is good to use `i = Range("B1").value`. VBA is able to guess what is it about, but it is good to cultivate such a habit to visually differentiate from using it like range. – FaneDuru Jun 07 '20 at 20:36
  • ByVal and ByRef are used for API calls. For strings ByVal sends a C string and ByRef sends a VB string, API calls want C strings. For other types you always use ByVal for in parameters (so the function you are calling doesn't mess with your variables) and ByRef for out parameters. –  Jun 08 '20 at 00:50

1 Answers1

1

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

FaneDuru
  • 38,298
  • 4
  • 19
  • 27