UDF works in 2003, but fails in 2007

**Puff_Adder** · 04-02-2010, 01:00 PM

I have a user defined function which works in Excell 2003, but fails in Excel 2007.

Please Login or Register  to view this content.

I have some code which copies a range from another workbook and then pastes this into the open workbook.
The user can choose whether to paste the data as "Values" or as "Link".
There is a cell in the worksheet which uses the "IsFormula" function to check the first cell of the pasted range to see if it contains Data (i.e. it is pasted values) or a Formula (i.e. it is linked) and then display a message to let the user know that they are currently using linked data or pasted values.
The whole thing worked perfectly in Excel 2003, but in Excel 2007 when the range is pasted, the "IsFormula" function gives a "#VALUE!" error. At this point F9 (recalc) sometimes cures the problem, but not always.
Copying the data in the referenced cell to another cell, then copying back again also cures the problem.

Any explanation of what is going on and how to solve it would be much appreciated.
TIA
Peter Ross

**royUK** · 04-02-2010, 01:05 PM

Works fine in 2007

**Puff_Adder** · 04-02-2010, 01:59 PM

Thanks, but not really very helpful. How did you test it?

I realise that the UDF works in 2007 when used to reference a cell into which data or a formula has been entered manually or pasted manually.
However, the problem I have is that it fails when the pasting is executed by VBA code.

Any suggestions?

Peter Ross

**royUK** · 04-02-2010, 02:29 PM

I created a workbook in 2007, added the Function & used it to test A1.

When I copied & pasted a formula into A! it returned True, using PasteSpecial it returned False

**Puff_Adder** · 04-04-2010, 05:12 PM

Thanks Roy,
I get the same result (i.e. it works) when pasting manually.
The problem only seems to occur when the pasting is executed by VBA code.
The code must be OK as it works perfectly in Excel 2003.

Any suggestions?

**mdbct** · 04-04-2010, 11:21 PM

If I change your Function to:

Please Login or Register  to view this content.

and add:

Please Login or Register  to view this content.

to a sub doing a paste operation, the formula calculates.

Without using Application.Volatile and Application.Calculate combination, I was left with the #Value as the formula's result.

**Puff_Adder** · 04-05-2010, 06:21 PM

Thanks for your help, but for some reason your solution doesn't work for me.
No need to bother any further though as I found a way to achieve what I wanted without using the IsFormula function.
I just use the VBA code to enter the required message, depending on which pasting method was chosen, rather than using the UDF to check for presence of values or linked formula.
Can't believe I didn't think of that in the first place.