| Author |
 Topic  |
|
|
johannes.kastl
Germany
71 Posts |
 Posted - 02/21/2013 : 04:32:20 AM
|
Hi again,
I was wondering if it is possible to check the contents of a worksheet cell with Labtalk, especially if the cell contains a numeric value or text or blanks or "--" or ...
Just to be clear, I'm not talking about the format of the cell, I'm trying to distinguish between cells containing numbers and other cells.
EXIST() seems to check just for the definition of a variable, whether is has been declared with "int Test" or "string Test", and not for the contents of the variable.
Any hints?
Origin Ver. and Service Release (Select Help-->About Origin): 8.6.0G SR3
Operating System: Win XP SP3
Regards,
Johannes |
|
|
greg
USA
1378 Posts |
Posted - 02/21/2013 : 6:05:44 PM
|
| The function ISNA(argument) returns 1 if the argument is a missing value, which it would be for text, or blank or missing value in a cell. |
 |
|
|
johannes.kastl
Germany
71 Posts |
Posted - 02/22/2013 : 05:23:04 AM
|
Thanks greg, that seems to do what I was looking for. At least in the examples I found on http://wiki.originlab.com/~originla/ltwiki/index.php?title=LabTalk:ISNA_ function
Two questions:
1. AFAIC the function itself does not output anything, you have to fill a variable and the output the value of the variable. Right?
int AAA=ISNA(...);AAA=;
2. I am using the "new" Origin8 notation of %([Book1]Sheet1, 1, 1) to address the cell. If the cell contains not a number but some text, Labtalk tries to handle the text as a variable and tries to get its value.
Example:
Some cells contain numbers, %([Book1]Sheet1, ..., ...)=; shows up as 289=289 if the cells value is 289
Some cells contain X, %([Book1]Sheet1, ..., ...)=; shows X=3.5 as there is a variable X that has been set to 3.5.
Some cells contain Y, %([Book1]Sheet1, ..., ...)=; shows Y=0, as the variable Y has been set to zero.
Some cells contain text ("some text"), %([Book1]Sheet1, ..., ...)=; throws an error as no variable called "some text" is defined.
How to output the actual value or text in a cell, not the value of the variable?
I could not get ISNA() to accept any of the notations:
ISNA([Book1]Sheet1, 1, 1)
ISNA(%([Book1]Sheet1, 1, 1))
ISNA($([Book1]Sheet1, 1, 1))
So I tried assigning the value of the cell to a variable and passing that variable to ISNA(). But I either get an error saying "cannot assign a string to a scaler, %(str$) should be used to convert"
string TEST=%([Book1]Sheet1, 1, 297);
BBB=ISNA(TEST$);
BBB=;
or "variable not defined: TEST" when using
string TEST=%([Book1]Sheet1, 1, 297);
BBB=ISNA(TEST);
BBB=;
And this seems related to the using-cell-value-as-variable-name thingy.
Sorry if these questions are pretty confusing, but I found no easy tutorial about getting cell values apart from http://wiki.originlab.com/~originla/ltwiki/index.php?title=LabTalk:Substitution_Notation#Worksheet_Column_and_Cell_Substitution
Thanks,
Johannes |
|
|
|
johannes.kastl
Germany
71 Posts |
Posted - 02/22/2013 : 05:30:40 AM
|
string TEST=%([Book1]Sheet1, ..., ...);
BBB=ISNA(%(TEST));
BBB=;
Works without error, but outputs 1 even if the cell contains a numerical value...
Declaring test as int makes no difference.
Confused,
Johannes |
Edited by - johannes.kastl on 02/22/2013 05:40:28 AM |
|
|
|
greg
USA
1378 Posts |
Posted - 02/22/2013 : 10:43:50 AM
|
We will have to do a better job in our Help documentation on range notation, because many people make this mistake...
You cannot use range notation to directly address a cell.
Range notation should only be used for declaring a range.
OK
range rady = [Book7]Sheet2!col(B);
int val = rady[5];
string str$ = rady[5]$;
NO!
int val = [Book7]Sheet2!col(B)[5];
string str$ = [Book7]Sheet2!col(B)[5]$;
That said, there is a caveat in that a worksheet range notation can be used if it's meant as part of an object reference.
So I could say:
NumCols = [Book1]Sheet2!wks.ncols;
and I could use the cell function which addresses by row,column:
double val = [Book1]Sheet2!cell(5,2);
string str$ = [Book1]Sheet2!cell(5,2)$;
And since ISNA(arg) returns 1 for text, missing value or blank, I could say:
row = 5;
col = 2;
if(isna([Book1]2!cell(row,col))) ty Text, missing value or blank;
else type $([Book1]Sheet2!cell(row,col));
|
|
|
|
johannes.kastl
Germany
71 Posts |
Posted - 02/22/2013 : 10:50:57 AM
|
Hi greg,
I can test on monday at work.
But just to be clear:
Even if addressing a cell via cell function is the 'older way' it is still preferred if we are talking about just one cell.
And range notation should be used for everything with a number of cells >1.
Man, once you really care about writing code which uses up-to-date methods and try to keep up with development you fail... ;-)
Regards,
Johannes |
|
|
|
greg
USA
1378 Posts |
Posted - 02/22/2013 : 12:01:09 PM
|
Sorry if I was not clear.
Using ranges is the preferred way to go. Ranges avoid all the old problems with active windows being required for many old LabTalk commands.
So my final example would be better written as:
range radB = [Book1]Sheet2!col(B);
row = 4;
if(isna(radB[row])) ty Text, missing value or blank;
else type $(radB[row]);
My mention of the cell function seems only to have confused things. My point there was ONLY to show a fragment of Range Notation ( [BookName]SheetNameOrIndex! being just a fragment of the full notation [BookName]SheetNameOrIndex!ColumnNameOrIndex ) could be used to directly address sheet properties. |
Edited by - greg on 02/22/2013 12:04:29 PM |
|
|
|
johannes.kastl
Germany
71 Posts |
|
| |
Topic |
|
|
|
Check if worksheet cell contains number or text