Thursday, August 15, 2013

Saving during code execution

Readers' Initial Reactions

"Heck!  Everybody knows to save a workbook during code execution all you need is 'Workbooks(<workbook_name>).save'.  What's this moron getting to?

The real problem...besides not being clear in the title of this entry

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.

Brilliant solution

Compare the memory used with the memory available and when a threshhold level is reached, save the workbook.

Less brilliant solutions

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.

Count then Save

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

Sub ImportFromInputsFolder()
    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
               wkbkTarget.Save
               FilesProcessedSinceLastSave  = 0
            End If
        End If
        FileLongName = dir
    Loop
End Sub

Changing Sets

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.

Sub ClearWksheets(wkbkTarget)
    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
                wkbkTarget.Save
                LastWSFamily = Right(ws.Name, 1)
            End If
        End If
        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>
                ClearWkshtDataAreas 
ws:=ws ' This is a Sub to clear Named areas DataArea, DataArea1, DataArea2 etc
            Case Else
                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.Cells.Clear
                    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.
                End If
        End Select
    Next ws
    Application.StatusBar = ""
End Sub