Dynamic Real Time Indicators in Calc

Real Time Visual Indicators in Calc
Sometimes a spreadsheet is just a blur of numbers and if there is a computation error or inputs that are out of bounds you need to visually and in real time notify user that there is a problem.

Most of the time we pull up a spreadsheet to do some basic calculations or use it as a modern day piece of scratch paper to work out some quick numbers. But, sometimes we can get into some complex problems and you don't want to do the same work twice, so you clean up the spreadsheet so you can reuse the analysis and another tool is born.
Design tools with lots of variables will eventually run you into some limitations with an equation or boundary conditions. This is when you want the tool to warn you or another user when the inputs no longer match the model's assumptions.

STYLE and CURRENT cell commands
The picture above shows a perfect example of a dynamic display. There are a list of values in cells B2 through B6. I summed the values in cell B7, but I also wanted to warn the spreadsheet user when the current sum is larger than 100.

Here is how I did it. I used two cell commands "STYLE" and "CURRENT" to test and modify the background color of the cell. Here is the command used in cell B7

=SUM(B2:B6)+STYLE(IF(CURRENT()>100; "Red"; "Default"))

• The Sum command sums the cells B2 through B6
• The Style command changes the style of the cell to either the default cell style or the custom style called "Red". We'll talk about how to create custom styles in a moment.
• The Current command, takes the current value of the cell. So, within the style command we create a test using the IF command and test if the current cell value is greater than 100. If it is, we use cell style "Red" otherwise, we use the Default cell style.

Creating Custom Styles

I'm sure a couple of you were wondering about how do I create a custom cell style "Red".

Simple, I'll walk you through it. Click on any cell and then hit the F11 key on your keyboard or click the "format" on the spreadsheet toolbar and then click the "Styles and formatting" from the submenu. When you do, you'll get this window:

 

You may only have a Default style in this box and the Default style is automatically selected when you bring up the "Styles and formatting" command.


Right Click the Default style and you will get a sub menu to either modify the selected style or create a new one as shown in the figure below.

Now Click New...

When you click new a new cell style textbox will pop up and you'll have a lot of different options for creating this new style like background color, cell border options, and options in different font or font effects and other style changes:

Here is the cell style textbox.

Give a name for your custom cell style. I used Red, but you could use Warning or any other name that suits your Customized cell style.

Now click on the background tab and select a color for your cells that will use this custom style. I used Red, but your color choices are up to you.

When you're done click the OK button.

If you duplicate the code example, you can put different combinations of values in B2 through B6 and you'll have a regular (Default) cell background until the values sum up to 100 or a greater value.

You might also want to add a text string explaining why you do not want values to be beyond a certain value. I did this in cell D7  

The formula for that cell is =IF(B7>100; "Value exceeds 100"; "")

Two years ago I created a structural analysis spreadsheet that worked out the dead loads for a platform to support a 30 foot diameter yurt and I used green, yellow, and red background styles to notify how close the loads were approaching (and exceeding) the burst strength of the concrete piers

Automated testing Display example

In my software QA test work, I've used MS excel to instrument a series of database tests on a new ETL tool developed by a firm in Massachusetts. My macro tool ran a series of sql command scripts that create a user database, run it through the ETL, which updated a larger data warehouse, and then ran some incremental updates to validate the functionality of the ETL tool.  On one excel spreadsheet sheet I had a column of tests status cells that were colored yellow, green or red, which represented untested, test passed, or test failed, respectively. The cell macro also put the text "untested","pass", or "fail" in the cells.

If I were to rewrite the tool in OpenOffice, I would create three styles for the background colors and use the following formula.

=IF(E18=-1;"FAIL"&T(STYLE("Fail"));IF(E18=1;"PASS"&T(STYLE("Pass"));"UNTESTED"&T(STYLE("Untested"))))
It's not fun to write, but once you've got one cell working, cut and paste makes it easy to replicate into other test cells.

Next month we'll talk about changing styles, colors, and cell display options in macros.

Monthly Solution Examples

I hope everyone had a great summer. Mine was spent architecting an addition to a spiritual center in New Hampshire. Of course after the design I ended up helping with the construction, so no vacation trips except to Southern New Hampshire.

As we enter fall I figured it was time to talk about Real Time Visual Indicators in Calc
Sometimes a spreadsheet is just a blur of numbers and if there is a computation error or inputs that
are out of bounds you need to visually and in real time let the user know there is a issue....More

