Thursday, August 15, 2013
Saving during code execution
"Heck! Everybody knows to save a workbook during code execution all you need is 'Workbooks(<workbook_name>).save'. What's this moron getting to?
Sometimes during code execution there are so many changes introduced to a workbook that Excel locks up because there is no more memory to hold the changes.
Compare the memory used with the memory available and when a threshhold level is reached, save the workbook.
OK...I would have liked to use the brilliant solution if I would have had time. Instead I used a couple of quicker and less brilliant solutions.
For one tool, I was importing a set of input files, posting them to worksheets and specific cells in worksheets. They came in no certain order, so the code runs like this
Constant MxFilesToProcessBeforeSave as Integer = 5
Dim FileLongName as String
Dim FilesProcessedSinceLastSave as Integer
Dim FileProcessed as Boolean
Dim wkbkTarget as Workbook
<excluded setup calls>
FileLongName = dir(Inputs_Dir & IIf(Right(Inputs_Dir, 1) = "\", "", "\"), vbReadOnly + vbHidden + vbSystem)
Do While FileLongName <> ""
FileProcessed = False
Call ProcessInputFile(FileLongName, FileProcessed )
' Get next file
If FileProcessed Then
FilesProcessedSinceLastSave = FilesProcessedSinceLastSave + 1
If FilesProcessedSinceLastSave > MxFilesToProcessBeforeSave Then
FilesProcessedSinceLastSave = 0
FileLongName = dir
Another problem arose when trying to reset the input worksheets for the new month. One solution would have been to delete the old worksheets and re-add them. However, the person who had originally created the workbook methodically named and ordered the worksheets. Each sheet was named with a number (1 through 12) indicating the organization, a period, then a letter (d,o,i,f, or c) indicating the content of the worksheet. Therefore when executing a "For each ws in Workbook.worksheets" loop I was able to add code to see when we changed the group.
Dim ws As Worksheet
Dim LastWSFamily As String
For Each ws In wkbkTarget.Worksheets
If Left(Right(ws.Name, 2), 1) = "." Then
If Right(ws.Name, 1) <> LastWSFamily Then
Application.StatusBar = "Saving before resetting " & ws.Name
LastWSFamily = Right(ws.Name, 1)
Application.StatusBar = "Resetting " & ws.Name
Select Case ws.Name
Case <name of worksheet that needs no processing>
Case <name of worksheet that needs has areas with a special name pattern that can be cleared by a subroutine searching for that name pattern>
ws:=ws ' This is a Sub to clear Named areas DataArea, DataArea1, DataArea2 etc
If LCase(Right(ws.Name, Len("xref"))) <> "xref" Then ' "xref" sheets have tables used in vlookups for cross-referencing different labels for the same data.
ws.Rows.OutlineLevel = 1 ' Removes outlining
ws.Rows.Hidden = False ' These two lines ensure the "Clear" gets all data on sheet
ws.Columns.Hidden = False
ws.Visible = xlSheetHidden ' This hides sheets. The procedure for adding current month's data shows (unhides) the worksheets as each worksheet is populated. This allows users to check progress without clicking on each worksheet to see if it is populated.
Application.StatusBar = ""