USOO67791.

51B2

(12) United States Patent (10) Patent No.: US 6,779,151 B2

Cahill et al. (45) Date of Patent: *Aug. 17, 2004

(54) STORING OBJECTS IN A SPREADSHEET OTHER PUBLICATIONS

(75) Inventors: Jason Cahill, Carnation, WA (US); K. Ewbank, VBA insider, Developer Network Journal, No.
Jason Allen, Redmond, WA (US) 18, May–Jun. 2000, pp. 34–38.
P. Lacoude and G. Ketema, Programming Excel COM
(73) Assignee: Microsoft Corporation, Redmond, WA objects in C++, C/C++ Users Journal v18, ná, Apr. 2000, pp.
(US) 22, 24–26, 28.*
Harris, Matthew; “Sams teach yourself Microsoft Excel
(*) Notice: Subject to any disclaimer, the term of this 2000 programming in 21 days", Indianapolis, Ind. Sams
patent is extended or adjusted under 35 Publishing, 1999.OO.*
U.S.C. 154(b) by 266 days. VBA inside; K. Ewbank; Developer Network Journal No.
18, May–Jun. 2000, pp. 34–38. *
This patent biect to a t 1 dis- P. Lacoude and G. Ketema:s “Programming
9. 9. Excel COM
R ent IS Subject to a terminal dIS objects in C++”; C/C++ Users Journal v 18, n.4, Apr. 2000,
p. 22.24-6, 28.*
(21) Appl. No.: 09/755,415 (List continued on next page.)
(22) Filed: Jan. 5, 2001 Primary Examiner Shahid Alam
O O Assistant Examiner-Chongshan Chen
(65) Prior Publication Data (74) Attorney, Agent, or Firm Merchant & Gould
US 2002/009 1871 A1 Jul. 11, 2002 (57) ABSTRACT
51) Int. Cl................................................. G06F 17100
2. S. C. 715/503; 707/2; 7077. A spreadsheet application provides a rich programming and
Oa - 1- O ---- ----- ------ --- ------ --- 707703 R 707/104 modeling environment. The Spreadsheet application of the

58) Field of S h 707 2, 103 R present invention does this by recognizing a new “object'
(58) Field o ears 102.104.1504,507 51 3. , 15/50 data type in addition to the conventional spreadsheet data
/102, • us s s s f types: numbers, text, date, Boolean, and error. The object
(56) References Cited data type can be used to create an instance of an external
object and Store the object in a cell of a spreadsheet. Once
U.S. PATENT DOCUMENTS an object has been created in a spreadsheet, the object can
5.255,363 A * 10/1993 Seyler ........................ 71.5/526
be invoked or accessed from any other cell or formula in the
Spreadsheet. The spreadsheet application of the present
5,303,146 A 4/1994 Ammirato et al. .......... 715/503 invention provides Several Special object functions that
5,317,686 A 5/1994 Salas et al. ................. 715/503
5,339,410 A 8/1994 Kanai ......................... 707/100 operate to enable a user to create and invoke an object. A
5,418.902 A * 5/1995 West et al. ................. 715/503 user can directly call functions from objects that are installed
5,485,617 A 1/1996 Stutz et al. ................. 71.9/315 on a local or remote machine, by integrating an object
5,603,021 A * 2/1997 Spencer et al. ................ 707/4 function within a spreadsheet formula. Thus, the user is
5,708,827 A * 1/1998 Kaneko et al. ............. 715/503 provided with a tool for integrating outside data Services into
5,721,847 A 2/1998 Johnson ...................... 345/786 a spreadsheet and may take advantage of pre-existing
5,784,545 A * 7/1998 Anderson et al............ 707/507 objects.
5,883,623 A 3/1999 Cseri .......................... 345/866
(List continued on next page.) 21 Claims, 8 Drawing Sheets

system Memory
roM 24
BOS 28 MONTOR

RAM 25
Operating system3s

Spreadsheet 36
Component
Spreadsheet
Object 38 Magnetic Network Locai Area Network
Disk Drive Interface
external Object 39 s

42 Keyboard 4
E-
Mousa- - - -
- .
operating Spreadshee? Spreadshes 39 Spreadsheet 38
System Component Object External Object Object
External Object 39 - a
 US 6,779,151 B2
Page 2

U.S. PATENT DOCUMENTS XAct, a program for construction, automated Setup and
5,893,123 A * 4/1999 Tuinenga .................... 707/504 bookkeeping of crystallization experiments; D.E. Brodersen,
5,983.268 A * 11/1999 Freivald et al. ............. 709/218 L.B. Jenner, G.R. Andersen, and J. Nyborg; Journal of
6,034,681. A * 3/2000 Miller et al................. 345/748 Applied Crystallography, 1999, v32, 5 (Oct. 1) pp.
6,122,649 A 9/2000 Kanerva et al. ............ 71.5/516 1012-1016.
6,157,934. A 12/2000 Khan et al. ................. 715/503
6,161,107 A * 12/2000 Stern ........... ... 707/104.1 Composite tree parallelism. language Support for general
6,192,379 B1 2/2001 Bekenn ...................... 715/503 purpose parallel programming; M.C. ChuCarroll and L.L.
6,247,020 B1 * 6/2001 Minard ........... ... 707/104.1 Pollock; Journal of Programming Languages; 1997 V5 n1,
6.256,649 B1 * 7/2001 Mackinlay et al. ......... 715/503
6,298,352 B1 10/2001 Kannan et al. ............. 707/102 pp. 1-36.
6,341.288 B1 * 1/2002 Yach et al. ..... 707/103 R
6,366,934 B1 * 4/2002 Cheng et al. ............... 707/513 VBA inside; K. Ewbank; Developer Network Journal No. 18,
6,401,099 B1 * 6/2002 Koppolu et al. ... 707/103 R May–Jun. 2000, pp. 34–38.
6,427,153 B2 * 7/2002 Nelson et al. .......... 707/103 R
6,453,328 B1 * 9/2002 Schaeffer et al. ........... 707/515 Programming Excel COM objects in C++, P. Lacoude and
6,701,485 B1 * 3/2004 Igra et al. ................... 715/503 G. Ketema; C/C++ Users Journal v18, n4, Apr. 2000, pp. 22,
OTHER PUBLICATIONS 24-26, 28.
Similarity inheritance: a new model of inheritance for Analyzing Java Software by combining metricS and program
spreadsheet VPLs; R.W. Djang and M.M. Burnett; IEEE visualization; T. Systa, Yu Ping and H. Muller; Proceedings
Symposium On Visual Languages, 1998, pp. 134-141. of the Fourth European Conference on Software Mainte
Linking Framework spreadsheets; D. Paquette and K. nance and Reengineering, 2000, pp. 199-208.
Paquette; Ashton-Tate Quarterly, Oct.-Dec. 1988, pp.
59-64. * cited by examiner
U.S. Patent Aug. 17, 2004 Sheet 1 of 8 US 6,779,151 B2

}O_LINW

#7
8
U.S. Patent Aug. 17, 2004 Sheet 2 of 8 US 6,779,151 B2

ZOZ

ZOZ

ZOZ

61-I
U.S. Patent Aug. 17, 2004 Sheet 3 of 8 US 6,779,151 B2

80€
U.S. Patent Aug. 17, 2004 Sheet 4 of 8 US 6,779,151 B2

(ZI8V0)LO?JATRSE= L(I£ON8R)STV3HEO)=, LT(†O78G)I01VJAaOn)s=e

U.S. Patent Aug. 17, 2004 Sheet 6 of 8 US 6,779,151 B2

[LVOWARHTS]X

\
v

|NTEV(\NOXG]
U.S. Patent Aug. 17, 2004 Sheet 7 of 8 US 6,779,151 B2
 US 6,779,151 B2
