SFB Program Features

Structured Formula Builder (SFB) provides the framework and tools for writing and maintaining complicated financial and business formulas. These formulas are often based on business rules expressed as logical (if, and, or) conditions. Here’s a difficult Excel business formula:

=IF(AND($D$7=5,NPV(0.15,$Q$77*0.85,$AE$77*0.85,$AS$77*0.85)*1/($Q$11+$AE$11+$AS$11)>2),NPV(0.15,$Q$77*0.85,$AE$77*0.85,$AS$77*0.85)*1/($Q$11+$AE$11+$AS$11),IF(AND($D$7=5,NPV(0.15,$Q$77*0.85,$AE$77*0.85,$AS$77*0.85)*1/($Q$11+$AE$11+$AS$11)<2),NPV(0.15,$Q$77*0.85,$AE$77*0.85,$AS$77*0.85)*1/($Q$11+$AE$11+$AS$11),IF(AND($D$7=3,NPV(0.15,$Q$77*0.85,$AE$77*0.85)*0.9/($Q$11+$AE$11)>2),NPV(0.15,$Q$77*0.85,$AE$77*0.85)*0.9/($Q$11+$AE$11),IF(AND($D$7=3,NPV(0.15,$Q$77*0.85,$AE$77*0.85)*0.9/($Q$11+$AE$11)<2),NPV(0.15,$Q$77*0.85,$AE$77*0.85)*0.9/($Q$11+$AE$11),IF(AND($D$7=2,NPV(0.15,$Q$77*0.85)*0.8/$Q$11>2),NPV(0.15,$Q$77*0.85)*0.8/$Q$11,IF(AND($D$7=2,NPV(0.15,$Q$77*0.85)*0.8/$Q$11<2),NPV(0.15,$Q$77*0.85)*0.8/$Q$11))))))

You can rewrite complicated Excel formulas as SFB solutions, which express formulas in a language that is easier to understand, modify, and document. The SFB user-interface provides a simplified programming environment and step-by-step procedure for coding complicated formulas. You can compile your SFB solutions into Microsoft COM (Component Object Model) components (DLLs), which you can then call from Excel and other programs (including SFB itself) that support the COM. You can also compile solutions into Microsoft .NET components.

When converting an Excel workbook to SFB, you should only rewrite the complicated Excel formulas; leave data tables and simple calculations performed by Excel in place. From within Excel you call your SFB COM components to perform the complicated calculations. SFB solutions replace the complicated business logic coded in Excel and other programming languages like Visual Basic, Java, and C++.

Program Features

· Variables are easy to create, modify, and document.

· Variables are displayed in a familiar user interface grid of rows and columns for easy access, sorting, searching, categorization, and filtering.

· Integrated end-user and developer help. You can create separate documentation for the developers who will maintain your solutions and the end‑users who will run your solutions.

· Connects to any ODBC data source. Variables in SFB can be linked to values and calculations from external tables: sum, max, min—anything you can compute with SQL select statements or stored procedure.

· Simple variable types: money, string, date, datetime, percent, and number with easy display format options.

· Consistent and powerful scripting language based on Perl for creating both calculations and constraints.

· Help screens and functions that assist you in the creation and editing of formulas. This includes quick views of existing variable names and current values.

· Forward and backward chaining engine that shows each variable’s precedents and dependents. You see how changing one variable affects other variables.

· Simplified testing. Input parameters are presented in an edit grid for quick changes.

· Variable names, data values, formulas, constraints, end-user help, and developer help is all stored in standard relational table format. Powerful reports on SFB solutions can be produced using standard report writers like Crystal Reports and Microsoft Access.

· Unlimited storage capacity for tracking different solutions, solution versions, and sets of input parameter values.

· SFB solutions are stored in any ODBC compliant database, including Microsoft Access and SQL Server.

· Sets of variable names and associated values can be saved locally and transmitted over the internet to other SFB users.

· Solutions can be run offline: Solutions (including changes to formulas and constraint logic) can be transmitted over the internet to any client program that supports COM or .NET components. This includes all Windows development tools like Visual Basic and Delphi, and all Microsoft Office applications including Word, Excel, and Visio. The newest version of any solution can be downloaded from a web server. Users need to be online only long enough to download new or updated solutions, or upload data sets.

