#32 Run-time error '6': Overflow

[Anonymous]

I'm trying to compare two spread sheets and am receiving a VB pop up box with 'Run-time error '6': Overflow'.
I receive this error message every time unless I start the compare at row number 3.
The 1st row is my general text headers for the sheet, and the 2nd row is a mixture of text, number and finance values (as are all the other rows after this point).

[SteveT]

Can you send me the files, or similar which have the issue so I can investigate.

Thanks, Steve

[olivier81]

I'm encountering the same problem by using Spreadsheet Compare V1.26 with Excel 2007 to compare two sheets of approximatively 1400 rows.
The comparison is starting and when the row 716 is reached, I am receiving a VB pop up box with 'Run-time error '6': Overflow'.

Thank you for your support Steve.

[SteveT]

Thanks for the report.

Can you tell me how many rows there are in the spreadsheets you are comparing. Also, what outputs have you selected (report and/or merged differences report).

Thanks, Steve

---

From: olivier81 olivier81@users.sf.net
To: [spreadshcompare:bugs] 32@bugs.spreadshcompare.p.re.sf.net
Sent: Thursday, January 16, 2014 1:13 PM
Subject: [spreadshcompare:bugs] #32 Run-time error '6': Overflow

I'm encountering the same problem by using Spreadsheet Compare V1.26 with Excel 2007 to compare two sheets of approximatively 1400 rows.
The comparison is starting and when the row 716 is reached, I am receiving a VB pop up box with 'Run-time error '6': Overflow'.
Thank you for your support Steve.

---

[bugs:#32] Run-time error '6': Overflow
Status: pending
Labels: overflow run-time error
Created: Fri Jan 18, 2013 04:05 PM UTC by Anonymous
Last Updated: Fri Apr 26, 2013 02:41 AM UTC
Owner: nobody
I'm trying to compare two spread sheets and am receiving a VB pop up box with 'Run-time error '6': Overflow'.
I receive this error message every time unless I start the compare at row number 3.
The 1st row is my general text headers for the sheet, and the 2nd row is a mixture of text, number and finance values (as are all the other rows after this point).

---

Sent from sourceforge.net because you indicated interest in https://sourceforge.net/p/spreadshcompare/bugs/32/
To unsubscribe from further messages, please visit https://sourceforge.net/auth/subscriptions/

[olivier81]

Dear Steve,

Thank you very much for your fast reply.
To answer your questions, the spreadsheets I'm comparing are in size 1427 rows X 26 columns.
I've not selected any of the options you are mentionning (report and/or merged differences report). I've only requested to Spreadsheet Compare to highlights changes with color.

I've read on internet that to solve this kind of error with VBA, it's necessary to use Long variables instead of Integer variables to make sure that we don't exceed the allowable variable value when running through large numbers of iterations (-32766/+32767 for Integer).

When the error appear and I click on debugging, the following code line is yellow highlighted:
Sheet2Value = CStr(wksWorksheet2.Cells(intwbkTwoRow, lngColumn).Value)

Hope it helps.

[SteveT]

Yes, that helps.

This should be fixed in  Rev 1.27.1. Should be available to download now if you restart Excel.

Steve

---

From: olivier81 olivier81@users.sf.net
To: [spreadshcompare:bugs] 32@bugs.spreadshcompare.p.re.sf.net
Sent: Thursday, January 16, 2014 2:25 PM
Subject: [spreadshcompare:bugs] #32 Run-time error '6': Overflow

Dear Steve,
Thank you very much for your fast reply.
To answer your questions, the spreadsheets I'm comparing are in size 1427 rows X 26 columns.
I've not selected any of the options you are mentionning (report and/or merged differences report). I've only requested to Spreadsheet Compare to highlights changes with color.
I've read on internet that to solve this kind of error with VBA, it's necessary to use Long variables instead of Integer variables to make sure that we don't exceed the allowable variable value when running through large numbers of iterations (-32766/+32767 for Integer).
When the error appear and I click on debugging, the following code line is yellow highlighted:
Sheet2Value = CStr(wksWorksheet2.Cells(intwbkTwoRow, lngColumn).Value)
Hope it helps.

---

[bugs:#32] Run-time error '6': Overflow
Status: pending
Labels: overflow run-time error
Created: Fri Jan 18, 2013 04:05 PM UTC by Anonymous
Last Updated: Thu Jan 16, 2014 07:13 PM UTC
Owner: nobody
I'm trying to compare two spread sheets and am receiving a VB pop up box with 'Run-time error '6': Overflow'.
I receive this error message every time unless I start the compare at row number 3.
The 1st row is my general text headers for the sheet, and the 2nd row is a mixture of text, number and finance values (as are all the other rows after this point).

---

Sent from sourceforge.net because you indicated interest in https://sourceforge.net/p/spreadshcompare/bugs/32/
To unsubscribe from further messages, please visit https://sourceforge.net/auth/subscriptions/

[olivier81]

Dear Steve,

I've installed your new release V1.27.1 but unfortunately, it does not solve the problem.

I still have the VB pop up box with 'Run-time error '6': Overflow' at the row 1200 this time with debugging showing another line:
'Get the sheet value
sSheetValue = CStr(wksCurrentWorksheet.Cells(lRow, lngColumn).Value)

I've attached screen copies of the application settings for you.

You might not be far to find the solution.

Thank you for your valuable support.

[SteveT]

It maybe that the installer picked up the wrong file. I have made the change again and updated it. Try installing again. I have tested with a 3000x40 spreadsheet and could not replicate the fault. If it still occurs, can you send me your files.

Thanks, Steve

[olivier81]

Dear Steve,

I've reinstalled the release v1.27.1 but it does not solve the problem.

I finaly managed to identify the reason of the bug: if the two spreadsheets you want to compare are integrating some cells with different format between spreadsheet n°1 and 2, then you get the error message Run-time error '6': Overflow.

You will find on attachment two test files so you will be able to understand the problem.

File "Test error 6 Spreadsheet Compare 1.xlsx": cell V15 -> value of the cell is 6306307781 with a cell format set to standard,
File "Test error 6 Spreadsheet Compare 2.xlsx": cell V15 -> value of the cell is 6306307781 with a cell format set to date (Excel is displaying ################## on the screen because this value is not recognised as date).

The easiest way to solve this problem is to select all the cells sheet of the workbook before starting a comparison, right click, and set cell format to standard.

I'm not an IT specialist so I don't know what need to be modified within your VBA macro in order to prevent this kind of problem.

By the way, I would like to thank you for your extreme reactivity to provide me some answers.
Your freeware is the best to do Excel sheets comparison and I'm using it very often.

Olivier

[SteveT]

Thanks for the donation! Much appreciated.

I have found a workaround for the problem. Will be a couple of days until the next release, but you can make the change yourself until then.

On the line that you receive the error, stop the debugger (click the 'stop' square on the toolbar) and then change .Value to .Value2:

sSheetValue = CStr(wksCurrentWorksheet.Cells(lRow, lngColumn).Value2)

Click on the Save button on the toolbar. This will return the value of the underlying value of the cell. I have tried it on your files and it runs without error. Because the value of the cell is the same, but formatted differently, it compares as the same (no highlighting etc.).

Steve

[SteveT]

Looks like this change has caused some issues with valid date data, so it may be necessary to remove this change as the display of valid data is more important than the display of invalid data.

Steve