Consulting

Results 1 to 11 of 11

Thread: sum visible cells

  1. #1
    VBAX Mentor
    Joined
    Jun 2005
    Posts
    374
    Location

    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?
    moshe

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Why not just use SUBTOTAL in Excel?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Mentor
    Joined
    Jun 2005
    Posts
    374
    Location
    i wanted to explore the vba option.
    moshe

  4. #4
    VBAX Mentor
    Joined
    Jun 2005
    Posts
    374
    Location
    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
    moshe

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    You cannot change the worksheet or other cells from a functrion invoked from within a worksheet.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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

  7. #7
    VBAX Mentor
    Joined
    Jun 2005
    Posts
    374
    Location
    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
    moshe

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Add

    Application.Volatile

    to the start of the UDF.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    VBAX Master
    Joined
    Jun 2007
    Location
    East Sussex
    Posts
    1,110
    Location
    Hiding rows does not automatically trigger a recalculation in Excel (unless you are filtering and have Subtotals)
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    VBAX Mentor
    Joined
    Jun 2005
    Posts
    374
    Location
    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
    moshe

  11. #11
    VBAX Mentor
    Joined
    Jun 2005
    Posts
    374
    Location
    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
    moshe

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •