Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Problem Description

I have a custom formula that I wrote, but it takes a while to calculate the answer, and since Excel updates function values (When you activate the workbook, or re-open the workbook, etc.), I can't have it re-running the function.

I tried calling a Sub process to copy the cell and paste values (Replace a formula with its result), but it returns =#VALUE!. Probably something to do with circular references...


Is there a way to overwrite the formula in a cell with its value from within a user defined formula?

share|improve this question
up vote 2 down vote accepted

There is no way to overwrite any cell from within a UDF, it can only return a value.

You can use the Worksheet_Change function to get around this...

In Sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A1"), Target) Is Nothing Then
    'do nothing - not worried about anything not in A1
    Range("A1").Value = Range("A1").Value
End If
End Sub

Whatever formula you type into A1, on the sheet you put this code into, will be converted into the value, and the formula removed.

share|improve this answer
Thanks! I guess that will have to do. I was hoping to put the UDF in an excel addon (.xlam) so that it would be available on all worksheets, but I guess that's not possible. – Gruzzles Dec 18 '12 at 5:24
  1. Select Cell
  2. Right-click > Copy (Or [CTRL]+[c])
  3. Right-click > Paste Special > Paste Values

This should replace the formula with the resulting number/value.

share|improve this answer
Thanks, but I was wanting to do that from within the UDF (please read my question more carefully). – Gruzzles Dec 18 '12 at 5:22

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.