Excel UDF Function Not Updating

Question

I'm trying to make it so that my UDF function updates whenever its reference cells update.

Here's the code:

Function ConCatRange(CellBlock As Range) As String Application.Volatile True Dim cell As Range Dim sbuf As String For Each cell In CellBlock On Error GoTo fred If Len(cell.Text) > 1 Then sbuf = sbuf & cell.Text & Chr(10) Next ConCatRange = Left(sbuf, Len(sbuf) - 1) fred: End Function

When reference cells are blank, however, it will not show #VALUE! - This is what I want. Instead, it will just not update and keep the data that was last populating the cell.

Thank you!

In addition to @GarysStudent suggestion, Application.Volatile is unnecessary. The UDF will update whenever the referenced cells are updated; and having that there will force it to recalculate unneccesarily. — Ron Rosenfeld
– Ron Rosenfeld, Commented Apr 2, 2020 at 1:49

Gary's Student · Accepted Answer · 2020-04-01 21:09:17Z

3

If you want to see #VALUE! just remove the On Error statement. This will allow the Left() function to report its failure.

answered Apr 1, 2020 at 21:09

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

HackSlash Over a year ago

Throwing an error is different than returning an error to the cell like a built-in UDF does.

Ron Rosenfeld Over a year ago

@HackSlash The end result will be the same in that #VALUE will be returned by the formula to the cell containing the formula.

Bobby Zullo Over a year ago

This does not achieve the desired result. The function will still fail to update automatically unless I click into the cell and hit "enter" manually.

Gary's Student Over a year ago

@BobbyZullo Have you set the calculation mode to Automatic ?

Frank Ball · Accepted Answer · 2020-04-01 21:34:47Z

Try this:

Function ConCatRange(CellBlock As Range) As String Application.Volatile True Dim cell As Range Dim sbuf As String For Each cell In CellBlock On Error GoTo exitErr If Len(cell.Text) = 0 Then GoTo exitErr If Len(cell.Text) > 1 Then sbuf = sbuf & cell.Text & Chr(10) Next ConCatRange = Left(sbuf, Len(sbuf) - 1) Exit Function exitErr: ConCatRange = CVErr(xlErrValue) End Function

Within the loop, you check for a Text length of 0 and if it finds one, returns CVErr(xlErrValue) - also known as "#VALUE!"

This does not achieve the desired result. The function will still fail to update automatically unless I click into the cell and hit "enter" manually.

Collectives™ on Stack Overflow

Excel UDF Function Not Updating

2 Answers 2

4 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Related