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

"(E-Mail Removed)" 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 <Mi...@discussions.microsoft.com> 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
>
> "ricc...@tin.it" 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, ricc...@tin.it wrote:
> On Jan 10, 1:27 pm, Mike H <Mi...@discussions.microsoft.com> 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
>
> > "ricc...@tin.it" 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, ricc...@tin.it wrote:
> On Jan 10, 1:27 pm, Mike H <Mi...@discussions.microsoft.com> 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
>
> > "ricc...@tin.it" 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, ricc...@tin.it wrote:
> On Jan 10, 1:27 pm, Mike H <Mi...@discussions.microsoft.com> 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
>
> > "ricc...@tin.it" 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

"(E-Mail Removed)" wrote:

> On Jan 10, 1:56 pm, ricc...@tin.it wrote:
> > On Jan 10, 1:27 pm, Mike H <Mi...@discussions.microsoft.com> 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
> >
> > > "ricc...@tin.it" 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 <Mi...@discussions.microsoft.com> wrote:
> Hi,
>
> Tap F9
>
> Mike
>
> "ricc...@tin.it" wrote:
> > On Jan 10, 1:56 pm, ricc...@tin.it wrote:
> > > On Jan 10, 1:27 pm, Mike H <Mi...@discussions.microsoft.com> 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
>
> > > > "ricc...@tin.it" 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, ricc...@tin.it wrote:
> On Jan 10, 1:27 pm, Mike H <Mi...@discussions.microsoft.com> 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
>
> > "ricc...@tin.it" 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, ricc...@tin.it wrote:
> On Jan 10, 1:27 pm, Mike H <Mi...@discussions.microsoft.com> 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
>
> > "ricc...@tin.it" 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.