Spreadsheet.Transform

This function Transform variable(s) according to the supplied formula(s).

Syntax Parameters Return Value 
Sub Spreadsheet.Transform( _
    Formulas As Variant, _
    Optional sep As String = ";", _
    Optional opt As SpreadSheetTransformOption = scIgnoreFormula)
  • Formulas [in]

Variant value containing the formulas to be placed in the corresponding spreadsheet variables.

Type: Variant

  • sep [in]

String value used as the formula separator. This only applies if multiple formulas are passed to the formulas parameter.

Type: String

Default value: ";"

  • opt [in]

SpreadsheetTransformOption specifying how the function should handle existing variable formulas.

Type: SpreadSheetTransformOption

Default value: scIgnoreFormula

This function does not return a value.

SVB Example

Transforming data via multiple formulas: 

Option Base 1
Option Explicit
Sub Main
    Dim formula As String
    'stores two functions into the variable formula.
    'Note that the ':' is used as the separator.
    'Also note that ConvertLocale is called to make the formula compatible with the current locale's formula parser
    formula = Application.ConvertLocale("v1=ToNumberEx(""5/17/62"",""mm/dd/yy""):v2=Trunc(Rnd(100))", scToInternational)
    'places the formula into the corresponding variables long name fields, replacing
    'any existing formula, and then executes it.
    'The ':' is used as the separator, which tells the function that a new formula is beginning
    ActiveSpreadsheet.Transform(formula,":",scReplaceFormula)
End Sub