Here is a late July programming example on building Complex Dialogs. The code is based on a task manager tool I created in Calc, but the discussion is on a programming technique to use flags....More


In May I offered a simple calc example on how to program a macro to label a set of columns and how to set the column widths....See More

Using SW flags to manage complex dialog actions

Managing a complicated macro dialog? Use flags
When you are just filling in a text box or selecting a list item, your OpenOffice macro coding is pretty straight forward. You write some routines and attach them to dialog events.

But this dialog has a bit more complexity:

In this little task management tool, I can move tasks from one listbox to another by selecting a listbox item and then clicking the command button between the lists. But if I double click on a list item in a listbox then a submenu pops up

The submenu allows me to promote or demote the selected item within the current listbox, delete the task item or to save the task on another spreadsheet as a completed or dropped task.

Now here is the rub, Humans change their minds on what to do next.

If the user clicks on an item in ListBox1 and then decides to work instead with an item in ListBox2, how will the program know whether or not the user doubled clicked the last listbox selection or clicked once in the new location?  On top of that, you'll probably have two items highlighted in two different Listboxes at this point.

We need to keep track in real time what the user has clicked on and how many times. So, we create a flag variable for each listbox event. When a different listbox is accessed we clear the click count on all the other listbox flags. If we have two consecutive clicks on the same item we start up the submenu dialog.

First, I created three integer variables called  ItmSlct1, ItmSlct2, and ItmSlct3. These three flag variables correspond to ListBox1, ListBox2, and ListBox3, respectively.
These are set to zero when the main dialog is initially displayed.

The next routines are the three event routines when the user clicks an item in one of the ListBoxes and they are called List1PromTst, List2PromTst, and List3PromTst and they manage the state of flags ItmSlct1, ItmSlct2, and ItmSlct3, respectively.

As an example. If a user clicks on a list item from ListBox1, the click event calls List1PromTst
below is the code:

Sub List1PromTst
   Ctrl1=oPkgDlg.getControl("ListBox1")
   x=Ctrl1.getSelectedItemPos  'Test for a selected ListBox item
   if  x>=0 then
      if ItmSlct1 = 0 then     'Test if this is a first click in this ListBox1
         ClearPrevStates(1)  'Clear all other flags except ItmSlct1
         ItmSlct1 = ItmSlct1 + 1   'Increment ItmSlct to one
         exit sub
      else
         call DsplyPromoDlg  'Item has been selected twice, call submenu dialog
      end if
   end if
end sub

If the flag is zero, this routine calls a routine called ClearPrevStates, which zeros out all the other flags except ItmSlct1, which is incremented to one. If it is greater than one, then the routine DsplyPromoDlg is called to display the submenu.
Now if the user clicks on an item in ListBox1 and then an item in ListBox2, then the event routine ListPrompt2 will be called and ListBox2's flag (ItmSlct2) will be zero by the previous ListBox1 event. The code in ListPrompt2 will clear all the ItmSlct flags and set ItmSlct2 to one by calling routine ClearPrevStates(2)

Here is the code for clearing the all flags except the one specified in "NewState". You'll note the first test is to see that the dialog package reference "oPkgDlg" is active before the rest of the code is run. It then tests for which flag is going to be incremented and which flags are
to be cleared

sub ClearPrevStates(NewState)
    'clear any previous selected listbox states
    if Not(IsNull (oPkgDlg)) then 'is notnull
       for x=1 to 3
          if x<>NewState then
             'This flag does not match the current flag, clear any selected item in the ListBox
             Ctrl1=oPkgDlg.getControl("ListBox" & format(x))
             CurSelectPos=Ctrl1.getSelectedItemPos
             if CurSelectPos >=0 then
                oResetSelection = Ctrl1.SelectItemPos(CurSelectPos, False)
             end if
             select case x
                case 1
                   ItmSlct1 = 0
                case 2
                   ItmSlct2 = 0
                case 3
                   ItmSlct3 = 0
             end select
          end if
       next x
   end if
end sub

Then I created a routine called ClearSelctStates. This routine is called when you close a submenu dialog and it clears all the flag variables and resets any highlighted (selected) listbox items.

sub ClearSelctStates
   'clear any previous selected listbox states
   if Not(IsNull (oPkgDlg)) then 'is notnull
      for x=1 to 3
         Ctrl1=oPkgDlg.getControl("ListBox" & format(x))
         CurSelectPos=Ctrl1.getSelectedItemPos
         if CurSelectPos >=0 then
            oResetSelection = Ctrl1.SelectItemPos(CurSelectPos, False)
         end if
      next x
  end if
  ItmSlct1 = 0
  ItmSlct2 = 0
  ItmSlct3 = 0
