Reduce Size of Excel Macros

[HSMMIT]

My problem deals with an Excel Workbook that takes almost 15 seconds to save. Probably because the code and forms it contains makes the size of the file over 500KB. The code calls for the workbook to be saved 3 different times and a new workbook created which also is saved. So it takes almost 1 minute just to go through the saving processes.

Is there a way to create a file that would use Excel as the interface but use code from an outside source so the workbook that is saved does not contain all that code.

Or do you have any other suggestions for reducing the size of the workbook or making the saving process go quicker.

Thanks in advance.

HSMMIT

 

[xlbo]

I doubt it's the code itself that makes it take time to save
Sounds like it's the job that the code is doing that takes the time - can you post the code - we may be able to speed it up....

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[ssVBAdev]

I have a spreadsheet that, the code alone, adds almost 1M to the file size. It saves in about 4-5sec. I'd be interested to know about calculations in your spreadsheet. If you are using lots of calculations that have to loop through many iterations then that would be where I would look first. That is generally what slows down my saves. I'd be surprized that the code was at fault.

 

[JamieDeVry]

I do not think that your code is the problem. I would consider boosting your hardware a little. Code takes very little space. If you would like, I can review your code for you. Simply copy and paste it in the next thread.

Jamie

http://nj.devry.edu/~jbates

 

[HSMMIT]

You all seem to be in agreement that the code is not the culprit for slowing down the saving process. But my workbook without the code is 200KB and with the code it is 560KB.
When I save it manually (File – Save) without the code it is very quick.
When I save it manually (File – Save) with the code it is very slow.

More information:
I am using Excel 2000
My computer is pretty typical for our company – Windows 2000sp3; 261KB RAM; 8GB HDD; 600MHz processor.
The workbook is being saved to a network.
Saving the workbook manually (without using the macro) is just as slow as saving it using the macro. Also fyi – all the macros I use in this workbook run fast – it is just the ones that save the workbook that are so slow.

In a nutshell this is what is happening in the workbook:
1. User Opens a Master Workbook (contains all my code)

2. User enters some data

3. User runs SaveUserTimeCard procedure –
a. SaveUserTimeCard procedure includes:
i. Saving Master Workbook in a designated folder on the network with a unique filename for that user, such as MyWorkbook1.xls.

4. User runs BegSignCard procedure –
a. BegSignCard procedure includes:
i. Validating user name and verifying info is correct
ii. Calling EndSignCard procedure –
1. EndSignCard procedure includes:
a. Calling Move_Data procedure which moves data from workbook to Access
b. Calling Approval procedure –
i. Approval procedure includes:
1. Entering user’s name in a cell
2. Copying worksheet in MyWorkBook1.xls to another workbook
3. Saving copied workbook to another folder on the network, and re-naming it as MyWorkBook1–Signed.xls
4. Closing the MyWorkBook1-Signed.xls workbook (MyWorkbook1.xls is still open)
c. Calling AskPrintCopy procedure which prints MyWorkbook1.xls, if user wants
d. Calling ProtectTimeCard procedure
i. ProtectTimeCard procedure includes:
1. Saving MyWorkbook1.xls as MyWorkbook1-Protected and
2. Protecting this workbook so no one can open it without the password.
e. Clearing content from some of the named cells in MyWorkbook1.xls
f. Calling CreateNewWeek –
i. CreateNewWeek procedure includes:
1. Saving MyWorkBook1.xls with a new filename, MyWorkbook2.xls
2. Deleting MyWorkbook1.xls

5. End Result is:
MyWorkbook1-Protected.xls and MyWorkbook2.xls is in the user’s folder
MyWorkbook1-Signed.xls is in the approver’s folder


There are several other procedures used during the user’s input, but these deal with validating the user’s entries, and as I said these procedures are not slow.

The only other procedures I have created are ones that run when the workbook is activated and deactivated.

