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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in
message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in
message
...
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" wrote in message
...
'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" wrote in message
...
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" wrote in message
...
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" wrote in
message
...
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" wrote in message
...
'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" wrote in message
...
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" wrote in message
...
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" wrote in
message
...
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