How to automatically expand named range with formulas?

[felipepinto]

On a sheet if i have a named range with a column that is calculated with a certain formula, if i add a new item below the range nor the range is expanded nor the formulas are expanded. how to have this to be done automatically?

I already checked the “tools>options>calc>general>expand references when new columns/rows are inserted” also with the same path: “expand formating”

[Regina]

Do you use menu Sheet > Named Ranges and Expressions or menu Data > Define Range to define a “named range”?

[Zizi64]

Here is a macro function to adjust a named range:

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

Function SetRange1(sNewAddress as string)

'LoadXray
	oDoc = thiscomponent
	oRange =oDoc.NamedRanges.getByName("MyNamedRange")
'Xray oRange
	oRange.setContent(sNewAddress)
SetRange1 = "Check the range by the Navigator."
End function

ChangeNamedRange.ods (12.5 KB)

[felipepinto]

Do you use menu Sheet > Named Ranges and Expressions or menu Data > Define Range to define a “named range”?

yep i did that. even checked the “insert or delete rows” option

[felipepinto]

@Zizi64

Here is a macro function to adjust a named range:

That is indeed a possibility, i would rather evict macros, but if its a must then we gotta do what we gotta do, on thing tho, since i have differents named ranges i would like to update, how should i modify the function to parametrize the namedrange as string? I tried the following but im getting an error:

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

Function SetRange(sNewAddress as string, sNamedRange as string)

'LoadXray
	oDoc = thiscomponent
	oRange =oDoc.NamedRanges.getByName(sNamedRange)
'Xray oRange
	oRange.setContent(sNewAddress)
SetRange1 = "Check the range by the Navigator."
End function

[Zizi64]

@felipepinto

but im getting an error

Please upload your sample file here.

[felipepinto]

This is the error i get:

Here is the file
test.ods (11.5 KB)

[felipepinto]

Update: i just noticed a typo on the last line SetRange1 should be SetRange as the function was renamed.

but even after the fix the macro doesnt work

[Zizi64]

NamedRanges are not same as the DataBaseRanges. You have not a NamedRange with the name “data1” in your document.

Do not use Excel exclamination sign as a formula separator.
I never tried my macros for Named Ranges with R1C1 type references.

[felipepinto]

Do not use Excel exclamination sign as a formula separator.

What would you suggest to use instead?

I never tried my macros for Named Ranges with R1C1 type references.

Hopefully this is not a problem, after i started coding on basic A1 references stopped making sense to me.

NamedRanges are not same as the DataBaseRanges. You have not a NamedRange with the name “data1” in your document.

Well then that means that updating the formula to search on the right place would fix right?

[felipepinto]

Im getting a new error:

this is the updated formula:

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

Function SetRange(sNewAddress as string, sNamedRange as string)

'LoadXray
	oDoc = thiscomponent
	odbRange =oDoc.DataBaseRanges.getByName(sNamedRange)
'Xray oRange
	odbRange.setContent(sNewAddress)
SetRange = "Check the range by the Navigator."
End function

[Zizi64]

@felipepinto

Do not use Excel exclamination sign as a formula separator.

What would you suggest to use instead?

The regular “.” (dot)

[felipepinto]

The regular “.” (dot)

I dont understand, the dot doesnt work:

(the bottom two cells are =counta(indirect(R1C)))

[Zizi64]

@felipepinto

Well then that means that updating the formula to search on the right place would fix right?

.
I never handled DataBaseRanges with macros. I do not know how to get the DataBaseRanges by macro. My macro is for the NamedRanges only.
Install an object inspection tool: MRI or the XrayTool. They can list the properties and methods of the programming objects…

[Villeroy]

Ordinary references, named ranges, database ranges, chart ranges, validation ranges, conditional formatting, source ranges of form controls expand when you insert new rows and columns.
When option “Expand references …” is NOT checked:

• References shift down when you insert rows on top of the reference.
• References do not expand when you insert rows directly below the reference.

References expand regardless of that option when you insert rows anywhere below the top and the bottom of a range.
Columns show the same behavior transposed.

[felipepinto]

I added a breakpoint and got the properties:

Now i just need to figure out how to define that com.sum.star.table.CellRangeAddress into the range i want to set

[felipepinto]

When option “Expand references …” is NOT checked:

Ok so i dont need macros, if it works thats good, imma try.
Another question: does formulas expand as well?

[Zizi64]

Here is some sample code for handling data base ranges:

Macro to take base data from DataSource to to Calc

@Ratslinger is there a way of refreshing all the ranges in a spreadsheet with a variation on the macro you referred to in Calc macro failing to update sheet data from Base query - #3 by Ratslinger please? I have a spreadsheet that imports the results of 5 queries from one database. All the queries use the same :AppraisalID criterion to select one record and it’s related linked records in other tables. I cannot use a single query partly because it would generate masses of unnecessary data and pa…

[felipepinto]

@felipepinto

Ok so i dont need macros, if it works thats good, imma try.

Checked or not checked that option, insertng a new cell imediatetly at the bottom of a range does nothing.

[felipepinto]

@Zizi64

Here is some sample code for handling data base ranges:

Thanks for referencing, i just checked on that, unfortunately that thread is more about getting the range of a database intead of setting it, it does set the range for a chart tho.