Using named cells in a VBA function

Question

I have a worksheet where I have defined names for certain cells. These cells will be used in the function and I am calling them using their names.

However, I get 0 as a return of the function when I call it with Excel, as if the names were not linked or had a 0 value.

Below is the code I wrote. "Sum_Len_1", "L_W_2" ans "L_W_1" are the names I gave to the source cells.

Function min_w(depth)

    If depth < Sum_Len_1 Then
        min_w = L_W_1 * 0.868 * depth / 1000
    Else
        min_w = L_W_1 * 0.868 * Sum_Len_1 / 1000 + L_W_2 * 0.868 * (depth - Sum_Len_1) / 1000
    End If

End Function

How can I solve the problem?

Answer 1

If you just write min_w = L_W_1 * 0.868 * depth / 1000 vba thinks L_W_1 it's variable (of the type variant with value=0). You have to do it like this Range("L_W_1").Value to reference the named cell.

It should work if you change it to:

Function min_w(depth As Long)
If depth < Range("SUM_LEN_1").Value Then
    min_w = Range("L_W_1").Value * 0.868 * depth / 1000
Else
    min_w = Range("L_W_1").Value * 0.868 * Range("SUM_LEN_1").Value / 1000 + Range("L_W_2").Value * 0.868 * (depth - Range("SUM_LEN_1").Value) / 1000
End If
End Function

Answer 2

You can just put them in brackets to mark them as a range: [Sum_Len_1].Value, [L_W_2].Value and [L_W_1].Value