Date: prev next · Thread: first prev next last
2018 Archives by date, by thread · List index

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("")
MyRanges.insertByName("some arbitrary name", MyRangeToBrowse)
MyEnum = MyRanges.Cells.CreateEnumeration
Do While MyEnum.hasMoreElements
        TheCell = MyEnum.NextElement
        'do smthg with the cell object
------------------------------------------------------ >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)

Jean-Francois Nifenecker, Bordeaux

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.