Cached worksheets

This example introduces cached worksheets.

Cached worksheets persist between recalculations, so they can be used to store values that take a long time to compute. Avoiding lengthy calculations can make subsequent recalculations faster.

You will learn...

  • How to create a cached worksheet.
  • How to delete a cached worksheet.
  • The Answer to the Ultimate Question.

Before you begin

Do one of the following to open the example workbook:

  • In Windows, select Start, Resolver One, Samples, Cached worksheets.
  • Download the file from the Resolver Exchange.

The example workbook

The example workbook demonstrates a typical use of cached worksheets. In this case it performs a long calculation to compute the Answer to Life, the Universe, and Everything, and stores the result in a cache.

Pre-formulae user code

from random import random
from System.Threading import Thread

def AnswerToLifeTheUniverseAndEverything():
    Thread.Sleep(5000) # what was the question again?
    return 42 + random() - 0.5

cachedWS = workbook.AddWorksheet('cache', WorksheetMode.Cache)
cachedWS.Visible = True

if cachedWS.A1 is Empty:
    cachedWS.A1 = AnswerToLifeTheUniverseAndEverything()

answer.Value = cachedWS.A1

How the code works

  1. Identify the cached worksheet 'cache', or create one if it does not already exist.

  2. Set the Visible property to TRUE. (Cached worksheets are invisible by default.)

    The worksheet appears in the tabs, with '(RESULT)' appended to the name, indicating that it contains results only and cannot be edited.

  3. If the cached worksheet does not already contain it, calculate the Ultimate Answer.

    Note that it takes Resolver One only 5 seconds to complete the computation that took Deep Thought 7.5 million years.

Deleting a cached worksheet

Cached worksheets contain results only, and cannot be deleted manually.

To remove a cached worksheet

  1. Remove the code that creates it.
  2. Select Data, Delete Cache Worksheets to recalculate the results.

(Just selecting Data, Delete Cache Worksheets causes Resolver One to drop the cache worksheet, but the next recalculation recreates it. Just removing the code does make it vanish from the worksheet tabs, but if you restore the code, the sheet reappears with the same data.)

Comments