How do I combine the two macros below into a single button function?

[Madmaxneo]

Below are two macros that have similar code, in fact so similar I believe whatever macro is second is causing the 1st macro not to work. For the second macro I changed Orange1 and Orange2 to Orange3 and Orange4 along with data1 and data2 to data3 and data4, and though the macro still worked it would still not let the first one work. I then thought to change the i& to k&, but that caused the macro to not work at all.
Please understand that each macro works fine when placed in their own separate buttons but not together in the same button.

Here is the first macro in the button:

Sub sumAndClearAllRanges3 'make sum from two ranges and put it to the second range
Dim oDoc As Object, oSheet As Object, data1(), data2(), oRange1 As Object, oRange2 As Object, i&, j&
Dim aAllRangesAddress As Variant 
	aAllRangesAddress = Array("A63:C72","I63:I72", "A74:C78","I74:I78", "A80:C90","I80:I90", "A92:C98","I92:I98", "A100:C118","I100:I118", "A120:C126","I120:I126", "N63:P74","V63:V74", "N76:P83","V76:V83", "N85:P88","V85:V88", "N90:P99","V90:V99", "N101:P109","V101:V109", "N112:P126","V112:V126" ) ' and all others
	oDoc=ThisComponent
	oSheet=oDoc.CurrentController.ActiveSheet
	For j = LBound(aAllRangesAddress) To UBound(aAllRangesAddress) Step 2
		oRange1=oSheet.getCellRangeByName(aAllRangesAddress(j))
		oRange2=oSheet.getCellRangeByName(aAllRangesAddress(j+1))
		data1=oRange1.getData
		data2=oRange2.getData
		for i=lbound(data1) to ubound(data1) 'make sum 
			data2(i)(0)=Fix(data1(i)(0)+data1(i)(1)+data1(i)(2)+data2(i)(0))
		next i
		oRange1.clearContents(1) 'clear all contents in the range
		oRange2.setData(data2) 'set new data
	Next j
end Sub

Here is the second macro that I want to place below the first one in the same button:

Sub AddStatIncrease 'make sum from two ranges and put it to the second range
	dim oDoc as object, oSheet as object, data1(), data2(), oRange1 as object, oRange2 as object, i&
	oDoc=ThisComponent
	oSheet=oDoc.CurrentController.ActiveSheet
	oRange1=oSheet.getCellRangeByName("I21:I28")
	oRange2=oSheet.getCellRangeByName("B21:B28")
	data1=oRange1.getData
	data2=oRange2.getData
	for i=lbound(data1) to ubound(data1) 'make sum 
		data2(i)(0)=data1(i)(0)+data2(i)(0)
	next i
	oRange1.clearContents(1) 'clear all contents in the range; number in SDK com.sun.star.sheet.CellFlags
	oRange2.setData(data2) 'set new data
End Sub

[Zizi64]

@Madmaxneo

Please understand that each macro works fine when placed in their own separate buttons but not together in the same button.

.
How you assigned the two macros to one button? Which Events to? Please upload a sample file here.
.
My tip:
Assign a third (combined one) macro to the button:

Make sure, if it is the proper order to calling the subs...

Sub CombibedSubs

 sumAndClearAllRanges3
 AddStatIncrease

end sub

.
…Or simly call the second one from the first one, (and assign the first only to the button).

[karolus]

def thats_readable_python(*_):
    #the first:
    doc = XSCRIPTCONTEXT.getDocument()
    sheet = doc.CurrentController.ActiveSheet
    ranges = (("A63:C72","I63:I72"),
              ("A74:C78","I74:I78"),
              ("A80:C90","I80:I90"),
              ("A92:C98","I92:I98"),
              ("A100:C118","I100:I118"),
              ("A120:C126","I120:I126"),
              ("N63:P74","V63:V74"),
              ("N76:P83","V76:V83"),
              ("N85:P88","V85:V88"),
              ("N90:P99","V90:V99"),
              ("N101:P109","V101:V109"),
              ("N112:P126","V112:V126"))
    for _input, _output in ranges:
        in_data = sheet[_input].Data
        sheet[_output].Data = [[sum(row)] for row in in_data]
        sheet[_input].clearContents(1)
    # the second:
    for r in range(21,29):
        sheet[f"B{r}"].Value += sheet[f"I{r}"].Value
    sheet["I21:I28"].clearContents(1)    

