Function to replace formulas

[jqpublic13]

I'm trying to write a function that would replace a range with that range's values. In other words, I need a function (as macro's are verb?ten) that would select the range and perform a copy/paste values on the range. Currently, my code is as follows:



Function RemoveFormula(rngInput As Range, Criteria As Variant)
Application.Volatile True
If Application.Evaluate(Criteria) = True Then
rngInput.Copy
rngInput.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
RemoveFormula = "Done!"
Exit Function
End If
RemoveFormula = "Not Done!"
End Function


I have a few dummy formulas in cells A1:B4 (=1+1, now(), etc.) and they remain untouched after the execution of the function.

All this does is hightlight the input range, then finish. The return values are currently checks to make sure the code is executed properly (it is).

Any suggestions?

[jqpublic13]

<<sigh>>

I found my own answer:


Function RemoveFormula(rngInput As Range, Criteria As Variant)
Application.Volatile True
If Application.Evaluate(Criteria) = True Then
rngInput.Replace "*", rngInput.Value
RemoveFormula = "Done!"
Exit Function
End If
RemoveFormula = "Not Done!"
End Function


If this helps anyone else, then it was worth me pulling my hair out.

[tpoynton]

interesting; might want to set application.volatile back to false? I actually havent had a need to deal with Volatile, but I think it is good practice to put things back to the defaults...

[Bob Phillips]

Application.Volatile is not necessary here at all.

A function is just a special type of macro, so if macros are verboten, so are functions.

[jqpublic13]

I don't want to use macros, because I don't want to include any VBA code in the workbook. I use conditional formulas to hide the error resulting from a missing .xla add-in.

Let me modify my question a bit.

First, here's my current code:

Function rFormula(rngInput As Range, criteria As Boolean, Optional _
oLabel As variant = vbNullString, Optional nLabel As variant = _
vbNullString) As Variant
Application.Volatile False
Dim i As Integer
If Application.Evaluate(criteria) = True Then
Application.ScreenUpdating = False
For i = 1 To rngInput.Count
If Not rngInput(i) = vbNullString Then
rngInput(i).Replace "*", rngInput(i).Value
End If
Next i
rFormula = nLabel
Application.ScreenUpdating = True
Exit Function
End If
rFormula = oLabel
End Function


I use this to make an invoice, where the cells in column A contain the function =NOW(), those in column B = "ITEM", and each cell in column C contain my function:


A B C
1: =NOW() "Item" =rFormula(A1,B1<>"Item",,"Quantity")
2: =NOW() "Item" =rFormula(A2,B2<>"Item",,"Quantity")
3: =NOW() "Item" =rFormula(A3,B3<>"Item",,"Quantity")
.
.
.


Whenever an item name is typed in a cell in column B, that row gets a timestamp in column A to note when the item was ordered (=NOW() is replaced by the current value, and since the actual formula is removed, the date/time is persistent even when the workbook is saved and closed). The formula is eventually overwritten when a quantity is entered in column C. Since my function is saved as an add-in instead of a macro in the workbook, it can be used even on computers where macro security is set to high.

Here's my new question: is there any way to include the cell where the function is being called (i.e. application.caller.address or something similar) without getting a circular reference error, so that when the function is activated, it removes the function from itself (i.e. I don't have to worry about someone putting a quantity in column C to remove the function)? I was playing with private functions and subs, but can't seem to do this. Any thoughts from anyone?

Thanks!

[Bob Phillips]

But you are still using VBA code, which you say you don't want/can't use.

[jqpublic13]

...then I'll amend my statement. I dont' want to use an event like on_click(), on_change(), etc. to initiate my function.