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.


2 Responses to “vbNullString – Empty Cells in Excel VBA”

  1. 1 Kevin June 28, 2008 at 3:15 am

    Well this would have been useful yesterday when I was writing this:

    Sub TextToNumber()

    Dim x As Worksheet, z As Range

    For Each x In Worksheets
    LastCell = ActiveCell.Address
    Range(“a1:” & LastCell).Select

    For Each z In Selection
    If Not IsEmpty(z) And IsNumeric(z.Value) And Not z.HasFormula Then
    z.Value = z.Value
    z.NumberFormat = “_(* #,##0_);_(* (#,##0);_(* “”-“”_);_(@_)”
    End If
    Next z


    Next x

    End Sub

  1. 1 vbNullString - Page 2 Trackback on June 12, 2014 at 10:48 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Blog Stats

  • 226,374 hits




%d bloggers like this: