Hi to everyone,
does anyone to know how to build a function, not a macro, to count the
number of comments in a cells range?

searching to this news group I found this code:

Public Function CommentCount()
Dim cell As Range
Dim cnt As Long
Dim cmt As Comment
Application.Volatile
On Error Resume Next
For Each cell In Selection
Set cmt = Nothing
Set cmt = cell.Comment
If Not cmt Is Nothing Then
cnt = cnt + 1
End If
Next cell
CommentCount = cnt
End Function

but it doesn't seem to work for me. Can someone help me?

I really do appreciate who will give to me an hand
Bye,Stefano.

Hi,

Sending a range to it looks simpler
call with
=CommentCount(A1:a100)
Not it must be inserted in a module

Public Function CommentCount(MyRange As Range)
Dim cell As Range
Dim cnt As Long
Dim cmt As Comment
Application.Volatile
On Error Resume Next
For Each cell In MyRange
Set cmt = Nothing
Set cmt = cell.Comment
If Not cmt Is Nothing Then
cnt = cnt + 1
End If
Next cell
CommentCount = cnt
End Function

" wrote:

Hi to everyone,
does anyone to know how to build a function, not a macro, to count the
number of comments in a cells range?

searching to this news group I found this code:

Public Function CommentCount()
Dim cell As Range
Dim cnt As Long
Dim cmt As Comment
Application.Volatile
On Error Resume Next
For Each cell In Selection
Set cmt = Nothing
Set cmt = cell.Comment
If Not cmt Is Nothing Then
cnt = cnt + 1
End If
Next cell
CommentCount = cnt
End Function

but it doesn't seem to work for me. Can someone help me?

I really do appreciate who will give to me an hand
Bye,Stefano.

On Jan 10, 1:27 pm, Mike H wrote:
Hi,

Sending a range to it looks simpler
call with
=CommentCount(A1:a100)
Not it must be inserted in a module

Public Function CommentCount(MyRange As Range)
Dim cell As Range
Dim cnt As Long
Dim cmt As Comment
Application.Volatile
On Error Resume Next
For Each cell In MyRange
Set cmt = Nothing
Set cmt = cell.Comment
If Not cmt Is Nothing Then
cnt = cnt + 1
End If
Next cell
CommentCount = cnt
End Function

" wrote:
Hi to everyone,
does anyone to know how to build a function, not a macro, to count the
number of comments in a cells range?

searching to this news group I found this code:

Public Function CommentCount()
Dim cell As Range
Dim cnt As Long
Dim cmt As Comment
Application.Volatile
On Error Resume Next
For Each cell In Selection
Set cmt = Nothing
Set cmt = cell.Comment
If Not cmt Is Nothing Then
cnt = cnt + 1
End If
Next cell
CommentCount = cnt
End Function

but it doesn't seem to work for me. Can someone help me?

I really do appreciate who will give to me an hand
Bye,Stefano.

Hi Mike,
now it works very well!

Many many thanks for the great help you give to me,
Bye, Stefano.

On Jan 10, 1:56 pm, wrote:
On Jan 10, 1:27 pm, Mike H wrote:



Hi,

Sending a range to it looks simpler
call with
=CommentCount(A1:a100)
Not it must be inserted in a module

Public Function CommentCount(MyRange As Range)
Dim cell As Range
Dim cnt As Long
Dim cmt As Comment
Application.Volatile
On Error Resume Next
For Each cell In MyRange
Set cmt = Nothing
Set cmt = cell.Comment
If Not cmt Is Nothing Then
cnt = cnt + 1
End If
Next cell
CommentCount = cnt
End Function

" wrote:
Hi to everyone,
does anyone to know how to build a function, not a macro, to count the
number of comments in a cells range?

searching to this news group I found this code:

Public Function CommentCount()
Dim cell As Range
Dim cnt As Long
Dim cmt As Comment
Application.Volatile
On Error Resume Next
For Each cell In Selection
Set cmt = Nothing
Set cmt = cell.Comment
If Not cmt Is Nothing Then
cnt = cnt + 1
End If
Next cell
CommentCount = cnt
End Function

but it doesn't seem to work for me. Can someone help me?