· Solutions can be run online: Input parameters values to a solution can be submitted from a web form or internet enabled application using HTTP or SOAP. The web server calls the solution (compiled as a COM component or.NET component), sets the component’s properties to the input values submitted by the client, and returns the results of the solution by calling the component solution’s methods.

Getting Started

To build a financial or business model with SFB, you start by creating an empty set of variables called a solution. You give the solution a name, author, version, and description. You then begin creating variables. The values of the first variables you create are the initial starting conditions of your solution. These initial input parameter values are set by the end-user (or client application) of your solution or retrieved from linked database tables each time your solution is recalculated. After creating the initial input and table-lookup variables you are ready to create your first level of computed (or formula) variables. Each time your solution is recalculated all the formulas are evaluated and the value of each formula variable is set to the result returned by its associated formula.

An SFB formula consists of a series of mathematical and logical statements that are associated with a single variable. Each time the solution is recalculated the result of the formula is assigned to the associated variable. For example, if you create the input variables A and B with the initial values 2 and 3, respectively, you could then create a formula variable named C having the formula A + B. When you recalculate the solution, SFB shows that C is equal to 5.

You continue creating formula variables, building on previously calculated values, initial input values, and table‑lookup values, to whatever level of complexity is necessary to arrive at a final value, or set of values, that represent the output or “result” of your solution.

The simple solution described above has two input variables, A and B, and one formula variable, C, equal to A + B. (Creating variable names like A, B, and C is not recommended; it’s better to use descriptive names like Total_Cost_Improvement or Annualized_Total_Hours.) Suppose you create a fourth input variable, D, and set its value to 4; and a fifth formula variable E, equal to C + D. When you click the Re‑Calc button, SFB recalculates C, which is 5, and then calculates E to be 9 because the formula for E is C + D which is 5 + 4. As your solution grows and becomes more complicated many calculations will depend on the values returned by previous calculations and, more importantly, on the conditional logic in your formulas. Using conditional logic is described below. But first lets look at variable types and data types.

VarTypes

When you create a variable, you assign it one of the following VarTypes:

· Direct Input: The value is set by the end-user (or client application) of your solution.

· Table Lookup: The value is retrieved from an externally linked data source each time the solution is recalculated.

· Formula: The value is the result of a calculation that you write and typically depends on other variables and logical conditions. The value is updated each time the solution is recalculated.

DataTypes

You also assign each variable one of the following DataTypes:

· Number

· Date

· DateTime

· String

· Money

· Percent

· List

Lists

SFB Lists are table-lookup variables that hold an ordered set of values. For example, this List variable contains three colors:

qw(red green blue)

qw is the Perl function to quote words with white space. See Temporary Variables and Lists for details on using Lists.

Direct Input, Table Lookup, and Formula Variables

When you create an SFB variable, you give the variable a name, category, vartype, datatype, and format (i.e., number of decimals to display). Variable names cannot exceed 60 characters and may consist of only letters, numbers, and underscores. Category names are also limited to 60 characters but may contain any character.

SFB is case-sensitive and automatically capitalizes the first letter of the variable names you create. This prevents name conflicts with the keywords used in formulas and SQL statements. For example, if you create a variable named Where, it will not conflict with the SQL keyword where.

Direct Input Variables

For direct input variables, you set the variable’s name, category, data type, format, and initial value.

Table Lookup Variables

When you create a table lookup variable, you will create or use an existing ODBC Data Source Name (DSN) that points to any ODBC compliant data source, such as SQL Server, MS Access, Excel, or CSV text file. The table lookup variable’s value is retrieved from the linked external table each time the solution is recalculated. For simple (single table) lookups use the SFB SQL Builder utility to help you create the SQL select statement that will retrieve the appropriate value(s) from your linked table. You can also link variables to the value (or values) returned by stored procedures.

Each time you click the Re‑Calc button, SFB executes the table lookup variable’s associated SQL select statement or stored procedure and assigns the value(s) retrieved to the lookup variable. Each lookup variable you create can be linked to a different data source by a different DSN (ODBC Data Source Name). There is no limit on the number of different DSNs you can use in a solution.

