This note documents a somewhat creative – and I suspect an unintended – way of using a slicer. A slicer is a control element introduced with Excel 2010. It is a large easy-to-use control to filter the results of one PivotTable or even multiple PivotTables. I like the UI look-and-feel of a Slicer and decided to explore it as a replacement for a data validation drop down list that has traditionally served as a selector.

So, instead of

I wanted to use

This also fit in very well with two other slicers I was already using to analyze the data.

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/0311%20slicer%20or%20data%20validation.shtml

Tushar Mehta

A contest judged by a group from mrexcel.com and Microsoft focused on visualizing data in Excel with PowerPivot where the data came from the Azure cloud data service. The details are at http://www.mrexcel.com/Challenge2011/challenge_52011.html.

I won one of the 2 prizes based on my three contest submissions (http://www.mrexcel.com/Challenge2011/challenge_52011_win.html). This post summarizes the three entries. My plan is to discuss each in detail in separate follow up posts and hopefully include the associated Excel file so that others can explore each approach by themselves.

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/data_visualization/10%20Dashboards.shtml

Tushar Mehta

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

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

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

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

This was inspired by Dick Kusleika’s post Masking Passwords (http://www.dailydoseofexcel.com/archives/2011/07/12/masking-passwords/). It’s an enhanced version of DK’s approach in the sense that the same userform module supports both entering a new password as well as entering a password for confirmation. There are other enhancements. The user can choose to either mask or unmask the password. The developer has more choices about what is and is not allowed in the password.

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/1061%20Password%20Userform%20module.shtml

Tushar Mehta