I have a spreadsheet (Excel 2000) that I use to calculate/check costs using
some lengthy (complex?) conditional formulas. These formulas have been
developing over a period and have become too unwieldy to leave lying around
in a worksheet (the sheet is used by others as well).

as a result I tried a couple of other avenues without great success until I
discovered named functions (thanks again Mr Walkenbach!!). These work fine,
except...

I build the sheet from code, and when I add the function I'm getting mixed
results - either the formula is truncated, or I get an error value...

I worked out that there is apparently a string length limitation (267
characters?) and have split the formula and replaced cell references with
named ranges where possible to keep the length down, but this doesn't seem to
make a difference.

the code I'm using is as follows (apologies for the wrapping..)

ActiveSheet.Names.Add Name:="Dur", RefersTo:="=IF(OR($A1="""", _
$F1=""Call Connect"",$G1=""Sensis 1234""),"""",(LEFT($I1, LEN($I1)-3) _
* 60) + RIGHT($I1, 2))"
ActiveSheet.Names.Add Name:="Cost", RefersTo:="=IF(OR('Long _
Dist'!$A1="""",'Long Dist'!$L1="""",Rate=0),"""",IF((Rate/60)*'Long _
Dist'!$L1<=Min,(Min+FFall)*1.1,IF('Long Dist'!$L1CapPer, _
(((Rate/60)*('Long Dist'!$L1-CapPer))+FFall+CapVal)*1.1,costA)))"
ActiveSheet.Names.Add Name:="CostA", RefersTo:="=IF(AND('Long _
Dist'!$L1<=CapPer,(Rate/60)*'Long Dist'!$L1CapVal),(CapVal + _
FFall)*1.1,IF(OR(AND((Rate/60)*'Long Dist'!$L1 Min,'Long _
Dist'!$L1<=CapPer,(Rate/60)*'Long Dist'!$L1<=CapVal), _
AND(Min=0,CapPer=0)), (((Rate/60)*'Long Dist'!$L1) + FFall)*1.1,""""))"

The first (shorter) one works fine, yet the others repeatedly fail. I've
been pulling my hair out about this (and I really can't afford to lose any
more!!)

FWIW, the formula works fine when written to a cell

many thanks (in advance)

S
--
there are 10 types of people in the world,
those who understand binary, and those who don't