User defined spreadsheet functions

• US 20040103366A1
• Filed: 11/26/2002
• Published: 05/27/2004
• Est. Priority Date: 11/26/2002
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Blackwell, Alan Frank, Peyton-Jones, Simon, Burnett, Margaret Myers
• Granted Patent: US 7,266,763 B2
• Time in Patent Office: Days
• US Class Current: 715/503
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174
• CPC Class Codes: Y10S 707/99931 Database or file accessing

Abstract

User-defined function capabilities in a spreadsheet application allow users to create new named functions that are defined using the spreadsheet formula paradigm. A user may define spreadsheet functions from formulae entered in a worksheet or function sheet of a spreadsheet application. The formula interface is well known to spreadsheet users; therefore, expanding the formula feature to provide encapsulated functions that can be imported, exported, customized, and compiled supplies new power and flexibility to users who do not wish to learn a programming language. Furthermore, the user-defined functions in spreadsheets represent vectors and matrices as “first class” values, similar to Boolean values, numbers, and strings.

Claims

1. A computer program product encoding a computer program for executing on a computer system a computer process for creating a user-defined function in a spreadsheet application, the user-defined function being callable by a calling label in the spreadsheet application, the computer process comprising:

receiving a formula in the spreadsheet application;

defining an interface identifying the calling label, an input parameter and a result parameter of the user-defined function;

transferring the formula to a function sheet of the spreadsheet application to define at least a portion of a function body, wherein the function body operates on an argument corresponding to the input parameter and generates a result corresponding to the result parameter; and

storing the interface in association with the function body to create the user-defined function.

2. The computer program product of claim 1 wherein the computer process further comprises:

receiving user input modifying the function body to provide customized operation that differs from operation of the formula.

3. The computer program product of claim 1 wherein the receiving operation comprises:

receiving the formula in a calling cell of a worksheet.

4. The computer program product of claim 3 wherein the computer process further comprises:

replacing the formula in the calling cell of the worksheet with a call to the user-defined function.

5. The computer program product of claim 1 wherein the input parameter represents a matrix value.

6. The computer program product of claim 1 wherein the transferring operation comprises:

creating the function sheet; and

copying the formula into the function sheet.

7. The computer program product of claim 1 wherein the operation of transferring the formula to a function sheet comprises:

revising the formula in the function sheet to reference an input argument cell of the function sheet, wherein the input argument cell is associated with the input parameter.

8. The computer program product of claim 1 wherein the operation of defining the interface comprises:

designating a result cell of the function sheet.

9. The computer program product of claim 1 wherein the operation of defining the interface comprises:

designating at least one input argument cell of the function sheet.

10. The computer program product of claim 9 wherein the operation of defining the interface further comprises:

inserting a reference from the at least one input argument cell of the function sheet to an input argument contained in a cell of the worksheet.

11. A method of creating a user-defined function in a spreadsheet application, the user-defined function being callable by a calling label in the spreadsheet application, the method comprising:

receiving a formula in the spreadsheet application;

defining an interface identifying the calling label, an input parameter and a result parameter of the user-defined function;

transferring the formula to a function sheet of the spreadsheet application to define at least a portion of a function body, wherein the function body operates on an argument corresponding to the input parameter and generates a result corresponding to the result parameter; and

storing the interface in association with the function body to create the user-defined function.

12. The method of claim 11 further comprising:

modifying the function body to provide customized operation that differs from operation of the formula.

13. The method of claim 11 wherein the receiving operation comprises:

receiving the formula in a calling cell of a worksheet.

14. The method of claim 13 further comprising:

replacing the formula in the calling cell of the worksheet with a call to the user-defined function.

15. The method of claim 11 wherein the transferring operation comprises:

creating the function sheet; and

copying the formula into the function sheet.

16. The method of claim 11 wherein the operation of transferring the formula to a function sheet comprises:

revising the formula in the function sheet to reference an input argument cell of the function sheet, wherein the input argument cell is associated with the input parameter.

17. The method of claim 11 wherein the operation of defining the interface comprises:

designating a result cell of the function sheet.

18. The method of claim 11 wherein the operation of defining the interface comprises:

designating at least one input argument cell of the function sheet.

19. The method of claim 18 wherein the operation of defining the interface further comprises:

inserting a reference from the at least one input argument cell of the function sheet to an input argument contained in a cell of the worksheet.

20. A system for creating a user-defined function in a spreadsheet application that is callable by a calling label in the spreadsheet application, the system comprising:

an interface definition module defining an interface identifying the calling label, an input parameter and a result parameter of the user-defined function;

a function sheet generator receiving a formula in the spreadsheet application and transferring the formula to a function sheet of the spreadsheet application to define at least a portion of a function body, wherein the function body operates on an input argument corresponding to the input parameter and generates a result corresponding to the result parameter; and

an encapsulation module storing the interface in association with the function body to create the user-defined function.