Wrong return value VBA function Excel

Question

I'm writing a VBA function which I need in my Excel spreadsheet, this is the code I wrote:

Function costo(x As Variant, d1 As Double, p1 As Double)
    Dim d As Variant
    d = Array(Array(129, 90), Array(129, 98), Array(142, 81), Array(133, 98), _
             Array(139, 102), Array(156, 144), Array(125, 127), Array(137, 222), _
             Array(213, 241), Array(145, 229), Array(206, 118), Array(152, 167))
    Dim c As Double
    c = 0
    Dim i As Long
    For i = 1 To 12
        c = c + 50 * x(i) * distanza(d1, p1, d(i)(0), d(i)(1))
    Next i
    costo = c
End Function


Function distanza(ByVal d1 As Double, ByVal p1 As Double, ByVal d2 As Double, ByVal p2 As Double) As Double
    Dim r As Double
    r = 6371
    distanza = 2 * r * WorksheetFunction.Asin(Sqr(WorksheetFunction.Power(Sin((d1 - d2) / 2), 2) + _
             Cos(d1) * Cos(d2) * WorksheetFunction.Power(Sin((p1 - p2) / 2), 2)))
End Function

x are 12 cells of my spreadsheet, while d1 and p1 are two cell.

When I run the code in debug, the variable costo have the correct value, but in my spreedsheet I get #VALUE!.

Any suggestion?

Your costo function appears to be missing an As value declaration. — igittr
– igittr, Commented Feb 19, 2023 at 19:45
May be, error occurs in function costo. Most likely pointed by @GSerg. In this case Excel not informs about error. Cell value become #VALUE!. Check errors with on error clause. — ValNik
– ValNik, Commented Feb 19, 2023 at 20:17
I believe the For Loop should be 0 to 11. And depending on how X is defined maybe it is referenced by x(i+1) or remains by x(i) — igittr
– igittr, Commented Feb 19, 2023 at 22:06

bankeris · Accepted Answer · 2023-02-19 23:08:41Z

0

Try to add error handler on error go to, exit function and messagebox. I got same #VALUE but i dunno what numbers you adding. My error says subcript out of range. Numbers too high.

Function costo(x As Variant, d1 As Double, p1 As Double)
    On Error GoTo MY_ERROR
    Dim d As Variant
    d = Array(Array(129, 90), Array(129, 98), Array(142, 81), Array(133, 98), _
             Array(139, 102), Array(156, 144), Array(125, 127), Array(137, 222), _
             Array(213, 241), Array(145, 229), Array(206, 118), Array(152, 167))
    Dim c As Double
    c = 0
    Dim i As Long
    For i = 1 To 12
        c = c + 50 * x(i) * distanza(d1, p1, d(i)(0), d(i)(1))
    Next i
    costo = c
    Exit Function
    
MY_ERROR:
    MsgBox Err.Description
End Function

answered Feb 19, 2023 at 23:08

bankeris

2011 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ron Rosenfeld · Accepted Answer · 2023-02-20 01:29:44Z

0

The bug in your code causing the premature exit occurs because d is a 12-element variant array with Lbound = 0 and Ubound = 11. So when you refer to the array elements in your c= line, when i reaches 12 you will have an out-of-bounds subscript error.

Change that line to:

c = c + 50 * x(i) * distanza(d1, p1, d(i - 1)(0), d(i - 1)(1))

and your code should run.

answered Feb 20, 2023 at 1:29

Ron Rosenfeld

62.1k7 gold badges35 silver badges71 bronze badges

Collectives™ on Stack Overflow

Wrong return value VBA function Excel

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related