An exceptional error

Auntyjack · May 6, 2024, 10:47pm

Hi folks
Can anyone tell me why this code . . .

rem Get “Capital” (CBA Transactions) sheet and ranges
Dim CapTrans, CapTotal
CapTrans = ThisComponent.Sheets(“CBA Transactions”)
CapTotal = CapTrans.getCellRangeByName(“CapTotal”)

. . . is throwing this . . .

BASIC runtime error.
An exception occurred
Type: com.sun.star.uno.RuntimeException
Message: ./sc/source/ui/unoobj/cellsuno.cxx:4742.

. . on the last line ? The sheet was copied from another file and it came over with all its named ranges intact - which I’ve tidied up to suit its new context - and everything looks good. Is it possible that there is some left over stuff from before it was copied ?

Cheers,
Aunty

Wanderer · May 7, 2024, 4:12am

My guess is you mix access to sheets by index and by name

CapTrans = ThisComponent.Sheets().getByName("TabelleCBA Transaction"

Auntyjack · May 7, 2024, 4:38am

That’s fantastic. It worked, but . . . why didn’t . .

Equity = ThisComponent.Sheets(Equity)
OwnerContribs = Equity.getCellRangeByName(OwnerContribs)

. . from only a couple of lines up fail like the other one? The only difference is that the sheets fail without .getByName were copied to the file that originally contained the sheet “Equity”. Maybe there’s something about them that stops them from being included in the Sheets array when the file loads . . .
Anyhow, thanks for the solution

Wanderer · May 7, 2024, 5:58am

Your Equity = ThisComponent.Sheets(Equity) has no quotes around Equity, so I guess this will use the index-number of the sheet

Compare here:

https://www.dannenhoefer.de/faqstarbasic/WiekannmanaufSheets(Arbeitsbltter)zugrei.html

Auntyjack · May 8, 2024, 12:22pm

Ha ! That caught me for a minute but no, the actual code did have quotes. There was definitely something dodgy about that copy and paste.

sokol92 · May 8, 2024, 12:34pm

the same as

CapTrans = ThisComponent.Sheets.getByIndex("CBA Transactions")

Since the argument is not a number, it will be converted to 0 and the CapTrans variable will contain a reference to the first sheet of the document.
A possible source of confusion is the difference in behavior between LO Basic and Excel VBA.

fpy · May 7, 2024, 3:57am

is CapTrans a valid sheet obj ?

Auntyjack · May 7, 2024, 4:14am

Yes. It’s the sheet named “CBA Transactions”
CapTrans = ThisComponent.Sheets(“CBA Transactions”)