[Solved] GetCellbyPosition().formula ="RC[4]" problem

[djbierman]

I am a beginner so please excuse me if this is a trivial problem.
I try to insert a formula in a cell like in the subject header so in full

Code: Select all

	osheet.GetCellbyPosition(24,2).formula= "=RC[4]" 

I get an error 508 in the addressed cell. The formula is there but is in LOWER CASE so: =rc[4]
If I correct this by hand to UPPER CASE it works.

Please help!!!!!
Dick

[Villeroy]

Formula and FormulaLocal are always in A1 notation.
I can offer 2 helper functions:

Code: Select all

Const cSheetPrefix% = 1
Const cAbsoluteSheet% = 2
Const cAbsoluteStartColumn% = 4
Const cAbsoluteStartRow% = 8
Const cAbsoluteEndColumn% = 16
Const cAbsoluteEndRow% = 32

'returns: any variant of string-address (eg. A1:B5,sheet.A1:B5,$sheet.A1:B5,sheet.$A1:$B5,...,$sheet.$A$1:$B$5)
'takes a spreasheet-doc, a range and optional flags(default returns relative A1:B5 without sheet name)
Function getRangeName(oRg,Optional iFlag%)as string
on error goto exitErr
Dim oAddr, oSh
Dim s$,sSh$,sC1$,sC2$,sR1$,sR2$,bR1 as boolean,bC2 as boolean,bR2 as boolean
	oAddr = oRg.getRangeAddress()
	oSh = oRg.getSpreadSheet()
	sSh = oSh.getName()
	sC1 = oSh.Columns.getByIndex(oAddr.StartColumn).getName()
	sC2 = oSh.Columns.getByIndex(oAddr.EndColumn).getName()
	sR1 = cStr(oAddr.StartRow +1)
	sR2 = cStr(oAddr.EndRow +1)
	if not isMissing(iFlag) then
		if iFlag AND cSheetPrefix then
			if (iFlag AND cAbsoluteSheet)=cAbsoluteSheet then s = "$"
			s = s & sSh &"."
		end if
		if iFlag AND cAbsoluteStartColumn then s = s &"$"
		bR1 = iFlag AND cAbsoluteStartRow
		bC2 = iFlag AND cAbsoluteEndColumn
		bR2 = iFlag AND cAbsoluteEndRow
	end if
	s = s & sC1
	If bR1 then s = s & "$"
	s = s & sR1
	s = s &":"
	if bC2 then s = s & "$"
	s = s & sC2 
	if bR2 then s = s &"$"
	s = s & sR2
	getRangeName = s
exitErr:
end function

Function getCellName(oCell,Optional iFlag%)as string
on error goto exitErr
Dim oAddr, oSh
Dim s$,sSh$,sC1$,sR1$,bR1 as boolean
	oAddr = oCell.getRangeAddress()
	oSh = oCell.getSpreadSheet()
	sSh = oSh.getName()
	sC1 = oSh.Columns.getByIndex(oAddr.StartColumn).getName()
	sR1 = cStr(oAddr.StartRow +1)
	if not isMissing(iFlag) then
		if iFlag AND cSheetPrefix then
			if (iFlag AND cAbsoluteSheet)=cAbsoluteSheet then s = "$"
			s = s & sSh &"."
		end if
		if iFlag AND cAbsoluteStartColumn then s = s &"$"
		bR1 = iFlag AND cAbsoluteStartRow
	end if
	s = s & sC1
	If bR1 then s = s & "$"
	s = s & sR1
	getCellName = s
exitErr:
end function

[djbierman]

Dear Villeroy,

Thanks for your help. This stuff is not really transparent for a beginner. Your functions should be extremely helpful to enter variable formula from basic

I quickly ran a little test like:

sub main
dim form as string
form = getCellame("RC[4]")
end sub

This gave me a BASIC runtime error: Object variable not set.

I tried to edit the getCellName function with getCellName(oCell as object, optional...)
to no avail.
I also have a few questions:
1. are the constants cSheetPrefix% etc. fundamental (i.e. have always the same value) or dependent on the range that you are interested in?
2. The real formula I want to use is RANK.AVG(A1; A1:A30) ;
I think I need to call the getCellNanme, for the first argument and the getRangeName for the second so I suppose I need to construct the formula like:
form= "RANK.AVG("+getCellName("RC1")+":"+getRangeName(":RC4")+")"

Is that correct?
Really appreciate any information you can give on this issue.

Best wishes,
Dick

[Villeroy]

I avoid any macros like pestilence when using a spreadsheet. If I wanted to write a program, I would not use a spreadsheet.
And I stopped writing macro code on forums. The above code is copied from an old archive.
For macro programming you need the MRI extension: [Tutorial] Introduction into object inspection with MRI
If you want to fill cells with formulas, R1C1 notation would be extremely helpful. Unfortunately, LibreOffice supports it only in the GUI. R1C1 is not implemented in the API. However, you can set the formula of the first cell and then use fillSeries https://api.libreoffice.org/docs/idl/re ... eries.html to fill a whole range of cells with a formula that would be identical in R1C1 notation.
See also viewtopic.php?f=20&t=95648&p=456566&hil ... es#p456566

[djbierman]

Edit: The striken text is a duplicate of BASIC runtime error; Argument is not optional  Volunteers: Do not respond here about this runtime error, only there djbierman: Please don't multi-post. It is considered rude. -- MrProgrammer, forum moderator

Dear Villeroy,
I have tried to make my own column-number to AA notation and wrote an extreme simple function to go from number (1...26) to character "A"..."Z" :
This generates the BASIC runtime error: Argument is not optional, with the arrow pointing to: test=s(n)
I am missing something that's clear.

Code: Select all

function test(n as integer) as string
dim s(26) as string
s(0)=" "
s(1)="A"
s(2)="B"
' etc. 
s(25)="C"
s(26)="Z"
test = s(n)
end function

BTW is any calc-function usable in Basic?[/strike]

[Villeroy]

1. Calc supports 1024 columns.
2. You have to learn a bit more about programming.

[eeigor]

I try to insert a formula in a cell...

Code: Select all

oSheet.getCellByPosition(24, 2).Formula = "=RC[4]"

BTW is any calc-function usable in Basic?

@djbierman, your problem is that you are using a different notation (not supported directly, Calc A1 syntax is supported only)
and do not know about the existence of a ready-made formula parser.
If you are using the alternative Excel R1C1 style (that is, notation, formula syntax), you need to do a formula conversion.
There is no need to go into the text of the user defined SetFormulaR1C1 function. Just use it. The code is quite short.
NOTE: getCellByPosition(24, 2) == getCellRangeByName("Y3"); RC[4] refers to AC3

Code: Select all

Sub Main
    Dim oSheet As Object, oCell As Object

    oSheet = ThisComponent.CurrentController.ActiveSheet
    oCell = oSheet.GetCellByPosition(24, 2)
    Call SetFormulaR1C1("=RC[4]", oCell)
End Sub

Sub SetFormulaR1C1(sFormulaR1C1$, oCell As Object)
    Dim oParser As Object
    oParser = ThisComponent.CreateInstance("com.sun.star.sheet.FormulaParser")
    oParser.FormulaConvention = com.sun.star.sheet.AddressConvention.XL_R1C1
    oCell.Tokens = oParser.parseFormula(sFormulaR1C1, oCell.CellAddress)
End Sub

[djbierman]

Dear eeigor,
Thanks for this reply. Actually I want to use just column and row as integers to address a cell so that I am consistent with the GetCellbyPosition calls
So I wrote a simple function to convert a column number to reference in Calc A1 notation after which I construct the formula that I need (actually a rank.avg formula)
And the function below worked until I edited and got this 'argument is not optional error. Then I restored the old code and then it still gives this error although before it worked!!!!!!!!!!!

Here is the code that first worked and after editing and restoring didn't:
function foo(n as Integer) as string
dim i, j as integer
dim a as string
i = int(n/26)
j= n- i*26
a= chr(i+64)+chr(j+64)
foo=a
end function

This worked except that I should have used ((n-0.5)/26) because of the rounding. So I edited this and I got the Basic runtime error 'Argument is not optional, The I restored the old code but the error was still there!!! And if I remove the offending line then the error is at the next line. If I do not declare the argument n as integer the problem stays. It seems like this has nothing to do with the code.
This is driving me crazy. What is going on?
Best wishes, Dick

[eeigor]

You have deviated somewhat from the stated topic. But the moderators advise to start a new topic in this case, so it is better to create it.
I don't quite understand what needs to be done. I am not an English-speaking user.
Attach a sample file.

I wrote a simple function to convert a column number to reference in Calc A1 notation...

Why? Perhaps this will help (see: UserInterfaceRepresentation).

Code: Select all

Sub Main()

	Dim oSheet As Object, oCell As Object
	Dim oConv As Object  'cell address conversion service (ScAddressConversionObj - deprecated)
	Dim s$

Rem	oCell = ThisComponent.CurrentSelection
	oSheet = ThisComponent.CurrentController.ActiveSheet
	oCell = oSheet.GetCellByPosition(24, 2)

	' An example of using the CellAddressConversion object.
	oConv = ThisComponent.createInstance("com.sun.star.table.CellAddressConversion")
	oConv.Address = oCell.CellAddress
	s = oConv.UserInterfaceRepresentation
	' ?BUG: If it's not the 1st sheet, then the sheet name is not skipped.
	s = Mid(s, InStr(s, ".") + 1)  'omits the name just in case

	MsgBox "Cell Name:  " & s, Title:="Active Cell Name"
End Sub

[MrProgrammer]

osheet.GetCellbyPosition(24,2).formula= "=RC[4]"

The INDIRECT function supports R1C1.

Sub T107294
Dim oSheet As Object
oSheet = ThisComponent.Sheets(0)
oSheet.getCellByPosition(24,2).Formula= "=INDIRECT(""RC[4]"";0)"
End Sub

However the target cell at position (24,2) is known to be Y3, so why not use the simple formula =AC3?

It seems that your proposed =RC[4] formula is peculiar, which suggests to me that we have XY Problem here. This topic concerns Err:508 from your macro and I've supplied two solutions for that, so you might want to consider creating a new topic where you explain your real goal so volunteers can suggest a more appropriate way to get there.

I am a beginner so please excuse me if this is a trivial problem.

You will continue to struggle until you become familar with this material: http://www.pitonyak.org/oo.php. Expect to spend a week or more learning how to use the complex Application Programming Interface. It will take even longer if you aren't already a programmer. It's important to understand expectations before you begin projects. The API's design is quite clever, but it takes everyone, even experienced programmers, a long time to appreciate its genius and learn how to use it effectively. Do not be discouraged if the learning curve is steep and long. Volunteers like Villeroy, JeJe, and Zizi64 might make macro programming look easy. It's not.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.