Solved

Is there a maximum number of cell comments an Excel 2010 spreadsheet can have?

Posted on 2013-01-02
14
814 Views
Last Modified: 2013-02-06
Hi, I have some VBA code which loops through data in a spreadsheet and depending on a set of criteria, adds comments to each cell if appropriate.  This works well and the code works.  However, after a certain number of cells, the code returns an error 91 when trying to execute:

currentcell.comment.add

I can't think of why this would break after thousands of comments added using the same code and in fact, if I delete some data, then the code can run past the cell where the last error occurred (and so it's not breaking at the same place each time).

And so the only thing I can think of is if there's a maximum number of comments that can be in a spreadsheet, but I can't seem to find any documentation of this.

Any ideas?

Thanks,

Hammer8
0
Comment
Question by:Hammer8
14 Comments
 
LVL 14

Expert Comment

by:theruck
ID: 38737288
are you able to write a comment after the vbs stops?
then maybe some other limit.
http://office.microsoft.com/en-gb/excel-help/excel-specifications-and-limits-HP010342495.aspx
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38737348
By experiment using the following macro there is no limit. C got to 66000 and stopped gracefully. The last value shown in the Immediate window was 66000.

Sub doit()
Dim c As Long

For c = 1 To 66000
    Debug.Print c
    Cells(c, 5).AddComment "comment"
Next
End Sub

Open in new window

0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38737378
I ran the above to 100,000 with no problems. If you want to try it yourself I suggest turning off screen updating. e.g.
Sub doit()
Dim c As Long
Application.ScreenUpdating = False
For c = 1 To 66000
    Debug.Print c
    Cells(c, 5).AddComment "comment"
Next
End Sub
Application.ScreenUpdating = True

Open in new window

0
 
LVL 13

Expert Comment

by:MrBullwinkle
ID: 38737461
I'm reading Err91 as "Object or With Variable not set".

If it is infact on that line, then it would seem "CurrentCell" is not set. Check if its equal to Nothing on the failed case, and if so, track back to where it should have been set and figure out why it wasn't.
0
 

Author Comment

by:Hammer8
ID: 38768647
Thank you all for your help.  I was able to track the problem down to a problem of "too much formatting" in the spreadsheet (either actual formatting or a new cell comment).  However, given Excel 2010's 64000 limit of different format combinations per spreadsheet, I know I am not hitting that limit, but yet if I try to apply new format/comments, I either trigger the error I noted above or Excel will crash.  Therefore, I think my spreadsheet is internally corrupt somehow.  

Does anyone know of a way to count the number of format combinations currently in use?  I have tried to google that, but closest I got was the number of styles in use, but that number is only 2 in the spreadsheet.

Thoughts?  Or is there a way to re-save the spreadsheet in a different format which can help fix the potential corruption?

Much appreciate everyone trying to help me!
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38768762
How is a typical comment of your formatted?
0
 

Author Comment

by:Hammer8
ID: 38768839
Hi, not sure what you mean by a comment being formatted.  I would say they are not formatted.  I just use:   cell.addcomment "text"

to add a comment.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 45

Expert Comment

by:Martin Liss
ID: 38768896
What do you mean by
I try to apply new format/comments
0
 

Author Comment

by:Hammer8
ID: 38769429
I mean the code I have will format certain cell (eg., set cell a certain color, change font size etc) or add comments to them depending on a set of criteria.  The code works since it loops properly until a certain point at which I will either get an error 91 (as described on opening post) or Excel will crash.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38769435
Can you attach the problem workbook or a dummy one that exhibits the same problem, and explain how to reproduce the problem?
0
 

Author Comment

by:Hammer8
ID: 38770699
I cannot since the spreadsheet is huge and also won't open unless some addins etc are in place...

Is there a way to know how many format combo's are currently in use by the spreadsheet.  I know I can alway try to re-create it from scratch to remove the internal corruption, but that will be a very time consuming process.

Anyway to re-save it as another format or other ideas?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38770714
How about a version that doesn't include the add-ins and while it's understood that that would remove some functionality, still exhibits the error?

Could the error be coming from one of the add-ins? You could try disabling them one at a time and see if disabling one of them eliminates the error. Other than saving the sheet as a CSV file and reloading in a new workbook which might mess up what you have, I'm out of ideas.
0
 

Author Comment

by:Hammer8
ID: 38845058
I don't have any addins except for Bloomberg which I need.

Hi, still hoping for a solution to this...any thoughts?
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 38845643
How about a version that doesn't include the add-ins and while it's understood that that would remove some functionality, still exhibits the error?
If you could do this then I'd have something to look at.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now