Excel VBA Macro

Do you ever think or need to clear/empty Clipboard Excel?

I have a big Excel file which abusing the power of copy and paste to format the data and formula. I prefer to update the formula once, then save the result as values, especially for lookup formula. If I am not doing this, each time I am changing something, it will trigger the lookup formula and make the Excel freeze a few minutes.

The down side of the approach, after several hours, Excel will become slower and slower. It’s look like after extensive abuse of copy and paste, the memory will full of data and slower the Excel application.

Fortunately, after applying empty clipboard Excel using VBA macro, the Excel application keep running smoothly until the end of the day.

There are two ways to clear Excel clipboard using VBA.

1. Empty Clipboard Excel Using Excel Application properties

Application.CopyObjectsWithCells = False

2. Empty Clipboard Excel Using Windows Empty Clipboard function

Public Declare Function OpenClipboard Lib "user32" ( _
    ByVal NewOwner As Long) As Boolean
Public Declare Function EmptyClipboard Lib "user32" () As Boolean
Public Declare Function CloseClipboard Lib "user32" () As Boolean

Sub ClearClipboard()
    ' call ClearClipboard() from your other VBA macro to clear/empty Windows Clipboard

    If OpenClipboard(0) Then
        EmptyClipboard
        CloseClipboard
    End If
End Sub

Both approach will empty the Clipboard, but the first approach will only empty copied data from Excel cells, both values and format. It will not empty Clipboard for any data that belong to application. Using Windows Empty Clipboard function, it will empty Clipboard regardless who is the owner of those data.

Just call one of the two functions above inside your macro, before or after you perform any extensive copy and paste macro to clear your Clipboard. For the 2nd approach, you need to put it in one of your VBA modules first.

Do you have another method to clear/empty Excel Clipboard? Please leave your comments below. Thanks.

Usually in the sequential database system, we can SUM only unique values in table column by adding all the values from SELECT DISTINCT query result only.

Based on the same principle, we can also create a simple VBA function in Microsoft Excel by adding the values only available in a collection of unique values we have created before hand.

Public Function DISTINCTSUM(Rg As range)
    Dim rCell As range
    Dim cCells As New Collection
    Dim vValue As Variant

    ' create a unique no duplicate value collection
    For Each rCell In Rg
        On Error Resume Next
        cCells.Add rCell.Value, CStr(rCell.Value)
    Next rCell

    ' sum all the data in previous collection
    For Each vValue In cCells
        DISTINCTSUM = vValue + DISTINCTSUM
    Next vValue

    Set cCells = Nothing
End Function

In here, first we create a Collection object that contains unique values only. Only then will we loop throught the collection and add up all the values stored there to get the DISTINCT SUM value.

Now we can call the above DISCTINCTSUM formula directly from Excel formula bar to sum only the unique or disctinct values in some range of cells, with the cell range as parameter.

=distinctsum(D4:D8)

Will sum all the unique values in range of D4 until D8. This is really helpful if we have a lot of duplicate data that we don’t want to count.

The other way around to get the sum of unique/distinct values in Excel is using Pivot Table.

Create a Pivot Table, then drop the column containing duplicate values in the Row fields area. Excel will then automatically filtered all the values and remove any duplicate values in that column. Later on, simply SUM all the data in the row fields to get the distinct sum.

The final result will be the same, but we don’t want to create a Pivot Table for every column we wanna sum, right?

Another way to get a list of unique values is to use the Advanced Filter feature. In advanced filtering options, check the option Copy to another location, and Unique records only option. Then select the cell where we want to put the unique values.

Easier to do compare to Pivot Table, but we still don’t have the flexibility of the previous DISTINCTSUM macro.