Applying formula without copying or dragging

Question

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 1-May-12 18:35pm

comred

Updated 2-May-12 19:32pm

v2

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2012-05-02T07:01:00

Solution 1

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.

Posted 2-May-12 7:01am

Maciej Los