Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

xlCalculationManual does not stop linked formulas from calculating?

Status
Not open for further replies.

Clark23

Technical User
Apr 6, 2006
6
US
So I am working on a bit of a stop-gap solution until a new set of workbooks/database are created.

User locates the workbook they need to link to and is prompted with a list of sheets in the workbook. The file path and sheet name are stored in two cells of my current workbook. A button triggers a macro to create 16 formulas for each row. Probably not more than 100-200 rows, but I'm having issues with 20-30 rows right now.

There are only a total of 10 possible workbooks to link to. Since INDIRECT doesn't work with linked workbooks I had to write VBA that goes through each line and creates the links. There are 10-15 people who will use distinct copies of the sheet and the links will need to be updated on a weekly basis. It's not a one time shot or I would just manually create the links myself.

Even with Calculations, EnableEvents, and ScreenUpdating turned off, Excel still checks each link. Is there a way to have Excel not calculate the links until my procedure has completed. I know it will take a minute or two to pull all of the links in regardless, but this is acceptable. The twenty minutes it's taking now is not acceptable.

I actually forgot to copy the file to a flash drive before I left Friday, and I don't have VPN; I'm rewriting the gist of the code below.
Code:
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False

For i = 1 to last_row
Cells(i,5).Formula = "distinct linked formula here"
Cells(i,6).Formula = "another distinct linked formula here"
Cells(i,7).Formula = "etc"
Cells(i,8).Formula = "etc"
' and so on....
Next

Application.Calculation = xlAutomaticCalculation
Application.ScreenUpdating = True
Application.EnableEvents = True
I normally don't disable events but I thought it might help. It didn't seem to have any effect though. Is there a way to write the formulas to an array and that set the range equal to that array? I've used this solution for big chunks of data in the past.

I also tried to preface the formula with an apostrophe and then use replace to change it to a formula but Excel didn't fall for that one. :) AFAIK using Replace calculates the cells one by one anyway, regardless of the manual calculation setting.
 


1. Is the data being linked in a TABLE in the remote workbook?

2. If so, is there a need for a LINK, as opposed to the DATA that is in a cell?

It might be quicker to QUERY the remote workbooks, grabbing some or all the data, and THEN populating these cells.

faq68-5829

Skip,
[sub]
[glasses]Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![tongue][/sub]
 
Thanks for your quick reply. Unfortunately the data is not setup in tables. I wish it was, but I did not design the linked sheets. I will definitely keep your suggested method in mind for future projects though, because I didn't know you could do it that way.

I can run a loop to open the workbooks and pull in the data from each workbook, but I was trying to avoid this. If the user forgets to update the data after making a change to the linked workbook, the sheets are no longer accurate. I suppose I could check the last modified date of each workbook upon opening and prompt the user to update.

When I just drag down a row of these formulas, they calculate fairly quickly. It is just a matter of forcing Excel to not resolve each link as I'm writing to the cells. So, I'm still hoping for some solution to my original problem but come Monday I might have to open them in the background and pull in the data manually.
 




" It is just a matter of forcing Excel to not resolve each link as I'm writing to the cells. "

Is it not a pay me now or pay me later? Either way, if it were possible, the link must be resolved and calculated. I don't see where there would be a time savings. Maybe I am missing something here.

Skip,
[sub]
[glasses]Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![tongue][/sub]
 
The formulas with links seem to calculate considerably faster if I were to say, drag a row of formulas down and repeat them 100 times. There is a bit of lag (less than a minute) but I thought Excel might optimize moving the data from another workbook if it "knows" it has 100 links to resolve in the same workbook, rather than doing it one cell at a time?
 




Have you tried using Edit > Replace to assign the workbook link?

Skip,
[sub]
[glasses]Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![tongue][/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top