Error using ThisComponent on startup

[MokeyIB]

I am trying to replace a HUGE formula that keeps getting bigger as I add sheets with my first macro function. I am a programmer. It's a simple recursive function. I'm trying to find the first occurrence of a name searching back through successive sheets until it is found. The function works properly when I insert it into a cell. But when I save, close, and reopen my spreadsheet, I get an error like "BASIC runtime error. Property or method not found: getSheets". I'm guessing this is because ThisComponent is not initialized yet. I've tried searching this forum and the internet in general, but can't find anything helpful even though this should be a common problem if I'm not doing something stupid.

Here is a simple function that gives the same error. It gets the name of the current sheet.

Code: Select all

Function SheetName( SheetNum As Integer )
SheetName = ThisComponent.getSheets.getByIndex(SheetNum-1).getName()
End Function

I call it using:

Code: Select all

=SheetName(sheet())

in a cell

Even if this is not the best way to get the name of the current sheet, I still need to figure out how to access ThisComponent for my recursive function. Here's the code for my recursive function in case that helps. It's called using =FindName("name", sheet()) (which starts the search on the next sheet, since sheet() starts counting at 1 and getByIndex() starts counting at 0).

Code: Select all

Function FindName( Name As String, CurSheet As Integer )
If Name = "" Then
  FindName = ""
Else
  FindName = FindNameInSheet(Name, CurSheet)
End If
End Function

Function FindNameInSheet( Name As String, SheetNum As Integer )
LastSheet = ThisComponent.getSheets().Count
If SheetNum = LastSheet Then
  FindNameInSheet = ""
Else
  Sheet = ThisComponent.getSheets().getByIndex(SheetNum)
  NamesCol = Sheet.getCellrangeByName("A1:A100")
  SearchDesc = NamesCol.createSearchDescriptor()
  SearchDesc.SearchString = Name
  SearchResult = NamesCol.findAll(SearchDesc)
  If IsNull(SearchResult) Then
    NextSheet = SheetNum + 1
    FindNameInSheet = FindNameInSheet(Name, NextSheet)
  Else
    FindNameInSheet = Sheet.getName
  End If
End If
End Function

[kingfisher]

If the code is not in the system 'Standard' module, the difficulty may relate to the fact that the library containing it has NOT been loaded on start up.

[B Marcelly]

As you have discovered, the document is not completely available when cells are recalculated during document load.
This is a limitation of the implementation.

A workaround is to ignore errors in the macro, then force recalculation when the document is ready, at event "View created".

Code: Select all

ThisComponent.calculateAll

Your function FindName/FindNameInSheet is more complex than needed. Recursivity is not needed, a simple loop is enough.

[JohnSUN-Pensioner]

Maybe you don't need a macro to perform this particular task? Try this

Code: Select all

=RIGHT(CELL("FILENAME");LEN(CELL("FILENAME"))-FIND("#$";CELL("FILENAME"))-1)

[MokeyIB]

Johnsun-Pensioner: Can you explain what your formula is doing? I'd rather not bother with a macro, but the formula I'd figured out (and I am no spreadsheet whiz) was using INDIRECT and LOOKUP in an ever more complex nested IF/THEN/ELSE which was not a good solution!

B-Marcelly: You're right, no need for recursion, that's just how I was thinking about the problem because of my nested if/then/elses. I will try your idea about ignoring errors and using the event. I may check back in if I can't figure out how to do it from the documentation.

[JohnSUN-Pensioner]

Yes, of course. Function = CELL("FILENAME") returns a long string 'file:///Disk:/Path/Directory/FileName.ods'#$SheetName
All other actions in the formula - is cutting this longest line by symbols #$
If you think that this formula is too cumbersome, you can hide it in a named range (for example, SheetName) and use it where is necessary =SheetName

[MokeyIB]

This is working, but it's really slow on startup. I'm sure it's the calculateAll call. Is there another way to do this?

[JohnSUN-Pensioner]

Of course, such a method must to exist. Let's look for it together.
Several times I read the code of the recursive procedure in the first message. I still don't understand what you are looking for in cells A1:A100.

[kingfisher]

This is working, but it's really slow on startup. I'm sure it's the calculateAll call. Is there another way to do this?

Just to be sure, are you are not using calculateAll during the loops ?

[RoryOF]

I have a vague memory of a fix for a similar problem which used a delay before the troublesome call was invoked. My memory is that this was felt to be rather inelegant.

[JohnSUN-Pensioner]

@MokeyIB I should note that there is a hidden threat of errors in your variant of search (and in my variant also). For example, if you try to find references to the sheet named Sheet2, it is quite possible to find a formula that refers to Sheet2_3 or to Sheet24

Correct variant of search would be to use a regular expression. Now I don't know how it will look the search string, sorry

Or look for all the formulas and parse them to extract the names of the sheets

Or look in the description of the АPI separate service to extract links from formulas. Calc somehow paints links to a different colors while editing the formula? So an appropriate mechanism must exist (IMHO)

[kingfisher]

Probably a silly idea but what about a 'wait' command before the existing code runs ?

[Lupp]

I do not actually know by what the problem is caused. The 'ThisComponent' object should, I think, exist and possess all its properties and methods during recalculation on loading a document. Maybe 'ThisComponent' is pointing to a loader object in the beginning. At least my simple test showed that it isn't NULL. Did somebody inspect that object using XRay or MRI ?

My workaround: Do not call custom subroutines explicitly using document properties on the first sheet.
I don't get the related error if (one of my few) functions using properties and methods of ThisComponent are only called from the second sheet (and those coming behind). As far as I can say this will also work if the second (or the other) sheet is selected for viewing. It will not work if the first sheet is hidden. Strange things happen.

Additing:
I forgtot to tell that I didn't get the "Premature-ThisComponent-Error" if the macro containing the call of methods of that object. was contained in the document itself (and macros enabled for the document).

[Rgemini]

The 'ThisComponent' problem has been causing me grief for ages. I have a user function that seeks the highest used row on a sheet, passing the sheet name as a parameter. Simples! But on loading, having clicked to permit macros to run, I get the 'ThisComponent' error. I tried using Xray, but got an error that the object didn't permit introspection.

The solution has been to implement the suggestion from B Marcelly (Oct 23, 2014):
a) put an error handler in the routine so it just stops silently and
b) associate a ThisComponent.CalculateAll macro with the "View created" event.

Heartfelt thanks! Although I feel I'm having to jump through hoops to do something that the system really should take care of automatically.