2

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?

Improve this question

2 Answers 2

Reset to default
2

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.

Improve this answer
1
  • 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, 2012 at 5:24
1
  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.

Improve this answer
1
  • Thanks, but I was wanting to do that from within the UDF (please read my question more carefully).
    – Gruzzles
    Dec 18, 2012 at 5:22

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .