sum visible cells

hello
this my version of summing only visible cells in a range:
[VBA]
Function sumonlyvisible(r As Range)
Application.Volatile
Dim cell As Range
For Each cell In r
If cell.Rows.Hidden = False Then
If cell.Columns.Hidden = False Then
sumonlyvisible = sumonlyvisible + cell.Value
End If
End If
Next
End Function

[/VBA]
i wanted to attach this macro to a button and place it along side the excel bulit in sum icon:
[VBA]
On Error Resume Next
Application.CommandBars("standard").Controls("sum visible").Delete
With Application.CommandBars("standard").Controls.Add(before:=24)
.Caption = "sum visible"
.faceid = 308
.Style = msoButtonIcon
.OnAction = ""
.BeginGroup = True
End With
[/VBA]
could it be doen?

Why not just use SUBTOTAL in Excel?

i wanted to explore the vba option.

i tought that by using :
[VBA]
application.volatile
[/VBA]
i will not have to use F9 to update my sheet after using my function.it did not happen .why?
thanks

You cannot change the worksheet or other cells from a functrion invoked from within a worksheet.

I created a User Defined Function (UDF) from your logic.

As a test, in L1 I put =SumOnlyvisible ("A1:C10") with row 2 hidden.


[VBA]
Function SumOnlyVisible(r As Range) As Double
Dim rCell As Range

Application.Volatile

Dim cell As Range

For Each rCell In r.Cells
With rCell
If Not .Rows.Hidden And Not .Columns.Hidden Then SumOnlyVisible = SumOnlyVisible + .Value
End With
Next
End Function
[/VBA]


HTH

Paul

i am trying togo a little further.i want replace the sum button with the following code:
[VBA]
activecell.FormulaArray = "=SUMONLYVISIBLE()"

[/VBA]
does anybody have a suggestion as to how to prevent the need to press f9.
the code of the change is :
[VBA]
CommandBars("רגיל").Controls("סכום אוטומטי").OnAction = "summacomsuper"

[/VBA]
thanks

Add

Application.Volatile

to the start of the UDF.

Hiding rows does not automatically trigger a recalculation in Excel (unless you are filtering and have Subtotals)

hello
suppose i found that a button faceid number 226 looks exactly like the excel xp sum button,and suppose even further that i manage to create a udf's function to deal with sum count and average only visible cells.how can i emulate the button as it appear on the standard toolbar so the user is faced upon pressing the button with a possibility to calculate this functions for only visible cells.
thanks

maybe the button type shoyld be different.instead of a ordinary button a
msoControlButton type it should be a msocontrolbuttonpopup so a dropdown list will appear.
thanks