end sub

A Calc Example for labeling columns

Labeling Columns macro examples

An Excerpt From my book OpenOffice Calc Solutions CookBook

Sometimes, if you are building a large spreadsheet system that you may want to duplicate the setup over and over again. It's worth the time to have a macro routine create and name sheets and label areas of the individual sheets.

I actually documented this coding example using three different methods. The method below will increment through a series of column positions using the .getCellByPosition. In my book I  demonstrate two other ways to label the columns using the .getCellRangeByName command.

Labeling Columns Examples

Method 1 Accessing Cells with  .getCellByPosition

The subroutine below demonstrates how a macro can access a sheet called “Sheet6” and pre-label a set of cells for the columns “D” through “G” on row 2. To run this code, edit this code into a module. Open a spreadsheet and make sure you actually have a Sheet6 or modify the code for a sheet name that already exists. NOTE: Sheet names are case sensitive. From the spreadsheet click on tools-run macros and from the module tree and your macro lists pop up. Select the macro. 

sub SheetSetupTest1
   'first create variable as an array of label strings
    Labels=array("","Product Name","Model Number","Unit Cost","Time to Install")
    dSprdSht=ThisComponent 'call the already opened spreadsheet
    CurrentSht=dSprdSht.getSheets().getByName( "Sheet6" ) ' Access calc sheet by its name
    'loop thru and label the cells 
    for x=1 to 4
       'place labels on columns D2 through G2
       oCell=CurrentSht.getCellByPosition( x+2, 1 )
       oCell.setString(Labels(x))
   next x
end sub

Here is the result:

 You'll notice that cell spacing is too good, but that can be fixed by keeping an array of cell sizes, using fixed size fonts, or a couple of other techniques.

Method 2 Accessing Cells with  .getCellRangeByName

Another method is to use two arrays. One array for the labels and one for the column letters.

This method uses the .getCellRangeByName command

