I got error message while Runing Macro, Can Any One tell me where i made mistake and Correct Macro Code?

sv.thiyagarajan · January 8, 2024, 3:10am

Hi Friends,
I am getting error message while running macro in LibreOffice Calc. But, I am getting results in VBA.
Find the below screen shots.

Data

Original Macro Code Of VBA

and the Result Of VBA Code

LibreOffice Macro Code With Error Message

LibreOffice Code

Sub FilmLength_Convert_MinsToHrs

		Dim Mins As Variant 
		Dim i As Integer 
		
		Dim Doc As Object, Sheet As Object, Cell As Object, Cursor As Object
		
		Doc = ThisComponent : Sheet = Doc.Sheets(0)
		Cell = Sheet.getCellRangeByName("B2")
		
		Dim LastRow As Long
		Cursor = Sheet.createCursorByRange(Cell)
		Cursor.collapseToCurrentRegion
		LastRow = Cursor.Rows.Count
		
		
		ReDim Mins(LastRow-1)
		Print  LBound(Mins)+1
		Print  UBound(Mins)
		For i = LBound(Mins)+1 To UBound(Mins)
				Mins(i)(0) = MinsToHours(Mins(i)(0).Value)
		Next i
		
		Cell = getCellRangeByName("C2")
		Cursor = Sheet.createCursorByRange(Cell)
		Cursor.collapseToResize(1, LastRow-1).String = setDataArray(Mins)
End Sub


Function MinsToHours(ByVal m As Integer) As String
		
		MinsToHours = Int(m / 60) & "h" & (m Mod 60) & "m"
		
End Function

Can anyone help me to get the correct code and where i made mistake ?

karolus · January 8, 2024, 4:32am

Formula in C2:

=B2/1440

pull down, and format the Range: [H]"h "MM"m"
done!

alternativ:

=QUOTIENT(B2;60)&"h "&MOD(B2;60)& "m"

pulldown…done!

sv.thiyagarajan · January 8, 2024, 5:38am

Hi Karolus, thanks for replay Yes, This is i Know… But, I want to Know Macro Code and Where i made Mistake… in LiberOffice Macro. For VBA i got answers.

karolus · January 8, 2024, 5:51am

With bad VBA examples and completely guessed Basic translations you will never learn it.
Especially not, if someone else corrects your poetry.

sv.thiyagarajan · January 8, 2024, 6:41am

Hi @karolus , Finally I got the answers and i find my self were i made a mistake… Here is the Correct
LibreOffice Macro Code

LibreOffice Macro Code

Sub FilmLength_Convert_MinsToHrs 
			
			Dim Doc As Object, Sheet As Object, Cell As Object, Cursor As Object
			
			Doc = ThisComponent : Sheet = Doc.Sheets(0)
			Cell = Sheet.getCellRangeByName("B2")
			
			REM Findout HowMany Rows are there in Dynmaically
			Dim Height As Long
			Cursor = Sheet.createCursorByRange(Cell)
			Cursor.collapseToCurrentRegion()
			Height = Cursor.Rows.Count-1
			
			REM Fixing the Size Of the Row From B2 To  B6 -Last Row Of the Column B - Dynamically
			Cell = Sheet.getCellRangeByName("B2")
			Cursor = Sheet.CreateCursorByRange(Cell)
			Cursor.collapseToSize(1, Height)
			
			Dim Mins As Variant 
			Dim i As Integer
			Mins = Cursor.getDataArray()
			
			REM iteration the Rows in the Column B, Starts from B2 To End Of Used Row B6 Dynamically
			For i = LBound(Mins) To UBound(Mins)
					n = Mins(i)(0)
					Mins(i)(0) = MinsToHours(n) 
			Next i
			
			REM Write The Data in to the Column C  that is ....From Range C2 
			Cell = Sheet.getCellRangeByName("C2")
			
            Rem Fixing The Size Of the C2 To C6 Dynamically
            Cursor = Sheet.createCursorByRange(Cell)
			Cursor.collapseToSize(1, Height)
			Cursor.setDataArray(Mins)
	End Sub	
	
	
	Function MinsToHours(ByVal m As Integer) As String
			
			MinsToHours = Int(m / 60) & "h " & (m Mod 60) & "m"		
	End Function

