Excel Range.Value returning 0 when range actually has data

Question

Environment: Windows Server 2008 (SP1) 64-bit | Excel 2007.

I have a value in a range (say 60.664). When I run the line of code in the immediate window I got a Null as a result a blank space:

?Workbooks("ddWorkbook.xlsm").Worksheets("Daily Dashboard").Range("D23").Value

Using a Select and ActiveCell statement, like below, works however.

Workbooks("ddWorkbook.xlsm").Worksheets("Daily Dashboard").Range("D23").Select
ActiveCell.Value

I can leave this quick and dirty with the Select | ActiveCell, but I would like to leave this done with best practice.

Has anyone ever come across this and how to get the Value to read right from the Range Object? I have not found anything in my research.

Answer 1

It could be that the cell D23 is in a merged area but not the first cell of this area. This would explain why you are getting the value after a Select. You can check it by printing the address once the range is selected:

Workbooks("ddWorkbook.xlsm").Worksheets("Daily Dashboard").Range("D23").Select
Debug.Print ActiveCell.Address

And if it's the case, then you can either fix the range or try to get the value from the merged area:

Workbooks("ddWorkbook.xlsm").Worksheets("Daily Dashboard").Range("D23").MergeArea.Cells(1,1).Value