Range.Formula in Excel 2003 gives Error reading formulas just below 1024 bytes

F

Frank Jones

The Range.Formula does not handle formulas that are just below 1024 bytes.
Excel 2000 allows access to these formulas. The code below sets the formula,
but then does not allow you to read it out. Is there any reliable way to
always get the contents of a formula without throwing this error?


Code Sample:

Sub Test()

Dim s As String
s = "='C:\temp\path1\path2-asdfasdfasdfasdfasdfasd\[file.xls]123
abcdef'!#REF!" _
& "+'C:\temp\path1\path2-asdfasdfasdfasdfasdfasd\[file.xls]123
asdfasdf'!#REF!" _
& "+'C:\temp\path1\path2-asdfasdfasdfasdfasdfasd\[file.xls]123
asdfasc'!#REF!" _
& "+'C:\temp\path1\path2-asdfasdfasdfasdfasdfasd\[file.xls]123
asdfasdfasr'!#REF!" _
& "+'C:\temp\path1\path2-asdfasdfasdfasdfasdfasd\[file.xls]123 asdf'!#REF!"
_
& "+'C:\temp\path1\path2-asdfasdfasdfasdfasdfasd\[file.xls]123
asdfasdf'!#REF!" _
& "+'C:\temp\path1\path2-asdfasdfasdfasdfasdfasd\[file.xls]123 aa'!#REF!" _
& "+'C:\temp\path1\path2-asdfasdfasdfasdfasdfasd\[file.xls]123 Aaaa'!#REF!"
_
& "+'C:\temp\path1\path2-asdfasdfasdfasdfasdfasd\[file.xls]123 aa'!#REF!" _
& "+'C:\temp\path1\path2-asdfasdfasdfasdfasdfasd\[file.xls]123 aaa'!#REF!" _
& "+'C:\temp\path1\path2-asdfasdfasdfasdfasdfasd\[file.xls]123 aaa'!#REF!" _
& "+'C:\temp\path1\path2-asdfasdfasdfasdfasdfasd\[file.xls]123 aa'!#REF!" _
& "+'C:\temp\path1\path2-asdfasdfasdfasdfasdfasd\[file.xls]123
aaaaaaaa'!#REF!" _
& "+'C:\temp\path1\path2-asdfasdfasdfasdfasdfasd\[file.xls]123
aaaaaaaa'!#REF!"

Dim range As range
Set range = Sheet1.Cells(1, 1)

' Can set the formula
range.formula = s

Dim formula As String

' Reading the formula gives an error
' 1004 Application-defined or object-defined error
' Something to do with length of formula?
formula = range.formula


End Sub
 
J

JWolf

I'd try commenting out all the lines after the first file name and
seeing if that works, then uncomment the next line, etc. to see where it
fails.
 
P

Peter Huang

Hi Frank,

Based on my test below, I think it is caused by the limitation of formula
property.

Sub test()
Dim s As String
Dim i As Integer
s = "=11111"
For i = 1 To 448
s = s + "+" + CStr(1)
Next
Debug.Print Len(s)
Dim rg As Range
Set rg = Cells(1, 1)
rg.Formula = s
Debug.Print rg.Formula
End Sub

If we change the s = "=11111" to s = "=111111"

the error " 1004 Application-defined or object-defined error" will occur in
the line below.
rg.Formula = s

You may have a try and let me know the result.

Also why you need such a long string formular?
Do you have any concern, maybe we can do that in another way?

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
T

Tom Ogilvy

A formula is limited to 1024 characters. The formula is put in R1C1
notation and then the length is determined.
 
F

Frank Jones

Thanks for the response Peter.
The first post might not have been very clear.

We have a pre-existing spreadsheet. We are trying to *read* the formula
from a cell using Range.Formula property. On certain cells when accessing
that property Range.Formula, we get the error "1004 Application-defined or
object-defined error". In the Excel GUI you can see that the formula is
long but technically within the 1024 byte limit. There is something related
to the formula being too long or almost too long (in this case it is only
1017 bytes...).

The problem is that Range.Formula allows one to set a formula to a cell that
later on if you try to read the Range.Formula property you can't get the
formula out.

So we are looking for a way to read the Range.Formula property without
throwing an exception.

The main purpose of the code example was to show that there are cases that
one can *set* a formula successfully using Range.Formula property that then
if one tries to use Range.Formula to *get* the property it will raise the
errror.

One would expect that the Range.Formula *set* to also fail if the formula
were too long, which indeed happens in your test case below Peter. We are
wondering why you might have a formula where rg.Formula = s works but
Debug.Print rg.Formula fails, and our first code example provides that case.

The question is: is there some way to *READ* the Range.Formula for any
arbitrary cell without running into a situation where the read will raise
and exception? You can see the formula in the GUI but programmitically you
cant get to it.
 
F

Frank Jones

Thanks for the reply Tom.

The formula length being related to the R1C1 length is interesting, maybe
that is why this formula is too long. The funny thing is that in an Excel
spreadsheet you can run across a cell that has a forumla that you can't read
programmically, but somehow someone set it to be a certain formula.

It is like the Range.Formula property fires off validation of the formula
length on a read. Yet somehow the Excel application GUI can display the
formula, just in VBA code you can't use the Range.Formula property.



Tom Ogilvy said:
A formula is limited to 1024 characters. The formula is put in R1C1
notation and then the length is determined.

--
Regards,
Tom Ogilvy

Frank Jones said:
The Range.Formula does not handle formulas that are just below 1024 bytes.
Excel 2000 allows access to these formulas. The code below sets the formula,
but then does not allow you to read it out. Is there any reliable way to
always get the contents of a formula without throwing this error?


Code Sample:

Sub Test()