1 2
STORING OBJECTS IN A SPREADSHEET sheet formula. Thus, the user is provided with a tool for
integrating outside data Services and functionality into a
Spreadsheet object. Moreover, the user may take advantage
FIELD OF THE INVENTION of pre-existing external objects.
The spreadsheet component makes available a new class
The present invention generally relates to electronic of object functions. This new class of object functions may
Spreadsheets. More particularly, the present invention relates be integrated into formulas (as can most spreadsheet
to Storing automation objects within the cells of a spread function). The spreadsheet component also recognizes the
sheet. “.’ operator that is the conventional means (in object
models) for separating object names and properties. This
BACKGROUND OF THE INVENTION operator enables reference to Specific object properties
Spreadsheet application users are utilizing spreadsheets in within the external object. Advantageously, the Spreadsheet
more and more Sophisticated ways. While spreadsheets were component of the present invention provides a Sophisticated
programming environment wherein a spreadsheet object can
once primarily used to facilitate financial and technical be enhanced with the ability to invoke the functionality of
calculations and analyses, users are demanding the ability to 15
external objects through a component management System.
create and invoke user-defined functions within spread In one aspect of the present invention a method is pro
sheets. Often, Spreadsheet applications are used to create Vided for embedding an external object in a spreadsheet cell
functioning spreadsheet objects that can be accessed over of a spreadsheet object. The method receives an object
the Internet.
function and an external object identifier as input. The
On a basic level, a spreadsheet is ultimately a program method parses the object function to determine whether the
ming tool. The cells of a Spreadsheet can be used to Store object function is valid and has a proper Syntax. The method
both data and formulas for manipulating data. However, also accesses the external object identified by the external
unlike more Sophisticated programming environments Such object identifier, to determine whether the external object is
as BASIC, C++, and Pascal, conventional Spreadsheets functional. If the object function is valid and has a proper
provide no means to implement functional components 25
Syntax, then the method Stores an embedded object in the
(automation objects) residing outside of the spreadsheet. Spreadsheet cell. If the external object is functional, then the
With the ever-expanding popularity of object-oriented pro method recalculates the Spreadsheet cell.
gramming models, valuable programming tools have been In another aspect of the present invention, a spreadsheet
made available in the form of discrete, Stand-alone automa component is provided having an object function for invok
tion objects. With the advent of component management ing an external object. The Spreadsheet component has a
systems, such as the Component Object Model, marketed by formula parser component and a recalculation component.
Microsoft Corporation of Redmond, Wash., such objects are The parser component functions to compare the object
generally readily accessible to other objects through the function to a function library to determine whether the
component management System. object function conforms to a predefined Syntax contained in
35
Unfortunately, conventional spreadsheet applications a library entry associated with the object function. The
have not provided access to external objects. As a result, parser component also functions to determine whether the
conventional spreadsheets have Served a limited role as a object function properly identifies the external object. If the
programming tool, as compared to the more Sophisticated parser component determines that the object function con
programming environments mentioned above. Therefore, 40 forms to the predefined Syntax and properly identifies the
there is a need in the art for a spreadsheet component that external object, then the recalculation component functions
can be used to create a spreadsheet object that can acceSS to invoke the external object identified by the object func
external objects through a component management System. tion.
The spreadsheet object should be able to process formulas The various aspects of the present invention may be more
that invoke external objects and Store objects in the cells of 45 clearly understood and appreciated from a review of the
the spreadsheet object. The spreadsheet object also should following detailed description of the disclosed embodiments
proceSS formulas invoking the external object after the and by reference to the drawings and claims.
object has been Stored in a spreadsheet cell and should
recognize an object as a new data type. BRIEF DESCRIPTION OF THE DRAWINGS
50
FIG. 1 is a block diagram illustrating an exemplary
SUMMARY OF THE INVENTION operating environment for implementing of the present
The present invention is a spreadsheet component that invention.
provides a rich programming and modeling environment. FIG. 2 is a block diagram illustrating Some of the primary
The spreadsheet component of the present invention does components of an exemplary embodiment of the present
this by recognizing a new “object' data type in addition to 55 invention, in the context of an exemplary component man
the conventional spreadsheet data types: numbers, text, date, agement System.
Boolean, and error. The object data type enables a spread FIG. 3 is a block diagram depicting a spreadsheet object
sheet object to have an embedded instance of an external and a spreadsheet component that is an exemplary embodi
object that exists outside of the spreadsheet component. ment of the present invention.
Once an object has been created within a spreadsheet object, 60 FIG. 4 is a block diagram depicting an exemplary Spread
the object can be invoked from any other cell or formula in sheet object having an embedded object and object functions
the spreadsheet object. invoking the object.
The spreadsheet component of the present invention pro FIG. 5 depicts an exemplary embodiment of the present
vides Several Special object functions that operate to enable invention utilized in the context of a web browser.
a user to create and invoke an object. A user can directly call 65 FIG. 6 depicts an exemplary embodiment of the present
functions from objects that are installed on a local or remote invention in which an extensible market up language (XML)
machine, by integrating an object function within a spread object is Stored in a spreadsheet cell.
 US 6,779,151 B2
