If you are more-than-average well-versed in Excel, this problem may interest you.

Attached is a very small (less than 0.5%) sample of my Library Index file. Note that it is a macro-enabled *.xlsm file, and it does contain a macro which despite Microsoft's nanny assertions probably won't set your computer on fire.

My Library Index is a heavily conditionally-formatted list of all my electronic books, both audio books and ebooks. My current project with this list is to add plot synopses to all of the ebooks; the audio books already have them, as do the ebooks that are duplicates of the audio books where I was able to just copy those synopses over from the audio books.

The synopses are entered as Comments to the book title cells. These cells are formatted by default in tiny little boxes, using a font called "Tahoma" in eight-point bold type. These settings are totally unsuitable for what I am doing, and the defaults cannot be changed. So, rather than go through nearly 7,000 comments one at a time and change the typeface, the size of the box and the size of the type I have an Excel VBA macro that does it globally for me in a single pass.

Actually, there are two macros, one for fixing the type, one for fixing the box size. For the purpose of this exercise, only the second one is included here, and that is the one that is causing the difficulty.

I didn't write this macro, I found it on the internet after literally hours of searching. I understand it (there is only one line of it that isn't obvious in function) and am completely baffled about why it doesn't work properly. What is the problem with it?

For most of the cells it works pretty much correctly. For many of the cells it puts excess blank space below the text in the Comment box, sometimes more blank space than the text itself takes up. For a very few cells it truncates the text in the Comment box.

I won't bore you with all the dead-end troubleshooting I have done on this, except to suggest that it might somehow be related to the actual text of the comment, and that once a Comment box size has been established for a cell, it stays established regardless of future efforts.

If anybody is knowledgeable and brave enough to take a look at this, I would very much enjoy hearing from you. Should you decide to play with this, note that the macro works only on selected (i.e., highlighted) cells.

tanstaafl.

Addendum 1: In a previous Excel-oriented thread, there was discussion of the much-despised "Ribbon" interface and its deficiencies. I have found that this ribbon is very configurable. You can add new tabs, you can move operations from one tab to another, you can delete entire groups (for example, I have gotten rid of that horrid "Styles" group that used to take up about a third of the ribbon, and added a "Quick Print" icon) and like many before me have discovered that once you've spent five or 10 hours learning the new interface, you'll never want to go back to the old one.

Addendum 2: As RobotCaleb suggested, Amazon.com is a good source of synopses. Probably 60-70% of my ebooks have Amazon book descriptions. With my programmable keyboard, if Amazon has the description I can put it into my Library index with three mouse "operations" (two drag-to-highlight and one left-click) plus three keystrokes, takes less than 10 seconds.

db

When all you have is a hammer, everything looks like a nail.

I think what Bitt is trying to say is that you're using a spreadsheet for something that clearly begs for a database, no?

Yes Excel sometimes breaks when you have large amounts of text in cells so not entirely surprising it breaks using comments. It seems to not completely know the size of text.

As the others have said you really are using the wrong tool here. My analogy would be trying to use a screwdriver to put in a nail

Ok. I've had a play. The code is flawed. What it does is calculate an area based on shape and height when auto sized. Auto sizing basically puts it all on one line. This works fine for a single paragraph. As soon as you have a second paragraph, regardless of how big it is, you end up with double the area. Third paragraph triples it and so on.

e.g. using characters rather than points/pixels

gives an area of 30 x 1 = 30 characters


Where the underscores are nothing (not spaces), gives an area of 30 x 2 = 60 characters.

If you then change the size to say 10 characters wide you end up with


Height = 30 / 10 = 3 lines. No problem.


Height = 60 / 10 = 6 lines. Problem

The more paragraphs (new lines) you have, the more space you'll have. Also the longer the first (or longest) paragraph is, the more space you'll have compounding the problem.

This doesn't explain the truncation but is your main issue.

I must respectfully disagree with both of you. Well, disagree, anyway.

In this particular case, the presentation is as important, possibly more important, than the data itself. I can't do the kind of conditional formatting with a database that this spreadsheet provides. There are twelve different scenarios or combinations thereof that control twelve different cell background colors depending on who has read the book, the source, the type of book, etc. (Column I summarizes these.)

Now I must confess that I am far more familiar with Excel (as Bitt suggested, Excel is definitely my "hammer") than I am with, say, Microsoft Access. I've poked around a bit in Access, but haven't explored it thoroughly. Does Access have the same sort of conditional formatting power that Excel has? Even it it has, there would be little advantage in using a relational database program to manage a flat-file database. I mean, why set up relational tables to handle, for example, the "Media" data which consists of only two data types?

Don't fall into the trap of thinking that Excel is only for making mathematical calculations. It is far more powerful and versatile than that.

tanstaafl.

Brilliant! Now why couldn't I have seen that?

A few minutes ago I was well on my way to pointing out the flaw in your analysis, because it only fixed about half of the problem cells. Then I discovered that the ones it didn't fix had paragraph/line-feeds at the end of the comment text. Once I got rid of those, then the macro worked exactly as it should.

At least I was right in my tentative supposition that the actual text itself was somehow a factor in the problem.

Would you care to hazard a guess as to why the one comment gets truncated? Likely something to do with it being the shortest comment, but what?

The one line in the macro that was inexplicable to me is this one:

If .Shape.Width > 0 Then

I could never understand the need for that qualification. I deleted that line (and the corresponding End If line, of course) and the macro still works.

Now, have you any suggestions for a macro to go through all the Comments and change every Paragraph/line-feed to a space, or perhaps to a pair of spaces? I understand just enough Visual Basic that I can sort of understand code that someone else wrote if it is simple enough but not enough to write it myself.

tanstaafl.

Ok. The "If .Shape.Width > 0 Then" seems to have originally been something like "If .Shape.Width > 300 Then" so that it would not apply it to short comments (or ones with one or more short lines). However I don't see that would have made any significant difference. The sheet you linked still has that >0 code, but it should always be true so it would always execute the size change code.

Which is the short "truncated" comment? The "Hawksmoor" one? If so the problem probably stems from the fact that the last line is only a single word. Basically this is kind of the opposite effect of the main problem. I think that Excel's numbers returned for Width are not the most accurate. Also it has a short description and only one word on the last line.

I think you need a +1 line or similar to round up to the next whole number of lines. Other wise if you have 2.5 lines of text your box will only be 2.5 lines high. Larger amounts of text tend to average that difference out/away

It looks like a standard line height is 13.5. So the original numbers look like this:
Width 704.25
Height 13.5
Area = 9507

Scale that to 305 wide and you get
Height = 9507 / 305 = 31.1
which is only enough to show two and a bit lines.

Really that 31.1 should be more like 40.5

Sooo... if we can assume a line is 13.5 high I would do something like this (thinking aloud).

Height = 9507 / 305
Lines = Height / 13.5
Lines += 1
Height = Lines * 13.5

Where Lines is an integer to ensure it's rounded down. Doing that doesn't appear to work since a line height isn't 13.5 it seems (that's what's reported for a single line). So it's 13.5 for the first line and then x for each line after that probably. This is all getting quite clunky and yucky with the hardcoded 13.5... Change the font and it all breaks.

Or just add 1 line height (once you figure it out). You'll have between 0 and 1 extra lines if you do on every comment).

For the main issue, are you sure you want to lose your paragraphs?

Biggest problem here is really Excel's text handling. It just doesn't quite work. A good example is wrapping text in a cell (so it changes the height automatically). If the text is close the length of the cell (but doesn't actually wrap), it still adds the extra line sometimes. Similarly once cells get simply too big it fails to calculate the correct height and the text runs off both the top and bottom of the cell.

I have extensive experience with Access and I can assure you that it can do everything Excel can do and much more when it comes to data presentation.

Indeed. But I think it would fail to provide Doug with what he wants here.

He is combining data collection, data manipulation and reporting into a single function. Not something that Access or indeed any other database solution is good at.

Rubbish. You could use a grid control or a continuous form to achieve the same effect. It'd take more work to get setup the way he wants it, but in the long run, he'd be better off. If I hadn't switched to a Mac, I'd work him up a sample database.

I've been playing with Access 2010 recently and I agree with John, but I guess there's a learning curve.