0

How can I extract the value of a cell from a range object? It seems like it should be simple. This Stackoverflow question and answers did not really help. Here is what I want to do but it fails every time with an exception on row.columns(0,0).

Dim rdr = oFindings.Range
For Each row As Excel.Range In rdr.Rows
   Dim Account = row.Columns(0,0).value2.tostring
   ' Do other stuff
Next

To make it work I had to implement this code:

For Each row As Excel.Range In rdr.Rows
    ndx = 0
    For Each cell As Excel.Range In row.Columns
        If ndx = 0 Then Account = NS(cell.Value2)
        ' Do other stuff
        ndx += 1
    next
Next

That seems like such a kludge. What is the secret?

Community
  • 1
  • 1
Ebassador
  • 339
  • 3
  • 20
  • Do they all have the same type? Do you know the cell's type in advance? – SpaceSteak Sep 02 '15 at 18:24
  • "an exception on row.columns(0,0)" seems logical as far Excel (and any 2D collections in VB.NET or in any other language I know) only expects 2 indices and you are providing 3. The variable row within the foreach loop is the same than rdr.Rows(indexRow); and then you are adding two more indices to the column. The correct syntax is row.Columns(0). – varocarbas Sep 02 '15 at 18:24
  • 2
    `0` is not a valid rowindex or columnindex in Excel VBA – Ron Rosenfeld Sep 02 '15 at 18:25
  • @RonRosenfeld Firstly this is not VBA and secondly 0 is a valid VBA index in quite a few functions (not in Excel cells though). The problem here is that it is inputting 3 indices rather than just 2 (a problem in VBA and here, in VB.NET). – varocarbas Sep 02 '15 at 18:26
  • 1
    @varocarbas Thank you for that clarification. I **did** include in my comment that it applied to **Excel VBA**, and in that language, `Columns(0,0)` is not a valid range reference. I am uncertain how it might apply to the OP's problem, but I felt it might be useful information. – Ron Rosenfeld Sep 02 '15 at 18:34
  • @RonRosenfeld (perhaps I misunderstood the a priori very clear sentence "0 is not a valid rowindex or columnindex in Excel VBA"; sorry about that, but a clarification is never a problem isn't it?). In any case, bear in mind that in VBA Columns(0,0) is as wrong as Columns(2,2), as far as Columns() collection only allows one index (equivalently to most of collections called Columns which usually are part of a table emulation, like DataTable in .NET). Is this clarification OK or I got your intention wrong once again? – varocarbas Sep 02 '15 at 18:40
  • Just a note - it's highly recommended to **not** use `row` or `cell` as a variable, because `Row` and `Cell` means something implicit to VBA. I'd change that variable to `xRow` or `cel` or something. Don't want to cause any confusion down the line! – BruceWayne Sep 02 '15 at 18:40
  • @RonRosenfeld Who do I give the credit to on this one? It was an issue of using two indices inside the For loop AND Excel is 1 based not zero based. Once again Stackoverflow proves to be a fantastic resource! Thanks so much! – Ebassador Sep 02 '15 at 18:41
  • @varocarbas My apologies. Your help is very much appreciated. – Ebassador Sep 02 '15 at 18:47
  • @varocarbas I can't argue with your findings. That is why I asked the question in the first place. As you said, .Net is 0 based. However, in this case whether I had used (0,0) or just (0), I still got the exception. Also, the correction to the code (VB.NET not VBA), required that I use (1). I'm sorry that I don't possess all the knowledge you obviiously do. I'm just trying to learn something new each day. Stackoverflow helps me with that! I love it. – Ebassador Sep 02 '15 at 18:53
  • 1
    @varocarbas To clarify further the point I was trying to make: `rowindex` and `columnindex` are the named arguments for the various VBA range objects. My point was that, in addition to what you had already pointed out about the invalidity of the OP's Columns object having two arguments, in VBA, that argument could not be zero. – Ron Rosenfeld Sep 02 '15 at 18:53
  • A couple of ideas for future readers. IDEA 1: this is VB.NET (coupled with Excel via Interop, but fully relying on .NET rules, for example ALL THE ARRAYS ARE 0-BASED). IDEA 2: the problem of this code was row.Columns(0,0) which as explained above is wrong (also explained the corrections), it would be also wrong in VBA because of using TWO INDICES (zero or any other number). IDEA 3: VBA (not VB.NET) is 1-based only in certain parts (like cells) but not in others (arrays) and it might be converted into zero-based (this is just the default behaviour). – varocarbas Sep 02 '15 at 18:54
  • Ebassador, you have to understand that the fact that it crashes in your specific situation does not make it absolutely wrong (might your code not be perfect?). If you don't have too much knowledge about something and someone (apparently) with much more knowledge on this matter is saying you something, perhaps you shouldn't think that a random interpretation might be good to argue with such a person. I insist: .NET (all the collections in any .NET language) is zero-based; when you read from Excel, the information is stored in a .NET collection (nothing to do with VBA), which is 0-based. – varocarbas Sep 02 '15 at 19:06
  • PS: the most likely reason for your problem is that you are doing .value2.ToString() without checking whether .value2 is actually null (a very bad idea, because if it is null ToString() wouldn't exist and an error would be triggered). The first cell in your spreadsheet (rdr.Rows(0).Columns(0) in VB.NET) is most likely blank, value2 is null and there you have your error. – varocarbas Sep 02 '15 at 19:07
  • @RonRosenfeld I think that I have let these ideas very clear in various comments, but just in case here comes a new clarification: the Columns collection in VBA (no relation at all with the one discussed in this question, a completely different collection having the same name and which follows the VB.NET rules and thus is 0-based) is 1-based (same thing for the rows, cells, etc.). There are quite a few things in VBA (e.g., arrays) which are 0-based (at least, by default; might be changed). In any case, insisting that VB.NET Interop has NOTHING TO DO with VBA (just a similar syntax). – varocarbas Sep 02 '15 at 19:14
  • 1
    @varocarbas I agree, but I was writing solely about the specific named arguments which I mentioned, and not about VBA arrays. – Ron Rosenfeld Sep 02 '15 at 19:16
  • @RonRosenfeld As said, all the collections related to actual cells (e.g., Columns, Rows, Sheets, WorkBooks, etc.) and quite a few functions are actually 1-based; but not everything in VBA is 1-based by default. – varocarbas Sep 02 '15 at 19:18

1 Answers1

1

Most of the problems have already been alluded to, but here is the answer with code.

    Dim rdr As Excel.Range = oFindings.Range
    For Each row As Excel.Range In rdr.Rows

        'explicitly get the first cell as a range 
        Dim aCell As Excel.Range = row.Cells(1, 1)

        'explicity convert the value to String but don't use .String
        Dim Account as string = CStr(aCell.Value2)

        If Not String.IsNullOrEmpty(Account) Then

            ' Do other stuff

        End If

    Next

Using aCell.Value2.toString will fail if the cell is empty because Value2 will be Nothing. Instead use CStr(aCell.Value2) which won't fail. But then you need to test if the string is null or empty before using the value.

D_Bester
  • 5,723
  • 5
  • 35
  • 77