Re: [libreoffice-users] Macros : For Each Cell in LibreOffice Basic ?

Hi Tom,

Le 23/01/2018 à 09:35, stgmdn a écrit :
> 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 :)

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,
--
Jean-Francois Nifenecker, Bordeaux


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted