Hi, All,
Am fairly new to coding in the LO IDE, though it does seem to be a pretty good clone of the old VB IDE I used 20 years ago. Fun dusting off those cobwebs!
I must congratulate the scads of people working on LO. It has matured quite nicely since forking from OO. Having gone Mac back in '09, I initially used OO but switched as soon as the fork was released. Gosh, has it been seven years already? 
Am currently automating a Calc spreadsheet so this is the first time I’ve really dug into coding LO.
Am on 6.1.0.3, OSX_X64
A few questions:
1: Is there a way to refresh the function list in the IDE itself without restarting LO? When assigning events, form control does pickup any new code I’ve written and shows the subs/functions alphabetically, but the IDE “Object Catalog” window doesn’t seem to reflect code changes until a restart.
2: In Design mode, is there a more direct way to assign subs/functions to controls, rather than manually clicking on the event then drilling down to the routine in question? The reference displayed in the various events is read-only, and the “Assign Action” popup always starts with all modules collapsed. Would save some steps if it would either remember the tree state, or even more ideally if one could just edit the string of the routine being called. If typing directly, the events section could have a simple error handler to ensure you had typed a valid routine.
3: In a macro, why do references to dynamic named ranges fail?
Let’s say I have a sheet called “mySheet” starting at A2 with 7 columns, and a named range called “myRange”
A standard named range might be expressed in Calc itself such as
$mySheet:$A$$2:$G$5001
hard-coding the number of number of 5,000.
A dynamic range might be expressed as
OFFSET($mySheet.$A$2,0,0,COUNTA($mySheet.$A$2:$A$1048576),COUNTA($mySheet.$A$2:$G$2))
where the range would automatically contract and expand as rows are entered into or deleted from it.
In Calc, this works as expected. I can verify the number of rows MyRange contains with a cell containing the formula
“=COUNTA(MyRange) / 7”
(i.e., the total number of cells returned by COUNTA divided by the number of columns).
Appending or deleting rows of data changes the formula result as one would expect, so the range appears to have been defined correctly.
Now, let’s say I have a sheet listener running a sub when cell contents change. I want to search the range and see if what was entered exists. (I am not entering data into the range, or affecting it in any way. It’s just for lookup.)
Let’s say that when the macro runs, there is data in the range from $A$2 to $G$1240
In a macro, one would access that range by something along the lines of
oRange = ThisComponent.NamedRanges.getByName(“myRange”)
which should return an object containing, amongst other things, a “ReferredCells” property containing an “Absolute Address” property showing the absolute address of the range ($A$2:$G$1240), and a “Data” property containing an array of the range’s contents (Data(0)->Data(0)(0), Data(0)(1), Data(0)(2), etc.)
This fails with a dynamic range. “ReferredCells” is null, so of course there is no “Data” property. Using a standard named range both properties are present and populated as expected.
Sure, I could break the range definition out into multiple steps, determining the last full row, then creating the object such as:
’ Create the sheet object
oSheet = ThisComponent.getSheets().getByname(“mySheet”)
’ Search column A of the sheet, and assume there are no empty cells at the top of the column
iLastRangeRow = oSheet.Columns(0).QueryEmptyCells.RangeAddresses(0).StartRow
’ Create the range object
oRange = oSheet.getCellRangeByName(“A2:G” & iLastRangeRow)
but it would be nice no know why this is failing.
Any pointers on these questions would be most appreciated. Thanks!