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

[Andrew Pitonyak]

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 ​

On Jan 23, 2018, 6:45 AM, at 6:45 AM, stgmdn  wrote:
>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.
>
>
>
>--
>Sent from: http://nabble.documentfoundation.org/Users-f1639498.html
>
>--
>To unsubscribe e-mail to: users+unsubscr...@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

-- 
To unsubscribe e-mail to: users+unsubscr...@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

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

[Jean-Francois Nifenecker]

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+unsubscr...@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

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

[stgmdn]

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.



--
Sent from: http://nabble.documentfoundation.org/Users-f1639498.html

-- 
To unsubscribe e-mail to: users+unsubscr...@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