The Quick Access Toolbar can refer to macros in a specific document

A long time ago I wrote some macros in an Excel workbook. A user put shortcuts to them in the Quick Access Toolbar. When the workbook was copied, we found that pressing the buttons made Excel open the old copy of the workbook - apparently it was trying to run the macro from the original document. This was confusing because the QAT customization screen referred to the commands as ThisWorkbook.MacroName and even showed the path to the new workbook on mouseover. But deleting the macro entries from the QAT and re-adding them from the new workbook made them work with the new workbook.

Compressing images in an Excel workbook is very similar to in PowerPoint

Quite a while back, I devised a PowerShell script to convert all images in a PowerPoint presentation to JPG and compress them. More recently, someone needed to do the same thing, but for an Excel workbook. It turns out that XLSX archives have essentially the same structure as PPTX archives except that Excel-specific data is under the xl folder instead of the ppt folder for PowerPoint. Word DOCX archives use word, so the script should also work for them after tweaking the path.

Converting embedded images in PowerPoint to JPG

One user wanted to know how to convert a bunch of PNG images already embedded in a PowerPoint presentation to JPG so that lossy compression could be used to reduce the file size of the overall presentation. I'm not aware of a way to do that in VBA, but I know that PPTX files (like all modern Office documents) are actually ZIP archives, so I took advantage of that fact to write a script that opens a PPTX, converts all PNG files in /ppt/media to JPG, and puts them back into the archive. Interestingly, PowerPoint doesn't seem to care that the extension doesn't match the image format, so that made the job easier. I also included an optional -Quality parameter that specifies how good the resulting images need to look. The full script can be seen in my answer.

PowerPoint has trouble with images over MTP

While working on a PowerPoint presentation, I needed to add a picture I had taken on my phone. So I connected my phone to the computer over USB and did the normal Insert | Picture thing, selecting the desired photo in the resulting standard file browser. But instead of inserting the image into my presentation, PowerPoint just froze and would not do anything at all. I had to end the process. Then I tried opening the picture in the Photos app off my phone, copying it with the context menu, and pasting that into PowerPoint, but that brought up a message about PowerPoint being made unstable. Copying the actual image file off the phone onto my disk then inserting that file worked perfectly fine.

Apparently, PowerPoint doesn't handle embedded images well from MTP (Media Transfer Protocol). I suspect other Office programs have the same issue, but I have not checked those.

What the ~$ Office files are actually used for

When you open an Office document, a small hidden file is created in the directory. It's named the same as the document, but with ~$ overwriting the first two characters. One might wonder why the Office programs bother doing that.

The answer, as included in this old Microsoft article, is that these little files are there so Office can tell you who's responsible for the file being in use. When a document is opened, Office notes the current user's display name into this file. When another Office instance tries to open the file but receives an error that the file is in use, it looks in the squiggly dollar file to report the human-readable name of the person who's holding the lock.

The advantage of knowing that fact is that users in an office trying to edit a file on a network share can go to the specific person and ask them to maybe finish working with the document.

Based on my Super User answer.

Office 2016 is apparently stable now

More than a year ago (wow), I tried a preview version of Office 2016. It did not go well. It did work on Windows 10, though, and I tried it a little in a VM. A few days ago on my Windows 8.1 main machine, Office 2013 asked me to update to the newest version of Office. I hesitantly accepted. It downloaded and installed the new programs successfully.

The only hiccup I ran into during setup was that the first post-upgrade run of each application put up the normal "didn't start correctly last time" dialog, but that went away on later runs. The only application I've been using heavily since the upgrade is Word, and it seems to be working fine. Evidently, Microsoft worked out the kinks.

Interestingly enough, PowerPoint always wants to run as administrator now. That's not happening with any other Office program. It still works fine once I elevate it, though.

SSL certificate acquisition security and Outlook Online groups

Many organizations that issue basic SSL certificates use e-mail to verify ownership of the domain. Such processes involve sending a verification code to hostmaster or postmaster or webmaster at the domain in question (or its parent, in the case of subdomains).

I know of some enterprise and academic organizations that use Office 365 and Outlook Online for their e-mail. One feature of Office 365 is groups, which can function as faux-addresses that forward all e-mails to the group members. Depending on the settings, users can form and disband groups as they please without administrative approval.

What do those two facts have to do with each other? Well, imagine what would happen if somebody could create a group called hostmaster. Sure enough, that's possible, and it will indeed produce the group address of hostmaster@domain.tld. If the Outlook addresses are at the root domain, anybody who can form groups can effectively pretend to be the hostmaster and create SSL certificates for the domain. A free issuing entity that uses e-mail verification is StartSSL.

