Did evalution of empty cells change in Excel 2007?

  • Thread starter Thread starter Gustaf
  • Start date Start date
G

Gustaf

From trying my VBA app in both Excel 2003 and 2007, I came to the conclusion that 2007 seem to handle empty cells differently. The code looks something like this:

lngVolume = wsMySheet.Cells(iRow, iCell)

lngVolume is a Long. In Excel 2003, the right hand expression returns "", while in Excel 2007, it returns 0. Can anyone confirm this?

Gustaf
 
It behaves identically for me.

The right-hand side does return null in both cases, but setting a long to
that value returns 0 in each case.
 
Assuming that A1 is empty:

Sub WhatIsIt()
Dim lngVolume As Long
lngVolume = Cells(1, 1).Value
MsgBox (lngVolume)
End Sub

displays 0 in both versions
 
Gustaf

Make sure you declare the variable lngVolume, ie

Dim lngVolume as Long

Then it should return 0 regardless. If you're getting "", it suggests
you haven't declared the variable's data type (ie, Long).

Paul Martin
Melbourne, Australia
 
If that cell contained a formula that evaluated to ="", then you'll see this.

If the cell with that formula was converted to values, then you'll see this.

But that cell isn't empty.

Select the cell, hit the delete key (or clearcontents) and try the code once
more.
 
Back
Top