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.