Add comments via a function - please could you ammend my code/ pseudocode to work

So then using comments to mark accessed cells (as this is the only option
with functions since they are not able to modifying cell formats in other
ways)



Function FindOldNominal(NomCode, definedRange)

FindOldNominal = WorksheetFunction.VLookup(NomCode, definedRange, 5, False)


'(now attempt to mark the accessed cell - the bit not yet working!!!!)


Dim rng As Range
Dim noOfRows As Integer

noOfRows = Match(NomCode, definedRange, 0)
rng = Offset(A1, noOfRows, 0, 1, 1)
rng.AddComment ("accesses")

EndFunction

I realise the above is probably incorrect syntax, but have only just started
to learn macro and excel functions (not even sure functions are found in
what classes etc).

Please could you ammend the above so it works. Hopefully the gist of what
i'm trying is clearer enough.
Many many thanks in anticipation
James







PREVIOUS POST

""Gary replied..............

A function can deposit comments in cells:


Function demo(r As Range) As Integer
demo = 1
If r.Comment Is Nothing Then
Else
r.Comment.Delete
End If
r.AddComment Text:="marked"
End Function


This dumb little macro just returns 1, but it marks its reference with a
comment

=demo(A1) results in A1 getting a comment. So even if you can't color A1,
you can mark it.
--
Gary's Student


"James Cornthwaite" wrote:

> o right, never straight forwards is it.
>
> Is there any other way around the problem, to achieve the same
> goal.??????????
>
> Thanks
> James
> "Chip Pearson" <chip@cpearson.com> wrote in message
> news:eAbRWH$hGHA.4284@TK2MSFTNGP05.phx.gbl...
> >A function called from a worksheet cell can only return a value to the
> >calling cell. It cannot change the value or format of any cell.
> >
> >
> > --
> > Cordially,
> > Chip Pearson
> > Microsoft MVP - Excel
> > Pearson Software Consulting, LLC
> > www.cpearson.com
> >
> >
> > "James Cornthwaite" <jamescornthwaite@btinternet.com> wrote in message
> > news:2LKdnTMWpcwdmR7ZRVnyhA@bt.com...
> >> Ideally i would like to color the contents of the cell accessed by the
> >> call of my function "findnewnominal(...)" The reason for this is the
> >> function is called many times on a range table range and I would like
> >> to
> >> know at a glance which rows in the table hadn't yet been accessed by a
> >> calls of the function.
> >>
> >> My macro to date (which works fine is)
> >>
> >>
> >> Function FindOldNominal(NomCode, definedRange)
> >>
> >> FindOldNominal = Worksheetfunction.VLookup(NomCode, definedRange, 5,
> >> false)
> >> EndFunction
> >>
> >>
> >>
> >>
> >>
> >> but if i want to color the cell accessed I imagine i have to use
> >> something like the following? (my attempt of something pseudocode)
> >>
> >>
> >> Function FindOldNominal(NomCode, definedRange)
> >>
> >> Activecell = reference(Worksheetfunction.VLookup(NomCode, definedRange,
> >> 5, false))
> >> Activecell.Interior.ColorIndex = 3 (i.e red etc)
> >>
> >> FindOldNominal = Worksheetfunction.VLookup(NomCode, definedRange, 5,
> >> false)
> >> EndFunction
> >>
> >>
> >> I appreciate the above is probably non sensical but know little about
> >> excel functions and just wanted to make as clear as possible what i
> >> wanted to do.
> >> Any help would be greatly appreciated.
> >>
> >> Thanks
> >> James
> >>
> >>
> >
> >
>
>
>

Why not go back and post in the original thread.

--
Regards,
Tom Ogilvy


"James Cornthwaite" <jamescornthwaite@btinternet.com> wrote in message
news:SpednVFNyJrlBxnZRVny2w@bt.com...
> So then using comments to mark accessed cells (as this is the only option
> with functions since they are not able to modifying cell formats in other
> ways)
>
>
>
> Function FindOldNominal(NomCode, definedRange)
>
> FindOldNominal = WorksheetFunction.VLookup(NomCode, definedRange, 5,
False)
>
>
> '(now attempt to mark the accessed cell - the bit not yet working!!!!)
>
>
> Dim rng As Range
> Dim noOfRows As Integer
>
> noOfRows = Match(NomCode, definedRange, 0)
> rng = Offset(A1, noOfRows, 0, 1, 1)
> rng.AddComment ("accesses")
>
> EndFunction
>
> I realise the above is probably incorrect syntax, but have only just
started
> to learn macro and excel functions (not even sure functions are found in
> what classes etc).
>
> Please could you ammend the above so it works. Hopefully the gist of what
> i'm trying is clearer enough.
> Many many thanks in anticipation
> James
>
>
>
>
>
>
>
> PREVIOUS POST
>
> ""Gary replied..............
>
> A function can deposit comments in cells:
>
>
> Function demo(r As Range) As Integer
> demo = 1
> If r.Comment Is Nothing Then
> Else
> r.Comment.Delete
> End If
> r.AddComment Text:="marked"
> End Function
>
>
> This dumb little macro just returns 1, but it marks its reference with a
> comment
>
> =demo(A1) results in A1 getting a comment. So even if you can't color A1,
> you can mark it.
> --
> Gary's Student
>
>
> "James Cornthwaite" wrote:
>
> > o right, never straight forwards is it.
> >
> > Is there any other way around the problem, to achieve the same
> > goal.??????????
> >
> > Thanks
> > James
> > "Chip Pearson" <chip@cpearson.com> wrote in message
> > news:eAbRWH$hGHA.4284@TK2MSFTNGP05.phx.gbl...
> > >A function called from a worksheet cell can only return a value to the
> > >calling cell. It cannot change the value or format of any cell.
> > >
> > >
> > > --
> > > Cordially,
> > > Chip Pearson
> > > Microsoft MVP - Excel
> > > Pearson Software Consulting, LLC
> > > www.cpearson.com
> > >
> > >
> > > "James Cornthwaite" <jamescornthwaite@btinternet.com> wrote in message
> > > news:2LKdnTMWpcwdmR7ZRVnyhA@bt.com...
> > >> Ideally i would like to color the contents of the cell accessed by
the
> > >> call of my function "findnewnominal(...)" The reason for this is the
> > >> function is called many times on a range table range and I would like
> > >> to
> > >> know at a glance which rows in the table hadn't yet been accessed by
a
> > >> calls of the function.
> > >>
> > >> My macro to date (which works fine is)
> > >>
> > >>
> > >> Function FindOldNominal(NomCode, definedRange)
> > >>
> > >> FindOldNominal = Worksheetfunction.VLookup(NomCode, definedRange, 5,
> > >> false)
> > >> EndFunction
> > >>
> > >>
> > >>
> > >>
> > >>
> > >> but if i want to color the cell accessed I imagine i have to use
> > >> something like the following? (my attempt of something pseudocode)
> > >>
> > >>
> > >> Function FindOldNominal(NomCode, definedRange)
> > >>
> > >> Activecell = reference(Worksheetfunction.VLookup(NomCode,
definedRange,
> > >> 5, false))
> > >> Activecell.Interior.ColorIndex = 3 (i.e red etc)
> > >>
> > >> FindOldNominal = Worksheetfunction.VLookup(NomCode, definedRange, 5,
> > >> false)
> > >> EndFunction
> > >>
> > >>
> > >> I appreciate the above is probably non sensical but know little about
> > >> excel functions and just wanted to make as clear as possible what i
> > >> wanted to do.
> > >> Any help would be greatly appreciated.
> > >>
> > >> Thanks
> > >> James
> > >>
> > >>
> > >
> > >
> >
> >
> >
>
>

