Difference between mycell(row) and mycell.offset(row)

[bbrother]

I have run into some weird behavior in the immediate window when querying values of some stopped code. mycell is dimmed as a range, and row is a long integer.

[COLOR=#574123]? mycell(row)[/COLOR]
[COLOR=#574123]'results in pop up window, overflow, run time error 6[/COLOR]


[COLOR=#574123]? mycell.Offset(row)[/COLOR]
[COLOR=#574123]3/10/1903 9:10:57 AM [/COLOR]


[COLOR=#574123]? row[/COLOR]
[COLOR=#574123]5148

[/COLOR]? vartype(row)
 3 
? vartype(mycell(row))
 9 
? vartype(mycell)
 8

I don't know if I am running out of memory (task manager has excel at 432 MB) and excel is throwing random errors or if there truly is a difference between mycell(row) and mycell.offset(row). If I am running out of memory then I need to find a way to read less data in, but I suspect the problem is something else.

----
Update

I changed the code to use offset, and it ran a tiny bit further, but still died on an overflow error. I see that Mycell.Offset(row).value=<overflow>.
How can simply quering the valye of a cell cause an overflow? Here's the code:

For Each Key In Header
    Row = 2
    Col = Col + 1
    RLCarray(1, Col) = Key
    
    Set MyCell = RLCSheet.Cells.Find(Key, lookat:=xlWhole)
    If Not (MyCell Is Nothing) Then
        Do
            If IsNumeric(MyCell(Row)) Then
                RLCarray(Row, Col) = Round(MyCell.Offset(Row).Value, 2)
            Else
                RLCarray(Row, Col) = MyCell.Offset(Row).Value
            End If
            Row = Row + 1
        Loop Until Row > UBound(RLCarray)
    Else
        MsgBox ("Missing Header: " & Key & Chr(13) & "stopping.")
        Stop
    End If
    Update = Update + 1
    StatusWindow.Status.Caption = "Reading RLC data into array (" & Format(Update / UBound(Header), "##%") & ")"
    DoEvents
Next

This is frustrating and I don't know the value of a cell in VBA could overflow, when I can see the value clear as day in Excel. The one funky thing is that Excel VBA sees this as a date, but in fact the value is a number, or a currency to be more specific.</overflow>

 

[joeu2004]

Look at:

? mycell(row).Address
? mycell.Offset(row).Address

If row is 1, mycell(row).Address is the same as mycell.Adress.

But mycell.Offset(row) is the same as mycell(2).Address.

In other words, Offset(row) adds row to the address of mycell. Whereas mycell(row) adds row-1 to the address; it indexes mycell.

 

[wideboydixon]

mycell(row) is looking for the 5148th element in the range. I'm guessing mycell doesn't contain that many cells. For example, try the following in the immediate window:

? Range("C5:D6")(3).Address

You should get $C$6 as that's the third cell in that range (C5, D5, C6, D6).

Now, mycell.offset(row) is taking your range and moving it down 5148 rows. Try this:

? Range("C5:D6").Offset(5148).Address

You should get $C$5153:$D$5154 as that's the range moved down by 5148 rows.

So mycell(row) and mycell.offset(row) are not the same and will produce different results.

WBD

 

[bbrother]

and now, Excel is doing the opposite thing to me:

? mycell(row)
6/5/2212 4:41:44 PM 
?mycell.Offset(row)
'generates run time error 6, overflow

 

[bbrother]

OK, I see the difference now between (row) and offset(row), thank you wideboydixon. However, mycell is defined as but a single cell. I know it doesn't contain 5148 cells - but if that was causing the error, shouldn't it have died when it tried to read the second cell? Take a simpler example of looking at the address of a single cell and offsetting it. it doesn't cause an error - it seems to simply index down just like offset (albeit different by 1). I can fix that, but it still doesn't solve the memory overflow error. I was sure hoping that there was some kind of ubound on mycell, but it doesn't seem to be relevant that I can find.

If mycell didn't contain 5148 cells, then certainly the range ("C5") wouldn't contain more than 1 cell, yet the following seems to work:

? range("C5")(2).Address
$C$6


? range("C5").Offset(2).Address
$C$7

 

[bbrother]

Also interesting is that

? row
 5148 
? mycell(row-1)
6/5/2212 4:41:44 PM 
? mycell(row+1)
3/10/1903 9:10:57 AM 
? mycell(row)
'generates overflow error

 

[bbrother]

Weird, but maybe solved?

mycell(row).select worked fine

I formatted the cell as a general instead of a date, and now the code is working.

Why oh why would a change in cell format have anything to do with an overflow error on a range that was being red into a variant? LOL

 

[RoryA]

If you have a value that is too large for a date in a cell that is formatted as Date, retrieving its Value property will result in an overflow (that's also why you will see ###### in the cell on the sheet). Using the Value2 property will resolve that since it retrieves the underlying Double as a Double rather than trying to return a Date value.

 

[bbrother]

YES! Thank you for that post! I feel much better now.