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.
[Calc] Error right at document opening
[Calc] Error right at document opening
Windows7+W10- libreOffice Version: 5.2.6.2
- JohnSUN-Pensioner
- Volunteer
- Posts: 876
- Joined: Fri Jan 14, 2011 1:21 pm
- Location: Kyiv, Ukraine
Re: [Calc] Error right at document opening
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 spreadsheetmraskin wrote:...sheets is not known in this context ...
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
Code: Select all
=CONSOLIDE("Sheet";"B4";RAND())
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Re: [Calc] Error right at document opening
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.
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.
Last edited by mraskin on Mon Aug 14, 2017 11:03 pm, edited 1 time in total.
Windows7+W10- libreOffice Version: 5.2.6.2
Re: [Calc] Error right at document opening
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?
Have you tried Ctrl S to Save, then Alt F4 to close the application?
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: [Calc] Error right at document opening
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
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
Windows7+W10- libreOffice Version: 5.2.6.2
Re: [Calc] Error right at document opening
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.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: [Calc] Error right at document opening
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.
Anytime I OK the pop-up, it comes back again. As if the tool was looping on the Basic error problem.
Windows7+W10- libreOffice Version: 5.2.6.2