Forcing the update of a cell Containing a Custom Formula 1

[JAG14]

Hi Guys,

This is probably really simple, but I've tried and can't do it...

Basically, I've created a Function which Loops through a specified column and finds Positive Values, it then adds these values together and returns a 'Double' Variable.

It goes like this...

Public Function CompletedDays(sColumn As String) As Double
    Dim iLoop As Integer
    Dim lAmount As Double
        
    For iLoop = 5 To 35 Step 1
        If Range(Trim(sColumn) & iLoop) <> 0 And _
          Range(Trim(sColumn) & iLoop) <> "N/A" Then
            dAmount = dAmount + Range(Trim(sColumn) & iLoop)
        End If
    Next iLoop
    
    CompletedDays = dAmount
   
End Function

and the Cell Content is

=CompletedDays("H")

The problem is, the bloody thing won't automatically update when one of it's dependant cells (i.e. in Column H) changes.
I have to double click on the cell containing the Function and press enter to force a re-calculation.

Column H itself is a Sum of the Previous 4 Columns on the same row.

Any help would be greatly appreciated...

Thanks in Advance.

Jag14

[tt]'Very funny, Scotty... Now Beam down my clothes.'[/tt]
​

 

[xlbo]

try this:

Public Function CompletedDays(sColumn As String) As Double
    Dim iLoop As Integer
    Dim lAmount As Double
        
    [b]Application.Volatile[/b]

    For iLoop = 5 To 35 Step 1
        If Range(Trim(sColumn) & iLoop) <> 0 And _
          Range(Trim(sColumn) & iLoop) <> "N/A" Then
            dAmount = dAmount + Range(Trim(sColumn) & iLoop)
        End If
    Next iLoop
    
    CompletedDays = dAmount
   
End Function

From XL help files:

Volatile Method
Marks a user-defined function as volatile. A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet. A nonvolatile function is recalculated only when the input variables change. This method has no effect if it's not inside a user-defined function used to calculate a worksheet cell.

expression.Volatile(Volatile)
expression Required. An expression that returns an Application object.

Volatile Optional Variant. True to mark the function as volatile. False to mark the function as nonvolatile. The default value is True



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[JAG14]

Perfect Geoff,

Thank you, You are a superstar...

Jag..

[tt]'Very funny, Scotty... Now Beam down my clothes.'[/tt]
​