Ask Your Question

Need some help with macro

asked 2018-01-09 15:16:06 +0100

calcUser gravatar image

updated 2018-01-09 16:15:14 +0100

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)
      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)
      PortfolioGain = doRound(sum, 2)
End Function

Error Dialog message:

          BASIC runtime error.
 Property or method not found: ActiveSheet.
edit retag flag offensive close merge delete


Edited for clarity.

Ratslinger gravatar imageRatslinger ( 2018-01-09 15:20:56 +0100 )edit


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.

Ratslinger gravatar imageRatslinger ( 2018-01-09 15:46:19 +0100 )edit

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

calcUser gravatar imagecalcUser ( 2018-01-09 16:16:10 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2018-01-09 16:24:36 +0100

librebel gravatar image

Hello @calcUser,

Please try:

Dim oDoc as Object : oDoc = ThisComponent
If oDoc.supportsService( "" ) Then
    REM oDoc is a valid spreadsheet
End If
edit flag offensive delete link more


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

calcUser gravatar imagecalcUser ( 2018-01-09 19:46:01 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-01-09 15:16:06 +0100

Seen: 71 times

Last updated: Jan 09 '18