Can't get named range in macro

[amlbl]

Edit: Split from solved nine-year-old topic, since amlbl's problem/solution may be different and a new poster requires a new topic to discuss their problem. -- MrProgrammer 2020-01-10 07:40 UTC

As far as I see, this topic is far from solved.

In my simplified text sheet (see attached doc), I have …
1. A cell B1 withe a named array defined for it (the name is Veld1)
2. A cell A1 with a call to a simple user defined function ( =TESTPROC("Veld1")) which returns the parameter
3. The function, before returning the parameter, searches for the NamedRanges of the curren document

When starting the document, I run into a runtime error (see attached doc).

When the same function call is added to an other cell when the sheet is completely build, there is no problem.

I think that the NamedRanges object is created after the creation of the first sheet.

Is this assumption correct and/or is there a solution for my problem (making the example working correct when starting the document)?

Thks

[Lupp]

(Sorry if this sounds dull.)
Your macro seems to be located in the local 'Standard' library. Therefore you can also call it while a document not being a SpredsheetDocument is active (ThisComponent). Such a document doesn't know about named ranges, of course.

For me your code works.
Since you didn't attach your example file, we cannot test it for special reasons probably making this fail.

[Zizi64]

Code: Select all

TabelDim

Do not use same variable name for different objects.

[Lupp]

Do not use same variable name for different objects.

Of course, this is correct and necessary advice, but a violation may be seen just as "bad style" as long as not the variable is used again supposing it refers to the object that was assigned before the reassignment. Variables get reassignments frequently in programming code and statements often contain a reference to the variable on the left also on the expression side of the assignment. (In Basic it would even work if the reassignment causes a change of the actual type of a variant variable.)

Code: Select all

REM  *****  BASIC  *****

Sub Main
Dim h As Object
h = ThisComponent
h = h.NamedRanges
End Sub

works for me in AOO 4.1.5 if ThisComponent is a SpreadsheetDocument.

The flaw you mentioned cannot be the cause of the indicated error.

[amlbl]

To Lupp from München ...
You stated: 'Your macro seems to be located in the local 'Standard' library. Therefore you can also call it while a document not being a SpredsheetDocument is active (ThisComponent). Such a document doesn't know about named ranges, of course'.
I really do not understand why OO should not know which type of document ThisComponent is, and therefore why OO should not know about named ranges when the active document is a spreadsheet.

I attached a document named Test.ods in my previous post.

To Tibor Kovacs, Hungary ...
Of course I know that same names for different variables is bad coding practice.
Normally I write it as Tabledim = Thiscomponent.NamedRanges, but to make it clear where the runtime error appears, I splitted this statement in two.

[Lupp]

I really do not understand why OO should not know which type of document ThisComponent is, and therefore why OO should not know about named ranges when the active document is a spreadsheet.

Did you actually read the statement you quoted?

Of course, AOO will know for wich document 'ThisComponent' the macro was called, and of course I know what example file you attached. (It did NOT contain a macro.) My suggestion was to check if probably the user might not have known.

What I cannot know is how many documents of what type were open when you started the macro on your system. If more than one document is open, it is a (probably far-fetched) possibility that a document different from the one the user is thinking of was active. Only because the reported issue is very strange I took the risk to hint to this fact, and to get misundertood the way you did.

If the macro was run from a library contained in the .ods document, it should refuse to run for different one. Therefore my stressing the fact that the attached image showed the local library, didn't it?

Every different explanation excluded: May your user profile be corrupted?

[amlbl]

Dear Lupp,

I had the problem in a far more complicated calc doc.
In order to post it on the forum, I created a new doc (test.ods), with only the bare necessities.
The macro itself is also a stripped version. I didn't include it as a macro, but as a screenshot on the moment where the error occurred, I thought this was more clear.
The reason why I used Standard lib is that I (as an OO/Basic novice) wasn't aware that you can include a macro in a lib specific to a doc (up to now).
The execution took place on a clean windows 10 machine with no other applications active.

Since (as I suppose), OO first builds all of his sheets and fields and then fills objects like NamedRanges, but also Sheets,...., the reason for the error looks obvious. After complete build, OO runs (as I suppose) a second time through all cells. At the end, everything will be as expected.
My problem, in this special case, can be solved by adding an On error clause, as I show beneat.

Of course, this is a fix for one problem, not a general appliable one.

Sy


'----------------------------------------------------------
Function TestProc (Naam As String) As String

Dim TabelDim As Object

on error goto errorlabel
TabelDim = Thiscomponent
TabelDim = TabelDim.NamedRanges
errorlabel:
TestProc = Naam

End Function