SetFormulas method
Sets the formulas of one or more cells.
Version added
4.5
Syntax
| |
|
intRet |
Integer. Number of SID_SRCStream entries which 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. |
Remarks
The SetFormulas method behaves like the Formula property, except 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 these 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 }nwhere 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 }nwhere 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), then the entry is ignored by the SetResults method. The motivation for this is that 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, then the i'th cell will be set to the formula in formulas(j), where j is the index of the most recent prior entry which 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), then 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 only pass one copy of the formula.
The flags argument should be a bit mask of the following values.
|
Constant |
Value |
Description |
|
visSetBlastGuards |
&H2 |
Override present cell values even if they're guarded. |
|
visSetTestCircular |
&H4 |
Test for establishment of circular cell references. |
|
visSetUniversalSyntax |
&H8 |
Formulas are in universal syntax. |
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, then the SetFormulas method raises an exception and returns i. Otherwise, n is returned.