VSTO using Cells(5,3):Cells(8,3) instead of "C5:C8" in Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

msc - 11 Apr 2006 14:13 GMT

I am trying to move from writing VBA in Excel to using VSTO. I am used to
being able to use Cells(Row,Col) just about everywhere and most of my VBA
code is written that way. The code at the bottom works but I don't want to
use the "C5:C8" syntax. I am able to go to a single cell with something like
this:
Me.Cells(5, HCol) = MyRead.Item(0).ToString

But I am not able to do a range.

This works:
Dim textInCell As Microsoft.Office.Tools.Excel.NamedRange
textInCell = Me.Controls.AddNamedRange(Me.Range("C5:C8"), "cellText")
textInCell.Value2 = "Hello world!"

This doesn't:
Dim textInCell As Microsoft.Office.Tools.Excel.NamedRange
textInCell =
Me.Controls.AddNamedRange(Me.Range(me.cells(5,3):me.cells(8,3)), "cellText")
textInCell.Value2 = "Hello world!"

Reply to this Message

Alvin Bruney - 12 Apr 2006 17:29 GMT

You syntax needs some minor adjustments. Here is an example based on a named
range (you can use an ordinary range if you want to as well):
Excerpted from professional VSTO 2005

'set a namedrange

Microsoft.Office.Tools.Excel.NamedRange fittedRange =

this.Controls.AddNamedRange(this.Range["A1", "B2"], "fittedRange");

fittedRange.Value2 = DateTime.Now.ToString("mm-DD-yyyy hh:mm:ss");

fittedRange.ShrinkToFit = true;

Signature

Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The Microsoft Office Web Components Black Book with .NET
Now Available @ www.lulu.com/owc
Forth-coming VSTO.NET - Wrox/Wiley 2006
-------------------------------------------------------

>I am trying to move from writing VBA in Excel to using VSTO. I am used to
> being able to use Cells(Row,Col) just about everywhere and most of my VBA
[quoted text clipped - 17 lines]
> "cellText")
> textInCell.Value2 = "Hello world!"

Reply to this Message

msc - 12 Apr 2006 18:41 GMT

Thanks for the response.

What I am really looking for is a way to use Cells(1,2):cells(2,2) instead
of ["A1", "B2"]

I have written a function to turn cells into the A1 syntax and one to turn
the A1 syntax back into the cells format but it seems like I should be able
to use the Cells syntax for the range address.

> You syntax needs some minor adjustments. Here is an example based on a named
> range (you can use an ordinary range if you want to as well):
[quoted text clipped - 31 lines]
> > "cellText")
> > textInCell.Value2 = "Hello world!"

Reply to this Message

Alvin Bruney - 12 Apr 2006 22:36 GMT

Understand that cells are implemented underneath using the range object. The
preferred strategy is to use a range object since it allows for
discontinuous as well as continuous range addressing. For instance, you can
address either 1,2:2,2 or (1,2:2,2 - 2,4:4,3). This is more flexible than
the cell approach because a cell cannot address a discontinuous range. Your
approach attempts to patch together cells to address a particular range.
Unless I misunderstood you completely...

Signature

> Thanks for the response.
>
[quoted text clipped - 45 lines]
>> > "cellText")
>> > textInCell.Value2 = "Hello world!"

Reply to this Message

msc - 13 Apr 2006 13:37 GMT

Addressing when I am trying to create the range object seems to be where I am
having trouble. I am doing a spreadsheet that looks up data in several
different places and it covers a two week time frame. The top row is the Key
so I create a named range with the Key as the name of the range. Then I grab
the data and use the named range to select the correct column and index down
to the correct row using the date on the record. I just find this a lot
easer to do when I can just reference the cells in the 3,8 style than in the
"C8" style. I have worked around it by writing a function that converts C8
to 3,8 and one that does 3,8 to C8 so the problem is solved. I guess I just
miss the ease of just using the 3,8 syntax directly.

Thanks for the reply.

> Understand that cells are implemented underneath using the range object. The
> preferred strategy is to use a range object since it allows for
[quoted text clipped - 53 lines]
> >> > "cellText")
> >> > textInCell.Value2 = "Hello world!"

Reply to this Message

VSTO using Cells(5,3):Cells(8,3) instead of "C5:C8" in Excel

Free Magazines