Transform2 - Automation

The Transform2 method is superseded by Transform3. Please consider using the more recent version. Transform2 has been retained for backward compatibility.

Transform2 applies a mathematical transform equation to columns, rows, or cells. Returns a Boolean. Returns 'True' if the process is successful, else returns 'False'.

Use Transform3 to apply a mathematical transform equation to columns, rows, or cells and consider empty, numeric, and text cells.

Syntax

object. Transform2( TransformType, RangeMin, RangeMax, Equation, TreatEmptyAsZero )

Parameter

Type

Required/

Optional

Default

Description

TransformType

Optional

wksTransformWithCol

This specifies whether the transformations to be applied to columns, rows, or cells.

RangeMin

Variant

Conditional

False

This specifies the first row, column, or cell number depending upon the TransformType.

RangeMax

Variant

Conditional

False

This specifies the first row, column, or cell number depending upon the TransformType.

Equation

String

Conditional

""

This specifies the transformation equation written as a string.

TreatEmptyAsZero

Boolean

Optional

False

By default, if any cell is empty, the formula is not calculated for that row. If you set it to 'True', the empty cell is replaced with 0.0 and the numeric result is placed in the destination column.

Remarks

  1. This method can be used as a replacement for the Transform method. The Transform method has been preserved for backwards compatibility.
  2. If the TransformType is set to wksTransformWithCol or wksTransformWithRow, the RangeMin and RangeMax values are required.
  3. If wksTransformWithCol is used, RangeMin and RangeMax are required. These are integers corresponding to the First row and Last row to apply the transform.
  4. 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.
  5. If wksTransformWithCell is used RangeMin and RangeMax are not used and should not be set.

See Mathematical Functions for more information on writing the Equation string.

Example 1

This example demonstrates how to apply a mathematical transform equation.

Wks.Transform2(FirstRow:=1, LastRow:=50, Equation:="C=(pow(A,2)+pow(B,2))*(sin(8*atan2(A,B)))")

Example 2

This example demonstrates how to apply a mathematical transform equation to rows, columns, and cells.

Sub Main

 

'Declares SurferApp as an object

Dim SurferApp As Object

 

'Creates an instance of the Surfer Application object and assigns it

'to the variable named "SurferApp"

Set SurferApp = CreateObject("Surfer.Application")

 

'Makes Surfer visible

SurferApp.Visible = True

 

'Creates a new Object named "Wks" Worksheet Document and opens it

Dim Wks As Object

Set Wks = SurferApp.Documents.Add(srfDocWks)

 

'Adds random whole numbers to the first five rows of column A, B, and C

Wks.Transform2(TransformType:=wksTransformWithCol, RangeMin:=1, RangeMax:=3, _

Equation:="A=FLOOR(RANDU(20))", TreatEmptyAsZero:=True)

Wks.Transform2(TransformType:=wksTransformWithCol, RangeMin:=1, RangeMax:=3, _

Equation:="B=FLOOR(RANDU(20))", TreatEmptyAsZero:=True)

 

'Sums columns 1, 2, and 3 and prints the output to row 5

'Underscores indicate a column wildcard

'Note the sum of column C is 0 because its rows are empty

Wks.Transform2(TransformType:=wksTransformWithRow, RangeMin:=1, _

RangeMax:=3, Equation:="_5 = _1 + _2", TreatEmptyAsZero:=True)

 

'Sums cells A5 and B5 and prints output to cell D6

'RangeMin, RangeMax, and TreatEmptyAsZero are omitted

Wks.Transform2(TransformType:=wksTransformWithCell, Equation:="D6 = A5 + B5" )

 

End Sub

Used by: WksDocument object