Posts Tagged 'null'

SQL Server – Enter Value from Keyboard

Here’s another of those things I forget if I don’t use SQL Server for a while.

To enter a value of <NULL> into a field in SQL Server just type:
[Ctrl] + 0

(Hold down the Ctrl key and press the zero key).

vbNullString – Empty Cells in Excel VBA

My post on IsNull in Excel VBA has generated some hard thought!

The site Daily Dose of Excel kindly mentioned my post at http://www.dailydoseofexcel.com/archives/2008/06/26/identify-empty-cells-in-vba.

This has lead to some excellent discussion of the use of “empty” cells in Excel within either formula or VBA code.

I’ll repeat here what I posted on that site as a reference for myself and others:

[I was reminded 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 “”.

Basically, using vbNullString seems to be suitable in some case but not in others (e.g. in charts where it returns a value of 0).

I’m sure I’ll be adding more about this soon after I’ve had time to play with it some more.


Blog Stats

  • 222,048 hits

Counter

ClustrMap