Author Archive
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.
Excel VBA/Macro example below will extract comment from every cells with comment, and put the summary in the cell selected by user using Excel input box (read more about how to get cell reference using input box here).
Sub CreateCommentsSummary()
Dim rgComments As Range, rgCell As Range, rgOutput As Range, iRow As Integer, iCol As Integer
' get all cells with comment
Set rgComments = ActiveSheet.Cells.SpecialCells(xlCellTypeComments)
' get cell reference where user want to place the summary
Set rgOutput = _
Application.InputBox(Prompt:="Select cell where you want to put the comments summary", _
Title:="Comments Summary", Type:=8)
iRow = rgOutput.Row
iCol = rgOutput.Column
' read each cell with comment and build the summary
For Each rgCell In rgComments
Cells(iRow, iCol) = rgCell.Address ' print cell address
Cells(iRow, iCol + 1) = rgCell.Value ' print cell value
Cells(iRow, iCol + 2) = rgCell.Comment.Text 'print cell comment text
iRow = iRow + 1
Next rgCell
End Sub
Happy coding 
The purpose of the following example of Excel VBA macro is to get the acronym or an abbreviation of each of the first letter of any given words.
Function Acronym(Words As Variant) As String
Dim aWord() As String, ix As Integer
aWord = Split(Words.Value, " ")
For ix = 0 To UBound(aWord)
Acronym = Acronym & UCase(Left(aWord(ix), 1))
Next ix
End Function
Using Split function and space as delimiter, we create an array of word. Then using simple for … to loop, we do looping of each word and take the first letter of each word in array using Left function.
By using iteration and array like this, we will get the acronym of the word in question.
We can directly use the function in Excel formula bar with a reference to the cell that contains the collection of words we want to make an acronym from it as input.
For the details information of each function in use, Split/Ucase/Left, select each function and push F1 while in the Visual Basic Editor.
If you read my latest update on how we can get position of last row containing data, then most probably you already have a glimpse on how we perform the same task with last column containing data in Excel.
The VBA code is the exact replica of the code to get the last row, only this time, we change the row into column.
Function LastColumn() As Long
Dim ix As Long
ix = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
LastColumn = ix
End Function
The function above will return the appropriate position of the column even when the last column containing data in a hidden state.
The results of this function is the column number, not the column name (1 for A, 2 for B, etc). If what we need is the column name, then we can use the excel column number to column name converter that I wrote before.
Simple Microsoft Excel formula to find the last day of any given month.
'
=DATE(YEAR(A1),MONTH(A1)+1,0)
'
I once asked by Joshuacht about how to make a macro running every 15 minutes. Let say because every 15 minutes we have to refresh or reload the data in the Workbook.
Below is a sample of how we can do this in Microsoft Excel *just an outline*.
The logic is simple, when we open the Workbook, we call for the first time the procedure needed to refresh the data. In that procedure, we also add a code to call back the procedure itself 15 minutes later. So while the Workbook is still open, the procedure will continue to call itself every 15 minutes.
' Put this procedure in ThisWorkbook module
Private Sub Workbook_Open()
' Run the procedure to refresh data when the workbook opened
Call RefreshData
End Sub
' Add new module and put this procedure in it
Public Sub RefreshData()
Debug.Print "Data refreshed at " & Now
'
' This is where we put everything needed to refresh the data
'
' Schedules this procedure to be run 15 minutes from now
Application.OnTime Now + TimeValue("00:15:00"), "RefreshData"
End Sub
Even looks like an infinite loop, this is not a problem for Excel when we use Application.OnTime function. The scheduling will stop automatically when the Workbook is close.
There is a time when we must create a set of Excel Worksheets templates on a regular basis in our work. For example, a set of Excel Worksheet for each month of the year or may be based on the type of work we should done in certain time base.
Here a simple example of how to create a new set 12 Worksheets for each month of the year.
Sub CreateMonthlyWorksheetTemplate()
Dim ix As Integer, oSheet As Worksheet, sMonth As String
For ix = 1 To 12
'add new worksheet
Set oSheet = Worksheets.Add
'get month name from index
sMonth = Format("1/" & CStr(ix), "mmm")
'rename the new worksheet
oSheet.Name = sMonth
Set oSheet = Nothing
Next ix
End Sub
Read also a previous post about how vba add worksheet complete with error handler when we counter duplicate Worksheet.
