Hyperlink function acceded with FunctionAccess doesnt show anything, returns an object

English
, ,
#1

I am trying to use the hyperlink function from calc in a macro, following the official guide [Using Calc Functions in Macros] but the when calling the code in a cell it doesn’t show anything, but its returning an object, i know because i checked the result with ‘TypeName’, i can’t find how to solve it or how to work with returned objects, help?

This is the code i am using

REM  *****  BASIC  *****

Function ReturnTwitterHyperlink(TwitterLink As String)
	Dim initiateAt As Integer
	Dim endsAt as Integer
	Dim shortName as String
	sFunctions = createUnoService("com.sun.star.sheet.FunctionAccess")
	
	initiateAt = InStr(TwitterLink, ".com/") + 5
	
	endsAt = InStr(initiateAt, TwitterLink, "/")
	
	shortName = Mid(TwitterLink, initiateAt, (endsAt - initiateAt))

	hyperArgs1 = "https://" & TwitterLink
	hyperArgs2 = shortName
	
	result = sFunctions.callFunction( "HYPERLINK", Array(hyperArgs1, hyperArgs2) )
	
	ReturnTwitterHyperlink = result

End Function

Edit: Adding example

i want to call that on a cell with a link (in this case twitter), and get a hyperlink same as the Hyperlink function but slicing from the link a string to be used as the cell text, like this:

=RETURNTWITTERHYPERLINK("twitter.com/XData/status/1782772388343521457")

and get

XData

Basically, a custom Hyperlink function

#2

Do you want a hyperlink in a cell as a return value of your macro function (called from the actual cell)??

#3

Yes, so it works like a modified Hyperlink function, like this
=RETURNTWITTERHYPERLINK(B4)

#4

What is the problem with the original HYPERLINK() function?

Your macro function will set the Cell value only, but not the Formula of the cell (because the formula of the cell is YOUR function, but not the returned Hyperlink(URL;shortname) “combo”).

#5

There is no problem with the hyperlink function, I am creating a custom function to make easy to get the ‘cell text’ from a link and use it in the hyperlink function, of course i can get a specific string without using macros but if i can create my own functions to make more readable and easy to use spreadsheets then why not use it?

?? I don’t get it, so you cannot use the returned objects/values of calc functions? I don’t think so

#6

A cell fuction can return only with the textual or numeric VALUE of the cell, and the cell function can set only that property of the cell.
.
A Subroutine can set the Formula, and other properties of any cells. But you can not call it from a cell. You must launch it by an event of controlling objects (buttons, menu items, etc…)

#7

105063.ods (15.1 KB)

#8

The HYPERLINK() function is special in that it returns an array where the first element is the string result and the second element the hyperlink URI, plus presence of the function in a formula expression sets a special “this is a hyperlink” flag at the cell. That can’t be accomplished from a macro user defined function.

You could use

=HYPERLINK("https://"&A4;RETURNTWITTER(A4))

to feed HYPERLINK() the desired display string result.

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