How to return #N/A
How to return #N/A
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
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
Mac OS X 10.5.8
- MrProgrammer
- Moderator
- Posts: 4907
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: How to return #N/A
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.
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).
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).
- Charlie Young
- Volunteer
- Posts: 1559
- Joined: Fri May 14, 2010 1:07 am
Re: How to return #N/A
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.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.
Apache OpenOffice 4.1.1
Windows XP
Windows XP
Re: How to return #N/A
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
If I understand Charlie correctly this approach does not work correctly anyway??
Thanks,
Saleem
OpenOffice 3.4.0
Mac OS X 10.5.8
Mac OS X 10.5.8
- Charlie Young
- Volunteer
- Posts: 1559
- Joined: Fri May 14, 2010 1:07 am
Re: How to return #N/A
I tried it with XFunctionAccess, among other things. No, that didn't work.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
Apache OpenOffice 4.1.1
Windows XP
Windows XP
- Charlie Young
- Volunteer
- Posts: 1559
- Joined: Fri May 14, 2010 1:07 am
Re: How to return #N/A
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().
but trying to use Any() in this fashion as a return from an add-in function doesn't work.
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);
Apache OpenOffice 4.1.1
Windows XP
Windows XP
- Charlie Young
- Volunteer
- Posts: 1559
- Joined: Fri May 14, 2010 1:07 am
Re: How to return #N/A
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.
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?
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();
}
Apache OpenOffice 4.1.1
Windows XP
Windows XP
Re: How to return #N/A
It seems to return an empty string for me when I try to return it as part of an array.
Saleem
Saleem
OpenOffice 3.4.0
Mac OS X 10.5.8
Mac OS X 10.5.8
- Charlie Young
- Volunteer
- Posts: 1559
- Joined: Fri May 14, 2010 1:07 am
Re: How to return #N/A
I get an empty string in the first entry which I'm trying to make #N/A, but subsequent such entries are zeros.saleem145 wrote:It seems to return an empty string for me when I try to return it as part of an array.
Saleem
Apache OpenOffice 4.1.1
Windows XP
Windows XP
- MrProgrammer
- Moderator
- Posts: 4907
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: How to return #N/A
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++.saleem145 wrote:Is there a way to call a worksheet function from C++ and assign the result to a C++ Any.
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).
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).
- Charlie Young
- Volunteer
- Posts: 1559
- Joined: Fri May 14, 2010 1:07 am
Re: How to return #N/A
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.MrProgrammer wrote: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++.saleem145 wrote:Is there a way to call a worksheet function from C++ and assign the result to a C++ Any.
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
Windows XP
- Charlie Young
- Volunteer
- Posts: 1559
- Joined: Fri May 14, 2010 1:07 am
Re: How to return #N/A
As an example, consider this function, which attempts to enter the NA() function in a cell specified by its arguments
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
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
Apache OpenOffice 4.1.1
Windows XP
Windows XP
Re: How to return #N/A
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
Mac OS X 10.5.8
Re: How to return #N/A
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
- Charlie Young
- Volunteer
- Posts: 1559
- Joined: Fri May 14, 2010 1:07 am
Re: How to return #N/A
We have access to the source code, though that is a jungle I haven't explored yet.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.
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:
The docmentation for setDataArray says:any[][]
for (two-dimensional) arrays of mixed contents. Each any will contain a double or a string, depending on the data.
and Any(), being void, is neither a double or a string, but could be interpreted as such in some cases.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 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
Windows XP