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:
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)
Globals.Sheet2.Range(Globals.Sheet2.Cells(2, 2), Globals.Sheet2.Cells(6, 3)).Select()
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.