VB.NET Update Excel

[DDB007]

I am trying to use variable in excel cell, my code works only when I put explicit range. Here is sample of code

Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim sSPC_BPC(2) As String
Dim sBPC(1, 4) As Integer


'The line of code below does not show any values in excel although when I try to look at this cell oSheet.Cells(sBPC(0, 0), sBPC(0, 1)) in Debug mode I could see value.

oSheet.Cells(sBPC(0, 0), sBPC(0, 1)) = sSPC_BPC(0)

' This code below works OK
oSheet.range("D5").value = sSPC_BPC(0)

Appreciate your help in advance!

[Hack]

Welcome to DevX

I don't understand what you are trying to do?

Is your question on how to use a variable range?

[DDB007]

I am just saying that code
oSheet.Cells(sBPC(0, 0), sBPC(0, 1)) = sSPC_BPC(0)
does not work. I did not see any changes in Spreadsheet but I could see that
?oSheet.Cells(sBPC(0, 0), sBPC(0, 1)) has value in it.

[DDB007]

I change my database table to keep cell location as combination of the Col ("A".. , ect) and Row (1,2... etc) instead of indexes and I change code to
oSheet.Range(Trim(sBPC(0, 2)) & CInt(sBPC(0, 1))).Value = sSPC_BPC(0)
Have no idea why cell integer indexes did not work.

[Hack]

I change my database table to keep cell location as combination of the Col ("A".. , ect) and Row (1,2... etc) instead of indexes and I change code to
oSheet.Range(Trim(sBPC(0, 2)) & CInt(sBPC(0, 1))).Value = sSPC_BPC(0)
Have no idea why cell integer indexes did not work.

So this does work for you and your problem is solved?

[DDB007]

Yes I solved the problem by using different code, but I am still wondering why cells(i,j) does not work. Is it some kind of mistake on my part or corrupted environment?

[JBourgeois]

Note that in all your examples, when you where careful to specify the property .Value, it worked.

In the cases it did not work, you did not specify the Value property, probably assuming that it was the default property.

In the ActiveX/COM world, where lives Excel, default properties were a standard.

In the .NET world, a default property is a rarity. You need to have an indexed property in order to have a default property. Value is not an indexed property, so it does not qualify.

You are mixing 2 very different world (COM vs .NET), and the debugger sometimes do strange things. Add to that the fact that Excel has been the testing ground for VBA in the old days, it carries with it strange ways of working that are not always what you expect.

You solved your problem, that is the point.

But since you were still wondering what happened in the first place (good thing for a programmer to keep trying to understand a problem that was solved by doing something else... that is how we learn), I thought my insight could be helpful.

[DDB007]

Thank you! You replay helped me to understand this case.