CODEWITHSUNDEEP
excelvba
karpeant
karpeant

Reputation: 3

Slow Excel with the VBA Code under the Open Workbook

so i have an Excel Workbook where the UserForm is shown to the user when opening this File. In the UserForm (Drop Down List) the user have to select different Sheets that will be displayed(visible) after the selection, the other sheets will be hide. Unfortunately to open a Workbook and show this drop down list take a lot of time, is there any way to speed up the procedure?

here is how the code in open workbook looks like, it's just make a call of the module where the main code is:

Private Sub Workbook_Open()

Call openexcel

End Sub

and here is the module with openexcel part:

Sub openexcel()

Dim xlApp As Application

Set xlApp = Excel.Application

xlApp.Visible = False

Call OpenForm.Show

End Sub

And there are 5 modules for each Spreadsheet to show or hide, each with the following code, e.g:

Sub spreadsheet1()
Dim xlApp As Application
Dim xlBook As Workbook

Set xlApp = Excel.Application
Set xlBook = ThisWorkbook


With xlBook
    .Sheets("1.1").Visible = True
    .Sheets("1.2").Visible = True
    .Sheets("2.1").Visible = xlVeryHidden
    .Sheets("2.2").Visible = xlVeryHidden
    .Sheets("3.1").Visible = xlVeryHidden
    .Sheets("3.2").Visible = xlVeryHidden
    .Sheets("4.1").Visible = xlVeryHidden
    .Sheets("4.2").Visible = xlVeryHidden
    .Sheets("5.1").Visible = xlVeryHidden
    .Sheets("5.2").Visible = xlVeryHidden
End With

OpenForm.Hide
xlApp.Visible = True


End Sub

So how can i speed up it? Maybe i should not split the code for each tab in a new module, but rather put everything in one module?

Upvotes: 0

Views: 1137

Answers (1)

HackSlash
HackSlash

Reputation: 5805

#1 If you are using Excel as your host you never need to make a new Application object.

Just use:

Application.Visible = False
...
Application.Visible = True

#2 You don't need 5 different modules with identical code. That breaks the coding law "don't repeat yourself". Any time you copy and paste code you should think "This is a new procedure that needs to be dynamic".

EXAMPLE:

Public Sub UnhideSpreadSheets(ByVal sheetName As String)
    On Error GoTo finally
    Application.ScreenUpdating = False
    
    Dim thisSheet As Worksheet
    For Each thisSheet In ThisWorkbook.Sheets
        If thisSheet.Name Like sheetName & "*" Then
            thisSheet.Visible = xlSheetVisible
        Else
            thisSheet.Visible = xlVeryHidden
        End If
    Next thisSheet

finally:
    Application.ScreenUpdating = True
    OpenForm.Hide
    Application.Visible = True
End Sub

#3 I agree with Mat. The problem is not your code, it's your workbook. There are many ways to optimize Excel workbooks. Please read this MS article on the topic as it's too long to post here: https://learn.microsoft.com/en-us/office/vba/excel/concepts/excel-performance/excel-tips-for-optimizing-performance-obstructions

Upvotes: 1

Related Questions