I really do appreciate who will give to me an hand
Bye,Stefano.

Hi Mike,
now it works very well!

Many many thanks for the great help you give to me,
Bye, Stefano.

Hi Mike,
let me ask to you just a last thing.

Do you know how to automatic update that formula?
I mean, without hit enter every time, on the cell where I insert the
function.

Bye, Stefano.

On Jan 10, 1:56 pm, wrote:
On Jan 10, 1:27 pm, Mike H wrote:



Hi,

Sending a range to it looks simpler
call with
=CommentCount(A1:a100)
Not it must be inserted in a module

Public Function CommentCount(MyRange As Range)
Dim cell As Range
Dim cnt As Long
Dim cmt As Comment
Application.Volatile
On Error Resume Next
For Each cell In MyRange
Set cmt = Nothing
Set cmt = cell.Comment
If Not cmt Is Nothing Then
cnt = cnt + 1
End If
Next cell
CommentCount = cnt
End Function

" wrote:
Hi to everyone,
does anyone to know how to build a function, not a macro, to count the
number of comments in a cells range?

searching to this news group I found this code:

Public Function CommentCount()
Dim cell As Range
Dim cnt As Long
Dim cmt As Comment
Application.Volatile
On Error Resume Next
For Each cell In Selection
Set cmt = Nothing
Set cmt = cell.Comment
If Not cmt Is Nothing Then
cnt = cnt + 1
End If
Next cell
CommentCount = cnt
End Function

but it doesn't seem to work for me. Can someone help me?

I really do appreciate who will give to me an hand
Bye,Stefano.

Hi Mike,
now it works very well!

Many many thanks for the great help you give to me,
Bye, Stefano.

Hi Mike,
let me ask to you just a last thing.

Do you know how to automatic update that formula?
I mean, without hit enter every time, on the cell where I insert the
function.

Bye, Stefano.

On Jan 10, 1:56 pm, wrote:
On Jan 10, 1:27 pm, Mike H wrote:



Hi,

Sending a range to it looks simpler
call with
=CommentCount(A1:a100)
Not it must be inserted in a module

Public Function CommentCount(MyRange As Range)
Dim cell As Range
Dim cnt As Long
Dim cmt As Comment
Application.Volatile
On Error Resume Next
For Each cell In MyRange
Set cmt = Nothing
Set cmt = cell.Comment
If Not cmt Is Nothing Then
cnt = cnt + 1
End If
Next cell
CommentCount = cnt
End Function

" wrote:
Hi to everyone,
does anyone to know how to build a function, not a macro, to count the
number of comments in a cells range?

searching to this news group I found this code:

Public Function CommentCount()
Dim cell As Range
Dim cnt As Long
Dim cmt As Comment
Application.Volatile
On Error Resume Next
For Each cell In Selection
Set cmt = Nothing
Set cmt = cell.Comment
If Not cmt Is Nothing Then
cnt = cnt + 1
End If
Next cell
CommentCount = cnt
End Function

but it doesn't seem to work for me. Can someone help me?

I really do appreciate who will give to me an hand
Bye,Stefano.

Hi Mike,
now it works very well!

Many many thanks for the great help you give to me,
Bye, Stefano.

Hi Mike,
let me ask to you just a last thing.

Do you know how to automatic update that formula?
I mean, without hit enter every time, on the cell where I insert the
function.

Bye, Stefano.

Hi,

Tap F9

Mike

" wrote:

On Jan 10, 1:56 pm, wrote:
On Jan 10, 1:27 pm, Mike H wrote:



Hi,

Sending a range to it looks simpler
call with
=CommentCount(A1:a100)
Not it must be inserted in a module

Public Function CommentCount(MyRange As Range)
Dim cell As Range
Dim cnt As Long
Dim cmt As Comment
Application.Volatile
On Error Resume Next
For Each cell In MyRange
Set cmt = Nothing
Set cmt = cell.Comment
If Not cmt Is Nothing Then
cnt = cnt + 1
End If
Next cell
CommentCount = cnt
End Function

" wrote:
Hi to everyone,
does anyone to know how to build a function, not a macro, to count the
number of comments in a cells range?

searching to this news group I found this code:

