sum visible cells

[lior03]

hello
this my version of summing only visible cells in a range:

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


i wanted to attach this macro to a button and place it along side the excel bulit in sum icon:

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

could it be doen?

[Bob Phillips]

Why not just use SUBTOTAL in Excel?

[lior03]

i wanted to explore the vba option.

[lior03]

i tought that by using :

application.volatile

i will not have to use F9 to update my sheet after using my function.it did not happen .why?
thanks

[Bob Phillips]

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

[Paul_Hossler]

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

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



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



HTH

Paul

[lior03]

i am trying togo a little further.i want replace the sum button with the following code:

activecell.FormulaArray = "=SUMONLYVISIBLE()"


does anybody have a suggestion as to how to prevent the need to press f9.
the code of the change is :

CommandBars("רגיל").Controls("סכום אוטומטי").OnAction = "summacomsuper"


thanks

[Bob Phillips]

Add

Application.Volatile

to the start of the UDF.

[rory]

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

[lior03]

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

[lior03]

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