How to return #N/A

Java, C++, C#, Delphi, ??? - Using the UNO bridges

How to return #N/A

Postby saleem145 » Tue Jul 10, 2012 1:29 am

Hello,

Question of the day --

how do I return #N/A. I tried to return it as a string but then =ISNA() returns false.

Thanks again for your help.

Saleem
OpenOffice 3.4.0
Mac OS X 10.5.8
saleem145
 
Posts: 130
Joined: Mon Jul 02, 2012 4:47 pm

Re: How to return #N/A

Postby MrProgrammer » Tue Jul 10, 2012 9:12 pm

Return the function =NA().

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
OpenOffice 3.2.0 Build 9483 on Mac OS X 10.6.8.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 1743
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: How to return #N/A

Postby Charlie Young » Tue Jul 10, 2012 9:50 pm

MrProgrammer wrote:Return the function =NA().

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.


I have been trying various tricks along those lines, but in an add-in function, assigning =NA() to a c++ Any in the most obvious way just gives the text "=NA()" in the cell.
Apache OpenOffice 4.0.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1438
Joined: Fri May 14, 2010 1:07 am

Re: How to return #N/A

Postby saleem145 » Tue Jul 10, 2012 10:23 pm

If I type in =NA() into an excel cell then it will return a #N/A. That is fine, but we need to assign a C++ Any a value of #N/A. Is there a way to call a worksheet function from C++ and assign the result to a C++ Any.

If I understand Charlie correctly this approach does not work correctly anyway??

Thanks,

Saleem
OpenOffice 3.4.0
Mac OS X 10.5.8
saleem145
 
Posts: 130
Joined: Mon Jul 02, 2012 4:47 pm

Re: How to return #N/A

Postby Charlie Young » Tue Jul 10, 2012 11:21 pm

saleem145 wrote:If I type in =NA() into an excel cell then it will return a #N/A. That is fine, but we need to assign a C++ Any a value of #N/A. Is there a way to call a worksheet function from C++ and assign the result to a C++ Any.

If I understand Charlie correctly this approach does not work correctly anyway??

Thanks,

Saleem


I tried it with XFunctionAccess, among other things. No, that didn't work.
Apache OpenOffice 4.0.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1438
Joined: Fri May 14, 2010 1:07 am

Re: How to return #N/A

Postby Charlie Young » Wed Jul 11, 2012 2:02 am

Probably worth noting.

Using c++ automation (not an add-in function), if I have a CellRange, call it naRange, I can fill its DataArray with #N/As by using the void Any constructor Any().

Code: Select all   Expand viewCollapse view

   Reference<XCellRangeData> naData (naRange, UNO_QUERY);
   Reference< XColumnRowRange > xColRows(naRange,UNO_QUERY);
   Reference< XTableColumns > xCols = xColRows->getColumns();
   Reference< XTableRows > xRows = xColRows->getRows();
   long naRows = xRows->getCount();
   long naCols = xCols->getCount();

   Sequence<Sequence<Any>> naDataArray(naRows);
   for(i = 0;i < naRows;i++)
   {
      naDataArray[i] = Sequence<Any>(naCols);
      for(j = 0;j < naCols;j++)
         naDataArray[i][j] = Any();
   }
   naData->setDataArray(naDataArray);



but trying to use Any() in this fashion as a return from an add-in function doesn't work.
Apache OpenOffice 4.0.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1438
Joined: Fri May 14, 2010 1:07 am

Re: How to return #N/A

Postby Charlie Young » Wed Jul 11, 2012 12:44 pm

The plot thickens.

The following does work. It returns #N/A if passed a number, and just echoes the input parameter if passed a string.

Code: Select all   Expand viewCollapse view
Any SAL_CALL MyService1Impl::NAIFnotString( Any const & inAny )
      throw (RuntimeException)
{
   int isString = (inAny.getValueTypeName() == OUString(RTL_CONSTASCII_USTRINGPARAM("string")));

   return isString ? inAny : Any();
}


Of course this is useless, since it could easily be done with built-in functions. The problem with the add-in though seems to occur only when I try to return an array. Maybe there are more things to try?
Apache OpenOffice 4.0.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1438
Joined: Fri May 14, 2010 1:07 am

Re: How to return #N/A

Postby saleem145 » Wed Jul 11, 2012 1:57 pm

It seems to return an empty string for me when I try to return it as part of an array.

Saleem
OpenOffice 3.4.0
Mac OS X 10.5.8
saleem145
 
Posts: 130
Joined: Mon Jul 02, 2012 4:47 pm

Re: How to return #N/A

Postby Charlie Young » Wed Jul 11, 2012 3:48 pm

saleem145 wrote:It seems to return an empty string for me when I try to return it as part of an array.

Saleem


I get an empty string in the first entry which I'm trying to make #N/A, but subsequent such entries are zeros.
Apache OpenOffice 4.0.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1438
Joined: Fri May 14, 2010 1:07 am

Re: How to return #N/A

Postby MrProgrammer » Thu Jul 12, 2012 4:34 am

saleem145 wrote:Is there a way to call a worksheet function from C++ and assign the result to a C++ Any.
In Basic, the statement ThisComponent.Sheets(0).getCellByPosition(0,0).setFormula("=NA()") assigns the =NA() function to A1. I tried it and it worked. =ISNA(A1) returns TRUE. I'll let someone else translate that from Basic to C++.
Mr. Programmer
OpenOffice 3.2.0 Build 9483 on Mac OS X 10.6.8.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 1743
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: How to return #N/A

Postby Charlie Young » Thu Jul 12, 2012 4:56 am

MrProgrammer wrote:
saleem145 wrote:Is there a way to call a worksheet function from C++ and assign the result to a C++ Any.
In Basic, the statement ThisComponent.Sheets(0).getCellByPosition(0,0).setFormula("=NA()") assigns the =NA() function to A1. I tried it and it worked. =ISNA(A1) returns TRUE. I'll let someone else translate that from Basic to C++.


What we are discussing here are add-in functions, which are a different animal. In this case, the function cannot use setFormula, as the cell value(s) is/are set by the return value(s) of the addin function. I'm sure you know how array formulas work, and you know you can't construct one that enters a formula in a cell (other than the array formula itself) -- this is the same deal.

The problem we're having here now is that while we can produce an #N/A in a single cell by returning a c++ void Any value, it won't let us assign these as elements in a returned array. I think I have a rather clumsy workaround: have the add-in function assign the string "#N/A" as Saleem originally tried, but don't use the add-in function directly, use it with an IF statement that looks for the #N/A and acts on that. I'm looking at examples, but there are complications here I'm still looking at.
Apache OpenOffice 4.0.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1438
Joined: Fri May 14, 2010 1:07 am

Re: How to return #N/A

Postby Charlie Young » Thu Jul 12, 2012 5:25 am

As an example, consider this function, which attempts to enter the NA() function in a cell specified by its arguments

Code: Select all   Expand viewCollapse view

Function ChangeCellFormula(row As Long,col As Long,sheet As Long) As String
   Dim oCell As Object
   
   oCell = ThisComponent.Sheets(sheet).getCellByPosition(col,row)
   oCell.setFormula("=NA()")
   
   ChangeCellFormula = "#N/A"
   
End Function


it will show #N/A in the cell in which it is entered, but it won't change any cell formula. Imagine if it could, and ChangeCellFormula(0;0;0) were entered in Sheet1.A1 :!:
Apache OpenOffice 4.0.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1438
Joined: Fri May 14, 2010 1:07 am

Re: How to return #N/A

Postby saleem145 » Fri Jul 13, 2012 1:37 pm

One thought -- do not know if we have access to the OpenOffice source code -- if so we can look at the Any() constructor and see what it is setting the object to, to set it to #N/A and do the same in the code.
OpenOffice 3.4.0
Mac OS X 10.5.8
saleem145
 
Posts: 130
Joined: Mon Jul 02, 2012 4:47 pm

Re: How to return #N/A

Postby Villeroy » Fri Jul 13, 2012 2:04 pm

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17294
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to return #N/A

Postby Charlie Young » Fri Jul 13, 2012 2:22 pm

saleem145 wrote:One thought -- do not know if we have access to the OpenOffice source code -- if so we can look at the Any() constructor and see what it is setting the object to, to set it to #N/A and do the same in the code.


We have access to the source code, though that is a jungle I haven't explored yet.

I think the issue here isn't with the Any() constructor, which I wouldn't want to mess with in any case, but with the way Any[][] is handled by add-in. The API documentation for AddIn says:

any[][]
for (two-dimensional) arrays of mixed contents. Each any will contain a double or a string, depending on the data.


The docmentation for setDataArray says:

fills the cell range with values from an array.

The size of the array must be the same as the size of the cell range. Each element of the array must contain a double or a string.



and Any(), being void, is neither a double or a string, but could be interpreted as such in some cases.

And as we have discovered, setDataArray will take an Any() and interpret it as #N/A, and so will add-in if the return is a scalar. It looks to me like add-in is doing more rigorous checking on the types for Any[][]. It could be something to mention on the issue tracker, but I doubt if it is a very common problem.
Apache OpenOffice 4.0.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1438
Joined: Fri May 14, 2010 1:07 am


Return to External Programs

Who is online

Users browsing this forum: No registered users and 1 guest