Public Function CommentCount()
Dim cell As Range
Dim cnt As Long
Dim cmt As Comment
Application.Volatile
On Error Resume Next
For Each cell In Selection
Set cmt = Nothing
Set cmt = cell.Comment
If Not cmt Is Nothing Then
cnt = cnt + 1
End If
Next cell
CommentCount = cnt
End Function

but it doesn't seem to work for me. Can someone help me?

I really do appreciate who will give to me an hand
Bye,Stefano.

Hi Mike,
now it works very well!

Many many thanks for the great help you give to me,
Bye, Stefano.

Hi Mike,
let me ask to you just a last thing.

Do you know how to automatic update that formula?
I mean, without hit enter every time, on the cell where I insert the
function.

Bye, Stefano.

On Jan 10, 2:46 pm, Mike H wrote:
Hi,

Tap F9

Mike

" wrote:
On Jan 10, 1:56 pm, wrote:
On Jan 10, 1:27 pm, Mike H wrote:

Hi,

Sending a range to it looks simpler
call with
=CommentCount(A1:a100)
Not it must be inserted in a module

Public Function CommentCount(MyRange As Range)
Dim cell As Range
Dim cnt As Long
Dim cmt As Comment
Application.Volatile
On Error Resume Next
For Each cell In MyRange
Set cmt = Nothing
Set cmt = cell.Comment
If Not cmt Is Nothing Then
cnt = cnt + 1
End If
Next cell
CommentCount = cnt
End Function

" wrote:
Hi to everyone,
does anyone to know how to build a function, not a macro, to count the
number of comments in a cells range?

searching to this news group I found this code:

Public Function CommentCount()
Dim cell As Range
Dim cnt As Long
Dim cmt As Comment
Application.Volatile
On Error Resume Next
For Each cell In Selection
Set cmt = Nothing
Set cmt = cell.Comment
If Not cmt Is Nothing Then
cnt = cnt + 1
End If
Next cell
CommentCount = cnt
End Function

but it doesn't seem to work for me. Can someone help me?

I really do appreciate who will give to me an hand
Bye,Stefano.

Hi Mike,
now it works very well!

Many many thanks for the great help you give to me,
Bye, Stefano.

Hi Mike,
let me ask to you just a last thing.

Do you know how to automatic update that formula?
I mean, without hit enter every time, on the cell where I insert the
function.

Bye, Stefano.

Hi Mike,
I'd like to make it automatic when I add and/or when I delete a
comment
I tried with "Application.Volatile True" but it does not work.

Bye, Stefano.

P.S.
sorry for the double previous post, I make I mistake.

On Jan 10, 1:56 pm, wrote:
On Jan 10, 1:27 pm, Mike H wrote:



Hi,

Sending a range to it looks simpler
call with
=CommentCount(A1:a100)
Not it must be inserted in a module

Public Function CommentCount(MyRange As Range)
Dim cell As Range
Dim cnt As Long
Dim cmt As Comment
Application.Volatile
On Error Resume Next
For Each cell In MyRange
Set cmt = Nothing
Set cmt = cell.Comment
If Not cmt Is Nothing Then
cnt = cnt + 1
End If
Next cell
CommentCount = cnt
End Function

" wrote:
Hi to everyone,
does anyone to know how to build a function, not a macro, to count the
number of comments in a cells range?

searching to this news group I found this code:

Public Function CommentCount()
Dim cell As Range
Dim cnt As Long
Dim cmt As Comment
Application.Volatile
On Error Resume Next
For Each cell In Selection
Set cmt = Nothing
Set cmt = cell.Comment
If Not cmt Is Nothing Then
cnt = cnt + 1
End If
Next cell
CommentCount = cnt
End Function

but it doesn't seem to work for me. Can someone help me?

I really do appreciate who will give to me an hand
Bye,Stefano.

Hi Mike,
now it works very well!

Many many thanks for the great help you give to me,
Bye, Stefano.

Hi Mike,
let me ask to you just a last thing.

Do you know how to automatic update that formula?
I mean, without hit enter every time, on the cell where I insert the
function.

Bye, Stefano.

