Macros : For Each Cell in LibreOffice Basic ?

users
#1

Hello,

I have to create a macro that read through a defined cell range, and for
each cell, will assign it to a variable (to compare it to another cell),
then it goes on the next cell.

In my researches, I found that we can do this with a For Each Cell in VBA
for Excel, but i can't seem to find the code that would do the same thing in
LibreOffice Basic.

Hope I've been clear enough, and thanks for your help.

#2

Hi Tom,

I have to create a macro that read through a defined cell range, and for
each cell, will assign it to a variable (to compare it to another cell),
then it goes on the next cell.

In my researches, I found that we can do this with a For Each Cell in VBA
for Excel, but i can't seem to find the code that would do the same thing in
LibreOffice Basic.

Yes, you're clear :slight_smile:

I can see two ways to achieve your goal :
1. Browsing the cell range
2. Browsing the cell range data

1. Browsing a cell range

You have to create an enumerator which will give you the means to browse the range. The enumerator cannot be directly created from the range: you'll need a "range of ranges" (see below) in which you insert the range to browse object.

8< ------------------------------------------------------
Dim MyRanges As Object 'a range of ranges
Dim MyEnum As Object 'the enumerator
Dim TheCell As Object 'each of the individual cells

MyRanges = ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges")
MyRanges.insertByName("some arbitrary name", MyRangeToBrowse)
MyEnum = MyRanges.Cells.CreateEnumeration
Do While MyEnum.hasMoreElements
  TheCell = MyEnum.NextElement
  'do smthg with the cell object
Loop
------------------------------------------------------ >8

Note that empty cells in the range will NOT be made avalaible (they are skipped by the enumerator).

2. Browsing cell range data

Once you've got the range to browse object, just refer to its .DataArray property and play with it. You'll have to use an external array as a buffer.

8< ------------------------------------------------------
Dim MyArray As Variant 'buffer array

MyArray = MyRangeToBrowse.DataArray 'MyArray is set according to the range dimensions

'do smthg with the array items
'and finish with:
MyRangeToBrowse.DataArray = MyArray
------------------------------------------------------ >8

Note that the .DataArray property (thus MyArray as well) is a nested array : items are referred to as MyArray(i)(j), NOT as MyArray(i, j)

HTH,

#3

I remember writing code to go through cells... I don't remember if it was in AndrewMacro.odt or OOME.odt...

Can't look it up now...

Much depends on things like sizes and what you want to compare....

Coming off a long shift, need to sleep, if you can't find it, let me know.

⁣Sent from BlueMail ​