1

Building a function to evaluate a string and return the results. However, svc and svc2 through an error, "Object Not Set". Though, when I run the code, the print statements clearly show that the objects are not null or nothing as they return FALSE.

What am I missing?

Is there another test that I could preform to make sure svc or svc2 are initialized?

Option VBASupport 1
Option Explicit

Function EVAL (str as String)
    dim svc as object
    dim svc2 as object

    SET svc = createUnoService( "com.sun.star.sheet.FunctionAccess" )
    SET svc2 = GetProcessServiceManager().createInstance("com.sun.star.sheet.FunctionAccess") 

    EVAL = svc2.callFunction ("Evaluate", str)
    EVAL = svc.callFunction ("Evaluate", str)
End Function

I now get error 91, cannot coerce argument during core reflection call!

Note: as noted in a response to this problem, there is an unresolved error in LibreOffice when calling Evaluate, so this problem will most likely remain unresolved until OpenOffice resolves their bug. =(

E Net Arch
  • 458
  • 5
  • 13
  • Try using `Set` on your assignment for svc and svc2. – Brian M Stafford Nov 07 '17 at 16:54
  • AFAIK, there isn't really a good way to do this. Perhaps it would help to give more details about what you want to do, and we could find a different kind of solution. What does your spreadsheet look like? – Jim K Nov 07 '17 at 17:40
  • FYI found something ... E1 = FORMULA (E3) ... produces the formula for the equation in ... E3 = C3 - D3 + E2 ... (standard bank balance equation). Where I wanted to use ... E3 = EVALUATE (E1) ... so that I could see the equation and adjust it as needed, this will suffice. Not an answer to the problem, but it might help others. – E Net Arch Nov 10 '17 at 02:38

1 Answers1

0

This is a misleading error message. svc and svc2 are initialized correctly. The issue is that Evaluate does not work as expected. A bug report has been filed at https://bugs.documentfoundation.org/show_bug.cgi?id=102076.

I could not get the following example to work at all.

Option VBASupport 1
Option Explicit

Sub Main()
    Evaluate ("A1:A2") ' Works both on Excel and on LibreOffice
    Evaluate ("TODAY()") ' Works on Excel, but Runtime Error on LibreOffice
End Sub

How to evaluate a string was asked at http://ooo-forums.apache.org/en/forum/viewtopic.php?f=9&t=3497. There weren't any simple answers.

EDIT:

To fix the new error, use this code.

EVAL = svc2.callFunction ("Evaluate", Array(str))

This produces the error message com.sun.star.container.NoSuchElementException, which I believe is correct because there is no such spreadsheet function EVALUATE.

To verify this, enter =EVALUATE() in a spreadsheet cell, which produces #NAME?.

Jim K
  • 12,824
  • 2
  • 22
  • 51
  • Ya, looks like this issue will be unresolved until the bug is repaired. – E Net Arch Nov 09 '17 at 05:02
  • I updated my code with the option lines, and used "SET" when assigning "svc" and "svc2". That helped get rid of "object not set error". However, as pointed out the Evaluate method doesn't work. It produces error 91, "cannot coerce argument type during core reflection call" – E Net Arch Nov 09 '17 at 05:05