CODEWITHSUNDEEP
vbaexcelexcel-2007user-defined-functions
BiGXERO
BiGXERO

Reputation: 7244

Excel custom function incorrectly updating

I am attemptinf to write (my first) custom function that will essentially loop through a report to sum the costs of a machine over the next 1, 3, 6 months(etc)

The function returns values, however, these values are incorrect, and more problematically double on a cell that contains the custom function changes the values of the surrounding cells that use the custom function.

The code that im dealing with is here:

Option Explicit

Dim months As Integer 'a month is considered as 30 days
Dim cost As Long
Dim FleetData As Range
Dim rowCounter As Long
Dim lastRow As Long
Dim firstRow As Long
Dim component As Range
Dim dateOfAction As Range
Dim totalApprox As Range
Dim dateHorizon As Date 'Date to which the user wants to total the maintenance cost for

Private Function totalCosts(xMonths As Range)
'Dim totalCosts As Long
Application.Volatile

dateHorizon = Date + (30 * months)

firstRow = [A10].Row
rowCounter = firstRow
lastRow = Range("A65000").End(xlUp).Row
Set FleetData = [A10:S14]

If IsNumeric(xMonths.Value) Then months = xMonths.Value Else
If IsDate(xMonths.Value) Then months = (xMonths.Value - Date) / 30
cost = 0
    Do While rowCounter < lastRow
        Set component = Range(Cells(rowCounter, 1), Cells(rowCounter, 19))
        Set dateOfAction = Cells(rowCounter, 7)
        Set totalApprox = Cells(rowCounter, 12)
        If dateOfAction <= dateHorizon Then
            cost = cost + totalApprox
        End If
        totalCosts = cost
        rowCounter = rowCounter + 1
    Loop

End Function

And the data that im using is:

DateOfAction totalApprox 
5/07/2014    $30,068.62 
24/05/2005   $6,300.00 
5/07/2012    $29,742.00
5/07/2012    $4,360.28
27/12/2012   $5,555.89

Clicking on a cell seems to shift the values around, but in no identifiable order.

Have googled and looked here but nothing has seemed to solve the problem thus far.

Any help would be greatly appreciated!

Upvotes: 0

Views: 327

Answers (1)

chris neilsen
chris neilsen

Reputation: 53136

Some hints and things to check:

  1. Don't use module scoped variables (unless you need to share those variables with other modules. Even then there is usually better ways)
  2. Don't use Volatile unless you really need to (I don't think you do in this case)
  3. Do pass all your ranges into the Function as parameters
  4. If you must use direct range references then remember that unqualified references such as Range(... and Cell(... refer to ranges on the active sheet. If your data sheet is not active this UDF will return unexpected results. Use something like Worksheets("Sheet1").Range(... instead. But I say again, passing the range reference into the function as a parameter is much better.
  5. Your code references the variable months before you set it.
  6. If you are calling the UDF in a formula from a cell, trying to set a cell value (as in totalCosts = cost) will not work. This is clearly stated in the link you provided (first point of the You cannot make a VBA UDF which directly: list). On the other hand, if you call the UDF from a Sub and run that Sub as a macro, it will work.

If you provide full details of your data layout and how you want to use the UDF, I could offer more specific advice.

Upvotes: 1

Related Questions