Demonstrates the use of BinaryName ranges to store binary data in an invisible named range in a worksheet

Overview

This sample add-in demonstrates how to store your own binary data in a worksheet using hidden named ranges that are accessible only to the programmer.

Features

The sample displays the following features:

• Saves the original book and sheet name and creation time in hidden BinaryName ranges that are not accessible to the user.
• Creates BinaryName ranges in every new workbook, by setting an event handler for the NewWorkbook event.
• Displays a menu of actions to let the user set and view the stored data.

Implementation

The following steps were significant in creating and completing the application.

1. The project was created with the Add a Menu option switched on, so that the code to display a menu was added to the project.

2. An observer class MyNewWorkbookObserver, derived from CXlNewWorkbookEventStaticObserver, was created, and an instance of it was placed within the application class.

   CopyC++

   class MyNewWorkbookObserver : public CXlNewWorkbookEventStaticObserver
   {
       protected:
       virtual void Update(CXlWorkbookEventArgs* e);
   };
   MyNewWorkbookObserver m_onNewWorkbook;

3. The observer class's virtual function Update was implemented. This function is called whenever a new workbook is opened.

4. The Update function uses CXllApp::DefineBinaryName to add numeric data (the creation time) to the front sheet of the workbook and to add string data (the original sheet name - e.g. [book1]sheet1) to every sheet in the new workbook.
5. Various command macros display or edit the values of the data stored in the BinaryName ranges of the active worksheet, using CXllApp::DefineBinaryName and CXllApp::GetBinaryName. These can all be triggered by the add-in's menu.

Worksheet functions

The add-in also contains 2 worksheet functions, BinGetOriginalName and BinGetOriginalName2. These two functions call CXllApp::GetBinaryName in an attempt to retrieve the contents of the BinaryName "OriginalName". Both functions fail in different ways.

BinGetOriginalName calls CXllApp::GetBinaryName without passing a workbook or sheet name. Consequently, the function looks for the value in the active sheet of the active workbook, rather than in the worksheet containing the cell which called the function. This behavior means that the function will return arbitrarily different values, depending on the state of Excel at the time, making it it pretty much useless.

The second function, BinGetOriginalName2, passes a workbook and sheet name to CXllApp::GetBinaryName. This call usually fails, because it requires Excel to activate a different book and/or sheet, which is illegal during a worksheet function.

These notes should make the developer wary of using BinaryName data within worksheet functions.

Excel 2007

There is a known problem with Excel 2007 as of SP0. Binary names can only be created at workbook level, not in a worksheet. If a binary name with the same name exists on another sheet, then a new binary name saved to a different sheet will overwrite it, regardless of which sheet is active when the binary name is saved.

Classes and functions used

CXllApp::DefineBinaryName | CXllApp::GetBinaryName | CXlMacros::WorkbookGetWorksheetNames | CXllApp::WinMessageBox | CXlFuncs::Text | CXlOper::GetCaller | CXlOper::GetActiveCell | CXlOper::Coerce | CXlOper::ChangeType | CXlOper::ToString | CXlOper::Ret

Sample project

Each sample project is located in a sub-directory of the Samples directory of the XLL+ installation. To use the sample project, open the solution file BinaryData.sln or the project file BinaryData.vcproj.

You can enable debugging under Excel by using the Setup Debugging command in the XLL+ ToolWindow.

When delivered, the help files are excluded from the build. You can enable the help build by selecting the files BinaryData.help.xml and BinaryData.chm in the Solution Explorer, and using the right-click menu to view Properties. Select the page "Configuration Properties/General" and set the "Excluded from build" property to "No". See Generating help in the User Guide for more information.

See Also

List of Sample Projects