sub SheetSetupTest2
   'create variable as an array of label strings

   Labels=array("","Product Name","Model Number","Unit Cost","Time to Install")
   'create variable as an array of column names
   Columns=array("","D","E","F","G”)
   'loop thru and label column heading cells
   for x=1 to 4
      oCell=CurrentSht.getCellRangeByName(Columns(x) & "2")
      oCell.setString(Labels(x))
   next x
end sub

Method 3 Incrementing the Column name string

The third method uses a custom function I created and describe in my book. The function

is called nxtColLtr (ColumnString)   The next-column-letter function takes a column letter or

letters as an argument and returns the next column letter(s) value.  The advantage to this method is you just need to specify a starting column value. This is handy if you have to shift the labels a few times in your design. The nxtColLtr function was tested to increment from column "A" through column "AMJ".

nxtColLtr(columnString)  function Examples:

nxtColLtr("A")  returns "B"

nxtColLtr("Z")  returns "AA"

nxtColLtr("AZ") returns "BA"

Here is the example using the nxtColLtr function

sub SheetSetupTest3
   'create variable as an array of label strings 

   Labels=array("","Product Name","Model Number","Unit Cost","Time to Install")
   'Set StartCol variable for first column heading to label
   StartCol="D"
   for x=1 to 4
      oCell=CurrentSht.getCellRangeByName(StartCol & "2")
      oCell.setString(Labels(x))

      StartCol=nxtColLtr(StartCol)   'get the next column name string
   next x

end sub

Solution Books Table of Contents

- Just to give you an idea of what kinds of solutions and design topics I've explained and documented in my Solution Books, The Table of Contents for each book is listed below:

OpenOffice Calc Macro Solutions Book

OpenOffice Writer Macro Solutions Book

OpenOffice Draw Macro Solutions Book

Appendix A talks about dialog components and is a common appendix to all the books

Calc Solutions Table of Contents

My OpenOffice CookBooks

How did the series come about?

    This OpenOffice Calc Macro CookBook 

What is Calc

Cell functions in Calc versus Excel

Calc API calls you need to know about

Macro code to access calc files

ThisComponent method

Creating a New Calc document method

Opening an Existing Calc document method

Accessing sheets and Cells

Accessing Cells

Examples of Access Cells by CellName or by Column/Row

Labeling Columns Example - By .getCellByPosition

Labeling Columns Example - By .getCellRangeByName

Labeling Columns Example – Adjusting Column Widths

Labels with Fixed width Font

Hiding Columns

Incrementing column names – nxtcolLtr function

Dynamic Test Indicators in Calc

A Calc Cell Style Workaround

Custom Cell Functions

Findnth (item within a) cell function

Personhours cell function

Array or cell reference?

Converting values with labels to standard Units

StatRollUp Cell Function

The formulaLocal Property – accessing the cell range string within the cell function call

AbsoluteName = changing functionality by cell function's location

Writing Calc Subroutines with Dialogs

A Sheet Group Navigator

Sheet Navigator Macro Example

Our first Dialog

Openoffice Bug or Feature

Custom Search Routines

Client Search

ClientSearch dialog and code

Access to Dialog and calc sheet

Basic Search Loop

Aligned columns in a listbox

Adding wildcards to the Search

Birthday Search

Access to Dialog and calc sheet

Search Setup and Preload

BirthDate Search Loop

Adding a search option

Using desktop functions to cut and paste sheets

dispatchURL

CopySheetToNewDoc

The Classic MS Office Example – an interactive two listbox Dialog

Finding an online solution and an API bug

Loading ListBox1 (AddItem and AddItems methods)

The => CommandButton(getSelectedItemPos method)

The <= CommandButton

Iterating Items within a ListBox

Appendix A - Dialog Controls Overview

Appendix B – Cell Formatting Fonts, Bolding, and Color Effects

Using the Debugger to find cell values

Left, Right, and Center Justify Cell text:

Setting Cell Font

Cell Font Height

Setting Font Color

Setting Background Color

Bolding Cell Text

Setting Line Wrapping

Underlining Cell Text

Two Underlining methods

Table Borders

Appendix C – Client Search and Birthday Search Tools

Client search Dialog box

Birthday search Dialog box

Back to top of Page

Writer Solutions Table of Contents

My OpenOffice CookBooks

How did the series come about?

This OpenOffifce Writer Macro CookBook 

Macro code to access writer files

ThisComponent method

Creating a Writer document method

Opening an Existing Write document method

Writer Document's collection paradigm

writer Tables - Mailing Labels Macro

Enumeration objects

Manipulating Documents with Cursors

Using text cursors

MoveCursorToText Example

MoveCursorToText

Automating Documentation

Module Mailer

LoadMailerOptionsDialog

Interactive Dialog Control routines

Basic MoveClient2MailList operation

Accessing Tables within Writer

Globals and main routine setup

Testing for Paragraphs and Tables

Retrieving Table Cell strings

Macro Book Construction

OpenOffice API calls to build writer documents

Using text cursors

BuildNewDoc code

LoadTempltFile

LoadContentAnHdrs

WriteContentLines

InsertingTableOfContent

Working with Frames – The Writer portion of the two listbox label making example

SOAP Example Extended

Dialog SoapSetup

Module Create Soaps

CopySheetToNewDoc(TempltType as Integer)

Appendix A - Dialog Controls Overview

Appendix B – Contract Automation Example and Code 59

Scrubbed Contract Document 61

Back to top of Page

Draw Solutions Table of Contents

My OpenOffice CookBooks 4

How did the series come about? 4

This OpenOffice Draw Macro CookBook 5

What is OpenOffice Draw 5

Vector versus Bitmap drawing systems 6

Draw and Impress Applications 6

Macro code to access draw files 6

ThisComponent method 7

Creating a New Draw document method 7

Opening an Existing Draw document method 7

Drawpages and Masterpages 8

DrawPages linked to MasterPage 8

Pages can have the same name 8

Draw Macro Basics 9

Portrait orientation and dimensions 9

Basic draw macro Routines 10

Adding Shapes to a page 10

Creating shapes routine 11

Erasing Shapes from the drawpage 15

Drawing Metadata 16

ConnectorShapes 16

Scanning a drawing for Metadata 18

Which Gluepoint? 21

How About Drawing Objects with Property Supports 23

Object Description Field 2

Appendix A - Dialog Controls Overview 28

Back to top of Page
 

Appendix A - Dialog Controls Overview

X-Ray or Debugger

Setting the Date in DateField

DateField Options

NumericField Control

CurrencyField Control

TimeField Control

ListBox Control

Aligned Columns in a ListBox

The Global Dialog reference

ComboBox Control

CheckBoxes and Option Controls

OptionButton Control

CheckBox Control

PatternField Control

ImageControl Controls

FixedLine Controls

ScrollBar Controls

ListBox and value list storage decisions

Dynamic ListBox item loading

Back to top of Page