Hello Office

Start Page
Tutorial

This document shows you how you can access the Hugin API ActiveX Server from MicroSoft Office products (Excel, Word, etc.).

The 5 Dice Model

The Bayesian belief network (Bbn) we are going to use in this example tries to predict of the number of sixes you get when rolling five dice (eg. in a game of Yatzy). To make it the example a little more interesting, we assume that two kinds of dice are available: The normal kind where the probability of rolling six is 1/6, and a "fake" kind where the probability of rolling six is 1/3 (of course the five dice could also be a mix, but we shall not make the example that interesting). Figure 1 shows this Bbn.

Figure 1: A Bayesian belief network predicting the number of sixes you get when rolling 5 normal or fake dice.

This model contains only two nodes: One node (the "Sixes" node) predicts the number of sixes you roll. It has six states: 0, 1, 2, 3, 4, 5 - the six possible outcomes of your roll. The other node (the "Fake" is a Boolean node representing whether the dice used are normal (probability 1/6 of rolling six) or fake (probability 1/3 of rolling six). Thus, it has two states: "False" and "True" indicating whether the dice used are fake or not.

Let us assume that the probabilities of "Fake" are 9/10 for "False" and 1/10 for "True". This is easily entered into the standard Hugin user interface.

Now, you want to enter the conditional probability table of "Sixes" given "Fake". Figure 2 shows this table as Hugin would present it before any probabilities have been entered.

Figure 2: The conditional probability table of "Sixes" given "Fake" as it would be presented by Hugin before you start editing the values.

Now, the two columns in this table can be calculated as Binomial distributions B(n,p), where n=5 and p=1/6 for the first column and p=1/3 for the second column. Hugin does not (currently) provide the option the specify a column as a Binomial distribution, so you have to use some other tool to calculate them. One solution would be to use Excel. The next section shows how you can export a table generated by Excel to Hugin through the Hugin API ActiveX Server.

Exporting a Table from Excel to Hugin

Figure 3 shows an Excel spread sheet where the conditional probability table of "Sixes" given "Fake" has been calculated in column B and C.

Figure 3: The conditional probability table of "Sixes" given "Fake" calculated in Excel.

Now, to copy the values from Excel into Hugin you create a macro for the Excel spread sheet that uses the Hugin API ActiveX Server. You create a new macro by selecting "Tools" in the menu, and then "Macro" and "Visual Basic Editor". Then you double-click spread sheet where the table is created (probably "Sheet1" if you started a new spread sheet). The, you can program your own macro as a Visual Basic sub procedure.

Below, a two objects are declared as members of the Worksheet object "Sheet1". These are supposed to hold a "Globals" and a "Domain" object of the Hugin API ActiveX Server.

Private glob As Object
Private dom As Object

Now, three macros are then defined as member sub procedures of Sheet1. These are "LoadDomain", "CopyTable", and "SaveDomain". Below, the "LoadDomain" macro is shown:

Public Sub LoadDomain()
    On Error GoTo errorhandl
    
    ' Get a Globals object which can do loading:
    Set glob = CreateObject("HAPI.Globals")
    
    ' Load the domain - the parse handler arguments are not used:
    Set dom = glob.LoadDomainFromNet("C:\Nets\dice.net", Nothing, 0)
    
    ' Tell:
    MsgBox ("Domain loaded!")
    Exit Sub
errorhandl:
    MsgBox ("Error: " & Err.Number & "; " & Err.Description)
End Sub

The "LoadDomain" macro first creates a "Globals" object from the "HAPI" ActiveX Server installed on the PC. This object is held in the "glob" variable. Then, it loads a "Domain" object into the "dom" variable from a Hugin NET file.

Now, we want to copy the table from Excel into the "Sixes" node. Below, the CopyTable macro is shown:

Public Sub CopyTable()
    Dim ndFake As Object
    Dim ndSixes As Object
    Dim tb As Object
    Dim i As Integer, j As Integer
    Dim ConfIndex As Integer
    Dim CellRow As Integer, CellCol As Integer
    
    On Error GoTo errorhandl
    
    ' Get the two nodes by their names:
    Set ndFake = dom.GetNodeByName("Fake")
    Set ndSixes = dom.GetNodeByName("Sixes")
    
    ' Get the table of "Sixes":
    Set tb = ndSixes.Table
    
    ' Copy values from sheet to table:
    For i = 0 To ndFake.NumberOfStates - 1
        For j = 0 To ndSixes.NumberOfStates - 1
            ' Determine configuration index:
            ConfIndex = j + 6 * i
            
            ' Determine sheet position:
            CellRow = j + 1
            CellCol = i + 2
            
            ' Copy value:
            tb.Data(ConfIndex) = Sheet1.Cells.Item(CellRow, CellCol)
        Next j
    Next i
    
    ' Tell:
    MsgBox ("Data Entered!")
    Exit Sub
errorhandl:
    MsgBox ("Error: " & Err.Number & "; " & Err.Description)
End Sub

The macro starts by getting the two Node objects "Fake" and "Sixes". Then, it gets the Table object of "Sixes" and iterates through all fields in this table and copies the values from the Excel sheet. A Table object has its field values stored in the Data property. An index specifies a configuration of the nodes of the table (a configuration is a selection of exactly one state for each node). You can find more about this in the description of the Table object in the reference manual.

What remains now is that the domain is stored with the new table for "Sixes". This is done in the "SaveDomain" macro below:

Public Sub SaveDomain()
    On Error GoTo errorhandl
    
    ' Save domain:
    Call dom.SaveAsNet("C:\Nets\dice.net")
    
    ' Tell:
    MsgBox ("Domain saved!")
    Exit Sub
errorhandl:
    MsgBox ("Error: " & Err.Number & "; " & Err.Description)
End Sub

When you have created these macros, they can be run by selecting the "Tools" menu, and then "Macro" and "Macros...". Then select the macro and press the "Run" button.

After you have called the "LoadDomain", "CopyTable", and "SaveDomain" macros, you should be able to load the "dice.net" file again in Hugin, and the new values should be in the conditional probability of "Sixes".

The Hello Office Files

The following lists all the files used in the Hello Office example:
hellooffice/book1.xls
hellooffice/dice.net 

HUGIN Expert A/S, 2007 - comments to activex@hugin.com