Daily Dose of Excel

A few weeks ago, out of nowhere, I started receiving the following error when I started Excel after a restart or resuming from a locked computer.

Run-time error ‘-2147467259 (80004005)

[Microsoft][ODBC Microsoft Access Driver] ‘(unknown)’ is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

I have a ton of add-ins that load at startup and more than a few of them connect to an Access database via ActiveX Data Objects (ADO). The offending code was simply establishing the ADO connection.

The global constant, gsMRPConn, pointed to the file correctly. I was able to access the network share via Windows Explorer. But when I executed the code

I got 0, meaning VBA couldn’t resolve the path. The fix for a while was to close Excel and restart. Sometimes one restart would fix it and other times it took up to five restarts. I was at a loss for why VBA couldn’t see the network share.

After much searching, I read something about offline files. I had no idea what offline files were or why I would want them. Apparently Windows makes a cache of network files locally so that I can access them when I’m not on the network. I typed “offline files” in the Win7 start menu and found the Offline Files dialog.

I disabled offline files and have not had the problem since. I still don’t know how offline files work. I was connected to the network, so there was no need for Windows to use files offline. And if it had a cache, I’m not sure why it didn’t use it. I guess Windows was working to sync offline files in the background, which is why it worked after some number or restarts. I’m just glad the nightmare is over.

Ever since Microsoft introduced the ribbon and I did my initial development work with it, an open issue has been how to handle the case where two, or more, add-ins offer the same functionality. One scenario is when the feature is something required for the larger functionality offered by an add-in. Here’s an example.

The TM Chart Utilities add-in offers the capability whereby for a chart series labels one can specify a range other than just the X or Y values.

The TM Chart Labels Hover add-in, developed to display a label only when the user hovers over the associated data point, incorporates, as a sub-function, if you will, the capability to specify a range as the source for a series’ data labels. The UI and the code are the same in the two add-ins (I essentially copy the form and the supporting modules from one add-in to the other).

The problem is that with both add-ins installed the UI displays two buttons, both labeled Set Data Labels, that do the same time. It looks clumsy, to say the least.

What I would like is that whether one or both of the add-ins are installed, there is only one Set Data Labels button.

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/1019%20Same%20function%20Different%20AddIns.shtml

Tushar Mehta

Hi all

FYI

A few weeks back I start working on my first Mac, and I must say I love the OS but there is a lot of work to do in Office for the Mac.
My idea is to go through all my webpages and see if I can make the VBA code also working in Excel 2011.

I start this problem page
http://www.rondebruin.nl/mac.htm

I also add VBA code examples last week to mail from Excel 2011 with Apple Mail and Outlook 2011.
If you have problems with Excel on a Mac post it here, maybe I can check it out and find a workeround for it.

Ron de Bruin
http://www.rondebruin.nl/tips.htm

The primary reason I write modular code is that it is self-documenting, easy to understand, and easy to maintain. A secondary reason is the ease of reuse.

One of the comments to my post Two new range functions: Union and Subtract (www.dailydoseofexcel.com/archives/2007/08/17/two-new-range-functions-union-and-subtract/) was a request for code to copy a range from one worksheet to another with certain ranges excluded. While I agree with DK that there’s no need to get fancy with something that is used once a month, I couldn’t pass up the opportunity to illustrate the ease of reuse of modular code.

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/1102%20Modular%20code%20-%20ease%20of%20reuse.shtml

Tushar Mehta

Andrew’s Excel Tips has a new home

Why the change?

Well, my old blog site was getting very tired. Comments were disabled due to the inability to fight spam effectively and I missed getting feedback.

People don’t move their blog unless they have some good stuff coming up. Update your RSS reader and don’t miss it!

Developers who have done any kind of programming with the Office 2007 (and later) Ribbon architecture have encountered almost certainly a scenario that resulted in the loss of their pointer to the ribbon. This happens because the ribbon object has to be stored in a global variable and any kind of unhandled error leads to a “loss of state,” which includes the loss of all global variables.