Are you using this UDF on a worksheet?

Do you pass the NomCode as a range?
Kind of:
=findoldnominal(a1,sheet2!a:e)

Are you trying to add the comment to the NomCode cell?

If yes, then maybe...

Option Explicit
Function FindOldNominal(NomCode As Range, definedRange As Range) As Variant

Dim res As Variant 'could be an error

res = Application.VLookup(NomCode, definedRange, 5, False)
If IsError(res) Then
FindOldNominal = "Not Found"
Else
FindOldNominal = res
End If

On Error Resume Next
NomCode.Comment.Delete
On Error GoTo 0

NomCode.AddComment Text:="accesses"

End Function

There are some "interesting" things about the way worksheetfunction.vlookup()
works in comparison to application.vlookup().

I chose to use application.vlookup() to make my life simpler.

If this doesn't do what you want and you can't modify it, post back with a few
more details. I'm sure you'll get some responses.

James Cornthwaite wrote:
>
> So then using comments to mark accessed cells (as this is the only option
> with functions since they are not able to modifying cell formats in other
> ways)
>
> Function FindOldNominal(NomCode, definedRange)
>
> FindOldNominal = WorksheetFunction.VLookup(NomCode, definedRange, 5, False)
>
> '(now attempt to mark the accessed cell - the bit not yet working!!!!)
>
> Dim rng As Range
> Dim noOfRows As Integer
>
> noOfRows = Match(NomCode, definedRange, 0)
> rng = Offset(A1, noOfRows, 0, 1, 1)
> rng.AddComment ("accesses")
>
> EndFunction
>
> I realise the above is probably incorrect syntax, but have only just started
> to learn macro and excel functions (not even sure functions are found in
> what classes etc).
>
> Please could you ammend the above so it works. Hopefully the gist of what
> i'm trying is clearer enough.
> Many many thanks in anticipation
> James
>
> PREVIOUS POST
>
> ""Gary replied..............
>
> A function can deposit comments in cells:
>
> Function demo(r As Range) As Integer
> demo = 1
> If r.Comment Is Nothing Then
> Else
> r.Comment.Delete
> End If
> r.AddComment Text:="marked"
> End Function
>
> This dumb little macro just returns 1, but it marks its reference with a
> comment
>
> =demo(A1) results in A1 getting a comment. So even if you can't color A1,
> you can mark it.
> --
> Gary's Student
>
> "James Cornthwaite" wrote:
>
> > o right, never straight forwards is it.
> >
> > Is there any other way around the problem, to achieve the same
> > goal.??????????
> >
> > Thanks
> > James
> > "Chip Pearson" <chip@cpearson.com> wrote in message
> > news:eAbRWH$hGHA.4284@TK2MSFTNGP05.phx.gbl...
> > >A function called from a worksheet cell can only return a value to the
> > >calling cell. It cannot change the value or format of any cell.
> > >
> > >
> > > --
> > > Cordially,
> > > Chip Pearson
> > > Microsoft MVP - Excel
> > > Pearson Software Consulting, LLC
> > > www.cpearson.com
> > >
> > >
> > > "James Cornthwaite" <jamescornthwaite@btinternet.com> wrote in message
> > > news:2LKdnTMWpcwdmR7ZRVnyhA@bt.com...
> > >> Ideally i would like to color the contents of the cell accessed by the
> > >> call of my function "findnewnominal(...)" The reason for this is the
> > >> function is called many times on a range table range and I would like
> > >> to
> > >> know at a glance which rows in the table hadn't yet been accessed by a
> > >> calls of the function.
> > >>
> > >> My macro to date (which works fine is)
> > >>
> > >>
> > >> Function FindOldNominal(NomCode, definedRange)
> > >>
> > >> FindOldNominal = Worksheetfunction.VLookup(NomCode, definedRange, 5,
> > >> false)
> > >> EndFunction
> > >>
> > >>
> > >>
> > >>
> > >>
> > >> but if i want to color the cell accessed I imagine i have to use
> > >> something like the following? (my attempt of something pseudocode)
> > >>
> > >>
> > >> Function FindOldNominal(NomCode, definedRange)
> > >>
> > >> Activecell = reference(Worksheetfunction.VLookup(NomCode, definedRange,
> > >> 5, false))
> > >> Activecell.Interior.ColorIndex = 3 (i.e red etc)
> > >>
> > >> FindOldNominal = Worksheetfunction.VLookup(NomCode, definedRange, 5,
> > >> false)
> > >> EndFunction
> > >>
> > >>
> > >> I appreciate the above is probably non sensical but know little about
> > >> excel functions and just wanted to make as clear as possible what i
> > >> wanted to do.
> > >> Any help would be greatly appreciated.
> > >>
> > >> Thanks
> > >> James
> > >>
> > >>
> > >
> > >
> >
> >
> >

