How to iterate through a range of cells in a Basic macro?

English
,
#1

Hello,

I’m trying to write a user-defined function that can iterate through cells in a range and do some operation based on the text in the cells. For example, I’d like a CountText function that I could invoke in the workbook as a cell formula (something like =CountText(A1:B2)), and it would return the number of cells in A1:B2 whose text was the word “Text”. This is what I’ve come up with so far:

Function CountText(range)
    Dim result As Integer
    result = 0

    For col = 0 To range.Columns.getCount() - 1
        For row = 0 To range.Rows.getCount() - 1
            Dim cell
            cell = range.getCellByPosition(col, row)
            If StrComp(cell.String, "Text") = 0 Then
                result = result + 1
            End If
        Next
    Next

    CountText = result
End Function

When I invoke this in a cell formula =CountText(A1:B2) I get this error:
“BASIC runtime error. Object variable not set.”
The message highlights the line with the first For loop. I wrote these loops based on the answer in this post (cycle through Cells in a Range). What am I missing here?

I see that this method of iterating over column and row counts only works for rectangular ranges, but what happens if range is a union (something like =CountText(A1:B2~C3:D4))? Is there some way to use a For Each loop for this?

I also get a different error if I try to invoke it with just one cell in the range (=CountText(A1)). Is it not legal to have a range object with only one cell?

I’d really appreciate some pointers in the right direction with this.
Thank you!

#4

The simplest answer to your question about the Object variable not set error is that when you pass what looks like a range address to a UDF it pops up in LO BASIC as a 2-dimensional array of the data in that range. If you come from Excel, this is confusing…LO BASIC has neither sense for ThisCell as an object representing the calling cell nor the contents of a passed range as a range object. This is why @KamilLanda uses sRange$ in his COUNTTEXT signature and then passes the address as a quoted string.

This, for example, works in Excel:

Function CountText(range As range)
    Dim result As Integer
    result = 0

For Each cell In range.Cells
    If StrComp(cell.Value, "Text") = 0 Then
        result = result + 1
    End If
Next

    CountText = result
End Function

Back to LO BASIC, what you see coming into the macro is similar to invoking the UNO .getDataArray() on a true range object that you formulate within the macro itself, but when coming in from a UDF the data are in the BASIC 2D array format, not the UNO nested array format. That is, you access the array with myArray(col,row) not with myArray(col)(row) as you would for the result of a .getDataArray() call.

So, in LO BASIC just start right in on the 2D array:

Function CountText(range)
    Dim result As Integer
    result = 0

    For col = LBound(range,1) To UBound(range,1)
        For row = LBound(range,2) To UBound(range,2)
            Dim cell
            If StrComp(range(col,row), "Text") = 0 Then
                result = result + 1
            End If
        Next
    Next

    CountText = result
End Function
2 Likes
#5

Thank you for the explanation. These are the details I was looking for, and I wish LO BASIC was more well documented!

Just to clarify: in the example code you write, would range always be a 2D array of strings? What if the cells were formatted as numbers in the spreadsheet?

#8

To learn the most important details about LibreOffice Basic (still almost the same as OpenOffice Basic), you should refer to the famous texts (table of documents: the book and the “Useful Informations”) by Andrew Pitonyak.
Despite the titles of these texts, it remains that Basic itself offers only fundamental functionality of a programming language. The rest are some useful predefined variables, and the relatively short and wide “bridge” to the LibreOffice API (ApplicationProgrammingIntertface). Andrew Pitonyak shows how to use the API by Basic code.

Actually … Variant/Double or Variant/String (see also my answer below).

1 Like
#2

You need add the used Sheet to the function, for example:

Function COUNTTEXT(iSheet%, sRange$, optional sWord$) 'count the sWord in sRange in iSheet
	if isMissing(sWord) then sWord="Text" 'default word for counter is: Text
	dim result&, oSheet as object, data(), oRange as object, row(), s$
	oSheet=ThisComponent.Sheets(iSheet-1) 'current sheet
	oRange=oSheet.getCellRangeByName(sRange) 'get range
	data=oRange.getDataArray() 'strings in range
	for each row in data 'traverse array with data
		for each s in row
			if s=sWord then result=result+1
		next
	next
	COUNTTEXT=result
End Function

and formula is (for word Text): =COUNTTEXT(SHEET(); "A1:B2")
or with other word: =COUNTTEXT(SHEET(); "A1:B2"; "Hello")

1 Like
#3

Or you can do it like this:

' Calculate the number of cells in a range with a txt value.
Function CountText(range, txt)
  Dim v
  CountText=0
  If Not IsArray(range) Then
    If v=txt Then CountText=CountText+1
  Else
    For Each v In range
      If v=txt Then CountText=CountText+1
    Next v  
  End If  
End Function

=COUNTTEXT(A1:A5; "a")

I don’t know of a way to pass range union (using the ~ sign) to UDF function.

Of course, this is a tutorial example of a UDF function to demonstrate the relevant constructs. You should always use the built-in Calc functions whenever possible.

2 Likes
#7

You expect LibreOffice Basic to “be” VBA. That’s wrong. Your function has a doubtable
Dim cell inside a loop, but it will nevertheless work if called from a Basic Sub with an actual SheetCellRange as argument.
If you try to call it with the given formula from a spreadsheet cell
you get the mentioned error because Basic UDF in LibreOffice don’t get passed the range as an object. They only get the data (Variant type) as a 2D-Array.
VBA manages to get a range in such a case, and to allow the user code at the same time to access the data without special measures. It’s something like “parameter overloading”.
If you think, you actually need the VBA-like feature while otherwise programming in LibreOffice Basic, you can dedicate one Module to mimic VBA partially by prefixing the option line
Option VBsupport 1.
Please only do so if you are sure you to understand the implications. There may be bad surprises, and many VBA constructs may still not be supported.

BTW: The mentioned 2D-array has elements of type Variant wich will return the actual type when asked with the Basic function like in
tN = Typename(element).

1 Like

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