Dynamically select Excel cells with VB .Net in a VS 2010 project

Question

I'm using Visual Studio 2010, to create a project from a Excel Workbook template. VS2010 already gives me a workbook with one worksheet. I added another one. The project consist of the following files: ThisWorkbook.vb Sheet1.vb Sheet2.vb

Each file consist of class for each object: Sheet1.vb has its Sheet1 class, Sheet2.vb Sheet2 class and so on.

From MSDN help and another sources, I understood I can access from on vb file, or another class, the other class objects using Globals statement:

    Me.Range("A10").Value = "Validation time:"
    Me.Range("B10").Value = ValidationTime_T1.Item(ValidationTime_T1.Count - 1) - ValidationTime_T0.Item(ValidationTime_T0.Count - 1)


    Dim x As Double

    Dim rowOffset As Integer

    rowOffset = 3

    For x = 0 To JobCounter
        Globals.Sheet2.Cells(x + rowOffset, 1) = x
        Globals.Sheet2.Cells(x + rowOffset, 2) = ASy_Start_Mem.Item(x)
        Globals.Sheet2.Cells(x + rowOffset, 3) = WSZ_Start_Mem.Item(x)
        Globals.Sheet2.Cells(x + rowOffset, 4) = WSZ_Start_Pk_Mem.Item(x)
        Globals.Sheet2.Cells(x + rowOffset, 5) = PFU_Start_Mem.Item(x)

As you can see, I move data from one sheet to another, or put data that is in memory to sheet2 from sheet1.

So far, so good. There were not that many issues I could not solve by my self.

I think is good to point out here that my background is C, C++, Perl and VBA in the last 4 years, so I'm struggling with VB since quite a bit already, I find .Net more useful, until I had to use it with Excel, it is giving me a hard time.

Now, I need to select specific cells base on positions and it is not always the same position so I need to select them dynamically, NO HARD CODED, as I have found in MSDN, forums, StackOverFlow and different sites, the solution they give is to use:

Globals.Sheet2.Range("C7").Select()

And yeah, that's fine for specific cells for which you use a string to select.

The problem is that I need to select different cells every time depending on the each day data, andt I don't know how to do it with Range. In the previous code you can see I used Cells for that.

I've tried:

Globals.Sheet2.Range(Globals.Sheet2.Cells(2, 2), Globals.Sheet2.Cells(6, 3)).Select()

Because one of Range definitions states that I can receive as parameter two Cells objects, but gives an error. I've tried creating Range objects for the cell positions. I have used Object object with CType, and it also fails.

If anyone could help how I can dynamically select cells, either with Range or Cells, or both, or if you have another solution, I will gladly appreciate it.

Answer 1

First thanks to TnTinMn, for replying, and I apologize for the late response again, it's been crazy at work.

Well, I found the solution thanks to your comment.

There seems to be a known issue with Excel.Interop and Visual Basic .Net called "two dots". Normally, when you go programming VB in VS write a few letter and IntelliSense prompts you with what may be available (property, method or object) then you enter a dot and another list comes up showing you what’s available, once you chose the previous two categories you either assign them to a variable or object, or given them a value. But you can still put another dot and get another list, here is where VB get messed up and not working, probably VS will let you put whenever amount of dos, but at runtime you'll get the error I got. Hope an experienced StackOverflow fellow can explain this much better.

So the solution for the Select method: First you need to make the sheet active, then you can use the Select method, as TnTinMn predicted the error was with the Select method.

Globals.Sheet2.Activate()
Globals.Sheet2.Range(Globals.Sheet2.Cells(2, 2), Globals.Sheet2.Cells(6, 3)).Select()

And now Select method does not throw the runtime error. With the previous code you can now have an option to go through any cell without hard coding.

The following can also be used to store data ranges from different section, to later use in chart for example:

Dim dataX As Excel.Range
Dim dataY As Excel.Range
Dim dataRange As Excel.Range

dataX = Globals.Sheet6.Cells(xRow, XColumn)
dataY = Globals.Sheet6.Cells(yRow, yColumn)

dataRange = Application.Union(dataX, dataY)

Thank you and hope this help someone.