UNIQUE in Status Bar

[sudipballa]

Hello Team


Is it possible to add a new function - UNIQUE so that we can select and see the value in status bar.

We have functions like SUM, AVERAGE, COUNT and so on. UNIQUE is about selecting a range of cells and seeing the UNIQUE count in status bar

Thanks
Balla

 

[Chihiro]

What is your exact requirement? Complexity of the code will depend on how flexible you need this to be.

1. Is it confined to single sheet or does it need to work on all worksheets?
2. Is it limited to single column or can it be for multiple range?
3. Is it going to be contiguous range only? Or will it need to accommodate Ctrl+LeftMouseClick selection made on non-contiguous cells?
4. How and/or when do you want to release the control of StatusBar back to Excel?

 

[sudipballa]

It should be available as any other similar functions are available...

1. If we select mutiple columns data, we can see sum for all of them. Similar to that.....

Basically it should be available globally...and work similar to other functions.

If this sounds too much to ask:

1. Is it confined to single sheet or does it need to work on all worksheets? - Single Sheet
2. Is it limited to single column or can it be for multiple range? Multiple Range
3. Is it going to be contiguous range only? Or will it need to accommodate Ctrl+LeftMoustClick selection made on non-contiguous cells? Non-contiguous cells
4. How and/or when do you want to release the control of StatusBar back to Excel? Not sure

Thanks
Sudip

 

[Chihiro]

Here. This should get you started.

In ThisWorkbook module.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.StatusBar = False
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.StatusBar = "UNIQUE:=" & UniqueCount(Selection)

End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Application.StatusBar = False
End Sub

In standard module.

Public Function UniqueCount(sRange As Range) As Long
Dim cel As Range
Dim uDict As Object

Set uDict = CreateObject("Scripting.Dictionary")
uDict.CompareMode = 1
tempArr = sRange
On Error GoTo sSingle:
For i = 1 To UBound(tempArr, 1)
    For j = 1 To UBound(tempArr, 2)
        uDict.Item(tempArr(i, j)) = 1
    Next
Next

UniqueCount = uDict.Count

sSingle:
If Err.Number = 13 Then
    For Each cel In Selection
        uDict.Item(cel.Value) = 1
    Next
    UniqueCount = uDict.Count
End If

End Function

It will display at left bottom corner.

NOTE: Unlike regular status bar update, you need to release left mouse button before value is recalculated.

 

[sudipballa]

Thanks a lot, let me try this.

Any suggestions on best way to put the code in STANDARD module?

 

[Chihiro]

In VBE (Editor), just add a module and paste it in there.

 

[sudipballa]

Great it worked perfect. Thanks a ton. I will try to figure out how to make this available for any excel workbook i use.

 

[Klayton]

Chihiro,

I came across this because I am in need of a similar function and I created both a workbook and worksheet module and copied and pasted the codes you came up with but I do not see the option when I click on the status bar to see unique values. I tried highlighting the values I'd like to see the unique values for, I even let go of left click as suggested, and still nothing. Any ideas?

I'm using MS office 2016

 

[Klayton]

Chihiro,

I came across this because I am in need of a similar function and I created both a workbook and worksheet module and copied and pasted the codes you came up with but I do not see the option when I click on the status bar to see unique values. I tried highlighting the values I'd like to see the unique values for, I even let go of left click as suggested, and still nothing. Any ideas?

I'm using MS office 2016

nvm. I didn't do the workbook module correctly. I've fixed this now.

 

[Chihiro]

I was just about to reply. Glad you got it working.