#VALUE
John Visser
=IF(ISBLANK(G4),"",G4*Rates!B1)
which returns the message #VALUE if G4 is blank. Not good.
Now the above formula refers to a cell (G4) that is blank, but if it is not
(i.e. >0), it will give a numerical value (not text). The cell (G4) that it
refers to has the formula
=IF(OR(ISBLANK(C4),ISBLANK(E4)),"",E4-C4)
which works fine. (E4 and C4 refer to inputed variables).
Amy I seeing x-eyed here, or is there a genuine reason that I get #VALUE? I
have notice that in this new version of Excel 97(SR2), I have gotton this
#VALUE message before, but if I delete the row and start again, I lose the
message. However in this case it would be too much to start again - these
things take time to think about and write...
Any help appreciated - please email as well, I do not get all posts
Cheers & thanks
Jerry W. Lewis
know how to multiply a string times a number.
Try
=IF(ISNUMBER(G4),G4*Rates!B1,"")
This type of problem is why people keep asking if there is any way a
formula can return a blank. As far as I know, it cannot. This is only
one of many instances where "" is not as good as an empty cell. In your
case there is a workaround, but it complicates your life. In many
instances there are no workarounds. Microsoft, are you listening?
Jerry W. Lewis
Stat...@erols.com
Leo Heuser
Just thought my answer to Laurent might interest you.
Best regards
Leo
Hi Laurent
If what you say " Excel doesn't check if there is a formula or not in
>the cell (the IS... functions don't take formulae into account, but just
>data types)." is true, I challenge you to show just one example, where ISBLANK(A1) is true and A1 contains a formula :-)
Check out this eye-opener:
B1 has the formula =ISBLANK(A1)
If you enter =Blank() in A1, B1 is FALSE
If you run Sub test1, B1 is TRUE
Why is it, that two different results are produced in B1. Apparently an empty string is inserted in both situations. The only
difference, as far as I can see, is that in the first example A1 contains a function, in the second it doesn't. Strange!
Function Blank()
Blank = ""
End Function
Sub test1()
[A1].Value = ""
End Sub
Sub test2 shows how to decide, if a cell in the worksheet is blank.
Sub test2()
If Application.Evaluate("ISBLANK(A1)") Then
MsgBox "Is blank"
Else
MsgBox "Is not blank"
End If
End Sub
One can't use Sub test3, as it will show "Is blank" on both occasions.
This explains, why one can't use LEN(A1)=0 to test for blankness. LEN(A1) is zero if A1 is blank, but also if A1 contains the
infamous empty string.
Sub test3()
If [A1].Value = "" Then
MsgBox "Is blank"
Else
MsgBox "Is not blank"
End If
End Sub
Combining Sub test2 and Sub test3 gives a routine, that can distinguish between a blank cell and a cell containing an empty
string.
Sub test4()
If [A1].Value = "" Then
If Application.Evaluate("ISBLANK(A1)") Then
MsgBox "The cell is blank"
Else
MsgBox "The cell contains an empty string"
End If
End If
End Sub
Best regards
LeoH
Laurent Longre skrev i meddelelsen <360580...@wanadoo.fr>...
>Hi Leo,
>
>Leo Heuser wrote:
>> Maybe the problem is, that "" is put into the cell from a formula *IN* the cell, so with some reason ISBLANK() can claim, that
the cell isn't blank. After all, there is a formula.
>
>If you enter ="" in A1 and then copy / paste this cell by value,
>=ISBLANK(A1) will still return FALSE, because the cell still contains an
>empty string (ie a string whose length is zero),even though the formula
>has been removed. Excel doesn't check if there is a formula or not in
>the cell (the IS... functions don't take formulae into account, but just
>data types).
>
>Laurent
Jerry W. Lewis skrev i meddelelsen <3604F1BF...@erols.com>...
Jerry W. Lewis
You have illustrated the difference between VBA and Excel. In VBA you
can have and manipulate a result of nothing (Empty, Null, ""), but not
in Excel. A more interesting form of your test1 routine would be
Sub test1()
[A1].Value = Blank()
End Sub
where
Function Blank()
Blank = ""
End Function
as you originally proposed. This gives the same results as you noted,
which shows that the issue is not function vs. subroutine, but Excel vs.
VBA. Your test1 routine is actually emptying A1, because "" is truly
empty in VBA. But Excel does not permit an empty return from a
function, so =BLANK() is the same as putting "" (as opposed to nothing)
into a cell in Excel, while [A1].Value = "" in VBA is equivalent to
deleting the contents of A1. You also see this with the VBA Null and
Empty keywords, which are converted to zero if they are returned to
Excel from a VBA function.
Allan London ("I need a BLANK function" thread in
microsoft.public.excel.programming) recently claimed to have found a way
around this, but has yet to respond to numerous queries asking how.
Laurent Longre
See my answer in excel.worksheetfunction ("Functions and "blank" type").
Laurent
Leo Heuser wrote:
>
> Hi Jerry
>
> Just thought my answer to Laurent might interest you.
> <snip>
leeeech
Earl Kiosterud
Part of this issue may be the "value" layer of the worksheet, and the
"formula" layer. That ISBLANK returns false to a cell containing
"=Blank()" may be because it examines the formula layer. Functions such as
LEN() examine the value layer, and will return 1 for a cell containing = 1
+ 2, not the number of characters in the formula. The value layer seems to
be treated as text in all cases.
From a programming standpoint, I do not understand what "nothing" means.
Text is "nothing" when it's a zero-length string (""). A value (integer,
bcd, floating-point, etc.) always has some value, 0 being as good as any
(which seems to appear at the value layer as a string containing 1 zero).
At the value layer, a deleted cell seems to be a zero-length string.
FWIW.
Regards from Virginia Beach, VA
Earl Kiosterud
ea...@livenet.net
-------------------------------------------------------------
Jerry W. Lewis <stat...@erols.com> wrote in article
<36086D0D...@erols.com>...
Laurent Longre
Bis repetita placent ;-)
We (myself, Jerry and LeoH) have built an intersting thread about that
in microsoft.worksheetfunction.
As far as I am concerned, I think that :
- No built-in worksheet function examines the formula layer of the
cells. =ISBLANK(Cell) checks just if there is any value in the cell,
*not* if the cell contains a formula. Actually, formulae can't return a
blank, so that =ISBLANK(Cell) will always return FALSE.
- "Blank" <> empty string :
A1 doesn't contain any value => =ISBLANK(A1) returns TRUE
A1 contains the formula ="" => ISBLANK(A1) returns FALSE
In this case, again, ISBLANK doesn't return FALSE because A1 contains a
formula, but because it contains an empty string. If you copy and paste
A1 by value, =ISBLANK(A1) stills returns FALSE:
[A1].Formula = "="""""
[A1].Copy
[A1].PasteSpecial xlPasteValues
Application.CutCopyMode = False
In Excel's BIFF file format, blank cells and strings are not coded in
the same way, and the Excel C API defines two different data types for
strings and blanks (blank = xltypeNil, string = xltypeStr).
HTH,
Laurent
Myrna Larson
wrote:
>From a programming standpoint, I do not understand what "nothing" means.
>Text is "nothing" when it's a zero-length string (""). A value (integer,
>bcd, floating-point, etc.) always has some value, 0 being as good as any
>(which seems to appear at the value layer as a string containing 1 zero).
>At the value layer, a deleted cell seems to be a zero-length string.
Integers, floats, strings, etc. always have a value. "Nothing" has meaning
only when you are talking about Variant variables.
A variant variable (which is basically what Excel's cells are) has a byte
(or two) that indicates the what kind of data the cell contains, plus the
data itself (or a pointer to it). Those data bytes can indicate that the
variable has never been written to (empty).
The "data type" is initially "empty". When you enter data, the data is
stored and the data type is set appropriately. The data type can later be
changed to "empty" if you Edit/Clear the cell. So "empty" or "nothing"
refers to the value stored in the data-type bytes, not what's in the data
bytes.
The previous messages in this thread do indicate (to me) that there is
something strange going on here. If from VBA you set a cell's value to an
empty string, I would expect it to (a) set the data type to string and (b)
set the data bytes to indicate the string has 0 length. But it doesn't do
that. Instead it sets the data bytes to "empty": the worksheet function
ISBLANK will return True.
Alan Beban
>. . . Integers, floats, strings, etc. always have a value. "Nothing" has meaning
> only when you are talking about Variant variables.
I think "Nothing" relates not to Variant variables, but to the state of
an object variable to which no object has been assigned.
Alan Beban be...@pacbell.net
Laurent Longre
>
> A variant variable (which is basically what Excel's cells are) has a byte
> (or two) that indicates the what kind of data the cell contains, plus the
> data itself (or a pointer to it). Those data bytes can indicate that the
> variable has never been written to (empty).
VBA uses variants, but are you sure that Excel does too? I'd rather
think that it uses a "proprietary" data type, perhaps some sort of
extended XLOPER structure.
Laurent
Myrna Larson
wrote:
>VBA uses variants, but are you sure that Excel does too? I'd rather
>think that it uses a "proprietary" data type, perhaps some sort of
>extended XLOPER structure.
You are correct: they don't call them variants, but XLOPERs.
I think I said something like "they are *essentially* variants". I didn't
mean to imply that the STRUCTURE of an XLOPER was the same as a VBA
variant, just that the CONCEPT or IDEA is the same: i.e. the variable can
store more than one type of data, and the structure includes the data
itself PLUS those "data-type" bytes that identify the type of the current
contents.
The last XLDK that I have (for version 5) says that an XLOPER is 10 bytes,
while (according to my VB5 Language Reference), a VB variant is 16 for
numbers, 22 for strings. I don't know if XL97 XLOPERs have changed since
XL5. Do you have any information on that?
Earl (I think it was) said he couldn't understand how the concept of
"empty" could apply to a numeric or string variable. The point I was trying
to make was, whether or not an Excel cell is "empty" has to do with what
value is stored in those 2 data-type bytes, not what's in the 8 data bytes.
Laurent Longre
Myrna Larson a écrit:
>
> I think I said something like "they are *essentially* variants". I didn't
> mean to imply that the STRUCTURE of an XLOPER was the same as a VBA
> variant, just that the CONCEPT or IDEA is the same: i.e. the variable can
> store more than one type of data, and the structure includes the data
> itself PLUS those "data-type" bytes that identify the type of the current
> contents.
Indeed.
> The last XLDK that I have (for version 5) says that an XLOPER is 10 bytes,
> while (according to my VB5 Language Reference), a VB variant is 16 for
> numbers, 22 for strings. I don't know if XL97 XLOPERs have changed since
> XL5. Do you have any information on that?
XLOPERs have not changed since Excel 5, and they still have a size of 10
bytes, regardless of the data type. Like in XL5, references to external
sheets (xltypeRef), strings (xltypeStr) and arrays (xltypeMulti) are
stored in extra memory blocks pointed by sub-fields of the
pxMyXLOPER->val field.
In my last post, I have said "some sort of extended XLOPER", because
built-in functions in XL97 can perform computations which are impossible
with basic XLOPERs. For instance, xltypeStr XLOPERs can't store strings
containing more than 255 characters (the length is stored in a single
byte), whereas built-in functions can handle and return up to 1024
characters.
This detail leads me to think that, since this version, Excel uses
internally a data type which is probably close to the XLOPER structure
as described by the SDK, but doesn't exactly match it.
> Earl (I think it was) said he couldn't understand how the concept of
> "empty" could apply to a numeric or string variable. The point I was trying
> to make was, whether or not an Excel cell is "empty" has to do with what
> value is stored in those 2 data-type bytes, not what's in the 8 data bytes.
I totally agree with you.
Laurent
Myrna Larson
wrote:
>For instance, xltypeStr XLOPERs can't store strings
>containing more than 255 characters (the length is stored in a single
>byte), whereas built-in functions can handle and return up to 1024
>characters.
>
>This detail leads me to think that, since this version, Excel uses
>internally a data type which is probably close to the XLOPER structure
>as described by the SDK, but doesn't exactly match it.
I don't think the string length is written to the XLOPER. I may be
misreading my XL SDK book (C always gives me fits! <g>), but I *think* it
says that the XLOPER data bytes consist of a 4-byte pointer, i.e. the
string's memory address. In XL5/95, what you find at that address is 1 byte
for the string length, followed by the characters of the string (a "Pascal"
string).
So you can implement strings longer than 255 characters without changing
the XLOPER at all. Of course the routines that retrieve the data need to
"know" that the data at the address is now 2 bytes for the length, followed
by the string characters...
That's probably one of the reasons that XL5/95 can't read an XL97 file --
and a good reason, too!
Laurent Longre
Myrna,
Actually, XLOPERs are used to exchange data between Excel and external
DLLs. When Excel's calculation engine calls one of your add-in DLL
function and passes to it an xltypeStr, the length of this string
is_stored_in_a_single_byte. If the real argument exceeds 255 characters,
XL97 won't even pass a truncated xltypeStr to your DLL function, but
either an xlerrValue error code, or an invalid XLOPER (xltype =
xlbitXLFree...!!!) if the argument is part of an array.
If you want Excel to behave differently and pass a "transformed"
xltypeStr using a 2-bytes string length to your DLL function, I think
that you should at first reverse-engineer Excel.exe and modify yourself
the corresponding code in the executable file. Good luck... <g>
Also, if you try to return a such xltypeStr to Excel, it will simply
interpret the second byte count as the first character of the string,
because Excel expects a basic Pascal string (byte count <= 255), and
*nothing else*.
The following "CONCAT.EXT(Range,Separators)" function, which
concatenates the contents of all cells in a range, illustrates the
limits of the xltypeStr type:
#include "xlcall.h"
#include "framewrk.h"
#define ERR(cErr) {xRet.xltype=cErr;return &xRet;}
#define LPARRAY val.array.lparray
UCHAR psRet[256]; str field (1 byte count + 255 characters)
extern "C" __declspec(dllexport) LPXLOPER WINAPI
ConcatExt(LPXLOPER pxTable,char *psSep)
{
static XLOPER xRet;
UINT i,uiDim;
WORD wLgChaine,wPos=1;
BYTE bLgSep;
if(pxTable->xltype==xltypeMissing) ERR(xlerrNA);
if(pxTable->xltype!=xltypeMulti) return pxTable;
uiDim=pxTable->val.array.rows*pxTable->val.array.columns;
bLgSep=lstrlen(psSep);
for(i=0;i<uiDim;i++){
switch(pxTable->LPARRAY[i].xltype){
case xltypeNil:
continue;
case xltypeStr:
if(wPos+(wLgChaine=(BYTE)pxTable->LPARRAY[i].val.str[0])>256)
ERR(xlerrValue);
CopyMemory(psRet+wPos,pxTable->LPARRAY[i].val.str+1,wLgChaine);
break;
case xltypeErr:
ERR(pxTable->LPARRAY[i].val.err);
case xlbitXLFree: // string > 255 characters
return pxTable->LPARRAY+i;
default:
Excel(xlCoerce,&xRet,2,pxTable->LPARRAY+i,TempInt(xltypeStr));
if(wPos+(wLgChaine=(BYTE)xRet.val.str[0])>256){
Excel4(xlFree,0,1,&xRet);
ERR(xlerrValue);
}
CopyMemory(psRet+wPos,xRet.val.str+1,wLgChaine);
Excel4(xlFree,0,1,&xRet);
break;
}
wPos+=wLgChaine;
if(bLgSep&&i!=uiDim-1){
if(wPos+bLgSep>256) ERR(xlerrValue);
CopyMemory(psRet+wPos,psSep,bLgSep);
wPos+=bLgSep;
}
}
*psRet=wPos-1;
xRet.xltype=xltypeStr;
xRet.val.str=psRet;
return &xRet;
}
BTW, another Excel data type is also incompatible with XLOPERs: 3D range
references. If you try to pass a 3D reference to a DLL function, Excel
replaces it also automatically with an error code. XLOPERs are basically
**unable** to store 3D references, because they can't store unions of
ranges located in different sheets (xltypeRef XLOPERs have only one
idSheet field).
Cordially,
Laurent
Myrna Larson a écrit:
Myrna Larson
wrote:
Thanks for the additional information. What you say about XLOPERs certainly
doesn't match what I find in my XL5 DK manual, but that wouldn't be the 1st
time MS had an error in their docs <g>.