1

I am using 32-bit 2013 Excel with VBA extensively. I have disabled hardware graphics acceleration and COM add-ins, yet I still struggle with the following problem:

I am importing the contents of another large workbook with formatting on the cells but with no formulas (~3mb Excel file) into the problematic Excel workbook. On the first attempt - when the contents have not been imported yet - the import succeeds. I am importing the content via VBA similar to the following code:

 Application.Workbooks(F_Home).Activate
 Workbooks(F_Home).Sheets(Sheet1).Visible = xlSheetVisible
 ...
 Application.Workbooks(F_Source).Activate
 Workbooks(F_Source).Sheets(S_Source).Cells.Copy Destination:=Workbooks(F_Home).Sheets(Sheet1).Cells
 Application.Workbooks(F_Home).Activate

F_Home is the problematic Excel workbook and F_Source is the workbook with the content we are importing in. When doing this the first time it works, and then I save the file and close out of it, and reopen the file, and try this a second time. On the second time we attempt to import the contents (when the contents already imported) the F_Home workbook crashes with the Out of Memory Error (There isn't enough memory to complete this action...) on the line that copies F_Source contents to F_Home.

Using Process Explorer I've found that the Excel process usually runs around 600mb - 700mb in virtual memory size, but when we run the VBA script to import the contents a second time, the virtual memory size suddenly jumps to 4gb (this does not happen on the first time around, which stays at the 600mb - 700mb range). How should I fix this? I cannot do a workaround such as saving the file before importing the contents a second time, because the timestamp on the file is used and saving the file through VBA will confuse some users.

Thank you for your help.

haans
  • 13
  • 4
  • No formulas on the workbook with contents to be imported - just data. – haans Apr 26 '17 at 21:52
  • 1
    Your problem comes from the fact that you copy 1048576*16384 cells. You know how big is that? Do you really need to do that? You can only copy the range that has data in it and it's gonna be much faster and less computationally expensive. – M-- Apr 26 '17 at 21:53
  • Also, when you have no formula consider assigning them to the range instead of copy/paste. – M-- Apr 26 '17 at 21:54
  • 1
    If you're copy/pasting *all the cells* in a sheet to another sheet, why not just copy the worksheet itself instead? – BruceWayne Apr 26 '17 at 22:02
  • 1
    @BruceWayne - one possible disadvantage of copying the worksheet is that you would (IIRC) be creating a new worksheet, and `Workbooks(F_Home)` may have other sheets which have formulas referencing cells in the existing `Workbooks(F_Home).Sheets(Sheet1)`. So overwriting the existing data may be the only viable option. – YowE3K Apr 26 '17 at 22:14
  • @YowE3K - Ah, good point. With that in mind, is it safe to say it's better practice to use the sheet name over the `Sheet1`? I.e. `Worksbooks(F_Home).Sheets("Sheet1")` over `...Sheets(Sheet1)`? Or is that really dependent on what the user wants to do/how the macro is written and hard to say generally? – BruceWayne Apr 26 '17 at 22:35
  • @BruceWayne - In order to not crash, `Sheet1` in the expression `Sheets(Sheet1)` would have to be a numeric or string variable containing the sheet number or name. (That variable may, for instance, contain the string "ThisIsMySheet" or the number 5.) I'm not sure whether the OP actually is using variables called `F_Home`, `F_Source`, `Sheet1`, etc, or whether they have "anonymised" their posted code by placing those "variable names" in there to substitute for hard-coded values in their actual code. – YowE3K Apr 26 '17 at 22:47
  • @YowE3K - Ah, duh I knew that...I was thinking `Sheets(1)` vs `Sheets("Sheet1")`. Don't mean to derail, so I'll just research on my own. Thanks though for clarifying and reminding me of that. – BruceWayne Apr 26 '17 at 22:50
  • 1
    @BruceWayne - Re `Sheets(1)` vs `Sheets("Sheet1")` - I always prefer the latter - it is too easy for a user to change the order of sheets, but they are less likely to change the sheet names. (Or, at least, if they do change the name then it is easier to search and replace in the code to change the names there as well.) – YowE3K Apr 26 '17 at 23:00

1 Answers1

0

Copy and pasting the whole sheet is not a wise approach. Let's assume you have data from "A1:Z99999". You can do this which is going to be much faster.

Set S_Range = Workbooks(F_Source).Sheets(S_Source).Range("A1:Z99999")
Set H_Range = Workbooks(F_Home).Sheets(Sheet1).Range("A1:Z99999")

H_Range.Value = S_Range.Value

Also read why you should avoid selecting and activating in vba.

Graham
  • 7,431
  • 18
  • 59
  • 84
M--
  • 25,431
  • 8
  • 61
  • 93
  • Unfortunately, I've encountered an issue with copying data using this method. When in debugging mode, the S_Range variable is populated with all the data from the F_Source workbook and copies over data to the F_Home Workbook. However, when calling this function without debugging and stepping through the code, it only copies over 3 rows of data versus the many thousands of row I have in the F_Source workbook. – haans May 02 '17 at 15:00
  • Please [edit](http://stackoverflow.com/posts/43644970/edit) your question and include the exact code you are using. This problem doesn't have anything with this part of the code. Something else triggers it. – M-- May 02 '17 at 15:12