Ask Your Question
0

custom function to test if cell contains a hyperlink

asked 2017-08-19 21:49:03 +0200

Rabbit gravatar image

Hi.

Is there a simple way to set up a custom function in LibreOffice Calc to test if a cell contains a hyperlink? Sort of like IFHYPERLINK.

I would like a Formula that could work like =IFHYPERLINK(A1,"Worksheet","")

Thanks!

edit retag flag offensive close merge delete

Comments

Thanks for everything so far. For some reason, if the hyperlink is removed from A1, the cell with the formula =CELL_HAS_HYPERLINK(0;0) in it still shows TRUE. If I delete the formula and type it back in, it updates to FALSE.

Rabbit gravatar imageRabbit ( 2017-08-26 04:23:09 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2017-08-20 00:30:52 +0200

Lupp gravatar image

https://ask.libreoffice.org/en/questi...

Generally: Type "extract hyperlink" into the "Search or ask your question" control. There will be matches.

https://ask.libreoffice.org/en/questi... (specifically if you aim at the HYPERLINK function.

You may simplify and use the code I posted in https://forum.openoffice.org/en/forum....

Hope you get answers knowing simpler means.

edit flag offensive delete link more
0

answered 2017-08-20 00:21:24 +0200

librebel gravatar image

updated 2017-08-24 09:18:28 +0200

Hello @Rabbit,

EDIT 2017-08-24 - made usable for ActiveSheet

EDIT2 - updated method of getting the Sheet index, Thanks @pierre-yves samyn

Function Cell_has_Hyperlink( lColumnIndex&, lRowIndex&, Optional lSheetIndex& ) As Boolean
REM Returns <TRUE> if the specified cell contains a Hyperlink.
REM The parameter indexes for Column, Row, and Sheet, are all zero-based.
REM Quietly fails if the specified Sheet index does not exist.
REM To use the current Sheet, you can omit the <lSheetIndex> argument.
    On Local Error GoTo Error_Exit
    If IsMissing( lSheetIndex ) Then lSheetIndex = ThisComponent.CurrentController.ActiveSheet.RangeAddress.Sheet   
    Dim oSheets As Object, oCell As Object
    oSheets = ThisComponent.getSheets()
    oCell = oSheets.getCellByPosition( lColumnIndex, lRowIndex, lSheetIndex )

    If HasUnoInterfaces( oCell, "com.sun.star.text.XTextFieldsSupplier" ) Then

        Dim oTextFields as Object, oTextField As Object
        oTextFields = oCell.getTextFields().createEnumeration()
        While oTextFields.HasMoreElements
            oTextField = oTextFields.NextElement
            If oTextField.getPropertySetInfo().hasPropertyByName( "URL" ) Then  REM found a URL TextField.
                Cell_has_Hyperlink = True
                Exit Function
            End If
        Wend

    End If

    Cell_has_Hyperlink = False
    Exit Function
Error_Exit:
    Cell_has_Hyperlink = False
'   Msgbox "Error in CELL_Has_Hyperlink( " & lColumnIndex & "," & lRowIndex & "," & lSheetIndex & " )"'
End Function

If you copy-paste that macro into your Standard Basic Macro Library, then you could write a Formula in Calc cells:

=CELL_HAS_HYPERLINK(0;0) ( Returns TRUE if cell A1 from ActiveSheet contains a hyperlink ). =CELL_HAS_HYPERLINK(0;0;0) ( Returns TRUE if cell A1 from Sheet1 contains a hyperlink ).

edit flag offensive delete link more

Comments

Thanks for your help. Librebel, is there a way to to use ActiveSheet instead of Sheet Index? I would like to be able to use this formula in multiple sheets without having to change the sheet index. Or am I not understanding something?

Rabbit gravatar imageRabbit ( 2017-08-24 04:28:33 +0200 )edit

Hello @Rabbit,

That would make the function much more useful indeed. i updated the macro, now it supports ActiveSheet as well. NB. the order of arguments has changed a little, plus a helper function getSheetIndex()

librebel gravatar imagelibrebel ( 2017-08-24 05:30:37 +0200 )edit

Hi @librebel my edit is only to add ' at the end of the commented Msgbox line (of course not needed in basic, but here for good formatting).

Regards

pierre-yves samyn gravatar imagepierre-yves samyn ( 2017-08-24 07:28:58 +0200 )edit

Merci @pierre-yves, it looks indeed much better that way

it seems that the color coding interprets everything after the apostrophe as a comment, including new lines, until it encounters another apostrophe...

librebel gravatar imagelibrebel ( 2017-08-24 07:50:52 +0200 )edit

By the way, the looping procedure on all the sheets is unnecessary. We have directly the sheet number in the property:

 ThisComponent.CurrentController.ActiveSheet.rangeAddress.sheet

Regards

pierre-yves samyn gravatar imagepierre-yves samyn ( 2017-08-24 08:17:17 +0200 )edit

i should've looked there before...

thanks and regards, lib

i updated the method and removed the obsolete helper function getSheetIndex() .

librebel gravatar imagelibrebel ( 2017-08-24 09:21:11 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-08-19 21:49:03 +0200

Seen: 305 times

Last updated: Aug 24 '17