Curiosities Archives - yoursumbuddy

Book1.Saved = True

Posted on January 20, 2015 by Doug Glancy

You may be familiar with VBA’s Workbook.Saved property. It’s read/write and lets you determine, or set, whether a workbook has been saved. Reading is straightforward but setting is more interesting. Declaring

MyBook.Saved = True

doesn’t actually save the workbook, it just tells Excel to act like it’s saved. So, if you don’t make any changes to the workbook and you go to close it, you won’t be prompted to save.

If you had asked me if it’s possible to set the Saved property on a never-been-saved workbook I would have said, “I don’t know, let me try it and get back to you.” Well you didn’t ask, but I’m getting back to you anyways. It turns out you can. You can open up a brand-new workbook, type “Movin’ to Montana soon” in A1 and “Gonna be a dental floss tycoon” in A2, then enter

Book1.Saved

in the immediate window, and you won’t be prompted to save if you close it.

I didn’t expect that. It kind of makes sense that you can declare an existing workbook to be saved, but one that doesn’t even have a location yet? That doesn’t sit right with the left side of my brain, which would prefer that the property be re-named to something like

Workbook.YouWontBePromptedToSaveUnlessYouChangeSomething

To expand on the above paragraph, a never-been-saved workbook doesn’t have a path. You can test the ActiveWorkbook with

ActiveWorkbook.Path = ""

, which returns True if the workbook has never been saved. I’d thought maybe setting Saved to True would change the Path property to the never-been-saved workbook location, but it doesn’t. Also, if you do decide to save, the Save As menu pops up, just as you’d hope. So it looks like setting Saved to True changes nothing except that you won’t be prompted to save.

I’ve found this useful for a certain kind of utility: one that prints out information which I want to examine but not keep. For instance, there’s a routine I use a lot that compares the sheets in two workbooks. It opens a new workbook and prints information about all the sheets in each book and whether they occur in the other book and, if so, the extent to which they match. The new workbook with the comparison is called “wbComparison,” so the last line of code is:

wbComparison.Saved = True

I know it’s a small thing, but it pleases me to look over the comparison and close it without being prompted to save.

Here’s a very simple sample for your testing pleasure:

Sub ShowZappaLyrics()
Dim wbZappa As Excel.Workbook

Set wbZappa = Workbooks.Add
With wbZappa
With .Worksheets(1)
.Cells(1, 1) = "Movin' to Montana soon"
.Cells(2, 1) = "Gonna be a Dental Floss tycoon"
End With
.Saved = True
End With
End Sub

Camera Tool Selfie

Posted on January 8, 2015 by Doug Glancy

Happy New Year!

This post-holiday post harkens back thematically to Pivot Table Circular References, while utilizing some of what we learned in Conditional Formatting Color Scales Based on Other Cells.

But enough self-referential posturing, let’s have some tautological photo fun!

I was reading Debra’s roundup the other day and somebody was talking about… actually, it doesn’t matter what, but it got me wondering what would happen if you used the camera tool to take a picture of itself. In case you were wondering, this is the “thematic harkening” to the first linked post above, in which I tried to base a pivot table on itself. This time the results were more colorful, if no more useful.

So what you see below is a dynamic picture taken with the camera tool of an area conditionally formatted with a color scale and the number format set to “;;;”. See the second link above for explanations of all that.

Add a little VBA automation and you can get rid of your cable subscription.

What I didn’t see coming was the infinite loopiness, where the camera tool takes a pic of itself taking a pic of itself taking a pic … Pretty cool.

Kind of like when you’re in a dressing room with the mirrors front and back and you get a reflection of a reflection of a reflection … Unless you’re like me and you just keep buying the same pants online.

Here’s the downloadable workbook. See you next time around!

Pivot Table Circular References

Posted on May 10, 2014 by Doug Glancy

The other day I ran into a workbook containing a circular reference between a pivot table and a regular table. The table based calculations on the pivot table, which were then used in a summarizing calculated field in the pivot table, or something like that. This seemed like a dicey approach, so those fields and formulas are with us no more. But it did get me wondering about pivot table circular references.

Content Advisory: This post contains material that will be useless to most, if not all, viewers. On the other hand, it’s mostly pictures.

Below is one of those interactive embedded workbooks I love so well. It has a dead simple example of a circular reference involving a pivot table and a table. The pivot table is based on the data cell, which in turn has a formula to multiply the pivot cell times two. Right-click somewhere in the pivot and hit “refresh” to see it in action.

Then I wondered if you can take it one step further and base a pivot table on itself. Turns out you can:

So there it sits, a self-referencing pivot table. Sit is about all it does though. You can’t change it:

And if you Refresh it, it disappears, because the original source column name has been overwritten. For example, the “Data” field above became “Sum of Data.”

I fooled around with calculated fields, but Excel doesn’t let you chain those back to themselves. And now I understand of why you can’t give a pivot table’s Value field the same name as its underlying data field. (For example if you remove the “Sum Of ” part Excel squawks at you.) If you could, I think you could make a calculated field that doubled itself.

If anybody comes up with a more interesting example or use of pivot table circular references, please share.

Curious Case of the Replaced Date Slash

Posted on November 15, 2013 by Doug Glancy

At my beloved workplace we’ve developed a fondness for slashes in institution names. If we’d been in charge, it would be Bachmann/Turner Overdrive, the Mason/Dixon line, Obsessive/Compulsive Disorder and well, you get the idea. Aesthetics aside, this causes problems when generating workbooks based on these names. Windows doesn’t like slashes in its file handles, because it uses them as path separators (except when it doesn’t, which is why you should use Application.PathSeparator in your code).

I recently wrote a little function to strip slashes and other shady characters out of file names and replace them with dashes, which don’t stick in Windows’ craw. Up until then, my co-worker was forced to do a search and replace on the offending monikers. In doing so she pointed out that the replace dialog mistakenly says it’s replacing all the slashes in the dates on the sheet as well, even though they remain in place.

For example, take a column like this, with one name and 1,048,574 dates:

And here it is with Excel proclaiming to have replaced 2,097,149 “/”s with “-“s, when clearly it only replaced the one. Silly old Excel!

I theorized that Excel was doing a two-step operation: first counting the slashes – including those in the dates – but then only replacing the non-date ones. I can certainly see myself doing something like that. We figured this was because Excel stores dates as numbers and the human/readable dates such like “11/15/2013” are just formats, and the slashes aren’t “really” there.

Turns out that’s wrong. I should have given Excel more credit. It really does replace the slashes if you ask it to. For example, try a find and replace with explanation points, or “bangs” as some folks call them. I get this:

What seems to happen with dashes is Excel recognizes them as date separators, but, at least on a computer with the slash set as the default date separator, it turns them right back into slashes. You can see this in it’s purest form by typing 11-15-2013 into a cell. It immediately converts the dashes, both in the formula bar and in the cell (at least if you’re using my laptop. I haven’t tested this extensively).

So it really is replacing all those millions of slashes with dashes. But then it converts them right back, without so much as a “by your leave.”

What’s confusing to me is that if I use a custom format like “yyyy.mm.dd” and try to replace the periods, Excel says there’s nothing to replace. That makes sense as the periods are just part of a format. But so are the slashes. What gives?

Speaking of dates, happy anniversary to my wonderful wife!