Did evalution of empty cells change in Excel 2007?

[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

 

[Bob Phillips]

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.

 

[Gary''s Student]

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

 

[Paul Martin]

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

 

[Dave Peterson]

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.