3.7.5.102 Wks.Col


wks.col objects refer to columns in a worksheet or matrix objects in a matrix sheet. These objects contain properties that read or write column attributes such as names, units, types, etc. and methods that set the filter, formula, etc.

There are two syntaxes to refer to this object.

  • wks.col -- active column (E.g. wks.col=2 set 2nd column to be active column. wks.col.lname$="Data" set 2nd column's long name.)
  • wks.colN -- Nth column (E.g. wks.col2.lname$="Data" set 2nd column's long name.)


Properties

Property Type Access Description
wks.col.comment$ string Read/write Column Comment row header string value.
wks.col.categorical.sort numeric Read/Write Read or set the sort method for column n: 0 = unsorted, 1 = ascending, 2 = descending and 3 = customizable (arbitrary).
wks.col.categorical.type numeric Read/Write Read or set the type for column n
wks.col.cmap.palette$ string Read/Write Read or set the color palette for the matrix object.
wks.col.digitMode numeric Read/write Set the digit display mode: 0 = display all, 1 = set the decimal places, and 2 = set the significant digits.
wks.col.digits numeric Read/write Number of decimal places when wks.col.digitMode = 1, number of significant digits when wks.col.digitmode = 2: 0 = display all, n = display n.
wks.col.edit numeric Read/write Adds one of the following controls to each cell in the column: check box, list box, combobox
wks.col.evenx numeric Read/write Read or set column n to be using Sample Interval: 0 = not using Sample Interval, 1 = using Sample Interval with Internal X Initial value = 0 and Increment value = 1.
wks.col.filter
wks.col.filter$
numeric/string Read/write Add or remove a filter.
Set or get filter query string. It must set the filter type to advance.
wks.col.filterenabled numeric Read/write Enable or disable a filter. 1 = enable filter, and 0 = disable filter.
wks.col.filterprescript$ string Read/write Set or get before query script.
wks.col.filterx$ string Read/write Set and get the variable used to represent the column.
wks.col.format numeric Read/write Column format: 1 = Numeric, 2 = Text, 3 = Time, 4 = Date, 5 = Month, 6 = Day of Week, 7 = Text and Numeric, 8 = Color and 9 = Binary as listed in the Format drop-down list in the Worksheet Column Format dialog box.
wks.col.formula$ string Read/write Column formula as in Set (Column) Values or in F(x)= label row (see also Column Label Row Characters).
wks.col.formularange$ string Read Column formula range as in Set (Column) Values.
wks.col.image numeric Read Get matrix object info:

0 = no image
1 = has image
2 = gray scale image
3 = image view

wks.col.index numeric Read/write Column index counting from left to right. Use this property to reorder worksheet column.
wks.col.iscat numeric Read/write Read or set column is Categorical or not. 0 = Column type is not Categorical, 1 = Column type is Categorical.
wks.col.label$ string Read/write Column label access for versions before 8.0. Still supporte to control long name, units and comments in a single call.
wks.col.lname$ string Read/write String value for the long name of column or matrix object.
wks.col.missing numeric Read/write Custom missing value. This property allow setting custom missing value such that non-double columns can have missing value support.
wks.col.name$ string Read/write Column short name
wks.col.nCols numeric Read/write Number of columns for Matrix Object; for worksheet column, always 1.
wks.col.nRows numeric Read/write Number of rows
wks.col.numerictype numeric Read/write Column numeric type
wks.col.py$ string Read/write Contents of Python Formula tab in Set (Column) Values
wks.col.script$ string Read/write Contents of Before Formula Script tab in Set (Column) Values
wks.col.subformat numeric Read/write Subformat options as listed in the Display drop-down list of the Worksheet Column Format dialog box (select from main menu Format: Column ... to open this dialog).
wks.col.svrm numeric Read/write Set Value Recalculate Mode for the column formula (in any) of the column. 0=none,1=auto,2=manual.
wks.col.tWidth numeric Read/write The number of characters that can be entered in a Text column.
wks.col.type numeric Read/write Column type: 1 = Y, 2 = disregard, 3 = Y Error, 4 = X, 5 = Label, 6 = Z, and 7 = X Error.
wks.col.unit$ string Read/write Units row header
wks.col.width numeric Read/write Width of a single worksheet column (e.g. "col1"), in units of characters.
wks.col.x1 numeric Read/write Get/Set matrix object 1st column mapping coordinates
wks.col.x2 numeric Read/write Get/Set matrix object last column mapping coordinates
wks.col.xinit numeric Read/write Internal X Initial value for column. The XINIT, XINC and XUNITS appear in the Sampling Interval row header if displayed.
wks.col.xinc numeric Read/write Internal X Increment value for column.
wks.col.xuints$ string Read/write Internal X Units string for column.
wks.col.xname$ string Read/write Internal X Name string for column. The XNAME and XUNITS appear as the X Axis Title on plots of this column.
wks.col.y1 numeric Read/write Get/Set matrix object 1st row mapping coordinates
wks.col.y2 numeric Read/write Get/Set matrix object last row mapping coordinates

Methods

Method Description
wks.col.filter Open the filter dialog
wks.col.setbinary Set binary auto convert.
wks.col.setformat Setformat method combines functionalities of the format and subformat methods, additionally allowing custom formats to be specified.
wks.col.setformula Set the column formula.
wks.col.findX Returns corresponding X column index

Examples

Edit Column Attributes

Set active column wks.col and then edit the attributes of it.

// Set the active column to 2.
wks.col = 1;

// Set the active column's(col2) long name as Time.
wks.col.LNAME$= "Time";

Alternatively, add index after wks.col to change the column attributes.

//Set column 1's long name as Time
wks.col1.LNAME$ = Time;

// You can reference another book, but the target sheet must still be active
// Set column 5 in the active sheet of Book5 to be an X column
Book5!wks.col5.type = 4;

// col0 to refer to last column
//set last column formula
wks.col0.formula$="A";

Set Custom Date Format

This script sets the format of column 2 of the active worksheet to a custom date format.

// Surround non-date characters in specifier by single quotes:
wks.col2.SetFormat(4, 22, yyyy'.'MM'.'dd);

Loop through columns and set column attributes

This script will loop through columns 2 to 10 and set each column long name.

// Set the number of columns to 10
wks.ncols=10;

for (i=2; i<=wks.ncols; i++)
{

   wks.col=i;  //set active column
   wks.col.lname$="Data$(i-1)";   //set active column's long name as

  //wks.col$(i).lname$="Data$(i-1) will also work
}