3 4
FIG. 7 depicts an alternative embodiment of the present as a CD-ROM or other optical media. Hard disk drive 27,
invention in which an image function Stores an image object magnetic disk drive 28, and optical disk drive 30 are
in a spreadsheet cell. connected to system bus 23 by a hard disk drive interface 32,
FIG. 8 is a flowchart depicting an exemplary method for a magnetic disk drive interface 33, and an optical disk drive
parsing a formula and processing an object function con interface 34, respectively. Although the exemplary environ
tained therein. ment described herein employs hard disk 27, removable
magnetic disk 29, and removable optical disk 31, it should
DETAILED DESCRIPTION OF EXEMPLARY be appreciated by those skilled in the art that other types of
EMBODIMENTS computer readable media which can Store data that is
accessible by a computer, Such as magnetic cassettes, flash
The present invention is a Spreadsheet application pro memory cards, digital Video disks, Bernoulli cartridges,
gram (spreadsheet component) that provides a rich program RAMs, ROMs, and the like, may also be used in the
ming and modeling environment. The spreadsheet compo exemplary operating environment. The drives and their
nent of the present invention does this by recognizing a new asSociated computer readable media provide nonvolatile
“object' data type in addition to the conventional data types: 15 Storage of computer-executable instructions, data structures,
numbers, text, date, Boolean, and error. The object data type program modules, and other data for personal computer 20.
enables a spreadsheet object to have an embedded instance For example, one or more data files 60 may be stored in the
of an external object that exists outside of the Spreadsheet RAM 25 and/or hard drive 27 of the personal computer 20.
component. Once an object has been created within a A number of program modules may be Stored on hard disk
Spreadsheet object, the object can be invoked from any other 27, magnetic disk 29, optical disk 31, ROM 24, or RAM 25,
cell or formula in the spreadsheet object. including an operating System 35, a spreadsheet component
The spreadsheet component of the present invention pro 36, a spreadsheet object 38, and an external object 39.
vides Several Special object functions that operate to enable Program modules include routines, Sub-routines, programs,
a user to create and invoke an object. A user can directly call objects, components, data structures, etc., which perform
functions from objects that are installed on a local or remote 25 particular tasks or implement particular abstract data types.
machine, by integrating an object function within a spread Aspects of the present invention may be implemented in the
sheet formula. Thus, the user is provided with a tool for context of a spreadsheet object 38 that can be created or
integrating outside data Services and functionality into a modified by a spreadsheet component 36. The spreadsheet
Spreadsheet object. Moreover, the user may take advantage component 36 generally comprises computer-executable
of pre-existing external objects. instructions for creating or modifying a spreadsheet object.
The spreadsheet component makes available a new class The spreadsheet object 38 generally comprises computer
of object functions. This new class of object functions may executable instructions constituting a spreadsheet modeling
be integrated into formulas (as can most spreadsheet environment (i.e., a spreadsheet) that can access and invoke
function). The spreadsheet component also recognizes the an external object 39. The external object 39 is typically
“.’ operator that is the conventional means used by object 35 maintained within the context of a component management
models to Separate object names and object properties. This System (not shown), which is generally functions to provide
operator enables reference to specific object properties a structure for brokering between objects that are designed
within the external object. Advantageously, the Spreadsheet to communicate with one another.
component of the present invention provides a Sophisticated A user may enter commands and information into per
programming environment wherein a spreadsheet object can 40 Sonal computer 20 through input devices, Such as a keyboard
be enhanced with the ability to invoke the functionality of 40 and a pointing device 42. Other input devices (not shown)
external objects through a component management System. may include a microphone, joystick, game pad, Satellite
Exemplary embodiments of the present invention will dish, Scanner, or the like. These and other input devices are
hereinafter be described with reference to the drawing, in often connected to processing unit 22 through a Serial port
which like numerals represent like elements throughout the 45 interface 46 that is coupled to the system bus 23, but may be
Several figures. FIG. 1 illustrates an exemplary operating connected by other interfaces, Such as a parallel port, game
environment for implementation of the present invention. port, a universal serial bus (USB), or the like. A display
The exemplary operating environment includes a general device 47 may also be connected to system bus 23 via an
purpose computing device in the form of a conventional interface, Such as a Video adapter 48. In addition to the
personal computer 20. Generally, a personal computer 20 50 monitor, personal computers typically include other periph
includes a processing unit 21, a System memory 22, and a eral output devices (not shown), Such as Speakers and
System buS 23 that couples various System components printers.
including the System memory 22 to processing unit 21. The personal computer 20 may operate in a networked
System bus 23 may be any of several types of bus structures environment using logical connections to one or more
including a memory bus or memory controller, a peripheral 55 remote computers 49. Remote computer 49 may be another
bus, and a local bus using any of a variety of bus architec personal computer, a Server, a client, a router, a network PC,
tures. The System memory includes a read only memory a peer device, or other common network node. While a
(ROM) 24 and random access memory (RAM) 25. A basic remote computer 49 typically includes many or all of the
input/output System (BIOS) 26, containing the basic routines elements described above relative to the personal computer
that help to transfer information between elements within 60 20, only a memory storage device 50 has been illustrated in
personal computer 20, Such as during Start-up, is Stored in the figure. The logical connections depicted in the figure
ROM 24. include a local area network (LAN) 51 and a wide area
Personal computer 20 further includes a hard disk drive network (WAN) 52. Such networking environments are
27 for reading from and writing to a hard disk, not shown, commonplace in offices, enterprise-wide computer
a magnetic disk drive 28 for reading from or writing to a 65 networks, intranets, and the Internet.
removable magnetic disk 29, and an optical disk drive 30 for When used in a LAN networking environment, the per
reading from or writing to a removable optical disk 31 Such Sonal computer 20 is often connected to the local area
 US 6,779,151 B2