and Here, is the Result in Screen Shots

karolus · January 8, 2024, 7:00am

sv.thiyagarajan:

Sub FilmLength_Convert_MinsToHrs 
			
			Dim Doc As Object, Sheet As Object, Cell As Object, Cursor As Object
			
			Doc = ThisComponent : Sheet = Doc.Sheets(0)
			Cell = Sheet.getCellRangeByName("B2")
			
			REM Findout HowMany Rows are there in Dynmaically
			Dim Height As Long
			Cursor = Sheet.createCursorByRange(Cell)
			Cursor.collapseToCurrentRegion()
			Height = Cursor.Rows.Count-1
			
			REM Fixing the Size Of the Row From B2 To  B6 -Last Row Of the Column B - Dynamically
			Cell = Sheet.getCellRangeByName("B2")
			Cursor = Sheet.CreateCursorByRange(Cell)
			Cursor.collapseToSize(1, Height)
			
			Dim Mins As Variant 
			Dim i As Integer
			Mins = Cursor.getDataArray()
			
			REM iteration the Rows in the Column B, Starts from B2 To End Of Used Row B6 Dynamically
			For i = LBound(Mins) To UBound(Mins)
					n = Mins(i)(0)
					Mins(i)(0) = MinsToHours(n) 
			Next i
			
			REM Write The Data in to the Column C  that is ....From Range C2 
			Cell = Sheet.getCellRangeByName("C2")
			
            Rem Fixing The Size Of the C2 To C6 Dynamically
            Cursor = Sheet.createCursorByRange(Cell)
			Cursor.collapseToSize(1, Height)
			Cursor.setDataArray(Mins)
	End Sub	
	
	
	Function MinsToHours(ByVal m As Integer) As String
			
			MinsToHours = Int(m / 60) & "h " & (m Mod 60) & "m"		
	End Function

You hero!
obviously:

Sub FilmLength_Convert_MinsToHrs 			
	Dim Doc As Object, Sheet As Object, Cell As Object, Cursor As Object			
	Doc = ThisComponent : Sheet = Doc.Sheets(0)
	Cell = Sheet.getCellRangeByName("B2")			
	Dim Height As Long
	Cursor = Sheet.createCursorByRange(Cell)
	Cursor.collapseToCurrentRegion()
	Height = Cursor.Rows.Count-1				
	Cell = Sheet.getCellRangeByName("B2")
	Cursor = Sheet.CreateCursorByRange(Cell)
	Cursor.collapseToSize(2, Height)
			
	Dim Mins As Variant 
	Dim i As Integer
	Mins = Cursor.getDataArray()
	For i = LBound(Mins) To UBound(Mins)
		n = Mins(i)(0)
		Mins(i)(1) = MinsToHours(n) 
	Next i
	Cursor.setDataArray(Mins)
			
End Sub	
	
	
	Function MinsToHours(ByVal m As Integer) As String			
			MinsToHours = Int(m / 60) & "h " & (m Mod 60) & "m"		
	End Function

JohnSUN · January 8, 2024, 7:53am

If you think about the problem a little more, you can write it like this:

Sub FilmLength_Convert_MinsToHrs
Dim Sheet As Variant, oRange As Variant, aData As Variant, aRow As Variant
Dim LastRow As Long
	GlobalScope.BasicLibraries.LoadLibrary("Tools")
	Sheet = ThisComponent.Sheets(0)
	LastRow = getLastUsedRow(Sheet)
	oRange = Sheet.getCellRangeByPosition(1,1,2,LastRow)
	aData = oRange.getDataArray()
	For Each aRow In aData
		aRow(1) = Format(Int(aRow(0))/1440,"[H]\h mm\m")
	Next aRow
	oRange.setDataArray(aData)
End Sub

sv.thiyagarajan · January 15, 2024, 2:09pm

Thanks Johnsun I learned New things… Finout Last Row Using GlobalScope.BasicLibraries.LoadLibrary(“Tools”) and its very Less Line Of Code …If i compare with My Code… Your Code very Less Line … Only 11 Lines…thanks…and got answers… again. … from your code…