--

Dave Peterson

Thanks for the reply. To be quite honest dont really understand this code so
not sure if it acheives what i want.

Don't know what "option explicit" means,
what a variant is or each line of code after res = vlookup(....... )

Can somebody please explain the code or suggest another way if this is
perhaps not suitable (in light of the extra info i give underneath)

I pass nomCode as a fixed integer (reason for function in first place is
function is called in several places in a seperate presentational style
worksheet)
I am using this UDF on a worksheet yes.

Many thanks in anticipation

James

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:4484AF04.ACC3C6EE@verizonXSPAM.net...
> Are you using this UDF on a worksheet?
>
> Do you pass the NomCode as a range?
> Kind of:
> =findoldnominal(a1,sheet2!a:e)
>
> Are you trying to add the comment to the NomCode cell?
>
> If yes, then maybe...
>
> Option Explicit
> Function FindOldNominal(NomCode As Range, definedRange As Range) As
> Variant
>
> Dim res As Variant 'could be an error
>
> res = Application.VLookup(NomCode, definedRange, 5, False)
> If IsError(res) Then
> FindOldNominal = "Not Found"
> Else
> FindOldNominal = res
> End If
>
> On Error Resume Next
> NomCode.Comment.Delete
> On Error GoTo 0
>
> NomCode.AddComment Text:="accesses"
>
> End Function
>
> There are some "interesting" things about the way
> worksheetfunction.vlookup()
> works in comparison to application.vlookup().
>
> I chose to use application.vlookup() to make my life simpler.
>
> If this doesn't do what you want and you can't modify it, post back with a
> few
> more details. I'm sure you'll get some responses.
>
> James Cornthwaite wrote:
>>
>> So then using comments to mark accessed cells (as this is the only option
>> with functions since they are not able to modifying cell formats in other
>> ways)
>>
>> Function FindOldNominal(NomCode, definedRange)
>>
>> FindOldNominal = WorksheetFunction.VLookup(NomCode, definedRange, 5,
>> False)
>>
>> '(now attempt to mark the accessed cell - the bit not yet working!!!!)
>>
>> Dim rng As Range
>> Dim noOfRows As Integer
>>
>> noOfRows = Match(NomCode, definedRange, 0)
>> rng = Offset(A1, noOfRows, 0, 1, 1)
>> rng.AddComment ("accesses")
>>
>> EndFunction
>>
>> I realise the above is probably incorrect syntax, but have only just
>> started
>> to learn macro and excel functions (not even sure functions are found in
>> what classes etc).
>>
>> Please could you ammend the above so it works. Hopefully the gist of what
>> i'm trying is clearer enough.
>> Many many thanks in anticipation
>> James
>>
>> PREVIOUS POST
>>
>> ""Gary replied..............
>>
>> A function can deposit comments in cells:
>>
>> Function demo(r As Range) As Integer
>> demo = 1
>> If r.Comment Is Nothing Then
>> Else
>> r.Comment.Delete
>> End If
>> r.AddComment Text:="marked"
>> End Function
>>
>> This dumb little macro just returns 1, but it marks its reference with a
>> comment
>>
>> =demo(A1) results in A1 getting a comment. So even if you can't color
>> A1,
>> you can mark it.
>> --
>> Gary's Student
>>
>> "James Cornthwaite" wrote:
>>
>> > o right, never straight forwards is it.
>> >
>> > Is there any other way around the problem, to achieve the same
>> > goal.??????????
>> >
>> > Thanks
>> > James
>> > "Chip Pearson" <chip@cpearson.com> wrote in message
>> > news:eAbRWH$hGHA.4284@TK2MSFTNGP05.phx.gbl...
>> > >A function called from a worksheet cell can only return a value to the
>> > >calling cell. It cannot change the value or format of any cell.
>> > >
>> > >
>> > > --
>> > > Cordially,
>> > > Chip Pearson
>> > > Microsoft MVP - Excel
>> > > Pearson Software Consulting, LLC
>> > > www.cpearson.com
>> > >
>> > >
>> > > "James Cornthwaite" <jamescornthwaite@btinternet.com> wrote in
>> > > message
>> > > news:2LKdnTMWpcwdmR7ZRVnyhA@bt.com...
>> > >> Ideally i would like to color the contents of the cell accessed by
>> > >> the
>> > >> call of my function "findnewnominal(...)" The reason for this is the
>> > >> function is called many times on a range table range and I would
>> > >> like
>> > >> to
>> > >> know at a glance which rows in the table hadn't yet been accessed by
>> > >> a
>> > >> calls of the function.
>> > >>
>> > >> My macro to date (which works fine is)
>> > >>
>> > >>
>> > >> Function FindOldNominal(NomCode, definedRange)
>> > >>
>> > >> FindOldNominal = Worksheetfunction.VLookup(NomCode, definedRange, 5,
>> > >> false)
>> > >> EndFunction
>> > >>
>> > >>
>> > >>
>> > >>
>> > >>
>> > >> but if i want to color the cell accessed I imagine i have to use
>> > >> something like the following? (my attempt of something pseudocode)
>> > >>
>> > >>
>> > >> Function FindOldNominal(NomCode, definedRange)
>> > >>
>> > >> Activecell = reference(Worksheetfunction.VLookup(NomCode,
>> > >> definedRange,
>> > >> 5, false))
>> > >> Activecell.Interior.ColorIndex = 3 (i.e red etc)
>> > >>
>> > >> FindOldNominal = Worksheetfunction.VLookup(NomCode, definedRange, 5,
>> > >> false)
>> > >> EndFunction
>> > >>
>> > >>
>> > >> I appreciate the above is probably non sensical but know little
>> > >> about
>> > >> excel functions and just wanted to make as clear as possible what i
>> > >> wanted to do.
>> > >> Any help would be greatly appreciated.
>> > >>
>> > >> Thanks
>> > >> James
>> > >>
>> > >>
>> > >
>> > >
>> >
>> >
>> >
>
> --
>
> Dave Peterson

'this line tells excel that we want to declare every variable that we use.
'it's a good way to make sure you don't make silly typing mistakes
'excel won't let your code even run if you have a variable that isn't
'declared somewhere with a Dim statement.
Option Explicit

'variant means that that thing could be a string "A", "x", "Test" or
'a number 1,2,3...3.14159, or anything else
Function FindOldNominal(NomCode As Range, definedRange As Range) As Variant

'variants can also hold errors
Dim res As Variant 'could be an error

'if you use =vlookup() in a cell, you can get a value or
'#n/a. Res is just a variable that holds that result
res = Application.VLookup(NomCode, definedRange, 5, False)

'if =vlookup() would return an error (#n/a), then this
'function returns the string "Not Found"
If IsError(res) Then
FindOldNominal = "Not Found"
Else
'if the =vlookup() worked ok, then it returns whatever
'was found
FindOldNominal = res
End If

'if there's no comment, then deleting the non-existent comment will
'cause an error.
'this line tells excel that I want that error ignored.
On Error Resume Next
'delete the comment from that cell that contained the NomCode
NomCode.Comment.Delete
'this line tells excel to go back and keep looking for errors
On Error GoTo 0

'add a comment to the cell that was passed to the function.
NomCode.AddComment Text:="accesses"

End Function

James Cornthwaite wrote:
>
> Thanks for the reply. To be quite honest dont really understand this code so
> not sure if it acheives what i want.
>
> Don't know what "option explicit" means,
> what a variant is or each line of code after res = vlookup(....... )
>
> Can somebody please explain the code or suggest another way if this is
> perhaps not suitable (in light of the extra info i give underneath)
>
> I pass nomCode as a fixed integer (reason for function in first place is
> function is called in several places in a seperate presentational style
> worksheet)
> I am using this UDF on a worksheet yes.
>
> Many thanks in anticipation
>
> James
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:4484AF04.ACC3C6EE@verizonXSPAM.net...
> > Are you using this UDF on a worksheet?
> >
> > Do you pass the NomCode as a range?
> > Kind of:
> > =findoldnominal(a1,sheet2!a:e)
> >
> > Are you trying to add the comment to the NomCode cell?
> >
> > If yes, then maybe...
> >
> > Option Explicit
> > Function FindOldNominal(NomCode As Range, definedRange As Range) As
> > Variant
> >
> > Dim res As Variant 'could be an error
> >
> > res = Application.VLookup(NomCode, definedRange, 5, False)
> > If IsError(res) Then
> > FindOldNominal = "Not Found"
> > Else
> > FindOldNominal = res
> > End If
> >
> > On Error Resume Next
> > NomCode.Comment.Delete
> > On Error GoTo 0
> >
> > NomCode.AddComment Text:="accesses"
> >
> > End Function
> >
> > There are some "interesting" things about the way
> > worksheetfunction.vlookup()
> > works in comparison to application.vlookup().
> >
> > I chose to use application.vlookup() to make my life simpler.
> >
> > If this doesn't do what you want and you can't modify it, post back with a
> > few
> > more details. I'm sure you'll get some responses.
> >
> > James Cornthwaite wrote:
> >>
> >> So then using comments to mark accessed cells (as this is the only option
> >> with functions since they are not able to modifying cell formats in other
> >> ways)
> >>
> >> Function FindOldNominal(NomCode, definedRange)
> >>
> >> FindOldNominal = WorksheetFunction.VLookup(NomCode, definedRange, 5,
> >> False)
> >>
> >> '(now attempt to mark the accessed cell - the bit not yet working!!!!)
> >>
> >> Dim rng As Range
> >> Dim noOfRows As Integer
> >>
> >> noOfRows = Match(NomCode, definedRange, 0)
> >> rng = Offset(A1, noOfRows, 0, 1, 1)
> >> rng.AddComment ("accesses")
> >>
> >> EndFunction
> >>
> >> I realise the above is probably incorrect syntax, but have only just
> >> started
> >> to learn macro and excel functions (not even sure functions are found in
> >> what classes etc).
> >>
> >> Please could you ammend the above so it works. Hopefully the gist of what
> >> i'm trying is clearer enough.
> >> Many many thanks in anticipation
> >> James
> >>
> >> PREVIOUS POST
> >>
> >> ""Gary replied..............
> >>
> >> A function can deposit comments in cells:
> >>
> >> Function demo(r As Range) As Integer
> >> demo = 1
> >> If r.Comment Is Nothing Then
> >> Else
> >> r.Comment.Delete
> >> End If
> >> r.AddComment Text:="marked"
> >> End Function
> >>
> >> This dumb little macro just returns 1, but it marks its reference with a
> >> comment
> >>
> >> =demo(A1) results in A1 getting a comment. So even if you can't color
> >> A1,
> >> you can mark it.
> >> --
> >> Gary's Student
> >>
> >> "James Cornthwaite" wrote:
> >>
> >> > o right, never straight forwards is it.
> >> >
> >> > Is there any other way around the problem, to achieve the same
> >> > goal.??????????
> >> >
> >> > Thanks
> >> > James
> >> > "Chip Pearson" <chip@cpearson.com> wrote in message
> >> > news:eAbRWH$hGHA.4284@TK2MSFTNGP05.phx.gbl...
> >> > >A function called from a worksheet cell can only return a value to the
> >> > >calling cell. It cannot change the value or format of any cell.
> >> > >
> >> > >
> >> > > --
> >> > > Cordially,
> >> > > Chip Pearson
> >> > > Microsoft MVP - Excel
> >> > > Pearson Software Consulting, LLC
> >> > > www.cpearson.com
> >> > >
> >> > >
> >> > > "James Cornthwaite" <jamescornthwaite@btinternet.com> wrote in
> >> > > message
> >> > > news:2LKdnTMWpcwdmR7ZRVnyhA@bt.com...
> >> > >> Ideally i would like to color the contents of the cell accessed by
> >> > >> the
> >> > >> call of my function "findnewnominal(...)" The reason for this is the
> >> > >> function is called many times on a range table range and I would
> >> > >> like
> >> > >> to
> >> > >> know at a glance which rows in the table hadn't yet been accessed by
> >> > >> a
> >> > >> calls of the function.
> >> > >>
> >> > >> My macro to date (which works fine is)
> >> > >>
> >> > >>
> >> > >> Function FindOldNominal(NomCode, definedRange)
> >> > >>
> >> > >> FindOldNominal = Worksheetfunction.VLookup(NomCode, definedRange, 5,
> >> > >> false)
> >> > >> EndFunction
> >> > >>
> >> > >>
> >> > >>
> >> > >>
> >> > >>
> >> > >> but if i want to color the cell accessed I imagine i have to use
> >> > >> something like the following? (my attempt of something pseudocode)
> >> > >>
> >> > >>
> >> > >> Function FindOldNominal(NomCode, definedRange)
> >> > >>
> >> > >> Activecell = reference(Worksheetfunction.VLookup(NomCode,
> >> > >> definedRange,
> >> > >> 5, false))
> >> > >> Activecell.Interior.ColorIndex = 3 (i.e red etc)
> >> > >>
> >> > >> FindOldNominal = Worksheetfunction.VLookup(NomCode, definedRange, 5,
> >> > >> false)
> >> > >> EndFunction
> >> > >>
> >> > >>
> >> > >> I appreciate the above is probably non sensical but know little
> >> > >> about
> >> > >> excel functions and just wanted to make as clear as possible what i
> >> > >> wanted to do.
> >> > >> Any help would be greatly appreciated.
> >> > >>
> >> > >> Thanks
> >> > >> James
> >> > >>
> >> > >>
> >> > >
> >> > >
> >> >
> >> >
> >> >
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