Note: SFB requires that you use System (not User) DSNs.

Table-lookup variables are linked to a single column of data. However, by assigning the table-lookup variable’s DataType to List you can retrieve multiple rows of data. Typically, your SQL select statement’s where clause determines which rows of the table are retrieved. SFB List values are stored as character strings regardless of the data types retrieved from the linked table. For example, if a List variable’s SQL statement retrieves numeric values from six rows of a table, the table lookup value will be displayed as follows:

qw(24221 45651 72453 35932 33452 33458)

See Temporary Variables and Lists for details on using Lists.

Your table lookup variable’s associated SQL select statement or stored procedure will often require parameters. Here is a parameterized SQL statement:

select [CompanyName] from [Customers] where [CustomerID] = ?; CustID

The current value of the SFB variable CustID replaces the question mark when you recalculate the solution. A semi-colon separates the SQL select statement from the parameters. Multiple parameters are separated by commas. Notice the use of brackets around the column names [CompanyName], [CustomerName], and [CustomerID]. Enclosing column names in brackets prevents conflicts with SFB variables that may have the same names as the fields in your external table. Type SQL keywords like select, from, and where in all lower case to prevent name conflicts with SFB variable names. (SFB is case-sensitive and capitalizes all variable names. For example, if you try to create a variable named select, SFB changes the name to Select.)

This parameterized stored procedure takes two parameters. Notice the use of the exec function.

exec sp_findvalue ?, ?; Total_Cost_Improvement, Annualized_Total_Hours

The values for SFB variables Total_Cost_Improvement and Annualized_Total_Hours are passed to stored procedure sp_findvalue.

Formula Variables

When you create a formula variable, you write one or more mathematical expressions that return a value of the appropriate data type. The possible data types are number, date, datetime, string, money, and percent. Formulas are written in Perl. Although the entire Perl programming language is available to you for writing formulas, only a small subset of the language is usually needed. An SFB developer will benefit from knowing Perl, but it is not necessary to become fluent in Perl to use SFB effectively and build complicated solutions.

The value returned by a formula is often based on conditional logic. For example, here is a simple three-line formula for a variable we will name TestVariable:

return 5 if 'Sam' ne 'Joe';

# This is a comment.

return 10;

