Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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...

Question

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

share|improve this question
add comment

2 Answers

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
Else
    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. –  Jesse Dec 18 '12 at 5:24
add comment
  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). –  Jesse Dec 18 '12 at 5:22
add comment

Your Answer

 
discard

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.