How to write macro to get hyperlink work

[bonny]

Hello…

I have trouble making hyperlink within macro. For example just create empty spreadsheet and create another sheet within so we have 2 empty sheets. In Sheet1 A:1 just paste

=HYPERLINK("#Sheet2")

For A:3 create macro and execute it

Sub TestMacro
    dim document as object
    oSheet = thiscomponent.sheets.getByName("Sheet1")
    oCell = oSheet.getCellRangeByName("$A$3")
    xx="=HYPERLINK(""#Sheet2"")"
    oCell.setString(xx)
End Sub

So sheet1 now have something on cell A1 and A3. the both should be link but just A1 works.
If jou press CTRL+click on Sheet1 A1 the Sheet2 is selected.
If you press CTRL+click on Sheet1 A3 nothing happens

I tested ou xubuntu 18.04LTS
LibreOffice Version: 6.0.7.3 Build ID: 1:6.0.7-0ubuntu0.18.04.10

and same after upgrading to
LibreOffice Version: 6.3.3.2 Build ID: 1:6.3.3-0ubuntu0.18.04.1

[mikekaganski]

Replace oCell.setString(xx) with oCell.setFormula(xx)

[Lupp]

Unfortunately @mikekaganski posted his answer as a comment.
Without claiming a preference I would like to state that recent versions of LibreOffice come with an API where SheetCell objects have a property .Hyperlink which makes the usage of the .Formula property dispensable.

Sub test
a1 = ThisComponent.Sheets(0).getCellByPosition(0,0)
a1.String    = "Jump to Sheet2.B12:E19"
a1.hyperlink = "#Sheet2.b12:e19"
a2 = ThisComponent.Sheets(0).getCellByPosition(0,1)
a2.Formula   = "=HYPERLINK(""#Sheet1.h6:j10"";""Jump to Sheet1.H6:J10"")"
End Sub  

There is, however, a bug concerning the sensitive area of the respective cell.