A semi-colon separates each statement (except for comments). The semicolon is optional after the last statement. White space, tabs, and line breaks are ignored. Format your formulas any way you like. The (#) symbol begins a line comment. Everything on the line following a (#) symbol is ignored. Comments should be used to explain complicated formulas. Formulas execute from top to bottom or until a return statement executes. This is how to read the formula above: return 5 if the string 'Sam' is not equal to the string 'Joe'; otherwise, return 10.

SFB formulas are case-sensitive. Write if not If, and return not Return.

Because 'Sam' ne 'Joe' is a true statement, the return statement on the first line executes, giving a result of 5 to the formula and causing the rest of the formula to be ignored; the second and third lines of the formula do not execute. Clicking the Re‑Calc button causes this formula to return 5 and, therefore, set the value of TestVariable to 5. TestVariable can now be used in other formulas where it will provide the value 5 within those formulas.

Suppose we create a table lookup string variable named FreeFruit that retrieves the value 'Apple' from a table. And we create a direct input string variable named FavoriteFruit, and also set its value to 'Apple'. The following two line formula for the variable we will name Cost_Of_Fruit returns 30, thereby setting the value of Cost_Of_Fruit to 30 each time you recalculate the solution.

return 30 if FavoriteFruit eq FreeFruit;

return 40;

Notice that the if test above is based on two previously defined variables, FavoriteFruit and FreeFruit. If the user of your solution changes the value of FavroiteFruit from 'Apple' to 'Grape' and recalculates the solution, Cost_Of_Fruit will be set to 40 instead of 30. 'Grape' is not equal to 'Apple' so this time the if test on the first line fails, return 30 is skipped, and the formula continues executing the next statement, return 40.

Essential Perl Operators

Here are the Perl operators you need to write formulas.

Different operators are used for comparing String variables and Numeric variables. Numeric variables in SFB are Number, Money, and Percent. (See DataTypes.)

`==`	`!=`	Numeric equality, inequality. Note the double equal sign.
`eq`	`ne`	String equality, inequality
`<`	`>`	Numeric less than, greater than
`lt`	`gt`	String less than, greater than
`<=`	`>=`	Numeric less (greater) than or equal to
`le`	`ge`	String less (greater) than or equal to

`not`		negation
`and`		AND
`or`	`xor`	OR, exclusive OR

Calling External Functions

External functions can be called from SFB formulas. The functions you can call include the Windows API, COM DLLs, built-in Excel functions like NPV, and XML Web Services. For Windows API functions, refer to the Perl Win32::API module documentation. This SFB formula calls the Windows API function GetTempPath:

use Win32::API;

my $GetTempPath = new Win32::API('kernel32', 'GetTempPath', ['N','P'],'N');

my $lpBuffer = " " x 80;

my $path =$GetTempPath->Call(80, $lpBuffer);

return substr($lpBuffer, 0, $path);

See Temporary Variables for details on using variables like $GetTempPath above.

Calling an external COMF DLL relies on the Perl Win32::OLE module. Here’s an example that calls a COM DLL written in Visual Basic from an SFB formula. A Visual Basic component named prj_dll_add contains a class named DLL_Add. Within the DLL_Add class, a function named 'add' adds two numbers.

use Win32::OLE;

my $ex = Win32::OLE->new('prj_dll_add.DLL_Add');

return $ex->add(3,6);

You can call COM components written in SFB from within other SFB solutions. This example calls a COM component (DLL) created from an SFB solution named Add. See Creating SFB COM Components. The Add component contains variables A, B, and C. A and B are direct input variables and C is a formula variable equal to A + B. This example returns 11. (Single quotes around 'A' and 'B' are unnecessary but prevent variable name conflicts.)

use Win32::OLE;

my $ex = Win32::OLE->new('Add.Add_obj');

$ex->{'A'} = 5;

$ex->{'B'} = 6;

return $ex->C;

Calling built-in Excel functions also relies on the Perl Win32::OLE module. This example calls the Excel function NPV:

use Win32::OLE;

my $excel = Win32::OLE->GetActiveObject('Excel.Application') ||

Win32::OLE->new('Excel.Application', 'Quit'); #get active Excel application

return $excel->NPV(.10, -10000, 3000, 4200, 6800);

The Excel documentation for the SLOPE function states that the arguments are numbers or names, arrays, or references that contain numbers. The Excel on-line help shows this example of a SLOPE function call:

SLOPE({2,3,9,1,8,7,5}, {6,5,11,7,5,4,4})

In Perl, however, brackets[], not braces{}, are used for array references. Therefore, this is the correct way to call the ExcelSLOPEfunction from within an SFB formula:

use Win32::OLE;

my $excel = Win32::OLE->GetActiveObject('Excel.Application') ||

Win32::OLE->new('Excel.Application', 'Quit'); #get active Excel application

return $excel->SLOPE([2,3,9,1,8,7,5],[6,5,11,7,5,4,4]);

Excel functions that accept and return arrays can be called with only one value at a time. (SFB formulas cannot return Lists.) This example returns the value for the GROWTH function evaluated for the array consisting of the single value 10. The Perl (@) symbol is used for arrays. See the Perl Reference Guide for more information on arrays.

return @{$excel->GROWTH([125,200,258,315],[2,4,6,8], [10])};

Win32::OLE can also be used to call other Microsoft Office applications. This example spell checks the value of the string variable named SomeWord:

use Win32::OLE;

my $word = Win32::OLE->GetActiveObject('Word.Application') ||

Win32::OLE->new('Word.Application', 'Quit'); #get active word application or new

my $correct = $word->CheckSpelling(SomeWord);

return "'" . SomeWord . "'" . ' is spelled correctly. ' if $correct;

return "'" . SomeWord . "'" . ' is spelled incorrectly.'

The period is the Perl string concatenation operator.

XML Web Services can be called by using the Perl SOAP::Lite module. This example calls the getStateName web service, located at http://www.userland.com. The function returns Wyoming because it is the 50^th state, alphabetically.

use SOAP::Lite;

# Frontier http://www.userland.com/

my $s = SOAP::Lite

-> uri('/examples')

-> on_action(sub { sprintf '"%s"', shift })

-> proxy('http://superhonker.userland.com/');

my $statename = $s->getStateName(SOAP::Data->name(statenum =>50))->result;

return $statename;

Rounding Numbers

Number, Money, and Percent variables are displayed with up to 8 digits after the decimal point. Numbers are rounded for display, but the actual (un-rounded) values are used in calculations regardless of the number of decimals displayed. The round function can be used to display exact values and eliminate round off errors.

For example, suppose a formula variable named M1 returns 1.32, another formula variable, M2, returns 1.54, and a third formula variable, M3, has the formula M1 + M2. If the decimals display format for all these variables is set to 2, you would see that 1.32 + 1.54 = 1.86. However, if you set all the decimal formats to 1, you would see 1.3 + 1.5 = 1.9, an apparent error. The sum appears to be in error because the actual values of M1 and M2, 1.32 and 1.54, are still used in the calculation M1 + M2 even though 1.3 and 1.5 are displayed. To avoid this round off error, either reset all the decimal formats back to 2, or leave the decimal formats set to 1 and use the round function in the formulas for M1 and M2. The round function can return actual values that have the same number of decimals that the displayed values have. For example, if the decimals format for M1 is 1, then change the return statement in the formula for M1 to:

return round(XXX,1)

where XXX is the expression for the value of M1.

Instead of returning 1.32 and displaying 1.3, M1 will now return and display 1.3. Use round(XXX,0) or just round(XXX) to round off all decimals and display only the rounded integer value.

Dates

When checking whether or not two date variables are equal, use the string eq and ne operators. For greater or less than date comparisons dates are converted to an absolute number of days and compared with numeric operators. The date to days conversion is made with the special SFB date_to_days function. (date_to_days is pre-defined so you do not have to use the Perl Date::Calc module.) See the Active Perl Users Guide for documentation on Perl modules like Date::Calc. The following formula compares two date variables, D1 and D2:

return 'D1 preceeds D2' if date_to_days(D1) < date_to_days(D2);

return 'D2 preceeds D1' if date_to_days(D2) < date_to_days(D1);

return 'D1 and D2 are the same date.';

SFB does not automatically recognize strings like 'May 21, 1952' in formulas as dates. To make greater than or less than date comparisons within a formula use the date_to_days function. (Writing formulas that contain dates strings is not recommended‑‑it’s better to create Date variables and use the variable names in formulas.) This formula returns 'Hello' using the date_to_days function with strings 'May 21, 1952' and 'May 22, 1952':

return 'Hello' if date_to_days ('May 21, 1952') < date_to_days ('May 22, 1952');

return 'Goodbye';

The following example uses three functions from the Perl Date::Calc module: Decode_Date_US, Date_to_Text, and Add_Delta_Days. The formula adds 90 days to May 21, 2002 and returns a properly formatted SFB Date value. Date_to_Text returns 4 extra characters (a leading three letter day‑of‑week plus a space), which we drop with the substr function.

use Date::Calc qw(:all);

my ($year, $month, $day) = Decode_Date_US('May 21, 2002');

substr(Date_to_Text(Add_Delta_Days($year, $month, $day, 90)), 4);

DateTime

The special SFB decode_datetime function is used with DateTime variables. In the example below "May 22, 1952 12:00:00" is converted to the @date array which contains these six values: year, month, day, hour, minute, second. Add_Delta_DHMS then adds 7 days, 4 hours, 25 minutes, and 2 seconds to @date. The result is formatted as a valid SFB DateTime value using the sprintf function. Date_to_Text formats the first three values of @date[0..2]: year, month, and day. The substr function drops the first 4 characters returned by Date_To_Text: the day-of-week (3 characters) followed by a space.

use Date::Calc qw(Add_Delta_DHMS Date_to_Text);

my @date = decode_datetime("May 22, 1952 12:00:00");

@date= Add_Delta_DHMS(@date, 7, 4, 25, 2);

return sprintf("%s %02d:%02d:%02d", substr(Date_to_Text(@date[0..2]),4), @date[3..5]);

The next example formula returns the string "2 1 40 22" which shows that May 23, 2002 13:50:32 is 2 days, 1 hour, 40 minutes and 22 seconds after May 21, 2002 12:10:10.

use Date::Calc qw(Delta_DHMS);

my @date1 = decode_datetime("May 21, 2002 12:10:10");

my @date2 = decode_datetime("May 23, 2002 13:50:32");

my @diff = Delta_DHMS(@date1, @date2);

return "@diff";

Statement Modifiers

Statements can be modified by an if or an unless statement. For example, here are two ways to express the same logic, first using if, then using unless:

return 'D1 preceeds D2' if date_to_days(D) < date_to_days(D2);

return 'D1 preceeds D2' unless date_to_days(D1) >= date_to_days(D2);

Use whichever statement modifier you prefer.

All function names, variable names, operators, and statement modifiers are case sensitive. Write if and unless, not If and Unless. All Perl functions (like round) are lower case and will not conflict with SFB variable names, which always start with a capital letter. (SFB capitalizes the first letter of all variable names for you.) See the Perl Reference Guide for a complete list of all Perl functions and operators.

Temporary Variables

Sometimes it’s convenient to the save the calculation of a value that is reused many times within a formula. You create a temporary variable with the keyword my. This example creates two temporary variables, $d1 and $d2.

my $d1 = date_to_days(D1);

my $d2 = date_to_days(D2);

return 'D1 preceeds D2' if $d1 < $d2;

return 'D2 preceeds D1' if $d2 < $d1;

return 'D1 and D2 are the same date.';

Temporary variable names are prefixed with a dollar sign ($) and are assigned a value with the equal sign (=). In Perl, use the single equals (=) for assignment and the double equals (==) for testing numeric equality. (See the table of Essential Perl Operators.) In long complicated formulas, temporary variables save typing and make formulas easier to understand. Temporary variables, like $d1 and $d2 above, exist only within the formula in which they are defined; they cannot be seen by other formulas.

Temporary Variables and Lists

Temporary variables are also used to access specific elements of SFB List values. For example, if a table lookup List variable named Colors retrieves the value:

qw(red green blue)

from a table, then this formula will return green:

my @colors = Colors;

return $colors[1];

Lists are represented in SFB as character strings consisting of the qw function followed by the list’s values in parentheses. We first convert the SFB List value qw(red green blue) to a temporary Perl array variable named @colors. The temporary variable name colors could be any name. The leading (@) symbol declares @colors as an array). Then we return element number 1 (array elements are indexed starting with 0). The number 1 in the formula above could be replaced by an SFB variable that returns a number between 0 and 2.

