Retrieving a value from a cell

[Paul Lee]

I am writing a program with VB .NET that uses the Excel
object library. I tried to get a value from a cell and
assign it to a variable:

strVar = CStr(Worksheet.Range("A10").Value)

But if the cell is empty, the returned value is Nothing.
So I modified the code to check for Nothing:

With Worksheet
strVar = CStr(IIf(.Range("A10").Value =
Nothing, "", .Range("A10").Value))
End With

However, I kept getting an Error 91.

I tried checking the .Text property of the cell, but that
always comes back with an empty string if the cell is
hidden.

Any suggestion on how I accomplish this?

 

[Norman Jones]

Hi Paul,

strVar = CStr(Worksheet.Range("A10").Value)

I had no problem with this.

However, the line:

With Worksheet

would might well cause a problem.

Try, instead:

With ActiveSheet

or With Worksheets("YourSheetName")

 

[Bob Phillips]

Paul,

Nothing isn't a value, it is a state

Try this (not tried on .Net)

With Worksheet
strVar = CStr(IIf(.Range("A10") Is Nothing, "", .Range("A10").Value))
End With

You might need

With Worksheet
strVar = CStr(IIf(.Range("A10").Value Is Nothing, "",
..Range("A10").Value))
End With

or even

With Worksheet
If .Range("A10") Is Nothing Then
strVar = ""
Else
strVar = CStr(IIf.Range("A10").Value)
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)