Creating a mutable Excel function that executes when a format is changed

Question

I'm new to creating Excel functions, and I need to create a function that takes a range of cells and calculates the sum of the values ​​of cells that are not crossed out.

Function SUMNOTSTRIKE(rng As Range)
    Dim cell As Range
    For Each cell In rng
        If Not (cell.Font.StrikeThrough) Then
            SUMNOTSTRIKE = SUMNOTSTRIKE + cell.Value
        End If
    Next cell
End Function

This function works fine, but I don’t understand why the result is not updated automatically when hit or deleted. I have to execute the function again.

I read something about Application.Volatile, but it only works when the value changes. I need a function that will fire when the format changes.

Answer 1

Extension of my comment. If you add

Application.Volatile

( , ), :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.Calculate
End Sub

, , . , , , .

, Application.Calculate Excel. .

Answer 2

. , A1 A100.

:

:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Target, Range("A1:A100")) Is Nothing Then Exit Sub
    Cancel = True
    Target.Font.Strikethrough = True
    Application.CalculateFull
End Sub

.

, :

• Excel.
• select View Code - VBE
• VBE

- , .

, . Excel 2003 , .xlsm, .xlsx

:

• VBE, .
• VBE

, .

http://www.mvps.org/dmcritchie/excel/getstarted.htm

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

( ), .

http://www.mvps.org/dmcritchie/excel/event.htm

!