Slow Load of VBA App.

[jgoulart]

I have an application that all of a suddend seems to take forever loading into excel. As a matter of fact there is even a fairly good size delay before the program prompts to allow or dissallow macros. How can I find out how to speed up the load time. I do have quite a bit of on workbook open processing but I don't think it is so much that it could be loading down the system. Any ideas would be appreciated. Here is a sample of what I am doing on workbook open:Private Sub Workbook_Open()
Dim test
Dim test2

Dim x
ActiveWorkbook.Unprotect Password:="2rsuccess"
setvars

Utl_BuildCmdBar "invtool", msoBarTop, Workbooks(fname).Sheets("menusheet").Range("cbdata"), fname

Application.ScreenUpdating = False

If Worksheets("invoice").Range("N:IV").EntireColumn.Hidden = False Then
Worksheets("invoice").Range("N:IV").EntireColumn.Hidden = True
End If

If Worksheets("invoice").Range("49:65536").EntireRow.Hidden = False Then
Worksheets("invoice").Range("49:65536").EntireRow.Hidden = True
End If

If Sheets("setup").Visible = True Then
Sheets("setup").Select
Sheets("setup").Visible = False
Sheets("setup").Visible = xlVeryHidden
End If

If Sheets("MenuSheet").Visible = True Then
Sheets("MenuSheet").Select
Sheets("MenuSheet").Visible = False
Sheets("MenuSheet").Visible = xlVeryHidden
End If

If Sheets("temp").Visible = True Then
Sheets("temp").Select
Sheets("temp").Visible = False
Sheets("temp").Visible = xlVeryHidden
End If

If Sheets("Nutritional Products").Visible = True Then
Sheets("Nutritional Products").Select
Sheets("Nutritional Products").Visible = False
Sheets("Nutritional Products").Visible = xlVeryHidden
End If

If Sheets("Personal Care").Visible = True Then
Sheets("Personal Care").Select
Sheets("Personal Care").Visible = False
Sheets("Personal Care").Visible = xlVeryHidden
End If

If Sheets("Colour Cosmetics").Visible = True Then
Sheets("Colour Cosmetics").Select
Sheets("Colour Cosmetics").Visible = False
Sheets("Colour Cosmetics").Visible = xlVeryHidden
End If

If Sheets("Literature").Visible = True Then
Sheets("Literature").Select
Sheets("Literature").Visible = False
Sheets("Literature").Visible = xlVeryHidden
End If

If Sheets("Art of Promotion").Visible = True Then
Sheets("Art of Promotion").Select
Sheets("Art of Promotion").Visible = False
Sheets("Art of Promotion").Visible = xlVeryHidden
End If


If Sheets("Sheet2").Visible = True Then
Sheets("sheet2").Select
Sheets("sheet2").Visible = False
Sheets("sheet2").Visible = xlVeryHidden
End If

If Sheets("Database").Visible = True Then
Sheets("Database").Select
Sheets("Database").Visible = False
Sheets("Database").Visible = xlVeryHidden
End If

If Sheets("Invoice Lookup").Visible = True Then
Sheets("Invoice Lookup").Select
Sheets("Invoice Lookup").Visible = False
Sheets("Invoice Lookup").Visible = xlVeryHidden
End If

If Sheets("Customer").Visible = True Then
Sheets("Customer").Select
Sheets("Customer").Visible = False
Sheets("Customer").Visible = xlVeryHidden
End If



If Sheets("Autofill").Visible = True Then
Sheets("Autofill").Select
Sheets("Autofill").Visible = False
Sheets("Autofill").Visible = xlVeryHidden
End If

If Sheets("Invoice").Visible = True Then
Sheets("Invoice").Select
Else
Sheets("Invoice").Visible = True
Sheets("Invoice").Select
End If

getsetup2
ActiveWorkbook.protect Password:="2rsuccess"
If CheckSysPass() = False Then
EnterPassword.Show
End If
clear
getdate
getnewinvoicenum
'drop = True
Sheets("sheet2").Range("invoice").Value = GetSetting(appname:=ThisWorkbook.name, section:="Startup", _
key:="Invoice", Default:="1000") + 1
Sheets("invoice").invcont = GetSetting(appname:=ThisWorkbook.name, section:="Startup", _
key:="Invoice", Default:="1000") + 1
'invcont = Sheets("sheet2").Range("invoice")
Application.ScreenUpdating = True


