UBBFriend: Email This Page to Someone!
  uCalc Message Forums
  Archives: Old messages for uCalc Fast Math Parser 2.0
  Tip of the Month: Implementing spreadsheets

Post New Topic  
profile | register | preferences | faq | search

next newest topic | next oldest topic
Author Topic:   Tip of the Month: Implementing spreadsheets
Daniel Corbier
posted 07-06-2000 02:14 PM     Click Here to See the Profile for Daniel Corbier     Edit/Delete Message Reply w/Quote
UCalc Fast Math Parser can serve as the underlying calculating engine for a spreadsheet program. However, the way to do it is not necessarily so obvious.

An inefficient way to do it would be to declare a variable for each cell, and parsing/evaluating cells and dependant cells each time. Some have discovered that there’s a significant speed penalty when you define large numbers of variables (this affects the speed of ucParse, and other functions that depend on ucParse, such as ucEval. But the speed of ucEvaluate is not affected).

Anyway, building on last month’s concept of UCalc “arrays”, spreadsheet cells can be mapped out onto an array. Also, instead of repeatedly parsing/evaluating cells with ucEval, cells need not be parsed more than once when they are changed. A cell is parsed with ucParse, and then that cell, along with all dependent cells are quickly updated with ucEvaluate, which is significantly faster.

The README.TXT file that comes with the sample spreadsheet program contains more explanations. The zip file includes a simple ready-to-run spreadsheet program in Visual Basic, C++ Builder, and Delphi. The code is just a quick primer. A number of additional things can be done to make the spreadsheet more sophisticated.

Download the sample spreadsheet code from http://www.ucalc.com/ucspread.zip (50K)

IP: Logged

posted 07-31-2000 03:31 PM     Click Here to See the Profile for ali     Edit/Delete Message Reply w/Quote
How would you avoid a loop condition ?

consider the scenario:
In the spreadsheet, if you have a var 'x' in cell 1 which has a dependant var 'y' in cell 2, but, var y has a math expression as it's val, but, the math expr uses var 'x' from cell 1. This is a direct 1->1 dependancy, updating the value of 'x' will update 'y' which in-turn will update the value of 'x'.
To complicate things, how about if there is a third variable 'z' whcih will create a dependancy list as follows:


Any suggestions other than checking thoroughly among the dependant variable list to avoid creating such a loop ?

I would like to hear from anyone, if they have any better solutions to the potential loop problem.


IP: Logged

Daniel Corbier
posted 08-13-2000 10:16 PM     Click Here to See the Profile for Daniel Corbier     Edit/Delete Message Reply w/Quote
There may be situations where you would actually want to be able make such circular references. Microsoft Excel has a setting that you can change which determines how many iterations it goes through before stopping (their default is 100). Using this concept you can set a global variable counter that exits after a predetermined number of iterations.

If you are using the VB sample, then in Module1 (ucSpread.Bas), beneath the line that says: Public CellEq$(), ... , place

Public CircularCount

Now within the Sub SetCellValue routine, place this line as one of the first statements of the Sub routine:

CircularCount = 0

Now at the beginning of the UpdateDepCells routine, place the following lines of code:


CircularCount = CircularCount + 1
If CircularCount = 100 Then Exit Sub

This way, it won't get locked up in a circular loop. I hope this helps.

IP: Logged

All times are Eastern Time (US)

next newest topic | next oldest topic

Administrative Options: Close Topic | Archive/Move | Delete Topic
Post New Topic  
Hop to:

Contact Us | uCalc

Copyright © 2000-2007 by Daniel Corbier

Powered by: Ultimate Bulletin Board, Version 5.42a
© Infopop Corporation (formerly Madrona Park, Inc.), 1998 - 1999.