Have a spreadsheet with multiple, identical sheets, where each sheet contains a cell that is updated by a macro that sets the formula of the cell. The formula varies by only the single range of cells referenced by the formula.
I have read most of the Calc macro documentation and various examples on the Web. I also scrubbed through Ask LO for relevant solutions. I think I followed the advice of all that I read. So here is the problem, my troubleshooting steps, and the workaround that I’ve implemented. I’m submitting this issue so that you might determine if there is a bug in Calc, or indicate where I fell short in the coding.
Problem: Invoking method oCell.setFormula(sString), where sString contains the formula and oCell was previously set via oCell = oSheet.getCellByPosition(HICOL, HIROW), causes the cell to contain the #NAME? error. Also, via Watch in the IDE, I saw where the error code is 525. I was also able to see via Watch that after the method is invoked both formula and formulaLocal have the proper string. The proper string was also observed in Calc in the cell’s value bar. I tried using Detective to no avail - seeing what one expected to see. Also, with the error visible in Calc, when I double-clicked the cell, the cell would be bounded by a purple box.
Troubleshooting Steps: Within Calc, inserting a blank at the end of the string in the value bar and hitting Enter, corrected the error with the proper expected value being displayed. Also, when using the Function Wizard with the supposed cell error condition, the Wizard had no trouble displaying the formula. Without doing any changes in the Wizard and selecting OK, the error condition was corrected as well.
WorkAround: The above got me thinking that something is missing in the code and I needed to do something to make the cell be re-evaulated. I inserted the following line of code, and the error condition went away: oCell.FormulaLocal = sString
I would appreciate it if you could tell me where I messed up. One of the documents where I found how to set a cell’s formula came from " Useful Macro Information For OpenOffice.org" By Andrew Pitonyak.
The entire macro Sub follows:
Sub CalcAllHIs
Dim i as Integer
Dim SheetType as Integer
Dim NmbrSheets as Integer
Dim NmbrScores as Integer
Dim oSheets as Object
Dim oSheet as Object
Dim oCell as Object
Const SHEETTYPECOL = 0 ' sheet type is currently stored
Const SHEETTYPEROW = 41 ' in cell location A42.
Const NMBRSCORESCOL = 2 ' # of scores is currently stored
Const NMBRSCORESROW = SHEETTYPEROW ' in cell location C42.
Const MAXHDSCORES = 20 'sort only top 20 rows in HD order
Const HICOL = 10 ' HI is currently stored
Const HIROW = 1 ' in cell location K2
Dim sString As String
Rem Get all of the sheets in this spreadsheet
oSheets = ThisComponent.getSheets()
Rem Get how many there are
NmbrSheets = oSheets.getCount() - 1
Rem Process all individual golfer sheets, skipping all other sheet types
For i = 0 To NmbrSheets
Rem Get the current/next sheet in the list
oSheet = oSheets.getByIndex(i)
Rem Get the sheet type (1 => golfer sheet)
oCell = oSheet.getCellByPosition(SHEETTYPECOL, SHEETTYPEROW)
SheetType = oCell.getValue()
If SheetType =1 Then
Rem Make the current sheet the "active" sheet
ThisComponent.currentController.setActiveSheet(oSheet)
Rem Sort all 40 rows by Date (col 0) in descending order
Call SortRange("b2:J40", 0, FALSE)
Rem Get the total number of scores entered into this sheet
oCell = oSheet.getCellByPosition(NMBRSCORESCOL, NMBRSCORESROW)
NmbrScores = oCell.getValue()
Rem Construct a string of the cell range to sort.
Rem If NmbrScores is greater than 20, then restrict it to 20
if NmbrScores > MAXHDSCORES Then NmbrScores = MAXHDSCORES
sString = Left("b2:j" & (NmbrScores+1), 6)
Rem Sort top 10 rows by HD (col 8) in ascending order
Call SortRange(sString, 8, TRUE)
Rem Set today's date in the "as of:" cell
SetDate
Rem Make room for entry of new/future scores
ClrSpace
Rem Set the proper HI formula based on NmbrScores
oCell = oSheet.getCellByPosition(HICOL, HIROW)
Select Case NmbrScores
Case 1 to 6
sString = "=TRUNC(AVERAGE(J2:J2)*0.96,1)"
Case 7, 8
sString = "=TRUNC(AVERAGE(J2:J3)*0.96,1)"
Case 9, 10
sString = "=TRUNC(AVERAGE(J2:J4)*0.96,1)"
Case 11, 12
sString = "=TRUNC(AVERAGE(J2:J5)*0.96,1)"
Case 13, 14
sString = "=TRUNC(AVERAGE(J2:J6)*0.96,1)"
Case 15, 16
sString = "=TRUNC(AVERAGE(J2:J7)*0.96,1)"
Case 17
sString = "=TRUNC(AVERAGE(J2:J8)*0.96,1)"
Case 18
sString = "=TRUNC(AVERAGE(J2:J9)*0.96,1)"
Case 19
sString = "=TRUNC(AVERAGE(J2:J10)*0.96,1)"
Case Else 'assume 20
sString = "=TRUNC(AVERAGE(J2:J11)*0.96,1)"
End Select
Rem write the appropriate formula to the HI cell
oCell.setFormula(sString)
Rem Next line of code was needed to solve an unexplained issue where the previous LoC resulted
Rem in the HI cell getting error #NAME? (code 525). It "works" for now...
oCell.FormulaLocal = sString
Endif
Next
Rem Create a new entry into the Indexes sheet
NewIndexes
End Sub 'CalcAllHIs
edit: by @karolus fenced ``````code
If there is anything else you’d like me to provide, just let me know.
Regards,
fbang652