Cells, Range & Strings

Hello every1,

I'm using Option strict On and doing the following:

anyString = CStr(.Cells(intRowIndex, intColumnIndex))


and I'm getting an unhandled exception of type 'System.InvalidCastException' occurred in microsoft.visualbasic.dll

Additional information: Cast from type 'Range' to type 'String' is not valid.

***?!?!? how am I supposed to read stuff from cells?!?!? Where have the Value and Text properties gone?

so you already tested?
anyString = CStr(.Cells(intRowIndex, intColumnIndex).value)

yes, I get, D:\Lavoro\AIVoceInternazionale\Application.vb(29): Option Strict On disallows late binding. :(

I've sorted it using Option Strict OFF....

I am also trying to put some text from a cell into a string variable:

I am using:

Dim myString As String = CStr(ws.Range(ws.Cells(i, 1)).Value)

and the error I get is:

Additional information: Exception from HRESULT: 0x800A03EC.

Please help!

Just as a test split it into two lines

Dim myString As String
myString = CStr(ws.Range(ws.Cells(i, 1)).Value)

I split the code and it broke on the second line with the same error

Dim myString As String
myString = CStr(ws.Cells(i, 1).Value)
Should make the same... make sure 0 < i < 65536

Dim myString As String
myString = CStr(ws.Cells(i, 1).Value)
Should make the same... make sure 0 < i < 65536


i = 6 in this case...

I also tried using DirectCast:

Dim myString As String = DirectCast((ws.Range(ws.Cells(i, 1)).Value), String)

This is a very unusual structure, that you are trying to implement... It looks like you are effectively looking to implement the =Indirect() worksheet function? That is, WS.Cells(i,1).Value holds a value such as "A1", which then is placed into WS.Range("A1").Value, which, in turn, holds a string, that is held in MyString?

Assuming that I understand this right, I think you could use something like the following:Dim WS As Excel.Worksheet
Dim myString As String
Dim i As Long

xlWS = ... ' <-- Adjust
i = ... ' <-- Adjust
myString = CStr(WS.Range(CType(WS.Cells(i, 1), Excel.Range).Value).Value) It doesn't make a whole lot of sense, but .Cells() is technically polymorphic, even though I don't know how to get to return anything but a Range object. However, at compile-time, technically, .Cells() is an unknown, and so you need to wrap it within a CType() call. The rest of your construction looks right. :)

Let us know how it goes...

-- Mike

Luna, sorry I didn't notice this post before... to maintain 'Option Strict On', which really is desireable, you could have used:anyString = CStr(CType(.Cells(intRowIndex, intColumnIndex), Excel.Range).Value)-- Mike

Luna, sorry I didn't notice this post before... to maintain 'Option Strict On', which really is desireable, you could have used:anyString = CStr(CType(.Cells(intRowIndex, intColumnIndex), Excel.Range).Value)-- Mike

OK, so I used the following line:

Dim myString As String = CStr(ws.Range(CType(ws.Cells(i, 1), Excel.Range).Value).Value)

and I am still getting the HRESULT error. Am I missing the bigger picture here? I am simply trying to put the text of a cell in Excel into a string variable. I cannot believe that Microsoft would make this a difficult or tricky process. If it were simply a number in the cell, .Value would work. What is going on here?

Thanks for your continuing help.

Jason

Ahhhh... Ok! :)

I kinda thought that this was a bit overwrought! Your construction, as written, is basically trying to execute an =Indirect() worksheet function call. Which is not what you are looking to do!

Ok, in simple terms, there are two basic ways to index a Cell on a Worksheet. I'll write these using a simple VBA structure for the moment:

(1) MyValue = WS.Range("A2").Value
(2) MyValue = WS.Cells(2, 1).Value

Notice in the above, that with .Cells(2,1) that the RowIndex comes first, whereas with .Range("A2") you are specifying the ColumnLetter first... So this can be a little confusing.

Doing the same in VB.Net is not *much* harder, however, the .Cells() property is defined 'As Object' and so under 'Option Strict On' requires the use of CType(). Fortunately, .Range() is strong-typed 'As Excel.Range' and so is fine. So to call these within .Net, you'd need something like this:

(1) MyValue = WS.Range("A2").Value
(2) MyValue = WS.CType(Cells(2, 1), Excel.Range).Value

In your case, it looks like you need to coerce to a String, so the constructions would each need to be wrapped within another CType() or within CStr():

(1) MyValue = CStr(WS.Range("A2").Value)
(2) MyValue = CStr(WS.CType(Cells(2, 1), Excel.Range).Value)

[CStr() is supposedly "old school" but I personally find using CStr(MyVar) much cleaner and easier to read than using CType(MyVar, String), but that's a matter for personal preference...]

Returning to your line, I think that you would want to try the following:Dim myString As String = CStr(CType(ws.Cells(i, 1), Excel.Range).Value) Give it a shot!

-- Mike

Aha! That did the trick! Thank you very much.

Jason

Cool! :cool:
And no problem, very glad it worked. :)