S 6
network 51 through a network interface or adapter 53. When will appreciate that the present invention can be imple
used in a WAN networking environment, the personal com mented in various kinds of spreadsheets, regardless of the
puter 20 typically includes a modem 54 or other means for exact context. For the purposes of this disclosure, the term
establishing communications over WAN 52, such as the Spreadsheet component is used to mean both desktop-type
Internet. Modem 54, which may be internal or external, is Spreadsheet application programs and spreadsheet develop
connected to system bus 23 via serial port interface 46. In a ment tools.
networked environment, program modules depicted relative Similarly, the spreadsheet object 306 can be a stand-alone
to personal computer 20, or portions thereof, may be Stored Spreadsheet document created by a desktop spreadsheet
in the remote memory storage device 50. It will be appre application program or can be a spreadsheet object that is
ciated that the network connections shown are exemplary
and other means of establishing a communications link embedded within another document (e.g., a web page). In
between the computers may be used. either case, the spreadsheet object contains cells that are
Moreover, those skilled in the art will appreciate that the broken into columns and rows. The spreadsheet object 306
of FIG. 3 has been broken into columns 308 and 310 and
present invention may be implemented in other computer rows 312–318. For simplicity, a cell in a spreadsheet object
System configurations, including hand-held devices, multi 15 is conventionally referred to by its column and row name,
processor Systems, microprocessor based or programmable this convention will be used in this disclosure.
consumer electronics, network person computers,
minicomputers, mainframe computers, and the like. The As depicted in FIG. 3, a formula has been stored in cell
invention may also be practiced in distributed computing A1. When any formula is stored in any cell in the spread
environments, where tasks are performed by remote pro sheet object 306, the spreadsheet component 300 must
cessing devices that are linked through a communications interpret the contents of the cell. The Spreadsheet component
network. In a distributed computing environment, program 300 can include a parser component 302 and a recalculation
modules may be located in both local and remote memory component 304. The parser component 302 examines the
Storage devices. contents of each cell in the spreadsheet object 306. The
FIG. 2 depicts an exemplary component management 25
parser component 302 examines every aspect of the formula
system 200. The component management system (CMS) (or data) to verify its syntax. The parser component 302
200 provides a means for brokering between objects 202 that includes a function library 320 and compares each function
are designed to communicate with one another. The objects in each formula of the spreadsheet object 306 to the function
202 can be written in various programming environments, library 320 to verify the functions syntax. The parser
Such as C++, and Java. At a basic level, a component component compares the CREATEOBJECT function to the
management System simply provides an agreed upon bro function library to determine whether the function is a valid
kering interface 204 through which the objects 202 can Spreadsheet function. In addition, the function library
communicate by adhering to a predetermined communica includes a template for each function that allows the parser
tions protocol. Microsoft's Component Object Model pro component 302 to determine whether the proper syntax has
been used.
vides a brokering interface, “Interface', that performs this 35
function. In addition to the basic brokering interface 204 The parser component also must determine the data type
provided by the component management System 200, a of a particular cell. Thus, the parser component 302 of an
Second layer of interface can be implemented between the exemplary embodiment of the present invention may rec
objects 202 and the component management system 200. An ognize data types in addition to those recognized by the
automation interface 206 can be used to enable the objects 40 conventional parser component. Specifically, the parser
202 to investigate the capabilities of another object. That is, component 302 should recognize an embedded object as a
an object can communicate with another object through the data type.
automation interface 206 to determine the other object's After the parser component has recognized the data type
functionality and/or capabilities. Once this information has and Syntax of a cell, the recalculation component 304 of a
been gathered from the other object, the investigating object 45 spreadsheet component 300 must then refresh the spread
can invoke the other object. sheet object 306 by recalculating the values stored in the
In an exemplary embodiment of the present invention, an cells of the spreadsheet object. While the recalculation
object can be embedded (stored) in a cell of a spreadsheet component 304 and the parser component 302 are compo
component. Once the object has been embedded in the nents that are commonly found in conventional
Spreadsheet, a spreadsheet user can write formulas against 50 Spreadsheets, the recalculation component and the parser
the object, thereby invoking the object. This embedding is component of an exemplary embodiment of the present
made possible by the expansion of the spreadsheet compo invention have been configured to enable communication
nent's data types to include an “object' data type. between the spreadsheet object 306 and other objects 202,
FIG. 3 depicts an exemplary spreadsheet component 300 via the component management System 200.
and a conventional spreadsheet object 306. The spreadsheet 55 Referring again to FIG. 3, Spreadsheet cell A1 contains
component 300 may be similar to a conventional desk the object formula “=CREATE OBJECT (progid,
top-based spreadsheet application program, Such as servername)". The CREATEOBJECT function can be used
Microsoft Excel, marketed by Microsoft Corporation of to embed an object in the spreadsheet object 306. The parser
Redmond, Wash. Alternatively, the spreadsheet component component 302 of an exemplary embodiment of the present
300 may be more of a development tool, such as Microsoft 60 invention is configured to read the CREATEOBJECT func
Spreadsheet Web Component, marketed by Microsoft Cor tion. In the embodiment of the CREATEOBJECT function
poration of Redmond, Wash. The Spreadsheet Web Com used in the formula stored in cell A1 of the spreadsheet
ponent provides a means for creating components to enable object 306, two arguments are used to identify the location
Spreadsheet-like functionality in other applications, Such as of the external object of which the embedded object will be
a web browser. Typically, in the context of a web browser, 65 an instance. Of course, other arguments could be used to
the Spreadsheet web component can be used to embed a identify an external object to embed an object in a spread
Spreadsheet object in a web page. Those skilled in the art sheet object.
 US 6,779,151 B2
