Tips for Writing User Code

Referring to a worksheet

Refer to a worksheet by its name in the workbook.

The name should be contained within square brackets and inverted commas:

workbook["Name of worksheet"]

e.g. workbook["Sheet1"] would refer to Sheet1 (the default name for the first unnamed sheet in a workbook).

If you need to refer to the worksheet often, you can use a variable to give it a shorter name:

s1 = workbook["Sheet1"]
funds = workbook["Funds"]

Referring to a cell

Refer to a cell by using its worksheet's Cells property.

There are four ways to do this:

• By property, e.g.:

  workbook["Sheet1"].Cells.C4
  

  This is the simplest way of accessing cells, but is very static - that is, a particular bit of code will always access the same cell. It is, however, quick and simple to type!

• By index, e.g.:

  cellReference = "C4"
  workbook["Sheet1"].Cells[cellReference]
  

  This is a little more complex. It's useful when, for example, you want to use a variable to change just which cell a given line of code operates on.

• By numerical index, e.g.:

  workbook["Sheet1"].Cells[3, 4]
  

  This refers to the cell in the third column and the fourth row (C4).

• By header index, e.g.:

  workbook["Sheet1"].Cells["Amount",4]
  

  If the header row had been set and column 3 (C) in the example above was headed "Amount", this header could be used instead of the numerical index.

Referring to the value of a cell

Refer to the value of a cell by using its Value property:

workbook["Sheet1"].Cells.C4.Value
workbook["Sheet1"].Cells["C4"].Value
workbook["Sheet1"].Cells[3, 4].Value
workbook["Sheet1"].Cells["Amount",4].Value

This mirrors the way you would access, for example, its Bold or DecimalPlaces properties.

workbook["Sheet1"].C4
workbook["Sheet1"]["C4"]
workbook["Sheet1"][3, 4]
workbook["Sheet1"]["Amount",4]

Referring to a row or column

Refer to a row or column by using its worksheet's Rows or Columns property.

Referring to rows and columns is similar to referring to cells. You access a worksheet, and then use either its Rows or its Cols property, providing the index—numerical for rows, numerical or alphabetic for columns.

workbook["Sheet1"].Rows[3]
workbook["Sheet2"].Cols["E"]
workbook["Sheet2"].Cols[5]

Just as with cells, header titles in header rows or columns can also be used to reference rows or columns:

workbook["Sheet1"].Rows["France"]
workbook["Sheet2"].Cols["Value"]

How to create a cell range

The recommended way to create a cell range is to use the to property of a cell. The two examples below both refer to the range A1 to B10:

workbook["Sheet1"].Cells.A1.to.B10
workbook["Sheet1"].Cells.A1.to[2, 10]

You can also use the CellRange constructor directly.

CellRange(workbook["Sheet1"].Cells.A1, workbook["Sheet1"].Cells.B10)

Variable names

Variable names...

• Should begin with a letter.
• Can comprise of uppercase and lowercase letters.
• Are case sensitive (i.e. 'UK' is not the same as 'Uk').
• Do not conventionally begin with a capital letter.

It's best to design long variable names to maximise readability. While interCaps are fine for short names (like 'buyPrice'), consider breaking up long names with underscores (like 'annual_percentage_rate').

Variable definition locations

Where you define a variable determines its effect on the spreadsheet:

Pre-constants user code

Default value of the variable.

Pre-formula user code

The new value overwrites any value stored in the Pre-constants user code and is used in the worksheet formulae.

Post-formulae user code

Overwrites any pre-existing value, but is not used in the worksheet formulae.

Defining a function

Most functions should be defined in the Pre-constants user code.

Here's a simple example function that calculates VAT (the UK sales tax):

def withVAT(amount):
    return amount * 1.175

Using a function

From a cell

Use the "=" sign and call the function in the format you typed it. For example, to find the value of the contents of cell B2 plus VAT, type the formula =withVAT(B2) into another cell.

From the user code

Call the function using the name withVAT. The function name is always case sensitive.

Example: Conditional Formatting in the Post-formulae user code

You can use Post-formulae user code to extract, highlight and manipulate formula results. For example, to look for values greater than 20 and apply PeachPuff background colour, you might enter the following code...

for cell in workbook["Sheet1"].Cells:
    if cell.Value > 20:
        cell.BackColor = Color.PeachPuff

(Note the colons, and the spelling of 'Color'.)

The above code also highlights cells containing text and dates—Python regards any text to be greater than any numerical value. To limit highlighting to cells containing numerical values, the code should be:

for cell in workbook["Sheet1"].Cells:
    if type(cell.Value) == float and cell.Value > 20 :
        cell.BackColor = Color.PeachPuff