Changing variable and .Formula giving strange results

[GingerUser]

Hello everyone.

MacOS Mojave version 10.14.3
LibreOffice Community Version: 7.1.1.2

So, I have the following bit of code, which I will explain:

Sub sum_changing_variable_test

Dim i As Long, b As Long
i = 0
b = 1

Formsomme = "=Sum(D" & b &":D9)"

Do Until i = 7 And b = 8

ThisComponent.Sheets.getByIndex(0).getCellByPosition(2,i).Formula = Formsomme

i = i + 1
b = b + 1

Loop

end sub

So basically, I am writing a macro that will put formula SUM(D1:D9) in cell C1, SUM(D2:D9) in C2, etc., all the way to SUM(D8:D9) in C8, thanks to the variables i and b that go up by one in the Do While…Loop. The variable i is for the rows, and b for the first cell in the D column.

However, for some reason, I just get SUM(D1:D9) from C1 to C9.

Therefore, I don’t know why D1 is not going up to D2, D3, etc., and why the formula is inserted into an additional row, when getCellByPosition(2,i), with i going from 0 to 7, means that the results should only appear in rows 1 to 8.

Would anybody know why this is happening ?

Thanks in advance for any potential attempts at explaining why.

[anon73440385]

Hello,

you never adapt your formula, since you set Formsomme = "=Sum(D" & b &":D9)" outside the loop, before the loop starts. This way you set always formula to = "=SUM(D" & 1 &":D9)" (i.e. to =SUM(D1:D9)) for all iterations. Change your code to:

Sub sum_changing_variable_test

  Dim i As Long, b As Long
  i = 0
  b = 1

  Do Until i = 7 And b = 8

    Formsomme = "=Sum(D" & b &":D9)"
    ThisComponent.Sheets.getByIndex(0).getCellByPosition(2,i).Formula = Formsomme

    i = i + 1
    b = b + 1

  Loop

end sub

Note(s):

• I’d adapt the code to reflect that b = i + 1 and thus simplifying the code.
• You could also use ThisComponent.Sheets.getByIndex(0).getCellByPosition(2,i).Formula = "=Sum(D" & i+1 & ":D9)" to make clear, why you need to set Formsomme to a new value on each iteration.
• You wrote “means that the results should only appear in rows 1 to 8.” - That’s not correct. it appears on rows 1 to 7 only since condition to run an iteration is checked on the very beginning of the loop. And using UNTIL means, that the loop is not performed if i equals 7 since in that case i=7 is true and no longer false (see also Libreoffice Help - Do…Loop Statement)

Hope that is, what your question is about.

[GingerUser]

Thanks for the detailed reply! The code generates what I had in mind. Thanks for the lengthy explanation as to why it didn’t work.