Rory Archibald came up with an ingenious way to protect the ribbon object by saving the pointer address in an Excel cell.

This note fleshes out Rory’s approach and makes it compatible to 32-bit and 64-bit Office platforms. It also demonstrates, in a reasonably compact example, how to write code that is compatible with (1) different versions of Office (2010 as well as earlier versions) and (2) both 32-bit and 64-bit Office 2010 platforms.

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/1017%20Save%20a%20global%20in%20an%20Excel%20workbook.shtml

Tushar Mehta

Microsoft made several changes to VBA in Office 2010, all of them targeted at the one major change in the Office 2010 architecture, i.e., the availability of 64-bit Office applications. This note summarizes how the changes affect developers. I imagine there is a comprehensive list somewhere in the microsoft.com universe but I could not find it.

Microsoft upped the version number of VBA to version 7. While most version changes introduce several new features and capabilities, that is not the case here. The only enhancement is support for 64-bit Office systems.

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/1016 Office 2010 VBA.shtml

Tushar Mehta

There’s an Excel Power Analyst Bootcamp in our nation’s capital next month. Well not our nation’s capital, but mine. OK, it’s Washington DC. More like Alexandria, VA. Or it might be Springfield, VA. Just ask the cabbie to take you to the Sheraton in old town Alexandria. He’ll know where to go. Here’s the details

Excel Power Analyst Bootcamp Washington DC

Monday, September 19, 2011 – Tuesday, September 20, 2011
This two-day boot camp is designed for Excel Power Analysts who are looking to more effectively build and manage better data reporting mechanisms. During this workshop, you’ll be introduced to a wide array of tips and techniques that will muscle up your skills in Data Crunching, Reporting, and Automation. Going beyond simple Excel tricks, you will learn to:

• Work more efficiently with powerful data crunching and spreadsheet auditing tips
• Go beyond basic analysis with advanced PivotTables techniques
• Create powerful dashboards with interactive data modeling methods
• Integrate external data sources (SQL Server, Access, Sharepoint) into your Excel reporting
• Introduce powerful new BI capabilities with PowerPivot for Excel 2010
• Implement macro-charged reporting with VBA

So go get your Excel on. Get in a pickup game at the White House basketball court. Skip a rock in the reflecting pool. Sit on Honest Abe’s lap. Get a job as a page. Some of those suggestions may be illegal, so deface monuments at your own risk.

Given a Bill Date and a Cycle Days, Kimberly wants to determine how many days in the cycle fall in Winter. For our purposes, Winter is defined as November 1st to April 30th. I put the season start dates in D1 and D2 and use this formula to find the difference.

It’s not as onerous as it might seem at first. Let’s start with the case of Bill Date after November 1, but before January 1. If we were restricted to that time period, the formula would be

Take the date in question and subtract the start of Winter. Take the smaller of that or the cycle time. Once you have that basic formula, it’s easy to expand it. There are three time frames that I care about: pre-May 1, post-Nov 1, and the time in between. If it’s after November 1, I use the formula above. If it’s after May 1, I use the formula above but use May 1 as the date. If it’s before May 1, I use November again, but the year prior. If the Cycle Days spans both dates, well, there’s trouble, so let’s call that a bug that we don’t care to fix.

You might think those days are one off. They match some sample data I was provided, so I assume it’s good to go. If you want the actual number of days from 1-Nov-2011 to 16-Nov-2011, you need to add 1 to the above formula.

I came across a very reasonable request from someone who wanted to see which entries in a list matched those in the current cell (http://answers.microsoft.com/en-us/office/forum/office_2010-excel/event/49aa9987-3cf5-4007-9f08-df076ff0beba). While the original request dealt with names, I abstracted the problem into a set of numbers. Column A in Figure 1 is one list of numbers. Column C represents a list that we want to check against column A. Selecting a cell in column C should highlight all the matches in column A.

Figure 1

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/0702 Highlight matches to current cell.shtml

Tushar Mehta