End Sub


Just to give you an example of the time on open it took about 15 seconds for the enable/disable macro prompt to come up and then it was another almost 1:30 min before it actually opened. Any ideas would be much appreciated.

John

 

[Jay Petrulis]

Hi John,

1. Try deleting all the files in your temp directory (just a guess here).

2. Rob Bovey, in the following thread: http://groups.google.com/groups?hl=...W.12191317@bgtnsc05-news.ops.worldnet.att.net

Suggests to wait a fraction of time when Excel opens to allow its native "garrbage cleanup" utility to kick in and clean house before any automatic macros are executed.

(By the way, Rob Bovey is an Excel guru who knows more about Excel and Excel VBA than you or I could ever possibly hope to know.)

So, following his suggestions, I recommend moving the routine from the workbook_open in the ThisWorkbook module to an Autpen routine in a standard module.

Also, you can speed up the code by removing all .select statements.

The following should execute faster.

------------------------
Sub Autpen()
Application.OnTime Now(), "AppAutoOpen"
End Sub

Sub AppAutoOpen()
''' Run application startup code here.

Dim test
Dim test2

Dim x
ActiveWorkbook.Unprotect Password:="2rsuccess"
setvars ' ???? wasn't dimmed, through error

Utl_BuildCmdBar "invtool", msoBarTop, Workbooks(fname).Sheets("menusheet").Range("cbdata"), fname

Application.ScreenUpdating = False

Worksheets("invoice").Range("N:IV").EntireColumn.Hidden = True
Worksheets("invoice").Range("49:65536").EntireRow.Hidden = True

If Sheets("setup").Visible = True Then Sheets("setup").Visible = xlVeryHidden
If Sheets("MenuSheet").Visible = True Then Sheets("MenuSheet").Visible = xlVeryHidden
If Sheets("temp").Visible = True Then Sheets("temp").Visible = xlVeryHidden
If Sheets("Nutritional Products").Visible = True Then Sheets("Nutritional Products").Visible = xlVeryHidden
If Sheets("Personal Care").Visible = True Then Sheets("Personal Care").Visible = xlVeryHidden
If Sheets("Colour Cosmetics").Visible = True Then Sheets("Colour Cosmetics").Visible = xlVeryHidden
If Sheets("Literature").Visible = True Then Sheets("Literature").Visible = xlVeryHidden
If Sheets("Art of Promotion").Visible = True Then Sheets("Art of Promotion").Visible = xlVeryHidden
If Sheets("Sheet2").Visible = True Then Sheets("sheet2").Visible = xlVeryHidden
If Sheets("Database").Visible = True Then Sheets("Database").Visible = xlVeryHidden
If Sheets("Invoice Lookup").Visible = True Then Sheets("Invoice Lookup").Visible = xlVeryHidden
If Sheets("Customer").Visible = True Then Sheets("Customer").Visible = xlVeryHidden
If Sheets("Autofill").Visible = True Then Sheets("Autofill").Visible = xlVeryHidden
Sheets("Invoice").Visible = True
Sheets("Invoice").Select

getsetup2
ActiveWorkbook.Protect Password:="2rsuccess"
If CheckSysPass() = False Then
EnterPassword.Show
End If
Clear
getdate
getnewinvoicenum
'drop = True
Sheets("sheet2").Range("invoice").Value = GetSetting(appname:=ThisWorkbook.Name, section:="Startup", _
key:="Invoice", Default:="1000") + 1
Sheets("invoice").invcont = GetSetting(appname:=ThisWorkbook.Name, section:="Startup", _
key:="Invoice", Default:="1000") + 1
'invcont = Sheets("sheet2").Range("invoice")
Application.ScreenUpdating = True
End Sub
---------------------------

Also, if you want the sheets hidden, you don't really need to test if they are visible.

You could possibly accomplish the same thing with

Dim ws As Worksheet

Sheets("Sheet1").Visible = True
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Sheet1" Then ws.Visible = xlSheetVeryHidden
Next ws


HTH,
Jay