What can be done about that? Some things:

• Reserve sensitive group names by creating private groups (so users can't join)
• Don't give normal users addresses at your root domain (do something like ourmail.example.com)
• Disable the group features if you don't need them

Microsoft might want to consider making sensitive names not allowable as group IDs.

What's the biggest number Excel can handle?

A fairly common question about Excel is the maximum number size. Getting a simple answer requires a bit of research. The most relevant document is "Excel specifications and limits."

The absolute largest positive number that can be dealt with in Excel is 1.79769313486231570 • 10³⁰⁸, using the Double data type. However, you can't have a literal number in a cell bigger than (i.e. you need an arithmetic formula to go above) 9.99999999999999 • 10³⁰⁷. Note that the Double type is floating point, so once your numbers get that large, you'll lose some precision if you try to add comparatively small numbers. (Excel only does 15 figures.)

Office 365 Deployment Tool Surprise: Updates element required

I tested the Office 2013 Deployment Tool today, and discovered an interesting phenomenon. The first time I asked it to download the installation media, I had only enabled the Add element, the one that specifies the products and edition to download. I left all the other elements, including Updates, commented out.

When I tried to use the tool in /configure mode to set up Office on another computer, it failed, leaving a message in the log about not being able to find a certain CAB file in the installation media. That file was in fact absent.

After a bit of trial and error, I found that enabling the Updates element for the /download phase seemed to make the tool download more/bigger files, including the one that had been absent. It's entirely possible that I did something wrong, but maybe it's required to declare whether you're getting updates?

Office 365 Deployment Tool

Office 2013's style of installation makes it tricky to deploy and license via normal methods. Streaming every installation package from the Internet seems inefficient to me, and Microsoft understands. Therefore, they created the Office 365 Deployment Tool. It enables you to download the installation media to local storage for simpler deployment. The Configuration.xml file governs the behavior of the tool, and the program itself has very sparse information on what you need to do, so you'll want to consult the reference.

Office Document Editing "Blocked due to Policy Settings"

Today I received a strange message from Word when I opened an old (Office 95) document. It placed the document in Protected View and said something to the effect of "editing blocked due to policy settings." I had not configured any Local Group Policy settings, and the computer was not joined to a domain, so the word "policy" threw me off a little. I was not super pleased that Word refused to let me edit my document, so I poked around a little and discovered the File Block Settings.

The File Block Settings are a tab of the Trust Center, which is accessible via the Trust Center tab of Word Options. An array of checkboxes controls whether or not Word will enable the editing of certain file types. By default, it seems, Word 95, Word 6.0, and Word 2 documents are uneditable. I'm not sure what makes those versions of Word so dangerous, but unchecking the boxes removes the restriction.

The File Block Settings in Trust Center

Office 2016 Works on Windows 10

Two days ago, I had a rather inconvenient experience with the Office 2016 preview. I did, however, continue trying to make it work - they wouldn't have released it if it didn't work at all.

My first plan was to throw it onto a Windows 10 VM, and that required getting the latest build. One 3GB download and half an hour of installation time later, I had a very nice Win10 virtual machine. I then installed the Office 2016 preview, the trial version, just as before.

This time, when the installer finished, there was an animation above the finishing button. Clicking on that button closed the window correctly without any errors being thrown in my face. I went to launch Word and... it worked! I activated it with the product key given on the webpage, and a little banner indicating the trial-version-ness of the product appeared. I was then able to use Word just as usual.

The only useful difference I've noticed so far - I haven't messed with it much - is the presence of a "what do you want to do?" text box that grants access to all of Word's features when you type a few keywords about what you want to do. It's kind of like searching for settings in Windows 8, except that it seems to actually work.

It also somehow knew who I was and signed me into OneDrive and Office automatically. That was a little creepy. (I'm guessing it looked at the Microsoft account I was signed into the computer as.)

So, I guess the Office 2016 preview only works on Windows 10?

Trying Office 2016 Preview

I decided to install the preview of Office 2016 on my main machine here to give it some actual testing. Short version of this article: it was a complete bust and I have been moderately inconvenienced.

Since the organization through which I get Office 365 has not enabled preview versions for installation, I chose the download under "Don't have Office 365?" on the product info page. The installer told me that it would remove the old version (Office 2013) from my computer, and I said OK to that. (It's always possible to reinstall the stable 2013 version after removing the preview.) So I waited for the installer to do its thing, and it completed as I expected.

Once I acknowledged the setup completion, an error about OneDrive file synchronization was thrown in my face. It claimed my upload cache had been corrupted, and wanted to replace it with a clean one. I accepted, and it opened the Upload Center, which I didn't really want. There was no close button, but right-clicking the title bar and choosing Close worked.

I then went to launch Word so I could continue editing a document I had been working on earlier. It showed the standard Office 2013 blue solid rectangle with loading dots, then crashed almost immediately without showing a main window. I tried all the Office applications, and all of them crashed similarly. A bit of poking around with Visual Studio revealed that the crash was caused by a null pointer dereference.

My next shot at getting these programs to work was via the Windows compatibility options. I fiddled with the settings and the troubleshooter, and eventually discovered that the apps sort of work if and only if they're run in compatibility mode for Windows 7. I was then able to see their main screen. It looks fairly similar to Office 2013, but with more web-like style (imagine Office Online) and non-all-caps tab names. It also crashed after letting me look at it for five seconds, but it wasn't the same fatal error dialog ("would you like to debug this program?") as before. Rather, it said something along the lines of "PowerPoint ran into an error and needs to close" and offered a "Repair" option, which didn't seem to do anything besides wait two seconds and close the program. (I did get to continue interacting with the UI while said repairing was being done.)

I did some Internet searching on the problem and discovered that having vestigial parts of old Office installations may trip up Office 2016. So I went to Programs and Features and - lo and behold - there was still an entry for Office 2013. I clicked Uninstall on it... and it said it couldn't proceed because there was a newer version of Office there.

Great.

So I uninstalled Office 2016, then uninstalled Office 2013 (leaving behind a bunch of broken shortcuts), then rebooted, and installed Office 2016 again. This time I got no OneDrive errors, but the programs still didn't work. Same crashes and error dialogs as before.

I then resignedly removed the Office 2016 installation and reinstalled Office 2013. At least that still works. I'll keep an eye on Office 2016 development, and maybe try installing the current version in a Windows 10 VM.

Office 2016 Preview is Available

Previously only available to large businesses, the preview of Microsoft Office 2016 is now available to everybody. It's completely free, even if you don't have a licensed version of Office already. (Once the actual Office 2016 comes out, the preview will function like a trial version and expire.) You can download it with a full, permanent license through a personal Office 365 subscription or one from your enterprise.

Learn more and get it at Microsoft's announcement page.

VBA Script to Search Google for Selected Text in Any Word Version

Though there is a way to tweak Word 2013 to turn the "Search with Bing" context menu option into "Search with Google", neither can be found in Word 2007. So, I wrote a little VBA script to make searching Google from Word's selected text just a keyboard shortcut or mouse click away.

To add this to your Word installation, enable the Developer tab under the advanced options (it's actually under Popular in Word 2007). On it, click Macros. Pull down "Macros in" and choose "Normal.dotm (global template)". Type "SearchGoogle" or any name without spaces into the "Macro name" textbox and click Create. The VBA Editor appears. Paste the following code into it:

Private Declare Function ShellExecute _
 Lib "shell32.dll" Alias "ShellExecuteA" ( _
 ByVal hWnd As Long, _
 ByVal Operation As String, _
 ByVal Filename As String, _
 Optional ByVal Parameters As String, _
 Optional ByVal Directory As String, _
 Optional ByVal WindowStyle As Long = vbMaximizedFocus _
) As Long
Sub SearchGoogle()
 Dim text As String
 text = Selection.text
 text = Replace(text, " ", "%20")
 Dim res As Long
 res = ShellExecute(0, "Open", "http://google.com/search?q=" & text)
End Sub

Click the Save button in the VBA Editor, then close it. Now decide whether you want a keyboard shortcut or a Quick Access Toolbar button for the macro.

If you want a keyboard shortcut, open the options and find the place to configure keyboard shortcuts. (On Word 2007, the button is on the Customize tab. On Word 2013, it can be found at the bottom of the page on the "Customize Ribbon" tab.) Choose Macros from the left list, then the macro you just created on the right. Click on the "Press new shortcut key" box and press the shortcut key you want for this. Save your changes and exit all dialog boxes.

If you want a mouse-accessible button, find the place to customize the Quick Access Toolbar under Word Options. Pull down "Choose commands from" and choose Macros. Select the macro, then click "Add >>" to move it onto the QAT. You can then modify its icon and mouseover text if you'd like, with the Modify button.

You can now use either a keyboard shortcut or a QAT button to search Google for your selected text.