Validation

You may want to limit variables in your solution to a specific range or set of values. The constraints you place on a variable are written as a single logical statement that should evaluate to true. For example, suppose you want to make sure that the variable A always has a value between 5 and 10. On the Variable Edit screen, select the Validation tab and enter the following expression: in the Validation Rule input box:

A >= 5 and A <= 10

In the Validation Error Message input box, type the error message you want the user to see if the Validation Rule is violated. For this example type 'The value must be between 5 and 10.' (Be sure to enclose Validation Error Messages within single or double quotes.)

You can include references to other variables in Validation Rules and Error Messages. If, for example, you wanted to include the additional constraint that A is also less than B, we would change the Validation Rule to:

A >= 5 and A <= 10 and A < B

Change the Error Message to either:

'The value must be between 5 and 10 and less than B.'

'The value must be between 5 and 10 and less than ' . B . '.'

Notice the use of the period (the Perl string concatenation operator) to concatenate the first part of the error message (enclosed in quotes) to the current value of B and then again to the final period.

Continuing with the validation example for A, suppose that if A is less than D, the other constraints on A should not be required. Modify the validation rule as follows:

(A >= 5 and A <= 10 and A < B) or A < D

The initial constraint is placed within parentheses and we’ve added or A < D.

Creating SFB COM Components

To create a COM Component from an SFB solution, you will need to purchase and install the ActiveState Perl Dev Kit. To create a COM component, simply select Create COM Component from the Solution menu. The component you create is compiled as a COM DLL and given the name of your current solution with a _COMM.DLL extension. For example, if you create a solution named Add, the COM DLL will be named Add_COM.DLL. SFB gives the COM component a ProgID based on the solution name. For the solution named Add, the ProgID is Add.Add_obj. (To change the current solution name, select Properties from the Solution menu.)

The DLL you create is a completely self-contained representation of all the variables, formulas, and validation constraints in your solution. The DLL is optionally registered for you by SFB but can be re-registered with RegSvr32.exe if you move the DLL to another machine. When you create a component, select the Freestanding option if you intend to move the component to a machine that does not have Perl installed. The Freestanding option adds about 800k to size of the DLL.

Calling SFB COM Components

SFB COM components contain three special functions: Initialize__, Clear_Cache__, and Validate__. All three special function names end with two underscores.

Initialize__ sets all the direct input variables to their default values and resets the cache of formula values.

Clear_Cache resets the cache of fomula values. Call Clear_Cache__ prior to re-setting any direct input values in your solution. After retrieving a formula result, call Clear_Cache before you change your direct input values and retrieve the formula again. Otherwise, you will retrieve the old value, not the one you expect. Check the examples below for the proper use of Clear_Cache__.

