Need some help with macro

[Cat22]

Here is the entire function, it is called by the spreadsheet reloading a “link to external data” html file every so many minutes. This works just fine. But if i then open writer and have it in the foreground and wait for the next refresh the macro fails as described below. I tried to mark the code showing where it fails, cant bold it tho, sorry but its where sheet = doc.CurrentController.ActiveSheet

Function PortfolioGain() as Currency
   Dim doc As Object
   Dim sheet As Object
   Dim sum as Variant
    
   doc = ThisComponent
   
    sheet = doc.CurrentController.ActiveSheet ' fails here
  
      startRow = getRowFromText(7, 90,  3, "CAVM")
      endRow =  getFirstEmptyRowInColumn(7, 3)
      ' loop from startrow to endrow 
      ' sum += (col4*col7)-(col4*col5)
     sum=0
      for r=startRow To endRow
          t1 = sheet.getCellByPosition(3, r).value ' col, row num shares
          t2 = sheet.getCellByPosition(6, r).value ' last value
          p1 = t1*t2
          
          t1 = sheet.getCellByPosition(3, r).value '  num of shares
          t2 = sheet.getCellByPosition(4, r).value '  purchase price
          p2 = t1*t2
          sum = sum + (p1 - p2)
      Next
      PortfolioGain = doRound(sum, 2)
End Function

Error Dialog message:

          BASIC runtime error.
 Property or method not found: ActiveSheet.

[Ratslinger]

Edited for clarity.

[Ratslinger]

Hello,

Based upon your question your function works in Calc. Are you then saying if you have a Writer doc open and you execute the function in Calc it fails? On which statement does it fail? Certainly there is more to the macro than what is presented.

[Cat22]

Edited my original question to make it clearer and posted more info as you requested

[librebel]

Hello @Cat22,

Please try:

Dim oDoc as Object : oDoc = ThisComponent
If oDoc.supportsService( "com.sun.star.sheet.SpreadsheetDocument" ) Then
    REM oDoc is a valid spreadsheet
End If

[Cat22]

That seems to work, thanks.
Too bad there isn’t decent documentation for libreoffice as its a great product