Hi - I have some code that falls over when it tries to read
ActiveCell.Formula when the contents of the cell are too long (good old error
number 1004). It actually happens when there's a long piece of text in the
cell, rather than a formula.

Now the limit for text is 1024 characters but I can't even capture this
error with a (rather clumsy)"If Len(ActiveCell.Value)1024" or similar
because in fact the limit in Excel for the number of characters for real
formulae (rather than just text) appears to be rather variable and certainly
less than 1024.

IsError doesn't seem to catch it. Is there anything else that can, apart
from normal error trapping code (which I don't think is very satisfactory for
the generic 1004 error)? The Watch window says the cell's Formula property
is <Application-defined or object-defined error which, of course, is the
problem: it can't even be read by VBA to then be tested.

Hope you can help.

the limit for text is 32K characters, not 1024. Prior to excel 97, the limit
was 255 characters. The formula is limited to 1024 characters when expressed
in R1C1 format.

if activecell.hasFormula then
msgbox ActiveCell.Formula
else
msgbox ActiveCell.Value
end if

--
Regards,
Tom Ogilvy


"Smallweed" wrote:

Hi - I have some code that falls over when it tries to read
ActiveCell.Formula when the contents of the cell are too long (good old error
number 1004). It actually happens when there's a long piece of text in the
cell, rather than a formula.

Now the limit for text is 1024 characters but I can't even capture this
error with a (rather clumsy)"If Len(ActiveCell.Value)1024" or similar
because in fact the limit in Excel for the number of characters for real
formulae (rather than just text) appears to be rather variable and certainly
less than 1024.

IsError doesn't seem to catch it. Is there anything else that can, apart
from normal error trapping code (which I don't think is very satisfactory for
the generic 1004 error)? The Watch window says the cell's Formula property
is <Application-defined or object-defined error which, of course, is the
problem: it can't even be read by VBA to then be tested.

Hope you can help.

you can always trap the error by number

if err.Number=1004 then
var=range.value
else
var=range.formula
endif

--
--
---
ZZ [underground]
Semi-musico,cuasi-poeta y loco

"Smallweed" wrote in message
...
Hi - I have some code that falls over when it tries to read
ActiveCell.Formula when the contents of the cell are too long (good old
error
number 1004). It actually happens when there's a long piece of text in
the
cell, rather than a formula.

Now the limit for text is 1024 characters but I can't even capture this
error with a (rather clumsy)"If Len(ActiveCell.Value)1024" or similar
because in fact the limit in Excel for the number of characters for real
formulae (rather than just text) appears to be rather variable and
certainly
less than 1024.

IsError doesn't seem to catch it. Is there anything else that can, apart
from normal error trapping code (which I don't think is very satisfactory
for
the generic 1004 error)? The Watch window says the cell's Formula
property
is <Application-defined or object-defined error which, of course, is the
problem: it can't even be read by VBA to then be tested.

Hope you can help.

Thanks so much Tom - .HasFormula exactly what I needed!

"Tom Ogilvy" wrote:

the limit for text is 32K characters, not 1024. Prior to excel 97, the limit
was 255 characters. The formula is limited to 1024 characters when expressed
in R1C1 format.

if activecell.hasFormula then
msgbox ActiveCell.Formula
else
msgbox ActiveCell.Value
end if

--
Regards,
Tom Ogilvy


"Smallweed" wrote:

Hi - I have some code that falls over when it tries to read
ActiveCell.Formula when the contents of the cell are too long (good old error
number 1004). It actually happens when there's a long piece of text in the
cell, rather than a formula.

Now the limit for text is 1024 characters but I can't even capture this
error with a (rather clumsy)"If Len(ActiveCell.Value)1024" or similar
because in fact the limit in Excel for the number of characters for real
formulae (rather than just text) appears to be rather variable and certainly
less than 1024.

IsError doesn't seem to catch it. Is there anything else that can, apart
from normal error trapping code (which I don't think is very satisfactory for
the generic 1004 error)? The Watch window says the cell's Formula property
is <Application-defined or object-defined error which, of course, is the
problem: it can't even be read by VBA to then be tested.

Hope you can help.