CODEWITHSUNDEEP
excelvba
Carlos Rios Navas
Carlos Rios Navas

Reputation: 11

How to return a sum in a VBA function

I am creating a function that takes a cell and a range of cells to create an array. The first four numbers of this array are used to perform some calculations. At the end, I want to return the sum of these to the cells. On the debug, I add a watch to the sum A+B+C+D and the result show up perfectly. However, it does not return the value to the cell when I call the function in the worksheet. Why is this?

Public Function getFone(temp As Long, coeffs As Range) As Double
Dim coeffArray As Variant
Dim A As Double
Dim B As Double
Dim C As Double
Dim D As Double
coeffArray = coeffs.Value
A = ((coeffArray(1, 1)) * (temp))
B = ((coeffArray(1, 2) / 2) * (temp * temp))
C = ((coeffArray(1, 3) / 3) * (temp * temp * temp))
D = ((coeffArray(1, 4) / 4) * (temp * temp * temp * temp))
getFone = (A + B + C + D)
End Function

Upvotes: 1

Views: 154

Answers (1)

TechnoDabbler
TechnoDabbler

Reputation: 1265

The code works if the range is four horizontal cells and the temp parameter and the range contain numerics. So the potential issues that might trigger a #Value result are:

  • The parameters (temp and values in the range) are not numeric
  • The calculated value exceeds the size of a double
  • The range is not a horizontal range of four cells

working example

Upvotes: 1

Related Questions