vba excel function return #VALUE!

[Julieta]

I have a function as below:


Function nadi_a(pocetak As Integer, n As Integer, x_nula As Double) As Variant

Dim i As Integer, celija_x As Double, celija_error As Double, suma As Variant

suma = 0

For i = pocetak To pocetak + n
celija_x = Cells(i, 1)
celija_error = Cells(i, 5)

suma = 1 / (celija_error * celija_error) * (celija_x - x_nula) * (celija_x - x_nula)
Next i

nadi_a = suma

End Function


I checked every step and everything is fine except the return value. On the cell where i need to have return value "nadi_a" i get #value! Does someone know what can be a problem? Tnx

[Paul_Hossler]

what are the inputs that return #VALUE

[Artik]

I'm afraid the function is spelled incorrectly. Currently returns the calculation value (unless an error has occurred) for the last row in the range. So you might as well write a function like this:


Function nadi_a_1(pocetak As Integer, n As Integer, x_nula As Double) As Variant

Dim i As Integer, celija_x As Double, celija_error As Double

i = pocetak + n

celija_x = Cells(i, 1)
celija_error = Cells(i, 5)

nadi_a_1 = 1 / (celija_error * celija_error) * (celija_x - x_nula) * (celija_x - x_nula)

End Function

Because
(celija_error * celija_error) = celija_error ^ 2 so you can write
nadi_a = 1 / celija_error ^ 2 * (celija_x - x_nula) ^ 2


The function should probably return a sum from the range, so it should look at least like this:

Function nadi_a_2(pocetak As Integer, n As Integer, x_nula As Double) As Variant

Dim i As Integer, celija_x As Double, celija_error As Double, suma As Variant

For i = pocetak To pocetak + n
celija_x = Cells(i, 1)
celija_error = Cells(i, 5)

suma = suma + 1 / celija_error ^ 2 * (celija_x - x_nula) ^ 2
Next i

nadi_a_2 = suma

End Function



But in the body of the procedure there should be no reference to "external" objects, so I would write it rather like this (without error handling):

Function nadi_a_3(RasponCelija As Range, x_nula As Double) As Variant

Dim celija_x As Double, celija_error As Double, suma As Variant
Dim Celija As Range


For Each Celija In RasponCelija.Columns(1).Cells
celija_x = Celija.Value
celija_error = Celija.Offset(, 4).Value


suma = suma + 1 / celija_error ^ 2 * (celija_x - x_nula) ^ 2
Next Celija

nadi_a_3 = suma

End Function
You pass the cell range of five columns and the value of the x_nula parameter to the function. Note that with the function written in this way, the input range does not have to be the range beginning in column A.

However, the function will still return an error if the input does not have the values expected by the function. E.g. cells contain sheet error or are empty.

Artik