Private Sub Workbook_Activate()
Application.ScreenUpdating = True
CreateTimeCardToolbar
HideBars
Application.EnableAutoComplete = False
StandardCodes
ProtectWkBk
End Sub

***************************************

Private Sub Workbook_Deactivate()
Application.ScreenUpdating = True
Application.EnableAutoComplete = True
ShowBars
End Sub


Here are some of the procedures outlined above:
I’ve added comments and blank line to make reading this easier, however in my real I have taken all comments out and all blank lines out.
***************************************
‘User clicks a button on the worksheet that runs this procedure
Sub SaveUserTimecard()
Application.ScreenUpdating = False
UnprotectWkBk

‘LoadVar assigns data to variables
LoadVar
swValid = True
On Error Resume Next

‘Checks approver folders for MyWorkbook1-SIGNED– if it exists, workbook is closed without saving
If Dir(sServer & sSelfApprovalPath & sAppCurrWkFileName & &quot;-SIGNED&quot; & &quot;.xls&quot <> &quot;&quot; Then
MsgBox &quot;Your TimeCard for &quot; & sDate & &quot; has already been signed and approved.&quot; _
& Chr(13) & Chr(13) & _
&quot; Contact your supervisor to make changes.&quot; _
& Chr(13) & Chr(13) & _
&quot; This TimeCard will now be closed without saving any changes.&quot;
ActiveWorkbook.Close SaveChanges:=False
Exit Sub
End If
If Dir(sServer & sApprovalPath & sCurrWkFileName & &quot;-SIGNED&quot; & &quot;.xls&quot <> &quot;&quot; Then
MsgBox &quot;Your TimeCard for &quot; & sDate & &quot; has already been signed and sent to your approver.&quot; _
& Chr(13) & Chr(13) & _
&quot; Contact your supervisor to make changes.&quot; _
& Chr(13) & Chr(13) & _
&quot; This TimeCard will now be closed without saving any changes.&quot;
ActiveWorkbook.Close SaveChanges:=False
Exit Sub
End If

‘Converts the formula in cell named “WkEnd” to a value
If Range(&quot;Saved&quot = &quot;MasterTimeCard&quot; Then
Range(&quot;WkEnd&quot.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range(&quot;EmpName&quot.Select
End If

‘Next few IF statements are tests for incorrect entries or incomplete entries on worksheet
CardCt = Range(&quot;CardCount&quot
MaxProj = CardCt * 8
For Count = 1 To MaxProj
If Range(&quot;TotB&quot; & Count) <> 0 And Range(&quot;Comm&quot; & Count) = &quot;00261&quot; Then
MsgBox &quot;Your Time Card cannot be saved with Overtime Hours in Commission Number '00261'.&quot;
Range(&quot;OvrHrs&quot; & Count) = &quot;&quot;
Exit Sub
End If
Next Count
If Range(&quot;EmpNo&quot = &quot;&quot; Then
MsgBox &quot;Employee Number is Required&quot;
Exit Sub
Else
ValidateProjectEntries
If swValid = False Then
Exit Sub
Else
Range(&quot;Saved&quot = &quot;UserTimeCard&quot;
LoadVar
End If
End If
ProtectWkBk

‘Saves workbook to a network folder as MyWorkBook1 or asks user if he wants to replace the MyWorkBook1– THIS IS VERY SLOW
ActiveWorkbook.SaveAs Filename:=sServer & sUserPath & sCurrWkFileName, _
FileFormat:=xlWorkbookNormal
End Sub

***************************************

Sub BegSignCard()
Application.ScreenUpdating = False
sServer = Range(&quot;ServerPath&quot
LoadVar

‘Following procedures that are called are validations for correct entries and user signature
ValidateSign
If swValid = False Then
Exit Sub
Else
ValidateSaveStatus
If swValid = False Then
Exit Sub
Else
EnterPassword
If swGreen = False Then
Exit Sub
Else

‘AskSaveCopy allows user to print a copy of workbook
AskSaveCopy

‘StatusBar1Show displays a status bar while some procedures are run
StatusBar1Show

‘EndSignCard continues the signing process
EndSignCard
End If
End If
End If
End Sub

***************************************

Sub EndSignCard()
‘Move_Data copies information from MyWorkbook1 to an Access database – this is pretty fast
Move_Data

‘Approval verifies information, copies file, renames workbook, saves workbook, deletes ‘workbook – VERY SLOW
Approval

‘Determines if MyWorkBook1-Signed already exists
If swSign = False Then
MsgBox &quot;Your TimeCard for &quot; & sDate & &quot; has already been signed and approved.&quot; _
& Chr(13) & &quot;Contact your supervisor to make changes.&quot; _
& Chr(13) & &quot;This TimeCard will now be closed without saving any changes.&quot;
Application.StatusBar = False
ActiveWorkbook.Close SaveChanges:=False
Exit Sub
Else

‘Prints a copy of MyWorkBook1 if user wants
If swCopy = True Then
AskPrintCopy
End If

‘Displays message that all was done correctly
If sApprove = 1 Then
Application.ScreenUpdating = True
Application.StatusBar = False
MsgBox (&quot;Your Time Card for the Week Ending &quot; _
& sFileDate & &quot; has been Signed and Approved.&quot
Else
Application.ScreenUpdating = True
Application.StatusBar = False
MsgBox (&quot;Your Time Card for the Week Ending &quot; _
& sFileDate & &quot; has been Signed and sent for Approval.&quot
End If

‘ProtectTimeCard saves My WorkBook1 as MyWorkBook1-Protected and protects this so no one can open it without the password.
ProtectTimeCard

‘Unprotects MyWorkBook1 and clears the contents of the named cells
UnprotectWkBk
Range(&quot;Comments1&quot = &quot;&quot;
Range(&quot;Comments2&quot = &quot;&quot;
Range(&quot;Comments3&quot = &quot;&quot;
Range(&quot;Comments4&quot = &quot;&quot;
Range(&quot;SignedName&quot = &quot;&quot;
Range(&quot;ApprovedSig&quot = &quot;&quot;
Range(&quot;HoursCard1&quot = &quot;&quot;
Range(&quot;HoursCard2&quot = &quot;&quot;
Range(&quot;HoursCard3&quot = &quot;&quot;
Range(&quot;HoursCard4&quot = &quot;&quot;

‘CreateNewWeek renames MyWorkBook1 to MyWorkBook2 and deletes MyWorkbook1
CreateNewWeek

ClearVar
End If
End Sub

***************************************

Sub AskPrintCopy()
Response = MsgBox(&quot;Do you want to print a copy of your signed Time Card for your records?&quot;, vbYesNo)
If Response = vbYes Then
PrintTimeCard
Else
Exit Sub
End If
End Sub

***************************************

Sub Approval()
Dim TemplatePath As String
Dim SignTemplate As Object
Dim CopyTimeCard As Object
TemplatePath = sServer & &quot;Files\Timecards\Master\&quot;
swSign = True
sFileName = ThisWorkbook.Name
Application.ScreenUpdating = False

‘Copies MyWorkBook1 to a workbook named SignedTemplate – SignedTemplate has no content but is formatted in the same way as MyWorkBook1
Set CopyTimeCard = Workbooks(sFileName).Sheets(&quot;TimeCard&quot.Range(&quot;AllCards&quot
Workbooks.Open Filename:=TemplatePath & &quot;SignedTemplate.xls&quot;
Set SignTemplate = Workbooks(&quot;SignedTemplate.xls&quot.Sheets(&quot;SignedTimeCard&quot.Range(&quot;All&quot
CopyTimeCard.Copy SignTemplate

‘Determines if MyWorkbook1-Signed already exists
If sApprove = 1 Then
If Dir(sServer & sSelfApprovalPath & sAppCurrWkFileName & &quot;-SIGNED&quot; & &quot;.xls&quot <> &quot;&quot; Then
swSign = False
ActiveWorkbook.Close SaveChanges:=False
Exit Sub
End If
Else
If Dir(sServer & sApprovalPath & sCurrWkFileName & &quot;-SIGNED&quot; & &quot;.xls&quot <> &quot;&quot; Then
swSign = False
ActiveWorkbook.Close SaveChanges:=False
Exit Sub
End If
End If

‘Saves MyWorkBook1 to approver’s folder with a different filename (MyWorkBook1-Signed)
If sApprove = 1 Then
ActiveWorkbook.SaveAs Filename:=sServer & sSelfApprovalPath & sAppCurrWkFileName & &quot;-SIGNED&quot;, _
FileFormat:=xlNormal
Else
ActiveWorkbook.SaveAs Filename:=sServer & sApprovalPath & sCurrWkFileName & &quot;-SIGNED&quot;, _
FileFormat:=xlNormal
End If
Range(&quot;A1&quot.Select
ActiveWorkbook.Close SaveChanges:=True
End Sub

***************************************

Sub CreateNewWeek()
On Error Resume Next
UnprotectWkBk
sNewDate = Range(&quot;HoldDate&quot + 7
Range(&quot;HoldDate&quot = sNewDate
Range(&quot;WkEnd&quot = sNewDate

‘StatusBar2Show displays a status bar while some procedures are run
StatusBar2Show
sNewFileDate = Replace(sNewDate, &quot;/&quot;, &quot;-&quot
sNewWkFileName = sFileEmpDept & &quot;\&quot; & sFileEmpNo & &quot;-&quot; & sNewFileDate

‘Determines if MyWorkbook2 already exists
If Dir(sServer & sUserPath & sNewWkFileName & &quot;.xls&quot <> &quot;&quot; Then
Kill (sServer & sUserPath & sCurrWkFileName & &quot;.xls&quot
ClearVar
MsgBox &quot;Your Time Card Will Now Close.&quot;
ActiveWorkbook.Close SaveChanges:=False
Exit Sub
End If
Range(&quot;A1&quot.Select
ProtectWkBk

‘Saves MyWorkBook1 as MyWorkBook2- VERY SLOW
ActiveWorkbook.SaveAs Filename:=sServer & sUserPath & sNewWkFileName, _
FileFormat:=xlNormal, password:=&quot;&quot;, WriteResPassword:=&quot;&quot;, _
ReadOnlyRecommended:=False, CreateBackup:=False

‘Deletes MyWorkBook1
Kill (sServer & sUserPath & sCurrWkFileName & &quot;.xls&quot
Application.StatusBar = False
MsgBox (&quot;This is Your TimeCard for the Week Ending &quot; & sNewFileDate & &quot;.&quot
End Sub


***************************************

Sub ProtectTimeCard()
ProtectWkBk
If sApprove = 1 Then
Exit Sub
Else

‘Renames MyWorkBook1 as MyWorkBook1-PROTECTED and protects this from opening without a password - VERY SLOW
ActiveWorkbook.SaveAs Filename:=sServer & sUserPath & sCurrWkFileName & &quot;-PROTECTED&quot;, _
FileFormat:=xlNormal, password:=&quot;work&quot;, WriteResPassword:=&quot;&quot;, _
ReadOnlyRecommended:=False, CreateBackup:=False
End If
End Sub
***************************************

Sub LoadVar()
‘All variables have been defined in a Public Module
sApprove = Range(&quot;ApproveStatus&quot
sFileEmpNo = Range(&quot;HoldEmpNo&quot
sFileEmpDept = Range(&quot;HoldDeptName&quot
sEmpCat = Range(&quot;HoldEmpCat&quot
sDate = Range(&quot;WkEnd&quot
sFileDate = Replace(sDate, &quot;/&quot;, &quot;-&quot
sAppCurrWkFileName = sFileEmpNo & &quot;-&quot; & sFileDate
sCurrWkFileName = sFileEmpDept & &quot;\&quot; & sFileEmpNo & &quot;-&quot; & sFileDate
End Sub

***************************************

Sub ClearVar()
sFileEmpNo = &quot;&quot;
sFileEmpDept = &quot;&quot;
sEmpCat = &quot;&quot;
sDate = &quot;&quot;
sFileDate = &quot;&quot;
sAppCurrWkFileName = &quot;&quot;
sCurrWkFileName = &quot;&quot;
sNewDate = &quot;&quot;
sNewWkFileName = &quot;&quot;
End Sub

***************************************

Sub StatusBar1Show()
Application.ScreenUpdating = False
Application.DisplayStatusBar = True
Application.StatusBar = &quot;Please wait while your card is being sent to your approver.&quot;
End Sub

***************************************

Sub StatusBar2Show()
Application.ScreenUpdating = False
Application.DisplayStatusBar = True
Application.StatusBar = &quot;Please wait while your card for the next week is being created.&quot;
End Sub

***************************************

Sub UnprotectWkBk()
ActiveWorkbook.Unprotect password:=&quot;work&quot;
Sheets(&quot;TimeCard&quot.Unprotect password:=&quot;work&quot;
Sheets(&quot;TimeCard&quot.Select
End Sub

***************************************

Sub ProtectWkBk()
Sheets(&quot;TimeCard&quot.Protect password:=&quot;work&quot;
ActiveWorkbook.Protect password:=&quot;work&quot;
Sheets(&quot;TimeCard&quot.Select
End Sub

***************************************

 

[ssVBAdev]

I stand by the point that I don't believe it's your code. As I said my code adds almost 1M to a spreadsheet of mine. Just looking at yout code quickly though I have some comments/questions:

The slowdown appears to occur at the points your code executes the .saveas method (correct?).
I am wondering about your network connection. Is it normally slow? I mean, do you use the network regularly without problems?
Is the network a LAN in your immediate office or is it connected to another office via land lines, T1 etc...
Also, I question again the amount of calculations that are in your spreasheet. If there are lots of calculations this will slow the workbook down at .saveas.
As a test of this:
- go to &quot;Tools&quot;-&quot;Options...&quot; and choose the &quot;Calculation&quot; tab.
- choose the &quot;Manual&quot; option button and make sure the &quot;Recalculate before save&quot; check box is unchecked.
- Then try and run you macros and see what happens...

Let me know.

 

[ssVBAdev]

Although, if you do want to shorten the code a little here are some thoughts (hopefully I'm not getting too picky ))...

You use this line (and associated lines) at least 4 times through your subs:
If Dir(sServer & sApprovalPath & sCurrWkFileName & &quot;-SIGNED&quot; & &quot;.xls&quot <> &quot;&quot; Then
If you made this into a function (perhaps substituting the second and third variables in your path concatenation with function arguments) then you could call the function from code and take action on the results of the returning value. This would save you a few lines of code.

Same sort of thing for this .Saveas method that seems to be giving you the problem. Set it up as a separate sub or function so that you can assign arguments for the different saves you would like to do and you could save even more lines of code.

When you check for the files in your SaveUserTimeCard sub you report the same &quot;Your time card has already been signed&quot; message twice. A similar message also appears in your EndSignCard sub. If this message was setup as a separate sub or function you would save a few lines of code and would standardize the message so that it looked the same each time it is called throughout your code (looks cleaner to the user).

A couple of With... End With statments could also clean things up a tiny bit. If you are using (say in your EndSignCard sub) you are using Application.Whatever, use the With Application... End With and you'll be able to erase all those references to Application.

 

[HSMMIT]

Our network uses T1 and the server is in my office.
Saving to the server is normally not slow.
I did check the manual option and unchecked &quot;Recalculate before save&quot; and noticed no difference in the amount of time it took to save.

I will try your suggestions for shortening the code.

Thanks for the help

 

[ssVBAdev]

Intersting. My suggestions are just that... suggestions. I don't think they'll help with the speed. I'll have to test out some things on my machine (I'm also using XL2000 on Windoz 2000).