none
Multiple target cells in Excel RRS feed

  • Question

  • How do I target multiple cells in VB.NET / VSTO in Excel? In VBA it works like

    Dim s as string

    s = "Cat,Dog,Horse"

    Range(s).operation = whatever

    but I can't get it to work in VSTO.

    I am aware that there are most likely limits of how many cells I can have in my union, but it should for sure be at least 30, if not 64.

    Friday, November 22, 2013 6:15 PM

Answers

  • Solved it. Since I'm using Finnish Excel, I can't do it like "A1,A3,A5", it has to be "A1;A3;A5". I have absolutely no idea why, but that's how it works.
    • Marked as answer by KilpAr Monday, November 25, 2013 9:17 AM
    Monday, November 25, 2013 9:16 AM

All replies

  • Hi,
    According to your description, you meet an issue when targeting multiple cells in VSTO with VB.Net.
    But What is the meaning of “I can't get it to work”?

    I try to reproduce your issue with the code below in my Excel 2013 VSTO add-in and I found it worked well.

    Private Sub ChooseMultipuleCell()
        Dim s As String
        Dim app As Excel.Application = Globals.ThisAddIn.Application
        Dim ws As Excel.Worksheet = app.ActiveSheet
        s = "Cat,Dog,Horse"
        ws.Range(s).ColumnWidth = 20
        ws.Range(s).Value = "aaa"
    End Sub

    >>I am aware that there are most likely limits of how many cells I can have in my union, but it should for sure be at least 30, if not 64.<<

    I set the Range(“Cat”) as more than 100 cells and it works, so I think this issue may not related to the limit of the Cells number.

    Are you sure that you have set the Range(“Cat”), Range(“Dog”) and Range(“Horse”)?

    Would you mind sharing the document and the sample code snippet in VSTO add-in through SkyDrive for us to reproduce your issue?


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, November 25, 2013 7:41 AM
    Moderator
  • Hi,
    According to your description, you meet an issue when targeting multiple cells in VSTO with VB.Net.
    But What is the meaning of “I can't get it to work”?

    I try to reproduce your issue with the code below in my Excel 2013 VSTO add-in and I found it worked well.

    Private Sub ChooseMultipuleCell()
        Dim s As String
        Dim app As Excel.Application = Globals.ThisAddIn.Application
        Dim ws As Excel.Worksheet = app.ActiveSheet
        s = "Cat,Dog,Horse"
        ws.Range(s).ColumnWidth = 20
        ws.Range(s).Value = "aaa"
    End Sub

    >>I am aware that there are most likely limits of how many cells I can have in my union, but it should for sure be at least 30, if not 64.<<

    I set the Range(“Cat”) as more than 100 cells and it works, so I think this issue may not related to the limit of the Cells number.

    Are you sure that you have set the Range(“Cat”), Range(“Dog”) and Range(“Horse”)?

    Would you mind sharing the document and the sample code snippet in VSTO add-in through SkyDrive for us to reproduce your issue?


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thanks for the answer. I tried to simplify this even further and it seems like I can't even set the value correctly.

    “I can't get it to work” means plain and simply that it crashes the program, giving error 0x800A03EC "COMException was unhandled".

    So now I'm trying to do this as simply as possible. I threw out the named range part to begin with (just to delete as many sources of error as possible) and I'm now at point where I try to do this:

    shtTarget.range("A1").value = 1

    works just as expected, but 

    shtTarget.range("A1, A2").value = 1

    crashes.

    The final goal I try to do here is that I have a list of cell names, like 900 of them and I need to set the style to them given at the same list. Doing it cell-by-cell works, but based on VBA experiment, doing it style-by-style (that is, select all cells it applies to, might be 1, might be 500 and set style) speeds it up by a magnitude. The cells can't be expected to be a continuous range, they are an union - which this A1, A2 would be too.

    Monday, November 25, 2013 8:36 AM
  • Solved it. Since I'm using Finnish Excel, I can't do it like "A1,A3,A5", it has to be "A1;A3;A5". I have absolutely no idea why, but that's how it works.
    • Marked as answer by KilpAr Monday, November 25, 2013 9:17 AM
    Monday, November 25, 2013 9:16 AM