How to reassign the address of a named range in Open Office VBA?

Question

In Excel VBA, I redefine the address of a named range with:

Ranges("MyRange").Cells(1).CurrentRegion.Name = "MyRange"

in the Worksheet_Deactivate event.

This way, after updating the spreadsheet, MyDataRange always references to the entire data. Ok with Excel, but when I tried to migrate this to Open Office VBA (OpenOffice.org 3.1.1 / OOO310m19 Build:9420) the ".Name" property of the Range isn't defined.

Then, I tried with:

ThisWorkbook.Names("MyDataRange").RefersTo = Range("MyDataRange").Cells(1).CurrentRegion

with no luck ("the property is Read Only" error)

Again, I tried with:

ThisWorkbook.Names.Add ("MyDataRange", Range("MyDataRange").Cells(1).CurrentRegion)

This works, but after that all the cells with validations that references to the named range loses the original reference and shows "#NAME#" in the popup.

Is there any way to reassign the address of a named range without losing the references to it?

TIA,

Pablo

Answer 1

I wonder if this technique would work? ozgrid.com/Excel/DynamicRanges.htm