Dim s As String
s = "='C:\temp\path1\path2-asdfasdfasdfasdfasdfasd\[file.xls]123
abcdef'!#REF!" _
& "+'C:\temp\path1\path2-asdfasdfasdfasdfasdfasd\[file.xls]123
asdfasdf'!#REF!" _
& "+'C:\temp\path1\path2-asdfasdfasdfasdfasdfasd\[file.xls]123
asdfasc'!#REF!" _
& "+'C:\temp\path1\path2-asdfasdfasdfasdfasdfasd\[file.xls]123
asdfasdfasr'!#REF!" _
& "+'C:\temp\path1\path2-asdfasdfasdfasdfasdfasd\[file.xls]123 asdf'!#REF!"
_
& "+'C:\temp\path1\path2-asdfasdfasdfasdfasdfasd\[file.xls]123
asdfasdf'!#REF!" _
& "+'C:\temp\path1\path2-asdfasdfasdfasdfasdfasd\[file.xls]123
Click to expand...
aa'!#REF!"
_
& "+'C:\temp\path1\path2-asdfasdfasdfasdfasdfasd\[file.xls]123 Aaaa'!#REF!"
_
& "+'C:\temp\path1\path2-asdfasdfasdfasdfasdfasd\[file.xls]123
Click to expand...
aa'!#REF!"
_
& "+'C:\temp\path1\path2-asdfasdfasdfasdfasdfasd\[file.xls]123
Click to expand...
aaa'!#REF!"
_
& "+'C:\temp\path1\path2-asdfasdfasdfasdfasdfasd\[file.xls]123
Click to expand...
aaa'!#REF!"
_
& "+'C:\temp\path1\path2-asdfasdfasdfasdfasdfasd\[file.xls]123
Click to expand...
aa'!#REF!"
_
& "+'C:\temp\path1\path2-asdfasdfasdfasdfasdfasd\[file.xls]123
aaaaaaaa'!#REF!" _
& "+'C:\temp\path1\path2-asdfasdfasdfasdfasdfasd\[file.xls]123
aaaaaaaa'!#REF!"

Dim range As range
Set range = Sheet1.Cells(1, 1)

' Can set the formula
range.formula = s

Dim formula As String

' Reading the formula gives an error
' 1004 Application-defined or object-defined error
' Something to do with length of formula?
formula = range.formula


End Sub
Click to expand...
Click to expand...
 
P

Peter Huang

Hi Frank,

Now I can reproduce the problem with your code, but the limitation on my
side seems to be 1003, if the length of formula is 1004, the error will
occur.

Now I am researching the problem, if I have any new information I will
update you with new information ASAP.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
P

Peter Huang

Hi Frank,

I am sorry for delay responding. I have reported the problem to our product
team. I will get back here update you with new information ASAP when I get
new information.
This may take a period of time.

Thank you for understanding.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
P

Peter Huang

Hi Frank,

Now we are researching the problem, I did not get any new information from
our product team, please keep waiting patiently.


Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
P

Peter Huang

Hi Frank,

Our product team is keeping working on this issue, please pay more patience
to wait.

Thank you for your understanding.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
P

Peter Huang

Hi Frank,

Here is the information I got from our product team, FYI.

It appears this stirng is hitting formulat limit that Excel has of 1024,
that what error indicates. Investigating with dev what is special about
this string that causes this since the string is actually 1004 long.
Possibly the way VBA is allocating for the stirng due to the #REFs.

Now our dev team is still working on the problem.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
P

Peter Huang

Hi Frank,

Here is the new information about the problem from our dev team.

That's a design limitation in the XLM OM implementation of
Range.[get]Formula. We can render a valid formula up to 1024 characters via
the OM but the cut-off is up to the last rendered 'chunk' in the formula so
it's not always 1024 characters.

If you still have any concern on this issue, please feel free to post here.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
F

Frank Jones

Thanks for the reply Peter. That explains why some formulas are cut off
below 1024 bytes.

However, our question was is there any way to *read* a formula in a cell via
VBA without throwing an exception. Since there was not a direct answer to
our question we are assuming the answer is "no".

So in general there may be some cells with some formulas out there that when
the attempt is made to call Range.Formula (get) it can throw an exception
and there is no other way to find out what is in that formula, even though
the Excel GUI can display the formula contents there is no way to
programmicatically access the formula. So you are saying that all calls to
get Range.Formula have to be wrapped with exception handling, correct?
Because reading a formula that is somewhat close to the limit of 1024 bytes
can throw an error while setting the formula might not.

Something like this:

Dim strFormula as String

On Error Resume Next
strFormula = oCell.Formula

If Err.Number <> 0 Then
strFormula = "Invalid Formula"
End If

On Error Goto 0

To us it still seems very odd that one might be able to set a formula into a
cell but then be unable to read that formula property later. Why can one
set a formula successfully only to have the get fail? Apparently we will
not get a direct answer to that question.

Thanks Peter.
 
P

Peter Huang

Hi Frank,

I am sorry.
But it seems that there is no workaround for this issue so far.
I think we would better check the formula's length first before we assign
to the cell so that we will not get such error afterwards.
If you still have any concern on this issue, I am glad to be assistant.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Y

Yan-Hong Huang[MSFT]

Hello Frank,

We talked to product group on it already. Peter has posted the result here.
I agree with you that a property can be set but not got is not a good user
experience. For the time being, the workaround is to use exception handling
when you got formula. I suggest you create a function specially to wrap it.
So you don't need to write exception handling code here and there.

You can also submit a service request to ask for a hotfix for it. The
product group will draw the final conclusion according to business impact
and etc. Since it is a product issue, it should be a free support incident.

If you feel there is any we can do for you, please feel free to post here.
Thanks very much.

Best regards,
Yanhong Huang
Microsoft Community Support

Get Secure! ¨C www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Top