Transform3

The Transform3 method applies a mathematical transform equation to columns. Returns a Boolean. This command replaces the Transform and Transform2 commands.

Syntax

object.Transform3( TranformType, RangeMin, RangeMax, Equation, TreatEmptyCells, TreatTextCells, TreatNumberCells )

Parameter

Type

Description

TransformType

wksTransformWith

optional; transform on rows, columns, or cells; default is wksTransformWithCol

RangeMin

Variant

optional; first row (or column) containing values to transform

RangeMax

Variant

optional; last row (or column) containing values to transform

Equation

String

optional; equation written as a string

TreatEmptyCells

wksTDHEmptyCells

optional; treat empty cells as blank, as 0, or as empty text (""); default is wksTDH_EmptyCell_BlankResult (blank)

TreatTextCells

wksTDHTextCells

optional; treat text cells as blank, as text, as 0, or as numbers (if possible); default is wksTDH_TextCell_BlankResult (blank)

TreatNumberCells

wksTDHNumberCells

optional; treat number cells as blank, as numbers, as text, or as empty text (""); default is wksTDH_NumberCell_UseExactly (treat as numbers)

Remarks

  1. See Mathematical Functions for more information on writing the Equation string.
  2. This method can be used as a replacement for the Transform and Transform2 methods. The Transform and Transform2methods have been preserved for backwards compatibility.
  3. If the TransformType is set to wksTransformWithCol or wksTransformWithRow, the RangeMin and RangeMax values are required.
  4. If wksTransformWithCol is used, RangeMin and RangeMax are required. These are integers corresponding to the First row and Last row to apply the transform.
  5. If wksTransformWithRow is used RangeMin and RangeMax are required. These are integers corresponding to the First column and Last column to apply the transform. An underscore denotes a variable row number in the Equation string.
  6. If wksTransformWithCell is used RangeMin and RangeMax are not used and should not be set.

Example 1

This example shows how to transform only numeric cell data by columns.

Wks.Transform3 (TransformType:=wksTransformWithCol, RangeMin:=2, RangeMax:=22, _

Equation:="D=A+B", TreatEmptyCells:=3, TreatTextCells:=4, TreatNumberCells:=2)

Example 2

This example shows how to transform cell data by rows. Text are converted to numbers if possible.

Wks.Transform3(wksTransformWithRow,1,7,"_30 = SUM( _1.._28 )", _

wksTDH_EmptyCell_BlankResult,wksTDH_TextCell_ConvertToNumber,wksTDH_NumberCell_UseExactly)

Example 3

This example shows how to transform individual cells. Both text and empty cells are treated as zero.

Wks.Transform3(wksTransformWithCell,3,3,"D3 = A1+A2+B1+B2-C1-C2", _

wksTDH_EmptyCell_ConvertToZero,wksTDH_TextCell_ConvertToZero,wksTDH_NumberCell_UseExactly)

Used by: WksDocument object