How to return #N/A

Java, C++, C#, Delphi... - Using the UNO bridges
Post Reply
saleem145
Posts: 130
Joined: Mon Jul 02, 2012 4:47 pm

How to return #N/A

Post by saleem145 »

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
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: How to return #N/A

Post by MrProgrammer »

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
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: How to return #N/A

Post by Charlie Young »

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.1.1
Windows XP
saleem145
Posts: 130
Joined: Mon Jul 02, 2012 4:47 pm

Re: How to return #N/A

Post by saleem145 »

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
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: How to return #N/A

Post by Charlie Young »

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.1.1
Windows XP
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: How to return #N/A

Post by Charlie Young »

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


	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.1.1
Windows XP
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: How to return #N/A

Post by Charlie Young »

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

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.1.1
Windows XP
saleem145
Posts: 130
Joined: Mon Jul 02, 2012 4:47 pm

Re: How to return #N/A

Post by saleem145 »

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
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: How to return #N/A

Post by Charlie Young »

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.1.1
Windows XP
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: How to return #N/A

Post by MrProgrammer »

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
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: How to return #N/A

Post by Charlie Young »

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.1.1
Windows XP
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: How to return #N/A

Post by Charlie Young »

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

Code: Select all


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.1.1
Windows XP
saleem145
Posts: 130
Joined: Mon Jul 02, 2012 4:47 pm

Re: How to return #N/A

Post by saleem145 »

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to return #N/A

Post by Villeroy »

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: How to return #N/A

Post by Charlie Young »

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.1.1
Windows XP
Post Reply