none
Excel showing data of dynmaic rows and columns RRS feed

  • Question

  • Hi, we have the following methods defined in the VSTO EXCEL AddIn for Excel 2003.

    public double MyAdd(double v1, doublev2) {return v1 + v2;} 
    
    
    
    public double[] MyArray(double v1, double v2) { return new double[] { v1, v2 }; }
    
    
    
    
    
    


    When we put =MyAdd(A1,A2) in a cell in the worksheet, it shows the returned value correctly.

    When we pub =MyArray(A1,A2), while it should two values in to cells, it shows only one value.

    How could I show the dyanmic number of rows and columns of data start from the cell where I put the functions in?

    The project is to return data queried from SQL server and the number of rows and columns will be dynamics depdening on the query parameter specified.

    Thanks

    • Edited by Stratford Monday, September 19, 2011 3:56 PM
    Monday, September 19, 2011 3:52 PM

Answers

  • Hi,

    It's unlikely to implement by using formula as far as I know. Formula only works for the cell where the formula lives in, it can't assign value for other cells. You have to try some other approaches, such as customize a button in Excel UI (adding a button in command bar for Excel 2003 or in Ribbon for Excel 2007 and above), adding a custom task pane in Office UI, inserting a UserForm and so on...

    I would be happy to help you on finding an appropriate approach if you can provide more details about your requirement.

    Good day,


    Best Regards, Calvin Gao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, September 21, 2011 6:51 AM
    Moderator

All replies

  •         public double[,] MyArrayRow(double v1, double v2)
            {
                return new double[,] { { v1 }, { v2 } };
            }
    
            public double[] MyArrayCol(double v1, double v2)
            {
                return new double[] {  v1 ,  v2 };
            }
    
    
            private void button1_Click(object sender, EventArgs e)
            {
                this.Range["a3:a4", missing].Value2 = MyArrayRow(1.2, 2.3);
    
    
                this.Range["a1:b1", missing].Value2 = MyArrayCol(1.2, 2.3);
    }
    

    I hope this helps .

    http://vsto.tistory.com
    Tuesday, September 20, 2011 12:31 AM
  • Hi Straford,

    Thanks for your post.

    One thing you should be more specific is what you are expecting the "MyArray" formula to do, fill one cell with the value to "va1, v2" or fill a series cells (lets say "A1" and "A2") with A1 of v1 and A2 of v2?

    If it is the first one, I think you need to return a string which equal to "v1, v2" rather than an array.

    If it is the second one, you need to have a look at VSTO_Beginner's code, which should be help on this scenario.

    I look forward to hearing of you.

     


    Best Regards, Calvin Gao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, September 20, 2011 6:34 AM
    Moderator
  • HI Calvin,

     

    Thanks for your reply.

    What we try to achieve is simliar to Second Scenario but the number of  elements returned will be dynamic.

    Is there anyway to shown the results without hard code the range values?

    Tuesday, September 20, 2011 8:04 AM
  • I don't know what you want exactly. I'm sorry.

    If you want to display data of sql server, please consider to use Listobject.


    http://vsto.tistory.com
    Wednesday, September 21, 2011 5:38 AM
  • Hi,

    It's unlikely to implement by using formula as far as I know. Formula only works for the cell where the formula lives in, it can't assign value for other cells. You have to try some other approaches, such as customize a button in Excel UI (adding a button in command bar for Excel 2003 or in Ribbon for Excel 2007 and above), adding a custom task pane in Office UI, inserting a UserForm and so on...

    I would be happy to help you on finding an appropriate approach if you can provide more details about your requirement.

    Good day,


    Best Regards, Calvin Gao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, September 21, 2011 6:51 AM
    Moderator