BinaryData Sample

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. Code was added to the Open event handler, OnXllOpenEx, to handle the COM event NewWorkbook:

   BOOL CBinaryDataApp::OnXllOpenEx() 
   {
       // Set an event handler to be called whenever a new
       // workbook is created
       SetComEventHandler(EVT_NEWWORKBOOK, "BinOnNewWorkbook");
       SetSinkEvents(TRUE);
       ...
   }

3. The event handler function, BinOnNewWorkbook, was added using the Function Wizard, following the signature defined in the help for CXllApp::SetComEventHandler.
4. BinOnNewWorkbook 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.

Functions used

Project files

If you are using MS Developer Studio 6, then you should open the project file BinaryData.dsp.

If you are using MS Visual Studio .NET 2002, then you should open the solution file BinaryData.sln or the project file BinaryData.vcproj.

If you are using MS Visual Studio .NET 2003, then you should open the solution file BinaryData71.sln or the project file BinaryData71.vcproj.

If you are using MS Visual Studio 2005, then you should open the solution file BinaryData8.sln or the project file BinaryData8.vcproj.

See Also

List of Sample Projects | Samples and Walkthroughs