In other news: In Firefox, Cntl+Click on a hyperlink and it will open that link in a new tab. Except for the aforementioned blog’s heading. When I Cntl+Click on the blog’s header link, it opens a new tab and it follows the link in the current tab. It seems to work normally for any other link on that site. I’ve never seen that behavior before.

Is there any way create an empty (null) cell? Specifically, suppose you’re doing a calculation based on the presence/absence of data in another cell; something like =if(len(othercell)=0,””,do_calc).
Rather than entering “”, can one make it an empty cell, e.g., if(len(othercell)=0, make_null, do_calc).

The reason why “” is sometimes a nuisance is that cells like this can show in a pivot table as 0 instead of empty. Of course you could filter the original calculated column for blanks, then do a “clear all”. Another way I found recently was a sub where you’d select a range, then loop thru, something like: For each cell in selection, If len(Trim(cell)) = 0 Then cell = empty or cell.clear. However, I found this to run very slowly. So what would be nice to have is the ability to create empty/null cells on the fly. Or have I missed something simple (mea culpa)?

vbNullString is not quite the same thing as “”, although they behave identically in VBA for all practical purposes. Under the covers, though, vbNullString is exactly what it says – a Null string. To see the difference, open the Immediate Window and compare the results of these two:

? StrPtr(“”)
? StrPtr(vbNullString)

The first is an allocated string that just happens to have a length of zero, the second is a string with no memory allocated.

The only time I’ve ever seen this difference become important as a practical matter is when dealing with API calls that expect a pointer to a null string, which you can only get in VBA using vbNullString.

This depends if you need what the computer knows, or the eye sees. A cell with empty text within looks blank, but ISBLANK(cell) is false, and the answer disagrees with your eyes. In most such cases, I test if LEN(cell) = 0, in formula or VBA, which brings the computer and my eyes into agreement.

No. We have suggested numerous times that MS add something like a NULL() or BLANK() worksheet formula that would act as a truly blank cell. This would help with downstream calculations, charting, and presentation. We’re still waiting.

Make a line or XY chart using Make_Null in place of blank cells. A cell containing a formula returning Make_Null is treated as if it has a value of zero. A cell that is truly blank behaves differently, resulting in either a gap where the cell’s value would be plotted, or a line interpolated across the gap. There is no formulaic way to simulate a cell that is truly blank.

It seems we’re all saying the same thing here but in different ways. On reflection I agree with Jon Peltier in that “there is no formulaic way to simulate a cell that is truly blank”.

I came across this issue using VBA when a cell (formatted as a date) would not return either IsEmpty, equal to NULL or even as a number. In that case the vbNullString solved the problem.

Rob Bovey’s comment above reminded me of some Win32 API programming in VB that I’d done years ago and I came across the following definition of vbNullString (now, I also note, repeated in the Office 2007 help file):

vbNullString – String having value 0 – Not the same as a zero-length string (“”); used for calling external procedures

As a VBA (or VB) string is only a pointer to a character array. The address in memory pointed to by this variable points to the start of the character array.

The array terminates in a 2-byte null terminator and is preceded by 4 bytes showing the length of the string in bytes.

Using “” to declare an empty string results in a character array of size 0. However, this will still contain the 4-byte length data, and the 2-byte terminator.

vbNullString still returns a pointer but one that does not point to any character array resulting in no 4-byte length field or 2-byte terminator.

An article by Microsoft (of which I have a printed copy but now can’t find the URL) on VBA optimization suggests that vbNullString would execute 50% faster than “”.

Having said all that I return to my original comment – use what works for you in the situation you’re faced with. vbNullString saved the days for me so may help others.

can you create a null cell (as a function result)?
no I don’t think so, by definition, by putting something in it (the formula) you are changing it from a null cell to some other type. That seems fair enough to me. =NA() is the next best thing for graphs etc.

If you enter =A2 in A1 where A2 is empty A1 returns 0. Excel coerces that empty cell to a number of value zero. If it didn’t, blanks would propagate all over and break select special stuff for example.

If you change A2 to be =”” then A1 looks blank, but its value is really a zero length string. If you paste the value over A2 you get a cell that looks and feels blank, nothing in the formula bar etc. But it is not a blank cell its a string cell with a zero length string. This means its not ISBLANK() and its not isEmpty() but it is the equivalent of vbNullString.

because VBA determines the length of a string as the first step in getting the string itself. That doesn’t necessarily help with rCell.Value, which trips out to the worksheet, but it does help with a string variable in memory and it also helps with “”.

It’s best to use Evaluate, as well as the [] modifiers, only in the Immediate window. For one thing, I’ve heard that the brackets are slower than the normal VBA referencing. For another, and more important, unless you are sure that the active sheet contains the cells you want to sum, you will not get what you expect.

v is being used to hold an array rather than a single variable. The code is using i and j to iterate through the rows and columns of the array and Trim (remove trailing spaces from) each item in it. Then it writes the contents of v (the entire array) back out to the range that it got it from.

I’m running into a similar issue w/empty cells, in that I’m trying to copy one column of data from one sheet to another.

However, the column in the first sheet may or may not have blank cells (identified by “”), depending on parameters set by user – and what I’d like to do is set up code that always pastes the actual data in consecutive rows.

Your firefox issue seems to be a Javascript – many websites use javascript on “a” tags to do … things. When you control click, it opens as a new tab AND runs the javascript. A tags should have href (or name) variables to support web browsers which don’t have Javascript (and also, search bots which might not understand JS).

Bascially, another example of Javascript being evil because someones written something evil in it…

Re: Macroman, Hans Schraven and Peltier discussion on Brackets.
Microsoft had a KB article which stated that using brackets for referencing ex: [A1:A10] (what theycalled tunneling as I recall) is slower than using references such as Range(“A1:A10?). This is for referencing ranges such as [A1:A10].Value = 1. I can’t say for executing a worksheet function such as [Sum(A1:A10)]