Click here to Skip to main content
15,936,602 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Dear All,

I have my excel project in VS 2010. I have made an Excel Add-In. Which has an UDF in dll. Its working fine. Since I am trying to fetch data from database and copying my formula in each cell, Its taking some time to populate the result in excel. I want to increase its response time so that it can give me result in a blink of an eye.

What I think is somehow if I copied the formula in just one cell and then for a given range if i just refer the same cell, then i can get rid of time consuming issue.

e.g. what i have is

HTML
DEMO              2-Apr-10              3-Apr-10                 4-Apr-10        
abc      myFunction("p1,p2,p3")  myFunction("p1,p2,p3")  myFunction("p1,p2,p3")  
pqr      myFunction("p1,p2,p3")  myFunction("p1,p2,p3")  myFunction("p1,p2,p3")  
xyz      myFunction("p1,p2,p3")  myFunction("p1,p2,p3")  myFunction("p1,p2,p3")  
lmn      myFunction("p1,p2,p3")  myFunction("p1,p2,p3")  myFunction("p1,p2,p3")  


after execution of each function returns appropriate value for corresponding cell. I have used absolute cell features i.e. with "$" to achieve above output.
I think this is responsible for poor performance because for each cell function is hitting my database. I have Server 2008.



This is what I want..
e.g.
VB
myFunction("p1,p2,p3")  2-Apr-10    3-Apr-10    4-Apr-10        
abc                      value1      value2      value3
pqr                      value4      value5      value6
xyz                      value7      value8      value9
lmn                      value10     value11     value12



The function written in the top most left cell should be able to populate the rest of excel.

I have tried lots of things but my excel knowledge is not enough to crack this. I have created some macros also but not succeeded.

Any help on this is highly appreciable.

Thanks..
Posted
Updated 2-May-12 19:32pm
v2
Comments
Maciej Los 2-May-12 7:53am    
Not enough clear. Please be more specific. Copying formula is very simple, but without an example of code we can't help you, becouse we don't know: 1) where is a database (in this file or in another one) and 2) how do you get data (connection...)?
comred 3-May-12 1:38am    
ok.. I have updated my question.. check it out..
Maciej Los 3-May-12 19:01pm    
Have you tried: Fill function?
"p1,p2,p3" - Is it input argument(s) for function?

Let me explain:

EXAMPLE 1: A1 = 5, B1 = 5, C1.FORMULA = $A$1+$B$1 then C1.VALUE = 10
When you fill formula for the rest of cells in range, all cells in column c are equal 10.

EXAMPLE 2: A1 = 5, B1 = 5, C1.FORMULA = $A1+$B1 then C1.VALUE = 10
When you fill formula for the rest of cells in range, all cells in column c, d, e, etc. are equal 10, but rows depend on value in col. A and B

EXAMPLE 3: A1 = 5, B1 = 5, C1.FORMULA = A$1+B$1 then C1.VALUE = 10
When you fill formula for the rest of cells in range, all cells in columns c, d, e, etc. are equal to values from row no. 1

EXAMPLE 4: A1 = 5, B1 = 5, C1.FORMULA = $A1+B$1 then C1.VALUE = 10
When you fill formula for the rest of cells in range, all cells in columns c, d, e, etc. are equal to values from column A and row no. 1

Depend on position of $, the result in filled cells is changing.

1 solution

My comment is still current...

Please, read the following articles:
1) Range.Formula Property[^]
2) How to automate Microsoft Excel from Microsoft Visual C#.NET[^]
and, if it still would be necessary, improve your question.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900