On Jan 10, 1:56 pm, wrote:
On Jan 10, 1:27 pm, Mike H wrote:



Hi,

Sending a range to it looks simpler
call with
=CommentCount(A1:a100)
Not it must be inserted in a module

Public Function CommentCount(MyRange As Range)
Dim cell As Range
Dim cnt As Long
Dim cmt As Comment
Application.Volatile
On Error Resume Next
For Each cell In MyRange
Set cmt = Nothing
Set cmt = cell.Comment
If Not cmt Is Nothing Then
cnt = cnt + 1
End If
Next cell
CommentCount = cnt
End Function

" wrote:
Hi to everyone,
does anyone to know how to build a function, not a macro, to count the
number of comments in a cells range?

searching to this news group I found this code:

Public Function CommentCount()
Dim cell As Range
Dim cnt As Long
Dim cmt As Comment
Application.Volatile
On Error Resume Next
For Each cell In Selection
Set cmt = Nothing
Set cmt = cell.Comment
If Not cmt Is Nothing Then
cnt = cnt + 1
End If
Next cell
CommentCount = cnt
End Function

but it doesn't seem to work for me. Can someone help me?

I really do appreciate who will give to me an hand
Bye,Stefano.

Hi Mike,
now it works very well!

Many many thanks for the great help you give to me,
Bye, Stefano.

Hi Mike,
let me ask to you just a last thing.

Do you know how to automatic update that formula?
I mean, without hit enter every time, on the cell where I insert the
function.

Bye, Stefano.

On Jan 10, 1:56 pm, wrote:
On Jan 10, 1:27 pm, Mike H wrote:



Hi,

Sending a range to it looks simpler
call with
=CommentCount(A1:a100)
Not it must be inserted in a module

Public Function CommentCount(MyRange As Range)
Dim cell As Range
Dim cnt As Long
Dim cmt As Comment
Application.Volatile
On Error Resume Next
For Each cell In MyRange
Set cmt = Nothing
Set cmt = cell.Comment
If Not cmt Is Nothing Then
cnt = cnt + 1
End If
Next cell
CommentCount = cnt
End Function

" wrote:
Hi to everyone,
does anyone to know how to build a function, not a macro, to count the
number of comments in a cells range?

searching to this news group I found this code:

Public Function CommentCount()
Dim cell As Range
Dim cnt As Long
Dim cmt As Comment
Application.Volatile
On Error Resume Next
For Each cell In Selection
Set cmt = Nothing
Set cmt = cell.Comment
If Not cmt Is Nothing Then
cnt = cnt + 1
End If
Next cell
CommentCount = cnt
End Function

but it doesn't seem to work for me. Can someone help me?

I really do appreciate who will give to me an hand
Bye,Stefano.

Hi Mike,
now it works very well!

Many many thanks for the great help you give to me,
Bye, Stefano.

Hi Mike,
let me ask to you just a last thing.

Do you know how to automatic update that formula?
I mean, without hit enter every time, on the cell where I insert the
function.

Bye, Stefano.

I'd like to make it automatic when I add and/or when I delete a
comment
I tried with "Application.Volatile True" but it does not work.

I don't have an answer as to how to do this (it appears that adding and deleting comments doesn't generate any events); however, unless I missed something, this function appears to do what your originally posted code does, but is a little bit shorter...

Public Function CommentCount(MyRange As Range)
Dim cell As Range
On Error Resume Next
For Each cell In MyRange
CommentCount = CommentCount - (cell.Comment.Text < "")
Next cell
End Function

Rick

On Jan 10, 5:10 pm, "Rick Rothstein \(MVP - VB\)"
wrote:
I'd like to make it automatic when I add and/or when I delete a
comment
I tried with "Application.Volatile True" but it does not work.

I don't have an answer as to how to do this (it appears that adding and deleting comments doesn't generate any events); however, unless I missed something, this function appears to do what your originally posted code does, but is a little bit shorter...

Public Function CommentCount(MyRange As Range)
Dim cell As Range
On Error Resume Next
For Each cell In MyRange
CommentCount = CommentCount - (cell.Comment.Text < "")
Next cell
End Function

Rick

Hi Rick,
many thanks the same for help!

Bye, Stefano.