[Lupp]

@ Madmaxneo:
I wouldn’t study your code, but ask you to clearly state if you want to

• execute the two Sub one after the other if the single button is clicked
• execute both after that click, but also allow for calling only one of them by different means (buttons e.g.)
• have a choice which one should be executed
  (Also tell in what way the choice should be made in this case.)

A discussion about the quality/errors of the code or about what programming language/system should be used is a different thing.

[Madmaxneo]

@Lupp

@ Madmaxneo:

• execute the two Sub one after the other if the single button is clicked

This first one is exactly what I want to do.

[Madmaxneo]

@Zizi64

.
How you assigned the two macros to one button? Which Events to? Please upload a sample file here.
.
My tip:
Assign a third (combined one) macro to the button:

.
…Or simly call the second one from the first one, (and assign the first only to the button).

This is exactly how I did it. Also, as mentioned above, I tried changing the oRange1 and 2 and data1 and 2 to 3 and 4 respectively in the AddStatIncrease macro, but it didn’t help any.

REM  *****  BASIC  *****

Sub sumAndClearAllRanges3 'make sum from two ranges and put it to the second range
Dim oDoc As Object, oSheet As Object, data1(), data2(), oRange1 As Object, oRange2 As Object, i&, j&
Dim aAllRangesAddress As Variant 
	aAllRangesAddress = Array("A63:C72","I63:I72", "A74:C78","I74:I78", "A80:C90","I80:I90", "A92:C98","I92:I98", "A100:C118","I100:I118", "A120:C126","I120:I126", "N63:P74","V63:V74", "N76:P83","V76:V83", "N85:P88","V85:V88", "N90:P99","V90:V99", "N101:P109","V101:V109", "N112:P126","V112:V126" ) ' and all others
	oDoc=ThisComponent
	oSheet=oDoc.CurrentController.ActiveSheet
	For j = LBound(aAllRangesAddress) To UBound(aAllRangesAddress) Step 2
		oRange1=oSheet.getCellRangeByName(aAllRangesAddress(j))
		oRange2=oSheet.getCellRangeByName(aAllRangesAddress(j+1))
		data1=oRange1.getData
		data2=oRange2.getData
		for i=lbound(data1) to ubound(data1) 'make sum 
			data2(i)(0)=Fix(data1(i)(0)+data1(i)(1)+data1(i)(2)+data2(i)(0))
		next i
		oRange1.clearContents(1) 'clear all contents in the range
		oRange2.setData(data2) 'set new data
	Next j
end Sub

Sub AddStatIncrease 'make sum from two ranges and put it to the second range
	dim oDoc as object, oSheet as object, data1(), data2(), oRange1 as object, oRange2 as object, i&
	oDoc=ThisComponent
	oSheet=oDoc.CurrentController.ActiveSheet
	oRange1=oSheet.getCellRangeByName("I21:I28")
	oRange2=oSheet.getCellRangeByName("B21:B28")
	data1=oRange1.getData
	data2=oRange2.getData
	for i=lbound(data1) to ubound(data1) 'make sum 
		data2(i)(0)=data1(i)(0)+data2(i)(0)
	next i
	oRange1.clearContents(1) 'clear all contents in the range; number in SDK com.sun.star.sheet.CellFlags
	oRange2.setData(data2) 'set new data
End Sub

Adding your suggested 3rd macro worked like a charm (as expected) and I did not need to change the oRange or data numbers. If I wanted to add more macros to the button is adding the name of the macro to that final CombinedSubs macro above all I need to do (with the corresponding macros in order above it)?
This is gold to me and greatly appreciated!

