SetFormulas Method [Visio 2003 SDK Documentation]

Sets the formulas of one or more cells.

intRet = object.SetFormulas (SID_SRCStream, formulas, flags)

intRet     Integer. Number of SID_SRCStream entries that processed successfully.

object     Required. An expression that returns a Page, Master, Shape, or Style object whose cells are to be modified.

SID_SRCStream     Required Integer. Stream identifying cells to be modified.

formulas     Required Variant. Formulas to be assigned to identified cells.

flags     Required Integer. Flags that influence the behavior of SetFormulas.

Version added

4.5

Remarks

The SetFormulas method behaves like the Formula property except that you can use it to set the formulas of many cells at once, rather than one cell at a time.

For Shape or Style objects, you can use the SetFormulas method to set results of any set of cells.

For Page or Master objects, you can use the SetFormulas method to set results of any set of cells in any set of shapes of the page or master.

In both of the previously mentioned cases, you tell the SetFormulas method which cells you want to set by passing an array of integers in SID_SRCStream. SID_SRCStream is a one-dimensional array of 2-byte integers.

• For Shape and Style objects, SID_SRCStream should be a one-dimensional array of 3n 2-byte integers for n >= 1. The SetFormulas method interprets the stream as:

  {sectionIdx, rowIdx, cellIdx }n
  

  where sectionIdx is the section index of the desired cell, rowIdx is its row index, and cellIdx is its cell index.

• For Page and Master objects SID_SRCStream should be a one-dimensional array of 4n 2-byte integers for n >= 1. The SetFormulas method interprets the stream as:

  {sheetID, sectionIdx, rowIdx, cellIdx }n
  

  where sheetID is the ID property of the Shape object on the page or master whose cell result is to be modified.

If the sheetID in an entry is visInvalShapeID (-1) or if the bottom byte of sectionIdx is visSectionInval (255), the entry is ignored by the SetResults method. This is because the same SID_SRCStream array can be used on several calls to SetFormulas, GetFormulas, and similar methods with the caller only needing to make minor changes to the stream between calls.

The formulas argument should be a one-dimensional array of 1 <= m variants. Each Variant should be a String, a reference to a String, or Empty. If formulas(i) is empty, the i'th cell will be set to the formula in formulas(j), where j is the index of the most recent prior entry that is not empty. If there is no prior entry that is not empty, the corresponding cell is not altered. If fewer formulas than cells are specified (m < n), the i'th cell, i > m, will be set to the same formula as was chosen to set the m'th cell to. Thus to set many cells to the same formula, you need pass only one copy of the formula.

The flags argument should be a bit mask of the following values.

The value returned by the SetFormulas method is the number of entries in SID_SRCStream that were successfully processed. If i < n entries process correctly, but an error occurs on the i + 1st entry, the SetFormulas method raises an exception and returns i. Otherwise, n is returned.

Example

The following macro shows how to use the SetFormulas method. It assumes that there is an active Microsoft Office Visio page that has at least three shapes on it. It uses the GetFormulas method to get the width of shape 1, the height of shape 2, and the angle of shape 3. It then uses SetFormulas to set the width of shape 1 to the height of shape 2 and the height of shape 2 to the width of shape 1. The angle of shape 3 is left unaltered.

This example uses the GetFormulas method of the Page object to get three cell formulas and the SetFormulas method of the same object to set the formulas. The input array has four slots for each cell, as it also would for Master objects. For Shape or Style objects, only three slots are needed for each cell (section, row, and cell).

Public Sub SetFormulas_Example()
 
    On Error GoTo HandleError 

    Dim aintSheetSectionRowColumn(1 To 3 * 4) As Integer 
    aintSheetSectionRowColumn(1) = ActivePage.Shapes(1).ID 
    aintSheetSectionRowColumn(2) = visSectionObject 
    aintSheetSectionRowColumn(3) = visRowXFormOut 
    aintSheetSectionRowColumn(4) = visXFormWidth 

    aintSheetSectionRowColumn(5) = ActivePage.Shapes(2).ID 
    aintSheetSectionRowColumn(6) = visSectionObject 
    aintSheetSectionRowColumn(7) = visRowXFormOut 
    aintSheetSectionRowColumn(8) = visXFormHeight 

    aintSheetSectionRowColumn(9) = ActivePage.Shapes(3).ID 
    aintSheetSectionRowColumn(10) = visSectionObject 
    aintSheetSectionRowColumn(11) = visRowXFormOut 
    aintSheetSectionRowColumn(12) = visXFormAngle 

    'Return the formulas of the cells. 
    Dim avarFormulaArray() As Variant 
    ActivePage.GetFormulas aintSheetSectionRowColumn, avarFormulaArray 

    'Use SetFormulas to: 
    ' - Set the width of shape 1 to height of shape 2. 
    ' - Set height of shape 2 to width of shape 1. 
    ' Note: avarFormulaArray() is indexed from 0 to 2. 
    Dim varTemp As variant 
    varTemp = avarFormulaArray(0) 
    avarFormulaArray(0) = avarFormulaArray(1) 
    avarFormulaArray(1) = varTemp 

    'Pass the same array back to SetFormulas that we 
    'just passed to GetFormulas, leaving angle alone. By setting 
    'the sheet ID entry in the third slot of the 
    'aintSheetSectionRowColumn array to visInvalShapeID, 
    'we tell SetFormulas to ignore that slot. 
    aintSheetSectionRowColumn (9) = visInvalShapeID 

    'Tell Microsoft Visio to set the formulas of the cells. 
    ActivePage.SetFormulas aintSheetSectionRowColumn, avarFormulaArray, 0 

    Exit Sub   

HandleError: 

    MsgBox "Error"
 
    Exit Sub   

End Sub  

Applies to | Master object | Page object | Shape object | Style object

See Also | Cells property | Formula property | GetFormulas method | ID property | Row property | Section property | SetResults method