Validate__ runs the validation tests for all variables and returns either the validation error message or the string 'OK'.

A Sample Add Solution

Suppose we create an SFB solution named Add that consists of three variables: A, B, and C. A and B are direct input variables, and C is a formula variable that returns the sum of A and B.

Calling SFB COM Components from Windows Scripting Host

A COM DLL created from the Add solution can be called from a Windows Scripting Host file, Add.vbs, as shown below:

'ProgID is always in the form SolutionName.SolutionName_obj.

Set obj = CreateObject("Add.Add_obj")

'Initialize all direct input variables to their default values and clear formula cache.

obj.Initialize__

'Set direct input variables for this run.

obj.A = 3

obj.B = 4

'Call Validate__ returns "OK" on success.

Validate = obj.Validate__

If Validate = "OK" then

'read result(s) of your solution into Run (variable C for this solution)

Run = obj.C

if left(Run,10) = "**ERROR**:" then

MsgBox("Runtime error: " & Run)

Else

'Output C is the sum of A and B.

MsgBox(Run)

end if

Else

'Solution failed validation so print the validation error message.

MsgBox(Validate)

End If

If you do not check validation or runtime errors, the VBScript file can be written simply as:

Set obj = CreateObject("Add.Add_obj")

obj.A = 3

obj.B = 4

MsgBox(obj.C)

obj.Clear_Cache__ 'About to reset A and B so call Clear_Cache__. Otherwise obj.C will still return 7, not 11.

obj.A = 5

obj.B = 6

MsgBox(obj.C)

This is the test script, Add.js, written in JScript.

var obj = WScript.CreateObject("Add.Add_obj");

obj.Initialize__(); // Sets all default values

obj.A = 3; // Set value for A.

obj.B = 4; // Set value for B.

var validate = obj.Validate__(); // Validate__() returns 'OK' on success.

if (validate == 'OK') {

var run = obj.C().toString();

if (run.substr(0,10) == '**Error**:') { // Check for runtime errors.

WScript.Echo(concat('Runtime Error: ' , run));

} else {

WScript.Echo(run);

}

} else {

// Print validation error message.

WScript.Echo(validate);

}

If validation and runtime error checks are not checked, the JScript file can be written simply as:

var obj = WScript.CreateObject("Add.Add_obj");

obj.A = 3;

obj.B = 4;

WScript.Echo(obj.C());

Calling SFB COM Components from Internet Explorer

<html>

<body>

var obj = new ActiveXObject("Add.Add_obj")

obj.A = 5

obj.B = 6

alert(obj.C())

</script>

</body>

</html>

Calling SFB COM Components from Active Server Pages

<html>

<body>

var obj = Server.CreateObject("Add.Add_obj")

obj.A = 5

obj.B = 2

Response.Write(obj.C());

</script>

</body>

</html>

Calling SFB COM Components from Microsoft Excel

From within the Microsoft Excel Visual Basic Editor (see Excel’s on-line help for information on Visual Basic for Applications) set a reference to the SFB Add COM Component. Select References… from the Visual Basic Editor Tools menu. Then select Add_TypeLib from the list of COM components registered on your machine; SFB components will appear in the registry listed with names in the form SolutionName_TypeLib. Enter two numbers you wish to add in cells A1 and A2 and place a CommandButton on the worksheet. In the CommandButton’s click event enter the following code:

