Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Slow Load of VBA App.

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    John G
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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=e...rldnet.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 Auto_Open routine in a standard module.

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

    The following should execute faster.

    ------------------------
    Sub Auto_Open()
    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •