[Calc] Error right at document opening

[mraskin]

Hi,

I have a document with some functions written in basic.
they work OK but sometimes , when I open the book, I am getting an error when the function is evaluated. : Property or method no found : Sheets
And I have no other solution than killing the process with taskmanager

here is the snaphot.

https://ibb.co/mSDOPv

Do you know of anyway to solve this ?
This looks rather strange that sheets is not known in this context ...

thanks!
Michel.

[JohnSUN-Pensioner]

...sheets is not known in this context ...

Yes, Michel, in context of Basic IDE sheets is unknown - there are no sheets. Just make sure that you run the macro when active window (in terms of the macro - "ThisComponent") is a Calc spreadsheet


Please try this variant:

Code: Select all

Function consolide (mois As String, cellule As String) As Integer
Dim oDoc As Variant, maFeulle As Variant
Dim i As Integer, sum As Integer
Dim nomFeulle As String
Dim oSheets As Variant
	GlobalScope.BasicLibraries.LoadLibrary("Tools")	
	oDoc = ThisComponent
	sum = 0
	If GetDocumentType(oDoc) = "scalc" Then
		oSheets = oDoc.getSheets()
		For i = 0 To oSheets.getCount()-1
			maFeulle = oSheets.getByIndex(i)
			nomFeulle = UCase(maFeulle.getName())
			If (nomFeulle <> UCase(mois)) And (InStr(nomFeulle,mois)>0) Then 
				sum = sum + maFeulle.getCellRangeByName(cellule).getValue()
			EndIf
		Next i
		consolide = sum
	EndIf
End Function

Just call it like as

Code: Select all

=CONSOLIDE("Sheet";"B4";RAND())

[mraskin]

Hi,

thanks a lot for your answer and the code example.
I am coming from Excel, ans this is very hard for me to move to Calc

And, sometimes, I just hate Calc : see below

https://ibb.co/gKHdPv

I did a typo in a variable, and when saving I am getting this looping pop-up, telling it cannot save because there is an error. Hey man, remove that pop-up so I can fix my typo !!!
After clicking more than 100 times on this OK button, I'm still getting this pop-up .
This book has about 250 sheets, don't know if this has any link with the number of times I may click on OK...)

Do you know a way for exiting this state I have tryed CTRL-C, ESC, CTRL-BK ... nothing does the job.
And If I kill the job, I probably have to redo my edit (OK, not a big deal, but very annoying as I hate doing twice the same thing in a row !

EDIT: OK, I have been lucky this time. After killing the process and reopening the workbook, OO recovered it. I hope this process is safe ...

thanks,
Michel.

[RoryOF]

It would not be surprising if there was a limit of 256 sheets.

Have you tried Ctrl S to Save, then Alt F4 to close the application?

[mraskin]

Hi,

no I haven't tried but look like no command is available as long as the pop-up is active. I'll try next time !
I also tried CTRL+click, SHIFT+click on the pop-up, no way to get rid of it

I have 237 sheets actually (1 per open day) . I know, not very efficient, especially at time of saving ... this takes ages (1mn30 !) and also I have refreshing issues sometimes. This may be due to this as well.
I'd better have a form with a dabtase behind but have no time to develop that

thanks,
Michel

[RoryOF]

I have seen rare occasions where a popup button was not clickable by the mouse, although one could move the cursor to it by the mouse; such button might be selectable by using the tab key to access it, and might respond to Enter when one had tabbed to it.

[mraskin]

The problem is not that it is not clickable, the problem is that the tool seems to loop.
Anytime I OK the pop-up, it comes back again. As if the tool was looping on the Basic error problem.