Thats great, thanks for taking the time to explain it.
It all makes good sense now except the part regarding--


- 'if there's no comment, then deleting the non-existent comment will
- 'cause an error.
- 'this line tells excel that I want that error ignored.
- On Error Resume Next

- 'delete the comment from that cell that contained the NomCode
- NomCode.Comment.Delete

- 'this line tells excel to go back and keep looking for errors
- On Error GoTo 0


I understand the possible problem of attempting to delete a comment which
doesnt exist, but am unsure of the suggestion of a loop here (goto etc).
Resume next, where does this resume to?
With my simplistic view (and probably java way of thinking of things i would
expect)

Nomcode.comment.delete
if error then ignore
else
continue

Many many thanks
James

(going to use your code it will serve my purpose great, just like to fully
understand it then I dont have any mistaken expectations or can understand
why it does something unexpected).

PS purely out of interest would an alternative have been to write a macro,
then have the UDF call the macro, passing a range reference and then have
the macro modify contents or is this non sensical.



"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:44872835.2A0A40F5@verizonXSPAM.net...
> 'this line tells excel that we want to declare every variable that we use.
> 'it's a good way to make sure you don't make silly typing mistakes
> 'excel won't let your code even run if you have a variable that isn't
> 'declared somewhere with a Dim statement.
> Option Explicit
>
> 'variant means that that thing could be a string "A", "x", "Test" or
> 'a number 1,2,3...3.14159, or anything else
> Function FindOldNominal(NomCode As Range, definedRange As Range) As
> Variant
>
> 'variants can also hold errors
> Dim res As Variant 'could be an error
>
> 'if you use =vlookup() in a cell, you can get a value or
> '#n/a. Res is just a variable that holds that result
> res = Application.VLookup(NomCode, definedRange, 5, False)
>
> 'if =vlookup() would return an error (#n/a), then this
> 'function returns the string "Not Found"
> If IsError(res) Then
> FindOldNominal = "Not Found"
> Else
> 'if the =vlookup() worked ok, then it returns whatever
> 'was found
> FindOldNominal = res
> End If
>
> 'if there's no comment, then deleting the non-existent comment will
> 'cause an error.
> 'this line tells excel that I want that error ignored.
> On Error Resume Next
> 'delete the comment from that cell that contained the NomCode
> NomCode.Comment.Delete
> 'this line tells excel to go back and keep looking for errors
> On Error GoTo 0
>
> 'add a comment to the cell that was passed to the function.
> NomCode.AddComment Text:="accesses"
>
> End Function
>
> James Cornthwaite wrote:
>>
>> Thanks for the reply. To be quite honest dont really understand this code
>> so
>> not sure if it acheives what i want.
>>
>> Don't know what "option explicit" means,
>> what a variant is or each line of code after res = vlookup(....... )
>>
>> Can somebody please explain the code or suggest another way if this is
>> perhaps not suitable (in light of the extra info i give underneath)
>>
>> I pass nomCode as a fixed integer (reason for function in first place is
>> function is called in several places in a seperate presentational style
>> worksheet)
>> I am using this UDF on a worksheet yes.
>>
>> Many thanks in anticipation
>>
>> James
>>
>> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
>> news:4484AF04.ACC3C6EE@verizonXSPAM.net...
>> > Are you using this UDF on a worksheet?
>> >
>> > Do you pass the NomCode as a range?
>> > Kind of:
>> > =findoldnominal(a1,sheet2!a:e)
>> >
>> > Are you trying to add the comment to the NomCode cell?
>> >
>> > If yes, then maybe...
>> >
>> > Option Explicit
>> > Function FindOldNominal(NomCode As Range, definedRange As Range) As
>> > Variant
>> >
>> > Dim res As Variant 'could be an error
>> >
>> > res = Application.VLookup(NomCode, definedRange, 5, False)
>> > If IsError(res) Then
>> > FindOldNominal = "Not Found"
>> > Else
>> > FindOldNominal = res
>> > End If
>> >
>> > On Error Resume Next
>> > NomCode.Comment.Delete
>> > On Error GoTo 0
>> >
>> > NomCode.AddComment Text:="accesses"
>> >
>> > End Function
>> >
>> > There are some "interesting" things about the way
>> > worksheetfunction.vlookup()
>> > works in comparison to application.vlookup().
>> >
>> > I chose to use application.vlookup() to make my life simpler.
>> >
>> > If this doesn't do what you want and you can't modify it, post back
>> > with a
>> > few
>> > more details. I'm sure you'll get some responses.
>> >
>> > James Cornthwaite wrote:
>> >>
>> >> So then using comments to mark accessed cells (as this is the only
>> >> option
>> >> with functions since they are not able to modifying cell formats in
>> >> other
>> >> ways)
>> >>
>> >> Function FindOldNominal(NomCode, definedRange)
>> >>
>> >> FindOldNominal = WorksheetFunction.VLookup(NomCode, definedRange, 5,
>> >> False)
>> >>
>> >> '(now attempt to mark the accessed cell - the bit not yet working!!!!)
>> >>
>> >> Dim rng As Range
>> >> Dim noOfRows As Integer
>> >>
>> >> noOfRows = Match(NomCode, definedRange, 0)
>> >> rng = Offset(A1, noOfRows, 0, 1, 1)
>> >> rng.AddComment ("accesses")
>> >>
>> >> EndFunction
>> >>
>> >> I realise the above is probably incorrect syntax, but have only just
>> >> started
>> >> to learn macro and excel functions (not even sure functions are found
>> >> in
>> >> what classes etc).
>> >>
>> >> Please could you ammend the above so it works. Hopefully the gist of
>> >> what
>> >> i'm trying is clearer enough.
>> >> Many many thanks in anticipation
>> >> James
>> >>
>> >> PREVIOUS POST
>> >>
>> >> ""Gary replied..............
>> >>
>> >> A function can deposit comments in cells:
>> >>
>> >> Function demo(r As Range) As Integer
>> >> demo = 1
>> >> If r.Comment Is Nothing Then
>> >> Else
>> >> r.Comment.Delete
>> >> End If
>> >> r.AddComment Text:="marked"
>> >> End Function
>> >>
>> >> This dumb little macro just returns 1, but it marks its reference with
>> >> a
>> >> comment
>> >>
>> >> =demo(A1) results in A1 getting a comment. So even if you can't color
>> >> A1,
>> >> you can mark it.
>> >> --
>> >> Gary's Student
>> >>
>> >> "James Cornthwaite" wrote:
>> >>
>> >> > o right, never straight forwards is it.
>> >> >
>> >> > Is there any other way around the problem, to achieve the same
>> >> > goal.??????????
>> >> >
>> >> > Thanks
>> >> > James
>> >> > "Chip Pearson" <chip@cpearson.com> wrote in message
>> >> > news:eAbRWH$hGHA.4284@TK2MSFTNGP05.phx.gbl...
>> >> > >A function called from a worksheet cell can only return a value to
>> >> > >the
>> >> > >calling cell. It cannot change the value or format of any cell.
>> >> > >
>> >> > >
>> >> > > --
>> >> > > Cordially,
>> >> > > Chip Pearson
>> >> > > Microsoft MVP - Excel
>> >> > > Pearson Software Consulting, LLC
>> >> > > www.cpearson.com
>> >> > >
>> >> > >
>> >> > > "James Cornthwaite" <jamescornthwaite@btinternet.com> wrote in
>> >> > > message
>> >> > > news:2LKdnTMWpcwdmR7ZRVnyhA@bt.com...
>> >> > >> Ideally i would like to color the contents of the cell accessed
>> >> > >> by
>> >> > >> the
>> >> > >> call of my function "findnewnominal(...)" The reason for this is
>> >> > >> the
>> >> > >> function is called many times on a range table range and I would
>> >> > >> like
>> >> > >> to
>> >> > >> know at a glance which rows in the table hadn't yet been accessed
>> >> > >> by
>> >> > >> a
>> >> > >> calls of the function.
>> >> > >>
>> >> > >> My macro to date (which works fine is)
>> >> > >>
>> >> > >>
>> >> > >> Function FindOldNominal(NomCode, definedRange)
>> >> > >>
>> >> > >> FindOldNominal = Worksheetfunction.VLookup(NomCode, definedRange,
>> >> > >> 5,
>> >> > >> false)
>> >> > >> EndFunction
>> >> > >>
>> >> > >>
>> >> > >>
>> >> > >>
>> >> > >>
>> >> > >> but if i want to color the cell accessed I imagine i have to use
>> >> > >> something like the following? (my attempt of something
>> >> > >> pseudocode)
>> >> > >>
>> >> > >>
>> >> > >> Function FindOldNominal(NomCode, definedRange)
>> >> > >>
>> >> > >> Activecell = reference(Worksheetfunction.VLookup(NomCode,
>> >> > >> definedRange,
>> >> > >> 5, false))
>> >> > >> Activecell.Interior.ColorIndex = 3 (i.e red etc)
>> >> > >>
>> >> > >> FindOldNominal = Worksheetfunction.VLookup(NomCode, definedRange,
>> >> > >> 5,
>> >> > >> false)
>> >> > >> EndFunction
>> >> > >>
>> >> > >>
>> >> > >> I appreciate the above is probably non sensical but know little
>> >> > >> about
>> >> > >> excel functions and just wanted to make as clear as possible what
>> >> > >> i
>> >> > >> wanted to do.
>> >> > >> Any help would be greatly appreciated.
>> >> > >>
>> >> > >> Thanks
>> >> > >> James
>> >> > >>
>> >> > >>
>> >> > >
>> >> > >
>> >> >
>> >> >
>> >> >
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson

The "on error goto 0" actually doesn't goto anywhere--it just gives the error
handling responsibility back to excel/vba.

The "on error resume next" is much clearer. If there's an error, just resume
with the next line (skip over the error.

There are sometimes when just turning error handling off (momentarily) is the
only way to do things--but other times, you can check things before you do
something.

That section:
On Error Resume Next
NomCode.Comment.Delete
On Error GoTo 0

Could have been written as:
If NomCode.Comment Is Nothing Then
'do nothing
Else
NomCode.Comment.Delete
End If

A comment is an object. It has many properties--kind of like a range. Because
it's an object, you can test its existence by "if someobject is nothing".

I was just a little lazy in my original code.

==============
For the most part, a formula can't change stuff in the worksheet--except for the
cell containing the formula.

If you really wanted to do something important, you'll find that your formula
can't call a Subroutine that does other stuff to the worksheet. So it's a
non-starter, so to speak.

About the only place I've seen documentation on what a UDF can do is on John
Walkenbach's site:
http://j-walk.com/ss/excel/odd/odd06.htm

It's called "excel oddities" for a reason <bg>.



James Cornthwaite wrote:
>
> Thats great, thanks for taking the time to explain it.
> It all makes good sense now except the part regarding--
>
> - 'if there's no comment, then deleting the non-existent comment will
> - 'cause an error.
> - 'this line tells excel that I want that error ignored.
> - On Error Resume Next
>
> - 'delete the comment from that cell that contained the NomCode
> - NomCode.Comment.Delete
>
> - 'this line tells excel to go back and keep looking for errors
> - On Error GoTo 0
>
> I understand the possible problem of attempting to delete a comment which
> doesnt exist, but am unsure of the suggestion of a loop here (goto etc).
> Resume next, where does this resume to?
> With my simplistic view (and probably java way of thinking of things i would
> expect)
>
> Nomcode.comment.delete
> if error then ignore
> else
> continue
>
> Many many thanks
> James
>
> (going to use your code it will serve my purpose great, just like to fully
> understand it then I dont have any mistaken expectations or can understand
> why it does something unexpected).
>
> PS purely out of interest would an alternative have been to write a macro,
> then have the UDF call the macro, passing a range reference and then have
> the macro modify contents or is this non sensical.
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:44872835.2A0A40F5@verizonXSPAM.net...
> > 'this line tells excel that we want to declare every variable that we use.
> > 'it's a good way to make sure you don't make silly typing mistakes
> > 'excel won't let your code even run if you have a variable that isn't
> > 'declared somewhere with a Dim statement.
> > Option Explicit
> >
> > 'variant means that that thing could be a string "A", "x", "Test" or
> > 'a number 1,2,3...3.14159, or anything else
> > Function FindOldNominal(NomCode As Range, definedRange As Range) As
> > Variant
> >
> > 'variants can also hold errors
> > Dim res As Variant 'could be an error
> >
> > 'if you use =vlookup() in a cell, you can get a value or
> > '#n/a. Res is just a variable that holds that result
> > res = Application.VLookup(NomCode, definedRange, 5, False)
> >
> > 'if =vlookup() would return an error (#n/a), then this
> > 'function returns the string "Not Found"
> > If IsError(res) Then
> > FindOldNominal = "Not Found"
> > Else
> > 'if the =vlookup() worked ok, then it returns whatever
> > 'was found
> > FindOldNominal = res
> > End If
> >
> > 'if there's no comment, then deleting the non-existent comment will
> > 'cause an error.
> > 'this line tells excel that I want that error ignored.
> > On Error Resume Next
> > 'delete the comment from that cell that contained the NomCode
> > NomCode.Comment.Delete
> > 'this line tells excel to go back and keep looking for errors
> > On Error GoTo 0
> >
> > 'add a comment to the cell that was passed to the function.
> > NomCode.AddComment Text:="accesses"
> >
> > End Function
> >
> > James Cornthwaite wrote:
> >>
> >> Thanks for the reply. To be quite honest dont really understand this code
> >> so
> >> not sure if it acheives what i want.
> >>
> >> Don't know what "option explicit" means,
> >> what a variant is or each line of code after res = vlookup(....... )
> >>
> >> Can somebody please explain the code or suggest another way if this is
> >> perhaps not suitable (in light of the extra info i give underneath)
> >>
> >> I pass nomCode as a fixed integer (reason for function in first place is
> >> function is called in several places in a seperate presentational style
> >> worksheet)
> >> I am using this UDF on a worksheet yes.
> >>
> >> Many thanks in anticipation
> >>
> >> James
> >>
> >> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> >> news:4484AF04.ACC3C6EE@verizonXSPAM.net...
> >> > Are you using this UDF on a worksheet?
> >> >
> >> > Do you pass the NomCode as a range?
> >> > Kind of:
> >> > =findoldnominal(a1,sheet2!a:e)
> >> >
> >> > Are you trying to add the comment to the NomCode cell?
> >> >
> >> > If yes, then maybe...
> >> >
> >> > Option Explicit
> >> > Function FindOldNominal(NomCode As Range, definedRange As Range) As
> >> > Variant
> >> >
> >> > Dim res As Variant 'could be an error
> >> >
> >> > res = Application.VLookup(NomCode, definedRange, 5, False)
> >> > If IsError(res) Then
> >> > FindOldNominal = "Not Found"
> >> > Else
> >> > FindOldNominal = res
> >> > End If
> >> >
> >> > On Error Resume Next
> >> > NomCode.Comment.Delete
> >> > On Error GoTo 0
> >> >
> >> > NomCode.AddComment Text:="accesses"
> >> >
> >> > End Function
> >> >
> >> > There are some "interesting" things about the way
> >> > worksheetfunction.vlookup()
> >> > works in comparison to application.vlookup().
> >> >
> >> > I chose to use application.vlookup() to make my life simpler.
> >> >
> >> > If this doesn't do what you want and you can't modify it, post back
> >> > with a
> >> > few
> >> > more details. I'm sure you'll get some responses.
> >> >
> >> > James Cornthwaite wrote:
> >> >>
> >> >> So then using comments to mark accessed cells (as this is the only
> >> >> option
> >> >> with functions since they are not able to modifying cell formats in
> >> >> other
> >> >> ways)
> >> >>
> >> >> Function FindOldNominal(NomCode, definedRange)
> >> >>
> >> >> FindOldNominal = WorksheetFunction.VLookup(NomCode, definedRange, 5,
> >> >> False)
> >> >>
> >> >> '(now attempt to mark the accessed cell - the bit not yet working!!!!)
> >> >>
> >> >> Dim rng As Range
> >> >> Dim noOfRows As Integer
> >> >>
> >> >> noOfRows = Match(NomCode, definedRange, 0)
> >> >> rng = Offset(A1, noOfRows, 0, 1, 1)
> >> >> rng.AddComment ("accesses")
> >> >>
> >> >> EndFunction
> >> >>
> >> >> I realise the above is probably incorrect syntax, but have only just
> >> >> started
> >> >> to learn macro and excel functions (not even sure functions are found
> >> >> in
> >> >> what classes etc).
> >> >>
> >> >> Please could you ammend the above so it works. Hopefully the gist of
> >> >> what
> >> >> i'm trying is clearer enough.
> >> >> Many many thanks in anticipation
> >> >> James
> >> >>
> >> >> PREVIOUS POST
> >> >>
> >> >> ""Gary replied..............
> >> >>
> >> >> A function can deposit comments in cells:
> >> >>
> >> >> Function demo(r As Range) As Integer
> >> >> demo = 1
> >> >> If r.Comment Is Nothing Then
> >> >> Else
> >> >> r.Comment.Delete
> >> >> End If
> >> >> r.AddComment Text:="marked"
> >> >> End Function
> >> >>
> >> >> This dumb little macro just returns 1, but it marks its reference with
> >> >> a
> >> >> comment
> >> >>
> >> >> =demo(A1) results in A1 getting a comment. So even if you can't color
> >> >> A1,
> >> >> you can mark it.
> >> >> --
> >> >> Gary's Student
> >> >>
> >> >> "James Cornthwaite" wrote:
> >> >>
> >> >> > o right, never straight forwards is it.
> >> >> >
> >> >> > Is there any other way around the problem, to achieve the same
> >> >> > goal.??????????
> >> >> >
> >> >> > Thanks
> >> >> > James
> >> >> > "Chip Pearson" <chip@cpearson.com> wrote in message
> >> >> > news:eAbRWH$hGHA.4284@TK2MSFTNGP05.phx.gbl...
> >> >> > >A function called from a worksheet cell can only return a value to
> >> >> > >the
> >> >> > >calling cell. It cannot change the value or format of any cell.
> >> >> > >
> >> >> > >
> >> >> > > --
> >> >> > > Cordially,
> >> >> > > Chip Pearson
> >> >> > > Microsoft MVP - Excel
> >> >> > > Pearson Software Consulting, LLC
> >> >> > > www.cpearson.com
> >> >> > >
> >> >> > >
> >> >> > > "James Cornthwaite" <jamescornthwaite@btinternet.com> wrote in
> >> >> > > message
> >> >> > > news:2LKdnTMWpcwdmR7ZRVnyhA@bt.com...
> >> >> > >> Ideally i would like to color the contents of the cell accessed
> >> >> > >> by
> >> >> > >> the
> >> >> > >> call of my function "findnewnominal(...)" The reason for this is
> >> >> > >> the
> >> >> > >> function is called many times on a range table range and I would
> >> >> > >> like
> >> >> > >> to
> >> >> > >> know at a glance which rows in the table hadn't yet been accessed
> >> >> > >> by
> >> >> > >> a
> >> >> > >> calls of the function.
> >> >> > >>
> >> >> > >> My macro to date (which works fine is)
> >> >> > >>
> >> >> > >>
> >> >> > >> Function FindOldNominal(NomCode, definedRange)
> >> >> > >>
> >> >> > >> FindOldNominal = Worksheetfunction.VLookup(NomCode, definedRange,
> >> >> > >> 5,
> >> >> > >> false)
> >> >> > >> EndFunction
> >> >> > >>
> >> >> > >>
> >> >> > >>
> >> >> > >>
> >> >> > >>
> >> >> > >> but if i want to color the cell accessed I imagine i have to use
> >> >> > >> something like the following? (my attempt of something
> >> >> > >> pseudocode)
> >> >> > >>
> >> >> > >>
> >> >> > >> Function FindOldNominal(NomCode, definedRange)
> >> >> > >>
> >> >> > >> Activecell = reference(Worksheetfunction.VLookup(NomCode,
> >> >> > >> definedRange,
> >> >> > >> 5, false))
> >> >> > >> Activecell.Interior.ColorIndex = 3 (i.e red etc)
> >> >> > >>
> >> >> > >> FindOldNominal = Worksheetfunction.VLookup(NomCode, definedRange,
> >> >> > >> 5,
> >> >> > >> false)
> >> >> > >> EndFunction
> >> >> > >>
> >> >> > >>
> >> >> > >> I appreciate the above is probably non sensical but know little
> >> >> > >> about
> >> >> > >> excel functions and just wanted to make as clear as possible what
> >> >> > >> i
> >> >> > >> wanted to do.
> >> >> > >> Any help would be greatly appreciated.
> >> >> > >>
> >> >> > >> Thanks
> >> >> > >> James
> >> >> > >>
> >> >> > >>
> >> >> > >
> >> >> > >
> >> >> >
> >> >> >
> >> >> >
> >> >
> >> > --
> >> >
> >> > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson