User Defined Function randomly throws out #Value error.

[Sundav]

I am trying to write a UDF to add a note to the cell it is in. Here's the code I have:

Function comcopy(cll2 As Range, cll As Range) As Integer
    
    Dim c As Integer
    Dim r As Integer
    
    c = cll.Column
    r = cll.Row + 1
    
    comcopy = 0
    
    
    Sheet7.Cells(r, c).Comment.Delete
    Sheet7.Cells(r, c).AddComment (cll2.Text)



End Function

The goal is to select a cell with the text I want in the note for variable 1, and select the cell above the cell comcopy is entered in so the note is generated in that cell.

However, when I first enter the function, I get a value error. Then, if I delete the "Comment.Delete" line it all works fine. Then, if I put the "Comment.Delete" line back in it works fine. However, if I put the function in another cell I have to do the whole process of deleting the line over again to get it to work there.

Is there a way to fix this code or is there a better way to do this?

Thanks!

 

[Rick Rothstein]

I can duplicate what you have said happens on my copy of XL2019 and I have to tell you I am surprised. After removing that comment delete line of code, the UDF does indeed add a comment to the cell below the cell containing the UDF. As far as I know, this should not have happened because UDF's are supposed to only be able to return values to the cells they are in... they are not supposed to be able to make any other physical changes to a worksheet. So, I am not sure what to tell you, but I don't think I would rely on what you have discovered always working in the future. My guess is that as soon as Microsoft becomes aware of this, I think they will close it off for future use.

 

[Sundav]

I can duplicate what you have said happens on my copy of XL2019 and I have to tell you I am surprised. After removing that comment delete line of code, the UDF does indeed add a comment to the cell below the cell containing the UDF. As far as I know, this should not have happened because UDF's are supposed to only be able to return values to the cells they are in... they are not supposed to be able to make any other physical changes to a worksheet. So, I am not sure what to tell you, but I don't think I would rely on what you have discovered always working in the future. My guess is that as soon as Microsoft becomes aware of this, I think they will close it off for future use.

I thought it might be something like that. I was trying to get around having to use a full on macro for this but I guess that's the only reliable way. Thanks for your help!

 

[Fluff]

I'm also surprised that it works, however this will get round the problem you are facing.

    If Not cll.Comment Is Nothing Then cll.Comment.Delete
    cll.AddComment cll2.Text

Although like Rick, I wouldn't count on it always working

 

[dcardno]

Functions cannot modify the spreadsheet - they can only return a value. The only way I can see this working is to run a sub that prompts for a cell reference, takes the value in that cell, and enters it as the 'comment' value for the selected cell, or possibly some other cell.

 

[Fluff]

Functions cannot modify the spreadsheet - they can only return a value.

Except as Rick & I have said, it does work.

 

[dcardno]

Except as Rick & I have said, it does work.

Yes - I was typing at the same time you and Rick were. I should have tried the function quoted, but neglected to do so. I was surprised to see any result, and I agree it probably won't be reliable.

 

[Rick Rothstein]

Apparently, creating comments (among some other surprising things) is something a UDF is allowed to do, so you should be able to use Fluff's suggested fix with confidence. I posted this question to a private Microsoft MVP mail list I belong to and fellow MVP Kevin Jones posted back this reply (the 3rd paragraph down is the one covering this question)...

A UDF or User Defined Function is a custom function written in VBA that accepts parameters and returns a value, just like any built in Excel function. A UDF can also return an array of values if it is used in a multiple cell array formula. But a UDF has a very strict limitation: it cannot, under any circumstances, change the structure of the workbook or worksheet, set any workbook or worksheet property such as a cell's value, color, border, or any other worksheet or cell formatting, or change the Excel environment including changing the cursor. This very strict rule also applies to any sub-routine that the UDF calls. If this rule is violated Excel stops the UDF and returns the #VALUE! error to the formula in which the UDF was called.

In addition to the above limitations, the Find, SpecialCells, CurrentRegion, CurrentArray, GoTo, Precedents, and Dependents methods do not function properly when used in a UDF (Find does work in Excel 2002 and later).

There are a few exceptions to this rule. A UDF or any subroutine called from the UDF can merge and unmerge cells (Merge and UnMerge), indent and outdent (InsertIndent), add, modify, and delete shapes, add, change, and delete cell validation rules, and add, change, and delete cell comments. With regard to shapes, it cannot set or manipulate the Characters object of a TextFrame.

As long as this rule is followed a UDF can read any application, workbook, worksheet, or cell property, define variables, arrays, and collections, process information, display message boxes, and read information from a closed workbook (using an ADODB connection, not ExecuteExcel4Macro as that is also prohibited in a UDF).

 

[dcardno]

Thanks Rick - that's interesting. I can't think of any reason I would want a UDF to do those thing - but the way things go, I will probably run into one now!