Spreadsheet Lingo

July 9, 2000
by Pat McClellan

Dear Multimedia Handyman,

I need to create a movie with a few fields in rows and columns (let's say 4x4). I need to have fields that act like Excel fields in a spreadsheet, with the last cell in each row holding the sum of the previous fields in the row, and the last column holding the sum of the previous fields in the column. I also want to limit the number of characters in each field.

Dejan Cicic

Dear Dejan,

Any time you wish to simulate the actions of another application in Director -- whether it's a word processor, graphics app, or spreadsheet -- you should remember that those original applications were developed over a long period of time with huge development budgets. Therefore, anything you create in Director will be an approximation. For example, my demo won't include the ability to enter formulas in the cells.

Whether you're dealing with fields or text members, the process will be the same.

user selects field
user presses key
keyDown handler on that sprite tests key for acceptability
accepted character is appended to field's text string
that field's value is updated in a global variable called gFieldList
command is sent to all sprites to updateValues
fields containing sum formula extract values from the global gFieldList and calculate sum
a string of the sum is placed in the text of sum fields

Here's what it'll look like:

Director 7 download for Mac or Windows.

To accomplish this, start by naming your fields according to their respective column and row. For example: "A1", "B1", "C1", "A2", "B2", etc. Next, create the fieldInput behavior so that the beginSprite handler converts the name of its member into the name of the property in gFieldList. Here's the behavior:

property pSprite, pMem, pFieldName, pAcceptList, pChars
global gFieldList

on getPropertyDescriptionList me

pdlist = [:]

addprop pdlist, #pAcceptList, [#comment:"Accept which keys?", #format:#string, #default:"0123456789."]
addprop pdlist, #pChars, [#comment:"How many characters accepted?", #format:#integer, #default:4]

return pdlist

end getPropertyDescriptionList

on beginSprite me

  pMem = sprite(me.spriteNum).member
  pFieldName = symbol(pMem.name)
  if voidP(gFieldList) then gFieldList = [:]
  setaProp gFieldList, pFieldName, 0

  pMem.text = ""
  pMem.wordWrap = FALSE
  pMem.editable = TRUE
  pMem.autoTab = TRUE

end beginSprite

on keyDown me

if the key = TAB then pass

  if the key = BACKSPACE then
    val = pMem.text
    delete the last char of val
    pMem.text = val
  else if (pAcceptList contains the key) then
    if pMem.text.length < pChars then
      pMem.text = pMem.text & the key
    end if
  end if

  the selStart = pmem.text.length
  gFieldList[pFieldName] = value(pMem.text)
  sendAllSprites (#updateValues)

end keydown

You'll notice that the getPropertyDescriptionList allows you to specify which characters will be accepted. The default accepts 0-9 and the decimal point. It also allows you to specify how many characters will be accepted into the field. The keyDown handler checks both of those factors and decides whether to ignore the keystroke or pass it on appropriately. The last line sends a message to all sprites to update their values.

That command is received by the subFields behavior that we've placed on the fields that are to contain the sum values. Those fields need to know which fields they're supposed to sum, so I've built that into the getPropertiesDescriptionList handler. All you need to do is drop the behavior on the sum sprites and specify which fields should be summed.

property pMem, pSumList
global gFieldList

on getPropertyDescriptionList me

pdlist = [:]

addprop pdlist, #pSumList, [#comment:"Sum list:", #format:#list, #default:[#a1,#a2,#a3]]

return pdlist
end getPropertyDescriptionList

on beginSprite me
pMem = sprite(me.SpriteNum).member
updateValues me
end beginSprite

on updateValues me

  sum = 0
  repeat with thisVal in pSumList
    sum = sum + gFieldList[thisVal]
  end repeat

pMem.text = string(sum)

end

You asked to make this like Excel. In Excel, you can move around the spreadsheet using the arrow keys, but to do that in Director seems much more complicated than it's worth, particularly when the ability to tab from field to field is built in. If you want, you can write that part yourself. What you'll have to do is add some additional testing to the keyDown handler to test for the arrow keys (keyCodes 123, 124, 125 and 126). Then, you'll need to convert those keyCodes into the appropriate commands to change the keyboardFocusSprite. This conversion will depend on the way you lay your sprites out in the score.

There are any number of other refinements you could add to make it more like Excel, such as a field at the top where values can be entered, or entering formulas directly into the fields. Perhaps some other readers would like to contribute these enhancements to the DOUGthreads forum for this article. Good luck with your program.

Patrick McClellan is Director Online's co-founder. Pat is Vice President, Managing Director for Jack Morton Worldwide, a global experiential marketing company. He is responsible for the San Francisco office, which helps major technology clients to develop marketing communications programs to reach enterprise and consumer audiences.