Libre Office not passing arrays to a user-defined function

English
,
#1

I am having trouble with a user defined function. I have pared down the function to this:

Function ARRAYTEST (a() as Variant) as String
Dim i as Integer
Dim result as string
i = 2
result = a(i)
ARRAYTEST = result
End Function

In the Calc spreadsheet I use the function:
=ARRAYTEST(A1:A5)
The cells A1:A5 contain a,b,c, ,e

When I run it I get this error on the line: result = a(i)
The error is: “Inadmissible value or data type. Index out of defined range.”

If I change the array to As String, I get this message: “BASIC runtime error. Object variable not set.”

Can anyone tell me what I am doing wrong?

Thanks,
Robert.

#2 
Function ARRAYTEST(data) As String
Dim i as Integer
Dim result as String
	
	i = 2
	result = data(i, 1)
	ARRAYTEST = result
	
End Function
#3

Thank you so much. That worked perfectly.

#4

I have a follow-on question. How do I ascertain how many elements are in data?

#5

For rows in data, you use:

rows = UBound(data)
#6

ubound( … ) returns the upper index, if you want »how many« you need to add 1.

rows = UBound(data)+1
#7

image
image1059×277 32.8 KB

#8

(With my pedant’s hat on) or strictly speaking, UBound(...) - LBound(...) + 1

#9

That worked perfectly in one function but I get an error in a second function.

If you look at the following two functions, they both have a statement:
for i = 1 to Ubound(data)
The first function (notblank) works perfectly. In the second function (namelist) it gives an error: “BASIC runtime error. Object variable not set.”

I cannot work out why. Do you have any ideas?

Thanks so much,
Robert.

Function notblank(data) As Integer
Dim i as Integer
Dim x as integer
Dim null as String
null = “”
x = 0
for i = 1 to Ubound(data)
if data(i, 1) <> null then
x = x + 1
else
end if
next i
notblank = x
End Function

Function namelist(data) As String
Dim result as String
Dim i as integer
Dim entry as integer
Dim null as String
Dim namearray(256) as String
result = “”
null =""
entry = 0

for i = 1 to UBound(data)
if data(i, 1) <> null then
entry = entry + 1
namearray(entry) = data(i, 1)
else
end if
next i

if entry = 0 then
namelist = null
else
if entry = 1 then
namelist = namearray(1)
else
for i = 1 to entry - 1
result = result & namelist(i) & ", "
next i
result = mid(result, 1, len(result) - 2)
result = result & " and " & namearray(entry)
namelist = result
end if
end if

end function

#10

Please attach a file with your code and example data.

#11

Functions.odt (19.9 KB)
Sample Data.ods (8.1 KB)

Thanks for taking the time to look at these.

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