Can't create a formula string

English
, ,
#1

I’m trying to write a formula into a series of cells. The formula is the same each time – the sum of a column of cells – except that the column number differs each time.

Here is my code (excluding some dim definitions in previous blocks):

Dim ColChar as string
Dim RangeFormula as string

For BColumnNum = BEndColumn to BEndColumn-8 step -1
ColChar=CHR(ColumnNum-64)
RangeFormula=""=sum("&ColChar&“4:”&ColChar&BEndRow+2")""
BufferCell=AssetSheet.getCellByPosition(ColumnNum,BEndRow+2)
BufferCell.setFormula(RangeFormula)
Next BColumnNum

This generates: “BASIC syntax error. Parentheses do not match.”

In the IDE code window, the =sum( part is in green whereas everything else between the first and last quotation marks is in red (ie as if the =sum( is an instruction rather than a string.

Would appreciate help on how to do this.

#2

first : This is the guide - How to use the Ask site? - #6 by erAck

then, attaching a sample .ods + screenshot would be much more clear

last, sounds like using macro is quite overkill :face_with_thermometer:
→ How do I sum the last 12 values in a column?

#3

Hmmm …

  • Always attach an example file in such a case.
  • Show at least for a few examples in the sheet what you actually want to get (in this case formulas). Identify the associated variables used in the code for the example.
  • Realise that there are only 26 columns of a sheet which can be referenced with a single letter.
  • Realise that column numbers for the UI are 1-based while coulumn indices in user code generally are API style, and therefore 0-based.
  • Start writing user code after some basic studies of the means.

Also:

  • The double quotes left and right of the 4: in your posted code are “typographical”.
  • The value of the formula property of a cell is a string, and is therefore shown in a watch window of the Basic IDE with doublequotes which are not part of the string itself.
  • Simply don’t try things this way.
  • Ask for help describing what you actually want to achive.
    There is the wll known XY problem.
#4

Try

#5

Thanks for the suggestion.
This gives me “Basic syntax error” with ColChar& highlighted.

#6

Well, seems nobody read my comments above

formula= "=sum(" & ColChar & "4:" & ColChar & BEndRow+2 & ")"

The so-called typographical doublequotes look like ordinary ones in “Preformatted text”.
Of course, this hokum should generally be omitted by the editor of the forum. Nobody needing “typographical” here.
“O my!” : straight doublequotes.
“O my!” : “typographical” doublequotes in disguise.

#7

Thanks for the advice. Trying to act on it here:

In the attached file, columns K through R contain cells showing numerical amounts. What I’m trying to achieve is to write the formula =sum(K5:K9) into row 11 (only for each column K to R). In the attached file, the desired result is highlighted in yellow.

In the code that I showed, BEndRow would be row 9. The For…Next is supposed to loop backwards from column R to column K, on each iteration writing the formula into row 11.

AskLib.ods (23.3 KB)

#8

Thanks, I didn’t know there was a distinction between typographical and straight double quotes. Interesting!

Your syntax worked, I’m happy to say. Alas, it didn’t have the result I had hoped for (almost certainly a fault in my coding). It wrote to Column A BEndRow + 2 this formula:
=SUM(￀4:￀30))

Back to the drawing board!

#9

This isn’t actually a solution. It only helps doing one step of an inappropriate attempt… We are still dancing arround an XY-situation. Insisting on your formula approach you would still have to rewrite the macro completely as soon as you once need column AA. I would assume it’s not unlikely to have five accounts more one day. 10 columns (5 accounts) behind column R comes column AB.
I sketched a different solution for you doing things in a way as I do it in a slightly similar case.
See attached example. It also contains some additional comments.
disask123874_insertionOfNewDataRows.ods (40.8 KB)

#10

Yes, I wrote it knowing it was unlikely I would go beyond column R – I’m just an amateur, occasional user who likes to macro-ise repetitive tasks. And I update the exchange rate by hand each time.

The whole exercise would probably have been much easier if there were a function to interconvert numerical addresses (ColumnNumber, RowNumber) and alphabetical ones (A1,Z3), but in my cursory search I couldn’t find a way of doing this (or not one that I could understand). Shouldn’t there be a getByName() for ranges/cell addresses?

The idea of putting totals above does make it much easier – thanks for the thought. But calling it an “inappropriate attempt” is a bit harsh, I thought! Your solution works for me, even if not for you!

#11

You can believe me: You don’t need this - and you should avoid to set formula strings.
However:

Function colName(pX As Long) ' pX is the 1-based column number.
colName        = ":err:"
On Local Error Goto fail
fa             = CreateUNOService("com.sun.star.sheet.FunctionAccess")
helpingAddress = fa.callFunction("ADDRESS", Array(1, pX, 4, 1))
out            = fa.callFunction("REGEX", Array(helpingAddress, "[\d+\$]+", "", "g"))
colName        = out
fail:
End Function

If you need it more general and both directions I can also povide code.

#12

Thank you for this - I will put it in my library.

What is the problem with writing formula strings?

#13

Not a real “problen” as soon as you have the formula string you need/want to assign to the .Formula property of a cell.
However, it is a complicated detour if you first have to create the string, including the distinction between “relative” and “absolute” in the referencing, with a bunch of text manipulations.
The working core of Calc isn’t much interested in these strings. As soon as a formula is entered by the user or read from a file, it is parsed to create a completely different representation better suitable for actual calculations. This results in a sequence of so-called tokens, and that is what actually is written to the cell property .Tokens .
The formula string is re-constructed from these tokens whenever it is needed for a human-readable output or for writing it to a file.
The great advantages of the tokens are that

  • they are locale-independent
  • they can be copied from one cell to another without bothering about adaptions.

If you are interested in details: I took the time to make an example file with user code showing how it works:
copyFormulasExample.ods (26.8 KB)

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.