Compare.xla

B

Bill Le May

I understand that Myrna Larson, a co-author of this utility, posts here, so
I'm hoping she might answer a question for me. I'm having a problem with it,
and I'm not certain whether there is a way around it. The problem occurs at
line 258

Private Sub CompareFormulas(Cell1 As Range, Cell2 As Range)
Dim F1 As Boolean, F2 As Boolean

mV1 = Cell1.Formula

The line above fails because Cell1's value is zero, and the cell in question
has an unreasonably large formula in it. The formula is over 1000
characters long. We are working to get the developers here to simplify the
spreadsheets that we have to send out to customers, but that's a battle
being fought on a different field. For the moment I have to use the
spreadsheets as they are.

My job in QA is to verify changes to spreadsheets to ensure product
compatibility. Compare.xla does a great job displaying differences, but I
can't use it on this spreadsheet.

I'm wondering whether the code can be modified to accommodate huge formulas.
If it can't, I totally understand. I can't imagine designing a utility with
conditions this outlandish in mind.

Whatever the response might be, let me say thanks for this utility - it's
helped us quite a lot just the way it is.

Bill Le May
 
M

Myrna Larson

Hi, Bill:

As you can see from the code, that line just calls a built-in method of the
range object. AFAIK, there's no other way to retrieve a formula from a cell.

Here's an excerpt from a MSKB article about XL2000 and XL2002 that may be
relevant.

"In Microsoft Excel 7.0 or earlier, ... strings greater than 255 characters
in length that are passed from a Visual Basic procedure to any Microsoft Excel
function or object are truncated to 255 characters.... This limit applies to
all strings that you pass from a Visual Basic procedure to an Excel sheet; it
is not exclusive to information you pass to cells."

This is the reference to the article: http://dpmzo.url.cjb.net/

In Excel 2002 I just tried this code:

Sub Test()
Dim F As String

On Error GoTo Trap
F = ""
n = 400
Do Until Len(F) > 1024
F = "=" & Application.Rept("+1", n)
Range("A2").Formula = F
n = n + 1
Loop
Exit Sub

Trap:
Debug.Print "Error at n = " & n & ", formula length = " & Len(F)
Exit Sub
End Sub

It stops when N = 451 and the length of the formula = 903

Admittedly, this code moving text in the other direction, but I believe the
issue is the same.

In summary, this is an Excel limitation, and AFAIK, we're stuck with it.

Myrna Larson
 
D

Dave Peterson

Maybe you could check for the error and if you find it, mark it as a "maybe
different" cell.

Without looking too deep (well, without looking at all!):

on error resume next
mV1 = Cell1.Formula
if err.number <> 0 then
'oh, oh, do some extra work!
err.clear
end if
 
M

Myrna Larson

Hi, Dave:

After I posted my reply, I had the same thought. I've emailed Bill saying that
if that approach would be helpful, I can modify the code along those lines.

Myrna Larson
 
B

Bill Le May

Hello again

Yes, the error trapping would allow the program to continue checking other
cells and indicate that the Franken-formula would have to be checked
manually. A MsgBox naming the offending cell would be ideal. If that change
could be made I would be very thankful. I could probably make an attempt at
it, but of course Compare.xla's digital signature would be invalidated by my
changes.

Thanks again to Myrna and Dave for their attention to my unusual problem.

Bill
 
M

Myrna Larson

I will make the needed changes and email the file to the address in the email
you sent me.

I thought I would list the cell along as a differences, but instead of giving
the formula or its value, show the text "Error reading formula!"
 
B

Bill Le May

Myrna Larson said:
I will make the needed changes and email the file to the address in the email
you sent me.

I thought I would list the cell along as a differences, but instead of giving
the formula or its value, show the text "Error reading formula!"
Click to expand...

Perfect. Thanks.
 
Top