7 8
The recalculation component 304 must also be configured OBJECT function enables the OBJECT function to refer to
to recognize the function that has been read by the parser a particular property of the object. In the example of cell C1,
component 302 and to perform calculations based on the the OBJECT function refers to the “LastPrice” property of
parsed formula. When a user enters the CREATEOBJECT the object in A1. As described above, the object in cell A1
formula depicted in cell A1 of FIG. 3, the recalculation 5 is the marketdata object. Thus, the LastPrice property of the
component 304 will find the external object whose name and marketdata object can be determined and Stored in cell C1.
location are identified by the “progid' and “servername’ The OBJECT function utilizes the period or dot operator
arguments and will inquire as to the external object's type. (“.”) to indicate that an object property name follows. This
The objects type can be identified through the automation is consistent with the normal convention of identifying
interface 206 discussed above in connection with FIG. 2. properties within an object in a conventional component
Once the objects type has been determined, the recalcula management System.
tion component 304 can display a string in cell A1 to identify Object properties can be more Specifically identified by
the object. Typically, the String takes the following form: using a parenthetical property identifier. In the example of
Object Objectname). Thus, the formula entered by a user cell C1, the LastPrice property is more specifically identified
can be represented in the spreadsheet in a more user-friendly 15 by the parenthetical property identifier “B1”. The property
way that provides the user with Some feedback as to the identifier “B1” refers to cell B1 of the spreadsheet object
identification of the object. 400. Thus, the formula of cell C1 invokes the object in cell
In an alternative embodiment, the recalculation compo B1 and determines the LastPrice property for that object.
nent can inquire as to whether the external object includes This action effectively returns the LastPrice for the property
information as to how the object can be displayed in the identifier in B1. In this example, the last stock price for the
spreadsheet object 306. For example, the external object MSFT stock is retrieved from the object marketdata and
may include code to create a graphical representation of the stored in cell C1. As described above, in connection with
object in cell A1. Alternatively, the object could include a FIG. 3, the notification interface can be used to update the
representation property with a reference or pointer to an value Stored in cell C1, when the object marketdata changes.
image file that can be embedded into cell A1 to graphically 25
Cells C2-C5 perform similar functions. Each parentheti
represent the object. cal property identifier in cells C2-C5 contain references to
The recalculation component 304 is responsible for caus corresponding cells in column B. Of course, the Stock
ing the spreadsheet object 306 to recalculate the values of its Symbol in column B can be changed to trigger an updated
cells. The recalculation component 304 is normally config price for the new symbol to be displayed in column C.
ured to trigger a recalculation anytime that a cell within a Similarly, the parenthetical property identifier in column C
spreadsheet object 306 is changed. However, the embedding could be modified to refer to a different cell containing a
of objects within the spreadsheet object 306, requires the different stock identifier. The object formulas in cells C1-C5
recalculation component 304 of an exemplary embodiment could also be modified to identify a different object all
of the present invention to trigger recalculation in other 35
together than that embedded in cell A1. Thus, the extension
circumstances as well. The recalculation component 304 of data types supported in spreadsheet object 400 to include
also must trigger a recalculation when the external object external objects provides a powerful means for embedding
changes. Sophisticated object functionality within a spreadsheet
By using a Standard notification interface, Such as the object.
IPropertyNotify interface used in Microsoft's Component 40 FIG. 5 depicts an exemplary embodiment of the present
Object Model, the component management system 200 can invention utilized in the context of a web browser, Such as
be used to notify the spreadsheet object 306 whenever the Internet Explorer, marketed by Microsoft Corporation of
external object is changed. To do this, the recalculation Redmond, Wash. In FIG. 5 a web page 500 is depicted
component 304 must inform the external object through the having an image element 502, a spreadsheet component 506
notification interface that the spreadsheet object 306 desires 45
and a text box element 504. The name of the text box is
to monitor the external object. If the external object is “TextBox1'. The spreadsheet object 506 has a column A and
changed, then the notification interface will notify the rows 1-3. The spreadsheet object 506 is hosted in a web
spreadsheet object 306 of that change. When such a change browser (not shown). That is, the spreadsheet object 506, has
has been identified, the recalculation component 304 can been embedded in a document (web page 500) that is being
trigger a recalculation of the spreadsheet object 306. Thus, 50 displayed by the web browser.
the spreadsheet object can maintain current information Another object function, the HOST function, has been
about the external object and modify the values of its cells stored in a formula in cell A1 of spreadsheet object 506. The
accordingly. HOST object operates to return the container object that is
FIG. 4 depicts an exemplary spreadsheet object 400 hosting the spreadsheet. In the example of FIG. 5, the
including columns A-D and rows 1-6. The CREATEOB 55 container object would be the web browser's document
JECT function has been stored in cell A1. In this example, object. In the context of a Visual Basic application, the
the external object is identified by the object name “mar container object would be a Form object.
ketdata” and by object location "//investor”. Thus, the create In a web browser, such as Microsoft's Internet Explorer,
object function operates to embed the marketdata object in the IHTML container object is returned by the HOST
cell A1 of spreadsheet object 400. 60 function. The IHTML container object provides access to all
A second object function “OBJECT can also be added to of the properties of the web page 500 on which the spread
the function library 320 of the parser component 302. The sheet is installed. AS described above, in connection with
OBJECT function is a reference object function in that it can FIG. 4, the “.” operator indicates that a property name
be used to refer to the object embedded in the spreadsheet follows. The “TextBox1' property name returns the textbox
object 400. In the example of FIG. 4, cell C1 contains the 65 property of the hosting web page. Of course, the textbox 504
formula “=OBJECT(A1). LastPrice(B1)”. Cell C1, thus, can have multiple properties. The “value” property name
refers to the object embedded in cell A1. The syntax of the identifies the property of the textbox 504 that will be
 US 6,779,151 B2
