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

  • Thread starter Thread starter James Cornthwaite
  • Start date Start date
J

James Cornthwaite

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.
 
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.
 
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
 
'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
 
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.
 
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 said:
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.
Click to expand...
 
Back
Top