2

I can't seem to find a way to use one of my VBA tricks in VB.net

For example selecting this range works in VBA:

Dim Border As Range   
Border = Range(Cells(2, 3), Cells(10, 4)).Select

In Visual Studio I can only use one "Cells" to call a range. The same code above underlines the word "Cells" saying its not declared even though I have:

Dim Border As Excel.Range
Border = Range(Cells(2, 3), Cells(10, 4)).Select()

I use .Row and .Column to identify the data-sets dimensions. I tried looking everywhere for an explanation/solution. Maybe I'm looking to hard in the wrong direction. Anyone have any idea?

Zabalba
  • 75
  • 1
  • 9

2 Answers2

1

You need to qualify Cells with the Range or Worksheet. Cells reference is not implied as it is in VBA.

C#:

internal static void TestRangeCells(Worksheet worksheet)
{
    Range range = worksheet.Range(worksheet.Cells[1, 1], worksheet.Cells[5, 10]);
    MessageBox.Show(range.Address);
}
Cor_Blimey
  • 3,260
  • 1
  • 14
  • 20
  • @Zabalba yeah, it must have been typed whilst I was writing my answer. Either way, glad it works :) – Cor_Blimey Aug 16 '13 at 22:42
  • @Zabalba oh, and fyi, you need explicit references for other things too: Range, ActiveCell, ActiveSheet, ActiveWorkbook (amongst others) need to be qualified. In VBA this is implicit. – Cor_Blimey Aug 16 '13 at 22:47
0

According to http://msdn.microsoft.com/de-de/library/aa288993(v=vs.71).aspx, the following should work:

Border = ws.get_Range("B3:J4",Type.Missing);

This just uses the cell names with columns coded as letters instead of the indexes (2 -> B, 10 ->J). You probably can build the string dynamicilly in code if you need to.

FrankPl
  • 13,205
  • 2
  • 14
  • 40
  • It still not taking what I entered. Could you kindly show a example using Cells(1,2), Cells(1,3) for example? – Zabalba Aug 16 '13 at 19:07
  • What is it that you want too achieve? I understood you want to define a range of some cells. And for that it would not matter how you do it. However now it seems the main purpose is to use the `Cells` function, no matter for which purpose. Is that your question? – FrankPl Aug 16 '13 at 19:11
  • Yes I would like to use the Cells Function rather than the A1 Style of referencing cells. – Zabalba Aug 16 '13 at 19:17
  • Maybe http://stackoverflow.com/questions/4319878/how-to-get-specific-range-in-excel-through-com-interop can give you an idea how to selve your problem. It is just C# and not VB. – FrankPl Aug 16 '13 at 19:23
  • Awesome! It took it when I dim'd each cell then used it inside a range. I did not think it would work because the above did not work either. It led me to realize I needed to state the sheet before the cells. `BorderRange = oSheet.Range(oSheet.Cells(1, 1), oSheet.Cells(LR1, LC1))` – Zabalba Aug 16 '13 at 19:38