Compare.xla
 

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

Compare.xla
 

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



On Fri, 6 Aug 2004 12:59:38 -0500, "Bill Le May"
wrote:

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

Compare.xla
 

OK, Myrna. Thank you very much for your thorough, timely response.

Bill

"Myrna Larson" wrote in message
...
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



On Fri, 6 Aug 2004 12:59:38 -0500, "Bill Le May"

wrote:

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

Compare.xla
 

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
on error goto 0



Bill Le May wrote:

OK, Myrna. Thank you very much for your thorough, timely response.

Bill

"Myrna Larson" wrote in message
...
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



On Fri, 6 Aug 2004 12:59:38 -0500, "Bill Le May"

wrote:

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



--

Dave Peterson

Compare.xla
 

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

On Fri, 06 Aug 2004 18:53:37 -0500, Dave Peterson wrote:

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
on error goto 0



Bill Le May wrote:

OK, Myrna. Thank you very much for your thorough, timely response.

Bill

"Myrna Larson" wrote in message
...
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



On Fri, 6 Aug 2004 12:59:38 -0500, "Bill Le May"

wrote:

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

Compare.xla
 

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

"Myrna Larson" wrote in message
...
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

On Fri, 06 Aug 2004 18:53:37 -0500, Dave Peterson wrote:

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
on error goto 0

Compare.xla
 

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!"

On Mon, 9 Aug 2004 08:13:09 -0500, "Bill Le May"
wrote:

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

"Myrna Larson" wrote in message
.. .
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

On Fri, 06 Aug 2004 18:53:37 -0500, Dave Peterson wrote:

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
on error goto 0

Compare.xla
 

"Myrna Larson" wrote...
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!"

Perfect. Thanks.