Converting a simple Excel VBA macro to LibreOffice Calc BASIC macro

[Timon-d]

0

I want to run the below Excel VBA code in LibreOffice BASIC on a Raspberry PI, what results in a BASIC-runtime-error ‘423’ Cells. If I run the same VBA code on a different version of LibreOffice Calc (desktop version), it actually does work.

I already know that BASIC has limited support for VBA statements (Option VBASupport 1). Probably, the desktop LibreOffice has more support, therefore it does work there and not on the Raspberry Pi. I also see that the error results from this line of code: eRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row.

What the code does is, it searches for the next empty row of a table and prints the values I have written in X3 - X9. X10 is the amount of repetitions, because it’s for an assignment list that needs a new row for every repetition.

Rem Attribute VBA_ModuleType=VBAModule
Option VBASupport1
Sub findnextemptyrow
Dim x As Integer
eRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
enter code here
For x = eRow to eRow + Range("X10") - 1
Cells(x, 1).Value = Range("X3")
Cells(x, 2).Value = Range("X4")
Cells(x, 3).Value = Range("X5")
Cells(x, 4).Value = Range("X6")
Cells(x, 5).Value = Range("X7")
Cells(x, 6).Value = Range("X8")
Cells(x, 7).Value = Range("X9")

Since I have not much knowledge about both VBA and BASIC and I would have to delve in both languages. I was hoping this is an easy conversion for someone here. Any help is much appreciated!

[JohnSUN]

“enter code here”? You don’t get an error here, on this line, but get an ‘423’ error when accessing Cells?

[mauricio]

I’m not sure if you need the next empty row in down, because you used xlUp.

But, for get next row or cell empy, you can used next functions.

Sub Main
	doc = ThisComponent
	cell = doc.Sheets.getByIndex(0).getCellRangeByName("A1")
	
	row = getNextRow(cell)
	MsgBox row
	
	next_cell = getNextCell(cell)
	MsgBox next_cell.AbsoluteName
End Sub


Function getNextRow(cell) As Long
	' Get next empy row like long
	' Args: cell object (ScCellObj)
	cursor = cell.SpreadSheet.createCursorByRange(cell)
	cursor.collapseToCurrentRegion()
	getNextRow = cursor.RangeAddress.EndRow + 1
End Function


Function getNextCell(cell) As Object
	' Get next empy cell like range
	' Args: cell object (ScCellObj)	
	cursor = cell.SpreadSheet.createCursorByRange(cell)
	cursor.collapseToCurrentRegion()
	col = cursor.RangeAddress.StartColumn
	row = cursor.RangeAddress.EndRow + 1
	getNextCell = cell.SpreadSheet.getCellByPosition(col, row)
End Function

It’s easy change for get next cell up

[JohnSUN]

I think this code is similar to the task you described:

Sub copyX3X9toEmptyRowsX10times
Dim oSheet As Variant
Dim oSourceData As Variant
Dim nRows As Long, i As Long 
Dim oOutData As Variant
Dim oTempArray(0 To 6) As Variant
Rem Sheet1 in VBA in StarBasic is 0
	oSheet = ThisComponent.getSheets().getByIndex(0)	
Rem Get all data from cells X3:X10 as array
	oCellRangeByName = oSheet.getCellRangeByName("X3:X10").getDataArray()
Rem Value of cell X10 - count of rows
	nRows = oCellRangeByName(7)(0) - 1
Rem Is there any rows?
	If nRows < 0 Then Exit Sub 
Rem Create array wit all output data
	ReDim oOutData(nRows)
	For i = 0 To 6
		oTempArray(i) = oCellRangeByName(i)(0)
	Next i
	For i = 0 To nRows
		oOutData(i) = oTempArray
	Next i
	Globalscope.BasicLibraries.LoadLibrary("Tools")
Rem Number of last used row
	i = GetLastUsedRow(oSheet)
	oSheet.getCellRangeByPosition(0, i+1, 6, i+nRows+1).setDataArray(oOutData)
End Sub

[Timon-d]

Thanks a lot!! This code works just fine