What is the code used to run all three macros in the below macro code (in Basic)?

[Madmaxneo]

I am having an issue running 3 macros in a button. When I select the “Assign Action” I can only select one of the macros and I do not know the syntax for the code to run all three.

This first code has what I was trying to run for all three macros in the button (but it doesn’t work). It is based on code that people on here helped me with in the past. Note that the first part originally read runThemBoth and not runThemAll. I assume there is supposed to be different syntax for this.

Sub runThemAll(pEvent)
PutDataIntoFirstEmptyRowOfTheTargetRange2 ()
GetNamedRange ()
GetFirstEmptyCellInAColumnOfRange ()
End Sub

This code has all three macros that I want to run combined in the button:

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

Option Explicit

Sub PutDataIntoFirstEmptyRowOfTheTargetRange2
 Dim oDoc as object
 Dim oSourceRange as object
 Dim oSourceCell as object
 Dim oTargetRange as object
 Dim oTargetCell as object
 Dim lColNr as long
 Dim sFormula as string  	
	oDoc = ThisComponent
	oSourceRange = GetNamedRange("TalList", oDoc)
	oSourceCell = oSourceRange.ReferredCells.getCellByPosition(0,0)
	sFormula = oSourceCell.Formula			
	oTargetRange = GetNamedRange("TalOwn", oDoc)
	lColNr = 0 'zero based numbering
	oTargetCell = GetFirstEmptyCellInAColumnOfRange(oTargetRange, lColNr)
	oTargetCell.Formula = sFormula
	oSourceCell.Formula = ""
end Sub
'_____________________________________________________________________________________________


Function GetNamedRange(sRange_name as string, optional oCalc_File as object) as object
 Dim oDoc, oRange as object
	If IsMissing(oCalc_File) then
		oDoc = ThisComponent
	else
		oDoc = oCalc_file
	end if 	
	If len(sRange_name) = 0 or sRange_name = "" then
		GetNamedRange = NOTHING
 		Exit function
 	End if
 	if oDoc.namedranges.hasByName(sRange_name) then 
		oRange = oDoc.NamedRanges.getByName(sRange_name)
		GetNamedRange= oRange
	else
		GetNamedRange = NOTHING
	end if		
end function
'_____________________________________________________________________________________________


Function GetFirstEmptyCellInAColumnOfRange(oTheRange as object, lColNr as long) as object
 Dim oCells as object
 Dim oCell as object
 Dim lRowNr as long 
 Dim i as long 	
 	oCells = oTheRange.ReferredCells
 	lRowNr = oCells.Rows.Count 	
 	For i = 0 to lRowNr-1
 		oCell = oCells.getCellByPosition(lColNr,i)
 		If  oCell.getType() = 0 Then 
 			GetFirstEmptyCellInAColumnOfRange = oCell
 			Exit function
 		end if 		
	next i	
end function

Understand that I am not a coder and this is something for myself at home that I have been trying to get to run better but I just can’t get a handle on the macros.

[Wanderer]

@Madmaxneo

I assume there is supposed to be different syntax for this.

Not really. If you had three SUBroutines your approach is possible. But in your case you have one SUB and two functions.
.
A function will do the same as an ordinary SUB, but returns a value, wich can be used.
.
If you inspect “your” code the two functions are called in the first SUB. There is no need to call them again outside of the first SUB.

@Madmaxneo

but it doesn’t work

The question would be, what you expect. The code seems to “move” (copy, then delete source) one formula from your TalList-Range to your TalOwn-Range.

@Madmaxneo

but I just can’t get a handle on the macros.

I suspect you don’t understand the code, wich you are “handling”, wich calls for trouble…

[fpy]

@Madmaxneo

something

maybe cite what source(s) you based your code on, and suggestions to clarify if needed.

• https://wiki.documentfoundation.org/Documentation/BASIC_Guide#Spreadsheet_Documents
• Reading and Writing values to Ranges

[Madmaxneo]

[quote=“fpy, post:4, topic:109595, full:true”]

@Madmaxneo

something

maybe cite what source(s) you based your code on, and suggestions to clarify if needed.

My sources are help from these forums.

[karolus]

remove ALL of this nonsense-code and use:

sub copy_and_clear
	doc = thisComponent
	source = doc.NamedRanges.getByName("TalList").ReferredCells
	target =  doc.NamedRanges.getByName("TalOwn").ReferredCells
	empties = target.queryEmptyCells().getByIndex(0)
	r = 0
	for each cell in  source.queryContentCells(15).Cells
		empties.getCellByPosition(0, r).Formula = cell.Formula
		r = r+1
	next
	source.clearContents(15)
end sub

[Madmaxneo]

Thanks, that worked perfectly!
I think all of the macros and formulas are done. Now all I need to do is make it easier to read…lol.

[karolus]

@Madmaxneo

Now all I need to do is make it easier to read…lol.

Does this mean, youre going to break it again?

[Madmaxneo]

@karolus

Does this mean, youre going to break it again?

Haha, no. I have learned enough that if I do move something that isn’t tied directly to a named range I can go in and change the references. I’ve had to do this before.
Right now I am changing merged cells and aligning things up along with changing the color coding so it’s more balanced and doesn’t look so chaotic.

[karolus]

@Madmaxneo

Right now I am changing merged cells and aligning things up along with changing the color coding so it’s more balanced and doesn’t look so chaotic.

Now I’m pretty sure… Madmax will break it …

[Madmaxneo]

Not yet it hasn’t broken. Just not exactly sure what colors to use to make it not look so chaotic or bland…lol