Private Sub CommandButton1_Click()

Dim obj

Set obj = CreateObject("Add.Add_obj")

obj.A = Range("A1").Value

obj.B = Range("A2").Value

Range("A3").Value = obj.C

End Sub

Clicking on CommandButton1 sets the value of cell A3 to obj.C (the sum of the values your entered in cells A1 and A2). If you change the Add solution, exit Excel and any other application using Add_obj before you recompile the COM component.

This Excel subroutine implements validation and runtime checks:

Private Sub CommandButton1_Click()

Dim obj

Set obj = CreateObject("Add.Add_obj")

Dim Run As Variant

Dim Validate as Variant

'Set direct input variables.

obj.A = Range("A1").Value

obj.B = Range("A2").Value

'Call Validate__ function returns "OK" on success.

Validate = obj.Validate__

If Validate = "OK" Then

'Read result(s) of your solution into Run (variable C in this example).

Run = obj.C

If Left(Run, 10) = "**ERROR**:" Then

MsgBox ("Runtime error: " & Run)

Else

'Output C is the sum of A and B.

Range("A3").Value = Run

End If

Else

'Solution failed validation so print the validation error message.

MsgBox (Validate)

End If

End Sub

To eliminate the need to click a command button, insert the code above in the Excel Worksheet _Change event.

Calling SFB COM Components from SFB

You can call a previously created SFB solution in a new solution. First, compile the solution you want to use into a COM component. The SFB formula shown below returns 11 by calling the SFB Add.Add_obj COM component compiled previously. In the new solution, the numbers 5 and 6 are input parameters to the Add component and could be replaced by the names of two SFB variables you wish to add.

use Win32::OLE;

my $ex = Win32::OLE->new('Add.Add_obj');

$ex->{'A'} = 5;

$ex->{'B'} = 6;

return $ex->C;

Creating SFB .NET Components

To create a Microsoft .NET component from an SFB solution, you will need to purchase and install the ActiveState Perl Dev Kit. To create a .NET component, simply select Create Microsoft .NET Component from the SFB Solution menu. The .NET DLL you create is a completely self-contained representations of all the variables, formulas, and validation constraints in your solution.

The component you create is given the name of your current solution with a _NET.DLL extension. For example, if you create a solution named Add, the .NET DLL will be named Add_NET.DLL. (To change the current solution name, select Properties from the Solution menu.)

When you create a component, select the Freestanding option if you intend to move the component to a machine that does not have Perl installed. The Freestanding option adds about 800k to the size of the DLL.

Calling SFB .NET Components using Visual Basic .NET

To call an SFB .NET DLL from within the Microsoft Studio .NET editor, first set a reference to Add_NET.DLL. Select References… from the Tools menu. Click Browse and then navigate to the Add_NET.DLL file. The following Visual Basic .NET example sets the variable result to 11. There are no special Initialize__ or Clear_Cache functions in SFB .NET components. SFB .NET components are initialized when they are created and the formula cache is cleared automatically whenever you set a direct input value (obj.A = 5, for example).

Dim obj As New Add()

obj.A = 5

obj.B = 6

Dim result As String

result = obj.C()

Here is a more complete example showing how to check the Add_NET.DLL component for validation and runtime errors in Visual Basic .NET. The example below evaluates two formula variables: an SFB Number variable, C, and an SFB String variable, D. For SFB .NET components, notice that runtime errors are checked differently for numeric and non-numeric variables:

Dim obj As New Pass_Counter()

obj.A = 100

obj.B = 0

Dim validation As String = obj.Validate__()

If validation <> "OK" Then

MsgBox(validation)

Exit Sub

End If

Dim Numeric_result As Decimal = obj.C

If Numeric_result = -999999 Then

MsgBox(obj.Runtime_Error__)

Else

MsgBox(Numeric_result)

End If

Dim String_result As String = obj.D

If Mid(String_result, 1, 10) = "**Error**:" Then

MsgBox(obj.Runtime_Error__)

Else

MsgBox(String_result)

End If