10
returned. In this case, the value property of TextBox1 is a method branches to step 808 and an error message is
string, “tractor sale'. Spreadsheet object 508 is simply the generated. The method then proceeds to step 810 and ends.
graphical representation of spreadsheet object 506 as it If, at decision block 806, a determination is made that the
would appear to a user after the formula of cell A1 has been function is a valid function, the method branches to decision
evaluated. The value property of TextBox1, “tractor sale” block 812. At decision block 812, a determination is made
has been returned and is displayed in cell A1 of the spread as to whether the function has a valid syntax. If the function
sheet object 508. Thus, the host function provides a way for does not have a valid Syntax, then the method branches to
a spreadsheet user to return a container object in which the step 808, via connectors A. At step 808, an error message is
Spreadsheet component is hosted. generated. The method then proceeds to step 810 and ends.
FIG. 6 depicts an exemplary embodiment of the present Returning to decision block 812, if a determination is
invention in which an extensible market up language (XML) made that the formula has a valid Syntax, then the method
object is Stored in a spreadsheet cell. In spreadsheet object branches to decision block 814. At decision block 814, a
determination is made as to whether the function is a
600, cell A1 includes a formula having an XMLDOM CREATE OBJECT function. If the method is not a CRE
function. The XMLDOM function returns an XML object ATEOBJECT function, then the method branches to deci
602. The XML object 602 is actually stored in cell A1. The 15
Sion block 816. At decision block 816, a determination is
XMLDOM function identifies an XML document by refer made as to whether the function is an OBJECT function. If
ence to a document location. In this case, the XML docu the function is not an OBJECT function, then the method
ment is a Uniform Resource Locator (URL) that identifies a branches to decision block 818. At decision block 818, a
document on the worldwide web: “http://www.w3.org/ determination is made as to whether the function is a HOST
XMLSchema”. In Microsoft's Component Object Model, function. If the function is not a HOST function, then the
the XMLDOM function will return and MSXML object and method branches to decision block 820. At decision block
Store the object in the proper spreadsheet cell. 820 a determination is made as to whether the function is an
Spreadsheet object 602 is simply the spreadsheet object XMLDOM function. If the function is not an XMLDOM
600 updated to depict the graphical representation of the function, then the method branches to step 810 and ends.
25
XMLDOM object in cell A1. After the formula shown in cell Returning to decision block 814, if a determination is
A1 of spreadsheet object 600 has been entered, the graphical made that the function is a CREATEOBJECT function then
representation of that formula will be as shown in cell A1 of the method branches to step 822. At step 822, the object
Spreadsheet object 602. Specifically, the graphical represen identified by the CREATEOBJECT function is retrieved and
tation can include the word “object” to inform the user that stored in the spreadsheet cell containing the CREATEOB
an object is Stored in cell A1 and can include the name of the JECT function. The method then proceeds to step 830 and
XML object 602 found in the XML document 604. the cell is refreshed to visually represent the object, either by
FIG. 7 depicts an alternative embodiment of the present object name or graphically, as described above in connection
invention in which an image function Stores an image object with FIGS. 3 and 7. The method then proceeds to step 832
in a spreadsheet cell. In the example of FIG. 7, the image 35
and ends.
function has been Stored in cell A1 of Spreadsheet object Returning to decision block 816, if the function is an
700. The image function includes a single argument that OBJECT function, then the method branches to step 824. At
identifies an image object by the image object's name and step 824, the embedded object that is identified by the
location. In this case, the image's name and location are OBJECT function (e.g. by a cell reference), is accessed and
identified in an URL, “http://www.msn.com/tractor.gif". 40 an object property identified by the OBJECT function is
Spreadsheet object 702 is a visual representation of the returned. The object property is Stored in the cell containing
spreadsheet object 700. After the user has input the formula the OBJECT function. The method then proceeds to step 830
in A1 of spreadsheet object 700, and the image object has and the cell is refreshed to visually represent the object,
been retrieved through the component management System, either by object name or graphically, as described above in
the image object can be graphically depicted as shown in 45 connection with FIGS. 3 and 7. The method then branches
spreadsheet object 702. As described above, in connection to step 832 and ends.
with FIG. 3, an external object can also be graphically Returning to decision block 818, if a determination is
represented by reference to an image property of the object. made that the function is a HOST function, then the method
FIG. 8 depicts an exemplary method for parsing a formula branches to step 826. At step 826, a container object of the
and processing an object function contained therein. A 50 spreadsheet components HOST is created. The container
Spreadsheet object may be parsed on a cell-by-cell basis object is stored in the cell containing the HOST function.
(e.g., at startup) or may be parsed whenever a cell is changed The method then proceeds to step 830 and the cell is
in the spreadsheet object. Those skilled in the art will refreshed to represent the object, either by object name or
appreciate that the exemplary method described in FIG. 8 graphically, as described above in connection with FIGS. 3
can be used to process object functions, regardless of the 55 and 7. The method then branches to step 832 and ends.
parsing technique used. Returning now to decision block 820, if a determination
The method of FIG. 8 begins at step 800 and proceeds to is made that the function is a XMLDOM function, then the
decision block 802. At decision block 802, a determination method branches to step 828. At step 828, an XML object is
is made as to whether the formula contains an object created and stored in the cell containing the XMLDOM
function. If the formula does not contain an object function, 60 function. The XML document that is identified in the XML
the method branches to step 810 and ends. If the formula DOM function is also loaded into the XML object. The
contains an object function, the method branches to step 804 method then proceeds to step 830 and the cell is refreshed to
and the object function is compared to a function library. represent the object, either by object name or graphically, as
The method then proceeds to decision block 806 and a described above in connection with FIGS. 3 and 7. The
determination is made as to whether the function is a valid 65 method then branches to step 832 and ends.
function. A function is typically valid if it is contained in the Although the present invention has been described in
function library. If the function is not a valid function, the connection with various exemplary embodiments, those of
 US 6,779,151 B2
