Is there a fast method in Lotus 123 to get all cell values in a range into an array. I'm looking for something similar to Excel VBA where it's very fast.
Dim arrValues() As Variant
arrValues = rg.Value
The resulting array can also be manipulated and put back into the sheet; In my case I want to then put it into Excel. Basically replacing copy and paste. Operating on an array in memory is much faster than manipulating cells one at a time.
I know how to do it by reading cells one at a time but I'm looking for a faster method similar to Excel.
'Lotus Script
Dim r As Range
Set r = CurrentDocument.Ranges("A:A1..A:D4")
Dim x, y As Integer
For x = 0 To 3
For y = 0 To 3
Print (r.Cell(x, y).cellvalue)
Next
Next
Note that I'm specifically NOT tagging with VBA because this can only be answered by someone that knows Lotus Script. This is NOT about VBA.
How do I get Lotus 123 range values into array?
Found this in help files: Range.GetCellData method.
Gets the data in a range, referenced as a set of pointers to the contents of individual cells. This method allocates memory for copying the contents of the range, sets the values in memory, and returns an array pointer that can be used by an external C program.
Syntax
arraypointer = range.GetCellData(celldatatype)
Parameters celldatatype
Variant (CellDataType enumeration). The format for the returned cell data. The following table lists the allowed values for this parameter.
Value and Description
- $CellValue Return a pointer to an array of strings. The strings contain the formatted values that the cells evaluate to. Blank cells result in NULL pointers.
- $FormulaContents Return a pointer to an array of strings. This is similar to
$CellValue, except the strings are the contents of formula cells (for example, "+A1+@SUM(B1)"). All other types of cells result in NULL pointers.
- $Double Return a pointer to an array of doubles. Blank cells and labels are returned as zero. NA and ERR are encoded as invalid numbers.
Return values:
Long. A pointer to an array of pointers, one for each cell in the range. The array is ordered by row/column/sheet. For example, an array representing the range [A:A1..B:B2] would be given in the order A:A1, A:A2, A:B1, A:B2, B:A1, B:A2, B:B1, B:B2.
Usage
This method allocates the memory for the returned array internally, and the caller must free that memory.
