BASIC Calc: Get sum of two cells of a sheet, paste and then MsgBox

Hello everyone.

My version of LibreOffice is 7.0.0.3 and I am on a Mac OSX Mojave (10.14)

And I have this code here which copies a value from A1 in Sheet1, pastes it into B2 in Sheet2, and gives a MsgBox depending on whether the value is strictly greater than 100, strictly less than 100, or equal to 100:

Sub MsgBoxEtCopieDuneCelluleVersDeuxiemeFeuilSixiemeTeste

Sheet1 = ThisComponent.Sheets.getByIndex(0) 
Sheet2 = ThisComponent.Sheets.getByIndex(1)

RangeOrg = Sheet1.getCellRangeByName("A1").RangeAddress 
RangeCpy = Sheet2.getCellRangeByName("C5").RangeAddress

CellCpy = Sheet2.getCellByPosition(RangeCpy.StartColumn,_ 
RangeCpy.StartRow).CellAddress

Sheet1.CopyRange(CellCpy, RangeOrg) 

If Sheet2.getCellRangeByName("C5").Value > 100 Then
MsgBox "YES"
ElseIf Sheet2.getCellRangeByName("C5").Value < 100 Then
MsgBox "NOOOOOOO"
Else
MsgBox "Equal to 100"
End If

End Sub

And I want to know how I can sum two cells from Sheet1 and paste that sum into C5 of Sheet2
(so for example: 8 from Sheet1,A1, and 16 from Sheet1,B1).

I have tried to look a bit for a way to Sum in LO Basic, but I have not been successful.

Would any of you know how to do this?

macros are somewhat ‘wooden’ in calc, but
math sign for ‘SUM’ is ‘+’, and

Sheet2.getCellRangeByName("C5").Value = Sheet1.getCellRangeByName("A1").Value + Sheet1.getCellRangeByName("B1").Value

works, tested in win, (really, i didn’t expect it to do),
(our ‘macro-popes’ will slaughter me as it’s ‘good practice’ first to define variables, then assign the cell values to them, then to calculate with the variables, then assign the calculated result to a cell … as already said, macros are somewhat ‘wooden’ in calc …)
alternative:

=sum(sheet1.A1:B1)  

in sheet2.C5 and conditional formatting to alert you about the result,
advantage: it’s ‘dynamic’, you have actual results in realtime without need to start a macro or messing around with wood …

Could you post the whole macro code that you wrote to get it to work? I tried inserting that piece of code you gave me:

Sheet2.getCellRangeByName("C5").Value = Sheet1.getCellRangeByName("A1").Value + Sheet1.getCellRangeByName("B1").Value

in my macro code in different ways but still can’t find a solution.

Not sure if it’s because of me being on a Mac.

hello @ASKLOguitarist,

of course, quite short, i tested only the addition:

sub test_calculating 

Sheet1 = ThisComponent.Sheets.getByIndex(0) 
Sheet2 = ThisComponent.Sheets.getByIndex(1)

'2 value1 = Sheet1.getCellRangeByName("A1").Value
'2 value2 = Sheet1.getCellRangeByName("B1").Value
'2 Sheet2.getCellRangeByName("C5").Value = value1 + value2

Sheet2.getCellRangeByName("C5").Value = Sheet1.getCellRangeByName("A1").Value + Sheet1.getCellRangeByName("B1").Value

end sub 'test_calculating

the - '2 - say ‘commented out’, it’s from my first test, just try if that works better for you,

as said before, macros in calc are everything but easy …

a vague idea … there are options you set in the header of your macro file?, there is one to restrict operations to declared variables?, such as ‘dim value1 as integer’?, is it ‘explicit’?, may be you suffer from such?

I got the whole thing working with the MsgBox. This is the code for anybody else who wants to know how to do it:

Sub test_calculatingdeux

Sheet1 = ThisComponent.Sheets.getByIndex(0) 
Sheet2 = ThisComponent.Sheets.getByIndex(1)


Sheet2.getCellRangeByName("C5").Value = Sheet1.getCellRangeByName("A1").Value + Sheet1.getCellRangeByName("B1").Value


If Sheet2.getCellRangeByName("C5").Value > 100 Then
      MsgBox "YES"
ElseIf Sheet2.getCellRangeByName("C5").Value < 100 Then
      MsgBox "NOOOOOOO"
Else
      MsgBox "Equal to 100"
End If

end sub

I’ll reply to the others parts of your comment a bit later.

@all: the absolute answer can be found in ASKLOguitarist’s comment above,

the general hint was:


hello @ASKLOguitarist,

of course, quite short, i tested only the addition:

sub test_calculating 

Sheet1 = ThisComponent.Sheets.getByIndex(0) 
Sheet2 = ThisComponent.Sheets.getByIndex(1)

'2 value1 = Sheet1.getCellRangeByName("A1").Value
'2 value2 = Sheet1.getCellRangeByName("B1").Value
'2 Sheet2.getCellRangeByName("C5").Value = value1 + value2

Sheet2.getCellRangeByName("C5").Value = Sheet1.getCellRangeByName("A1").Value + Sheet1.getCellRangeByName("B1").Value

end sub 'test_calculating

the - '2 - say ‘commented out’, it’s from my first test, just try if that works better for you,

(as said before, macros in calc are everything but easy …)

(a vague idea … there are options you set in the header of your macro file?, there is one to restrict operations to declared variables?, such as ‘dim value1 as integer’?, is it ‘explicit’?, may be you suffer from such?)


fishing for compliments … srry …
if this answers your question pls. mark it by clicking the grey ‘circled-v-hook’ top left of it to turn it green,
if you like the answer pls. express that by ‘upvoting’ - the ‘^’ sign above the number of votes,
if you dislike the answer pls. express that by ‘downvoting’ - the ‘v’ sign below the number of votes,

Don’t worry about the fishing for compliments part. I was trying to find a way to show that your answer replies to the question. Done it now. Thanks for helping!