11 12
ordinary skill in the art will understand that many modifi 11. A computer System having a graphical user interface
cations can be made thereto within the Scope of the claims including a display and a user interface Selection device, the
that follow. Accordingly, it is not intended that the Scope of computer System comprising:
the invention in any way be limited by the above description, a spreadsheet component receiving an object function as
but instead be determined entirely by reference to the claims an input into a cell of a spreadsheet object to embed an
that follow.
What is claimed is: external object in the spreadsheet cell, wherein the
1. A method for embedding an external object in a object function includes an external object identifier for
Spreadsheet cell of a spreadsheet object, the method com invoking an external object, the spreadsheet component
prising the Steps of 1O
comprising:
receiving an object function as an input into a spreadsheet a formula parser component operative to compare the
cell of a spreadsheet object to embed an external object object function to a function library to determine
in the spreadsheet cell, wherein the object function whether the object function conforms to a predefined
includes an external object identifier; Syntax contained in a library entry associated with the
parsing the object function to determine whether the 15 object function and operative to determine whether the
object function is valid and has a proper Syntax; object function properly identifies a data type of the
accessing the external object identified by the external external object in order to Store an embedded object in
object identifier, to determine whether the external the cell of the Spreadsheet object wherein at least one
object is functional; data type comprises an object data type; and
Storing an embedded object in the spreadsheet, cell in a recalculation component operative to invoke the exter
response to receiving the object function as the input nal object identified by the object function, in response
into the Spreadsheet cell and a determination that the to a determination that the object function conforms to
object function is valid and has a proper Syntax, and the predefined Syntax and properly identifies the exter
recalculating the spreadsheet cell, in response to a deter 25 nal object.
mination that the external object is functional. 12. The spreadsheet component of claim 11, wherein
2. A computer readable medium having Stored thereon invoking the external object comprises Storing the embedded
computer-executable instructions for performing the method object in the cell of the Spreadsheet object.
of claim 1. 13. The spreadsheet component of claim 12, wherein the
3. The method of claim 1, wherein the object function is Spreadsheet component further comprises a reference object
a CREATEOBJECT function. function identifying the embedded object and a property of
4. The method of claim 1, wherein the object function is the embedded object.
a HOST function. 14. The spreadsheet component of claim 13, wherein the
5. The method of claim 1, wherein the object function is Spreadsheet component is operative to Store a value of the
a XMLDOM function. 35 property in association with a Second cell of the Spreadsheet
6. The method of claim 1 further comprising the steps of: object.
receiving as input a reference object function identifying 15. The spreadsheet component of claim 11, wherein
invoking the external object comprises retrieving informa
the embedded object and a property of the embedded tion from the external object and Storing the information in
object; the cell of the Spreadsheet object.
40
Storing the reference object function in a Second Spread 16. The spreadsheet component of claim 11, wherein the
sheet cell; and object function is a CREATEOBJECT function.
determining the value of a property of the embedded 17. The spreadsheet component of claim 11, wherein the
object and Storing the value of the property in associa object function is a HOST function.
tion with the Second spreadsheet cell. 45 18. The spreadsheet component of claim 11, wherein the
7. The method of claim 1, wherein the step of accessing object function is an XMLDOM function.
the external object is accomplished by use of a component 19. The spreadsheet component of claim 11, wherein
management System. invoking the external object is accomplished by use of a
8. The method of claim 7, wherein the component man component management System.
agement System has a brokering interface and an automation 50 20. The spreadsheet component of claim 19, wherein the
interface. component management System has a brokering interface
9. The method of claim 1 further comprising the step of and an automation interface.
registering the spreadsheet object with a notification inter 21. The spreadsheet component of claim 11, wherein
face to receive a notification when the external object is invoking the external object compriseS registering the
modified. 55 Spreadsheet object with a notification interface to receive a
10. The method of claim 9, further comprising the step of notification when the external object is modified.
recalculating the spreadsheet cell in response to receiving a
notification that the external object has been modified. k k k k k