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.
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
Sheets(x.Name).Activate
Range(“a1″).Select
ActiveCell.SpecialCells(xlLastCell).Select
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
Range(“a1″).Select
Next x
End Sub