VLOOKUP in macro gives error

English
, ,
#1

Hi folks,

I’m trying to use the function VLOOKUP inside a macro from calc, but always get the following error message…

BASIC runtime error
An exception occurred
Type: com.sun.star.uno.RuntimeException
Message: .

Im running LibreOffice on Linux:
Version: 6.4.5.2
Build ID: 1:6.4.5-0ubuntu0.20.04.1

What could be the reason for this?

This is my code…

Sub Main

Dim ticket_array(0) As Integer

sheet = ThisComponent.Sheets.getByName("Kalkulation")
cellG9 = sheet.getCellByPosition(6,8)
ticket_string = cellG9.getString()

sSeparator = ", "
ticket_array = split(ticket_string,sSeparator)


For Each i in ticket_array()

  FA = CreateUnoService("com.sun.star.sheet.FunctionAccess")
  args = Array(i,sheet.getCellRangeByName("tickets"),9,0)
  print FA.callFunction("VLOOKUP", args)

Next i

End Sub

Best regards,
Stefan

#2

I’m not sure that after split() your ticket_array contain correct values - function Split() return array of strings

#3

For me work this code:

Option Explicit 

Sub Main
Dim ticket_array As Variant, FA As Variant, args As Variant
Dim i As Long 
Dim sheet As Variant, cellG9 As Variant, oTicketRange As Variant
Dim ticket_string As String, sSeparator As String
	sheet = ThisComponent.Sheets.getByName("Kalkulation")
	cellG9 = sheet.getCellByPosition(6,8)
	ticket_string = cellG9.getString()
	
	sSeparator = ", "
	ticket_array = split(ticket_string,sSeparator)
	oTicketRange = sheet.getCellRangeByName("tickets")
	FA = CreateUnoService("com.sun.star.sheet.FunctionAccess")
	For i = LBound(ticket_array) To UBound(ticket_array)
		args = Array(Val(ticket_array(i)),oTicketRange,9,0)
		print FA.callFunction("VLOOKUP", args)
	Next i
End Sub

UPDATE Please try with your Calc this sample - VLOOKUP with CallFunction.ods

#4

Okay, I get the same error message with that code. Seems like there’s something wrong with my LibreOffice then. :frowning:

#5

How do you describe named range “tickets”? How many columns there?

#6

@linux_steve I have added sample test data to my answer. Try it. Let’s make sure the problem is not with the LibreOffice.

#7

I just tried to strip the code down, it’s the same with this…

Sub Main

  sheet = ThisComponent.Sheets.getByName("Kalkulation")

  FA = CreateUnoService("com.sun.star.sheet.FunctionAccess")
  args = Array(27146,sheet.getCellRangeByName("tickets"),9,0)
  print FA.callFunction("VLOOKUP", args)

End Sub
#8

Is there value 27146 in first column of “tickets”?

#9

Yes, I just added the sheet. This is how my data looks like and it’s not working. The one you uploaded works for me though. How can I get my data to work?

#10

Don’t be discouraged, it can happen to anyone :slight_smile:

You try get named range “tickets” from ‘Kalkulation’ sheet. not from ‘Tickets’ sheet, Just change sheet.getCellRangeByName("tickets") to ThisComponent.Sheets.getByName("Tickets").getCellRangeByName("tickets")

#11

Ahhhhh, it’s not wokring because “tickets” is on a different sheet than “Kalkulation”. That’s it! It’s working now! Many thanks!!! :slight_smile:

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