Using Worksheet Functions in Web Page Scripts

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

 

Wade Tai
Microsoft Corporation

July 1999

Summary: Describes how to use worksheet functions in Web page scripts, and shows how using worksheet functions in the Spreadsheet component differs from using them in Microsoft® Excel. (3 printed pages)

There are times when it is convenient to use Microsoft Excel worksheet functions to obtain the values you need in your code. Why write the algorithm for calculating medians or permutations when you can use Excel's MEDIAN and PERMUT functions, for example? By using worksheet functions, you can write code that is more concise, easier to read, and self-documenting in some respects. You are probably familiar with the WorksheetFunction class in Excel, and the use of the WorksheetFunction property to access the function you need. Consider the following code in a Visual Basic® for Applications (VBA) module in Excel:

Sub DisplayMedian()
    MsgBox Application.WorksheetFunction.Median(4, 17, 53, 94)
End Sub

When you run this code, the median of the numbers 4, 17, 53, and 94 is displayed. The message box displays 35, the midpoint between 17 and 53.

Not all worksheet functions can be used in Visual Basic for Applications in this way. For a list of supported functions, see the "List of Worksheet Functions Available in Visual Basic" topic in the Microsoft Excel Visual Basic Reference Help.

In Microsoft Excel 2000, you can create an interactive spreadsheet in a Web page. As in Excel, worksheet functions can also be used in these Web page spreadsheets, and in the scripts used to automate the controls on these pages. However, the method used to access these functions in script differs from the method used in VBA.

Let's take a look at some Visual Basic Scripting Edition (VBScript) code in a Web page. Type or paste the following HTML code into a new file and save it as Function.htm.

<html xmlns:v="urn:schemas-microsoft-com:vml"
    xmlns:o="urn:schemas-microsoft-com:office:office"
    xmlns:x="urn:schemas-microsoft-com:office:excel"
    xmlns="-//W3C//DTD HTML 4.0//EN">
<body onload="FunWithFunctions()">
<object id="MySpreadsheet"
    classid="CLSID:0002E510-0000-0000-C000-000000000046">
</object>
</body>
<script language=VBScript>
Sub FunWithFunctions()
    MsgBox MySpreadsheet.ActiveSheet.Eval("=Median(4, 17, 53, 94)")
End Sub
</script>
</html>

Next, open Function.htm in your Internet Explorer Web browser. Notice that the onload attribute in the body HTML tag specifies that the FunWithFunctions subroutine should be executed when the document is opened in the browser. In the subroutine, the MEDIAN worksheet function formula specified in the string argument to the Eval method is evaluated, and the median of 4, 17, 53, and 94 is displayed. The message box displays 35, the midpoint of 17 and 53.

Again, not all worksheet functions are supported. For a list of worksheet functions that are supported by the Spreadsheet component, click the Help button in the Microsoft Office Spreadsheet component when Function.htm is displayed in your Web browser, and click the "Spreadsheet function categories" topic under the "Working with Functions" category.

What if you want to use values from other functions, expressions, or variables that can change at run time? Let's change the Visual Basic for Applications code we mentioned earlier, as follows:

Sub DisplayMedian()
    Dim intValue As Integer
    intValue = 53
    MsgBox Application.WorksheetFunction _
        .Median(4, ActiveSheet.Range("A1").Value, _
            intValue, 94)
End Sub

Enter the value 17 in cell A1 of the active worksheet and run this code. The median of 4, the numeric value of cell A1, the value of the intValue integer variable, and 94 is displayed. The message box displays 35, the average of 17 and 53.

As you can see, using expressions and variables as arguments is pretty straightforward. Merely enter the expressions and variables in the argument list of the worksheet function call.

Now let's take a look at how we do this in the Web page we created earlier. In Function.htm, change the VBScript code as follows:

Sub FunWithFunctions()
    Dim intValue
    MySpreadsheet.ActiveSheet.Range("A1").Value = 17
    intValue = 53
    MsgBox MySpreadsheet.ActiveSheet _
        .Eval("=Median(4, " & _
        MySpreadsheet.ActiveSheet.Range("A1").Value & _
        "," & intValue & ", 94)")
End Sub

Open or refresh Function.htm in your Web browser. The value in cell A1 is 17, and the value of variable intValue is 53. Again, we want to display the median of 4, the numeric value of cell A1, the value of the intValue variable, and 94. The message box displays 35, the average of 17 and 53. Note that you have to construct the MEDIAN function formula string in order for the Eval method to evaluate it.