EDIT: One more thing: Is it possible to change the cell references to named ranges in the first macro (sumAndClearAllRanges3)? Is it as easy as just changing something like this line:

aAllRangesAddress = Array("A63:C72","I63:I72" ) ' and all others

To something like this:

aAllRangesAddress = Array("Set1","Tset1" ) ' and all others

Or would there be more involved with it?

[karolus]

@Madmaxneo

Or would there be more involved with it?

Yes my dear

    for _input, _output in ranges:
            in_data = sheet[_input].Data
            sheet[_output].Data = [[sum(row)] for row in in_data]
            sheet[_input].clearContents(1)

becomes:

    for _input, _output in ranges:
        in_data = doc.NamedRanges[_input].ReferredCells.Data
        doc.NamedRanges[_output].ReferredCells.Data = [[sum(row)] for row in in_data]
        doc.NamedRanges[_input].ReferredCells.clearContents(1)

[Lupp]

@Madmaxneo

@Lupp

• execute the two Sub one after the other if the single button is clicked

This first one is exactly what I want to do.

Assign the following Sub (same module as the other two) to your button event.

Sub runThemBoth(pEvent)
sumAndClearAllRanges3 ()
AddStatIncrease()
End Sub

disask96395demo.ods (12.5 KB)

[karolus]

@Lupp

Assign the following Sub

@Lupp: youre late, he was already there six hours ago:

@Madmaxneo

Adding your suggested 3rd macro worked like a charm (as expected) and I did not need to change the oRange or data numbers. If I wanted to add more macros to the button is adding the name of the macro to that final CombinedSubs macro above all I need to do

the follow up was:

@Madmaxneo

EDIT: One more thing: Is it possible to change the cell references to named ranges in the first macro (sumAndClearAllRanges3)? Is it as easy as just changing something like this line:

aAllRangesAddress = Array("A63:C72","I63:I72" ) ' and all others

To something like this:

aAllRangesAddress = Array("Set1","Tset1" ) ' and all others

…which is also answered some posts above!

[Lupp]

You won’t believe. I had even read through those posts.
But I had posted a direct question and got a direct answer.
Therefore I answerd in return directly again in a very short way.

BTW: What do you think is your actual reason for your attituide concerning me?

[karolus]

@Lupp

BTW: What do you think is your actual reason for your attituide concerning me?

so sensible! what about your own attituides:

@Lupp

A discussion about the quality/errors of the code or about what programming language/system should be used is a different thing.

there was no discussion ( only a Example how it can be written in python )
And you are not the ONE who alone defines the rules

[Madmaxneo]

Well, that’s Python and confusing to me as it does not resemble any of the basic code I am referring to.

[karolus]

@Madmaxneo

as it does not resemble any of the basic code I am referring to.

It may confuse you, but that’s no reason to BOLDLY lie that it won’t work!

[Madmaxneo]

@karolus

It may confuse you, but that’s no reason to BOLDLY lie that it won’t work!

I didn’t say it wouldn’t work (I actually have no idea if it would) but I am saying I do not know how to implement that into the code I already have.

[karolus]

@Madmaxneo

I do not know how to implement

in another Thread I gave you already the hint to use apso.oxt to install and organize python-code.
The link between button-event and python works the same way as it does between button-event and Basic!

[Madmaxneo]

But, why would I go through all that just to implement this python code when already have what I need in basic and I did not need to install anything extra?

[karolus]

1. If you have everything you need, you don’t have to ask!

2. apso.oxt is not absolutely necessary, but it would make it easier to get started!

3. On the other hand, I thought that you should also acquire some knowledge, instead of letting others deliver you the complete customised solution free of charge, without you contributing anything yourself.

ps. for me it would only be a finger exercise to deliver you a complete file with all the bells and whistles, but that’s exactly how you learn nothing at all yourself.

[Madmaxneo]

I have been learning. I’ve already taken some of the macros that people have helped me with and used them for other parts of my spreadsheet but everything started in basic so that’s where I’d like to stay for now.