How to get the values of a row of cells in Calc into a one-dimensional array

English
,
#1

Hi I am new to Libre Office Basic. I do have some background in Visual Basic.net, but it has been many years ago. What I am trying to accomplish in part is to get the values of a range of cells into a one-dimensional array. I declared the array thus:

Dim Data(14) as Single

inside a For Next loop. I want the array to be reset every time through the loop.

So the pertinent code is as follows:
Sub Main

Dim i, j As Integer
For i = 3 To shtCount Step 3
Rem I want Data() to be reset every time through the loop
rem Dim i as Integer
Dim Data(14) as Single
oSheet = ThisComponent.Sheets(i)
Rem oRng = oSheet.getCellrangeByPosition(3,33,19,33) 'D34:S34(left, top,right,bottom)
oRng = oSheet.getCellRangeByName(“D34:S34”)
Rem oRng = oSheet.Range(“D34:S34”)
Data = oRng.Data
j = myMatch(array(Data))
(subsequent code omitted)
End Sub

I’ve also tried to get the Range with the Rem’ed out code:
oRng = oSheet.getCellrangeByPosition(3,33,19,33) 'D34:S34(left, top,right,bottom)

So when I watch Data in the watch window, before the line

Data - oRng.Data is executed, Data is a one-dimensional array populated with zeros as expected. After execution of the line, Data becomes a two-dimensional array with the expected values in the second dimension. My question is why, and how do I keep a second dimension from being added and get the values in the first dimension as expected?

Another question would be why when I enter the Function myMatch (in this same module the parameter is missing?

Function MyMatch(matchData() as Single) as Integer
Dim i as Integer
Dim mPosition as Integer
Dim theMax as Single

theMax = 0
mPosition = 0
For i = LBound(matchData) to UBound(matchData)
	If matchData(i) > theMax Then
		theMax = matchData(i)
		mPosition = i	
	end If	
next i
myMatch =  mPosition

End Function

Do you need anymore information?

#2

disask79780verySpecialThirdSheetEval.ods (21.2 KB)

The suggestion contained in the attached example does not try to answer the questions, but gives a clue how to solve the underlying task either without any “macro” or with the help of a more generally applicable user-defined function.
If you (the OQ) insist on a solution along your own approach, you will need to learn a lot about the LibreOffice API and the way it’s applied using Basic.
Anyway:

  • Small type in Basic covers the values -128 through +127. Calc doesn’t know the type.
  • cellRange.DataArray cellRange.Data always is a 1D-array of elements describing row content, and being in turn equally long 1D-arrays of Double type. A NAN value is used to mark cells containing text.
  • Your arrays have 15, 16, and 17 elements respectively. You need to tidy that up.
  • LibO API mostly prefers 0-based indexing.
  • :::

<edit about 2022-07-21 16:25 UTC>
Thanks to @Sokol92!
Because I rarely use the cellRange.Data property, I wrongly wrote .DataArray in place of .Data. The related explanation then applied to .Data as originally intended. Sorry for the mess I caused. I will now strke out the wrong content, and add the intended one.
To be clear: The stucture of the properties .Data and .DataArray is the same. Concerning the types of elements there is the difference that .DataArray has elements of variant type which also can return strings (may be empty).
</edit>

#3

Thanks for your reply. the actual solution requires examining 40 sheets. I am currently using a spreadsheet with only three sheets to be examined for testing purposes. That’s why I opted to try to use a macro. The test data requires me to structure the loop to skip certain irrelevant sheets. When I get the actual data, I will not need to skip sheets.

Nevertheless, I may opt for your advice and just do the task manually. I have not been able to find sufficient documentation of the Libre Office Basic API. So I am reduced trial and error or finding code snippets online that I can try.

How may I proceed to learn what I need to know to do such a task in the future? Any help in finding instructional material and adequate documentation of the API would be appreciated

Thanks again.

#4

Not certainly in that way. Each element of the result contains a double or a string. Empty cell values are converted to zero-length strings.

#6

in terms of the Size all three ( …DataArray, …Data or …Formula ) returns the same, a nested array of array[s]
there is only a difference on the Contents:
…Data return doubles (in case of empty cells or string its the close-to-zero double-value )
…DataArray return String in case of Stringcontent, double in case of numeric content and also close-to-zero double-value empty string for empty cells.
…FormulaArray returns always strings, the true Formula (if there is one) or the Content as String.

#7

How to get the values of a row of cells in Calc into a one-dimensional array 

rg = ThisComponent.Sheets(0).getCellRangeByPosition(0, 0, 9, 0) 'A1:J1
data = rg.getDataArray()
row = data(0)

data(0) is the first array within the data array having the first row of data as you can find out using the variable watch window.
Within that row array strings are strings.
Empty cells are represented by the empty string.
All numeric data are represented by their actual unformatted cell values (doubles).
Errors are represented by the Empty Basic type.

Similar:

formulas = rg.getFormulaArray() 'returns a 2D array of strings.
row = formulas(0)

Empty cells are represented by the empty string.
All numeric constants are represented by their actual unformatted numeric strings.
Numeric strings have the leading apostrophe (for instance '00123)
Instead of calculation results, this array contains the formulas in English notation as stored in the document’s XML source code.

#8

I’ll try to clarify. The leading apostrophe is placed in front of the string, which can be interpreted as the number, date, logical value in the en_US locale.

#9

its a method on the cellrange-object …Typo??

#10

Yes, it’s a typo. Fixed.
Thank you.

#5

Try this

Function DataArrayTo1D(aIn)
'''	Convert 1D cell range data array (array of arrays) to 1D array.
'''
'''	Argument:
'''		aIn:
'''			1D array of arrays (one row/column).
'''	Returns: 1D array (vector).
'''		If the input data array is not a vector,
'''		execution will terminate and the input array is returned.
'''
	On Local Error GoTo HandleErrors
	Dim aOut, c&, r&, bByRow As Boolean

	If UBound(aIn) > 0 And UBound(aIn(0)) > 0 Then
		MsgBox "Data array is not one row or one column (not a vector)." _
		 , MB_ICONEXCLAMATION, "Error in DataArrayTo1D"
		DataArrayTo1D = aIn
		Exit Function
	End If
	bByRow = (UBound(aIn) = 0)  'And UBound(aIn(0)) >= 0

	If bByRow Then
		ReDim aOut(UBound(aIn(0)))  'number of columns (upper bound of nested array)
	Else
		ReDim aOut(UBound(aIn))  'row count
	End If

	' NOTE: Dimension 1 is used by default and omitted.
	For r = LBound(aIn) To UBound(aIn)
		For c = LBound(aIn(r)) To UBound(aIn(r))
			If bByRow Then
				aOut(c) = aIn(r)(c)
			Else
				aOut(r) = aIn(r)(c)
			End If
		Next c
	Next r

	DataArrayTo1D = aOut
	Exit Function

HandleErrors:
	MsgBox Error, MB_ICONEXCLAMATION _
	 , "Error " & Err & " at line " & Erl & " in DataArrayTo1D()"
End Function

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.