IDE behavior, defining events, dynamic named ranges in macros

asked 2018-08-12 18:48:57 +0200

osxtra gravatar image

updated 2018-08-12 18:51:25 +0200

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 ... (more)

edit retag flag offensive close merge delete