I'm looking to convert a MS Excel Spreadsheet into a LibreOffice Calc Spreadsheet.
The Excel file contains a VBA Macro that iterates over a range of cells selected by the user, the Visual Basic code simply looking like this:
For Each Value In Selection
' Manipulate Value
Next Value
Where Selection
contains the cells in the worksheet manually selected by the user.
So, my question is: how can I reproduce this, accesing a user selected range of cells, in Libre Basic?
If you want to use the value of the cells
Sub Learn
Dim myController as Object, myRange as Object
Dim Tmp as Integer
Dim i, j
myController = ThisComponent.getCurrentController()
myRange = myController.getSelection().getDataArray()
'additional info that we can use
print myRange.AbsoluteName
print myRange.RangeAddress.StartColumn
print myRange.RangeAddress.StartRow
Tmp = 0
For Each i in myRange
For Each j in i
Tmp = Tmp + j
Next j
Next i
print Tmp
End Sub
If you want to manipulate the cell's value, use a 2 dimensional array
Sub Learn2
Dim myController as Object, myRange as Object
Dim newRange(0, 0)
Dim Tmp as Integer
Dim i, j, col, row
myController = ThisComponent.getCurrentController()
myRange = myController.getSelection().getDataArray()
Redim newRange(UBound(myRange, 1), UBound(myRange(0), 1))
row = 0
col = 0
For row = 0 to UBound(myRange, 1)
For col = 0 to UBound(myRange(0), 1)
newRange(row, col) = myRange(row)(col) * 2
Next col
Next row
myController.getSelection().setDataArray(newRange)
End Sub