Trying to get a clear understanding of namedRange and namedFunction by reading ask.LibreOffice questions and answers is like assembling a puzzle missing pieces.
Is there some place a discussion giving a more complete definition of these things and their general uses?
In general looking at their methods in Xray really tells me what it’s called but not what the method does nor how it’s used. Likewise, I don’t always understand the implications of their properties.
Any help will be appreciated.
Mike
represents a named range in a spreadsheet document.
In fact a named range is a named formula expression. A cell range address is one possible content of a named range.
What do you mean by namedFunction?
I meant named formula . As I say, I’m confused.
Sub AddNamedFunction()
’ from OOME 4
’ Listing 447. Create the AddLeft named range.
In the narrow sense, a named range is a fully absolute reference. When you selected A1:B12 on Sheet1, absolute $Sheet1.$A$1:$B$12 is what you get automatically when you type a valid name into the name box left of the formula bar.
However, anything you can enter into a cell can be a “named range”, therefore “named formula” would be a better term.
- a constant number or text
- a formula with absolute, relative and mixed references
Demo:
ask123464.ods (26.6 KB)
Sir I appreciate that you’re trying to help but there is not a macro anywhere that I could see. My guess is that you did all that through the menus in Calc though I admit to not knowing how and haven’t a clue what FORMULA(I3) is.
Maybe I wasn’t clear initially so I’ll ask if you know where I might find a document explaining how to create and use named ranges and formulas in basic macros.
Thanks again for trying.
Be well,
Mike
The Named Ranges and Expressions are not related to the macros directly. It is a feature of the Calc application. Of course you can handle the Named Ranges by you macros. For example you can get the referred cell range by your macro, and yo can get the values from the referred cells.
where I might find a document explaining how to create and use named ranges and formulas in basic macros.
I suggest you to download and study Andrew Pitonyak’s free macro books:
“In a macro, a named range is accessed, created, and deleted using the NamedRanges property of a Calc document. Use the methods hasByName(name) and getByName(name) to verify and retrieve a named range. The method getElementNames() returns an array containing the names of all named ranges. The NamedRanges object supports the method addNewByname, which accepts four arguments; the name, content, position, and type.”
https://www.pitonyak.org/database/
https://www.pitonyak.org/database/Calc_As_A_Simple_Database.odt
and:
https://www.pitonyak.org/oo.php
and:
https://forum.openoffice.org/en/forum/viewtopic.php?t=75811
I will attach an example containing a sloppy macro which shows how to access and use the containers for named expressions (“NamedRanges”). It’s not for actual use. The UI and also the API provide better means.
The example stresses the fact that a Calc document as a whole as well as every sheet separately has such a container. This allows a simple handling of the scopes.
See also the links contained in the example.
disask123464_NamedExpressions.ods (14.1 KB)
You guys are way ahead of me and it appears you are declaring your named ranges via the UI.
This is where I am, well below you:
Sub testNamedFormula()
Dim aRange, bRange, cRange, oRange
Dim aCell as Object, oCell as Object
Dim fnName as String, fnStr as String
Dim thisController, currentSheet
Dim oRanges 'The NamedRanges property.
oRanges = ThisComponent.NamedRanges
thisController = ThisComponent.CurrentController
currentSheet = thisController.ActiveSheet
aRange = currentSheet.getCellRangeByPosition( 0, 2, 0, 6 )
aRange.getCellByPosition(0,0).Value = 1
aRange.getCellByPosition(0,1).FormulaLocal = "=R[-1]C+1"
oCell = aRange.getCellByPosition(0,1)
fnName = "abovePlus1"
'fnStr = "=R[-1]C+1" ' no Joy
fnStr = "=A3+1"
'fnStr = "=OFFSET(R[-1]C,-1,0)+1" ' no Joy
'fnStr = "=OFFSET(RC,-1,0)+1" ' no Joy
If NOT oRanges.hasByName(fnName) Then
oCellAddress = oCell.getCellAddress()
’ addNewByName( aName as string, aContent as string, aPosition as struct, nType as long )
oRanges.addNewByName(fnName, fnStr, oCellAddress, 0)
End If
aCell = currentSheet.GetCellbyPosition( 0, 4 )
aCell.Formula = abovePlus1
’ aCell.Formula = “abovePlus1” ’ no Joy, puts text
End Sub ’ testNamedFormula ---------------------------------------------
I’m pretty sure the named formula is getting created but it is not getting copied into a cell’s formula and it is not doing what I want which to set this cells value equal to the above cell’s value plus 1.
Perhaps one of you can point out where I’m going wrong in basic.
Thanks,
Mike
How do you want to write macros manipulating an application you don’t know? Before you can program anything, you’ve got to know what your program relates to. API and UI are 2 different ways to manipulate the same application. And most macros become obsolete if you know how to use all features productively in the UI.
mikekaganski wrote for YOU:
As said: just use R1C1 syntax, also in your Basic. It is available in VBAcompatibility mode
In my opinion the “VBAcompatibility mode” is only a workaround in the LibreOffice. The MS VBA is not a part of the LibreOffice.
Use the existing methods, features, functions, and the API of the LibreOffice.
After all your help, reading all you suggested, I never found a sample of someone assigning a namedFormula to a cell via basic.
After many wrong paths I finally made it work.
The function MUST be stated in “A1” terms. When I declared it as “R[-1]C” it actually became “r[-1]c” which doesn’t work. Since I had already filled cells “A1” with 1 and “A2” with the formula “R[-1]C” I made the cellAddress in the ‘oRanges.addNewByName’ point to “A3” then the formula became “A3+1” which was converted to “R[-1]C+1” internally.
With the namedFormula name as “abovePlus1”, to assign that formula to another cell the command became ‘aCell.Formula = “=abovePlus1”’.
Even assigning the formula in the addNewByName statement as “=R[-1]C”, the cell assignment must still be “=abovePlus1”.
In the above code I had to add a phrase to remove the old namedFormula in order for any changes I made to take effect.
Herb40’s solution in the forum link Zizi64 posted shows that the formula can be modified after a namedFormula is created.
So thanks for sharing your thoughts,
Be well,
Mike
PS: Looking back through my notes I see where Zizi64 posted Function SetRange1(sNewAddress as string) which could be used to reset the formula in a namedFormula.
Thanks Zizi64.
Mike
1 Like
YOU are the developer of YOUR program. As a developer, you don’t need to copy anybody’s ample code. Just set up a NamedFormula (whatever that is) in the GUI and use your favorite object inspector to get each and every detail about the thing.
In 23 years, I never set up a single named range programmatically. I set up named ranges together with styles manually and store them in templates. If such template needs any macro code, I can refer to the named ranges. This way, I don’t have to rewrite the macro code when the position of some range has changed.