Slow Excel - large Modules and lots of formulas

E

eugene7

Board Regular
Joined
Apr 29, 2006
Messages
94
I'm building a rather large Excel workbook, with lots of formulas and originally lots of VB mobules and Forms. I merged the Modules together to help writing the code, but Excel is now running very slowly...

Is it better to have lots of small VB Modules, rather than one large one with lots of Subs, or is there no real difference?

The size of the Excel workbook is 39MB, but it was running fine until I merged the Modules together.

Have I got to un-merge them now?

I have about 6 sheets of formulas - but am only ever running one sheet at a time.

Any thoughts?

Steve
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Hi Steve,

On a computer with infinite RAM I don't believe you should see any difference. I suspect that whereas the two workbooks were running in real memory before, they are now requiring a lot of virtual memory paging. I suggest you use the Performance Monitor to check the memory usage/paging while running. If this is the problem you should be able to eliminate it by adding more RAM or possibly by changing the Windows settings related to max process real memory allocation.

Damon
 
Upvote 0
Thanks for the thoughts, but the Excel workbook will be used on a range of different PCs, so I really need to find a way to improve the speed of the Excel workbook by altering the way I'm making it.

Does the size of VB Modules have any effect?

Steve
 
Upvote 0
Steve

Could we see an example of the code/formulas?
 
Upvote 0
Hi Norie,
I have to say up-front I am ot a VB coder, and I'm making my way through this mainly with help from this forum!!!

What I've got works thanks to such help.

It's not pretty, and it's not clever, but it functions - slowly at the moment.

I have just tried an old version of the Excel Workbook prior to me merging the Modules together, and it runs a lot faster, so I think my big combined modules are the issue.

For example, if you look at the first VB Script below you can see the mergerd Subs that were originally seperate Modules...

Formulas look like:
Code: 
AVERAGE(E1:E289)/4.0029
=curve($AE$3:$AE$19,$AF$3:$AF$19,AK1)
=IF(AL1<0,0,IF(AL1>1023,1023,AL1))
="33 33 33 " &IF(AL133<0,0,IF(AL133>1022,255, TEXT(AL133/4, "0"))) &" " &IF(AN133<0,0,IF(AN133>1022,255, TEXT(AN133/4, "0"))) &" " &IF(AP133<0,0,IF(AP133>1022,255, TEXT(AP133/4, "0")))
=IF(G34<0,0,IF(G34>65535,4095,G34/16.004
Of which there are 6 sheets with literally 100's, if not 1000's of such formulas...

The VB Scripts look like the follwoing, with this example to load a *.* file (txt mainly):
Code: 
Sub loadAvid2DLUT()

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Sheets("LUT Converter").Select
    ActiveSheet.ChartObjects("Chart 19").Activate
    ActiveChart.SeriesCollection(1).XValues = "0,0"
    ActiveChart.SeriesCollection(1).Values = "0,0"
    ActiveChart.SeriesCollection(2).XValues = "0,0"
    ActiveChart.SeriesCollection(2).Values = "0,0"
    ActiveChart.SeriesCollection(3).XValues = "0,0"
    ActiveChart.SeriesCollection(3).Values = "0,0"

    Range("C20:V5000").Select
    Selection.ClearContents
    Range("A1").Select

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

    On Error Resume Next
    Dim strFileName
    
    strFileName = Application.GetOpenFilename("Text Files (*.*), *.*")
    If strFileName <> False Then
        
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
        
        Workbooks.OpenText Filename:=strFileName, Origin:= _
            xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
            xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
            Comma:=False, Space:=True, Other:=False
        
        If Err.Number = 1004 Then
            Exit Sub
        End If
        
        ActiveSheet.UsedRange.Select
        Selection.Copy
        ActiveWorkbook.Close
        Sheets("LUT Convverter").Select
        Range("C20").Select
        ActiveSheet.Paste
        Range("C20:E1043").Select
        
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    MsgBox "LUT load done", vbInformation
    
    End If
    
    
End Sub

Sub loadChromeImaging3DLUT()

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Sheets("LUT Converter").Select
    ActiveSheet.ChartObjects("Chart 19").Activate
    ActiveChart.SeriesCollection(1).XValues = "0,0"
    ActiveChart.SeriesCollection(1).Values = "0,0"
    ActiveChart.SeriesCollection(2).XValues = "0,0"
    ActiveChart.SeriesCollection(2).Values = "0,0"
    ActiveChart.SeriesCollection(3).XValues = "0,0"
    ActiveChart.SeriesCollection(3).Values = "0,0"

    Range("C20:V5000").Select
    Selection.ClearContents
    Range("A1").Select

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

    On Error Resume Next
    Dim strFileName
    
    strFileName = Application.GetOpenFilename("Text Files (*.*), *.*")
    If strFileName <> False Then
        
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
        
        Workbooks.OpenText Filename:=strFileName, Origin:= _
            xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
            xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
            Comma:=False, Space:=True, Other:=False
        
        If Err.Number = 1004 Then
            Exit Sub
        End If
        
        ActiveSheet.UsedRange.Select
        Selection.Copy
        ActiveWorkbook.Close
        Sheets("LUT Convverter").Select
        Range("C20").Select
        ActiveSheet.Paste
        Range("D22:F4934").Select
        
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    MsgBox "LUT load done", vbInformation
    
    End If
    
    
End Sub

Sub loadCinetal2DLUT()

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Sheets("LUT Converter").Select
    ActiveSheet.ChartObjects("Chart 19").Activate
    ActiveChart.SeriesCollection(1).XValues = "0,0"
    ActiveChart.SeriesCollection(1).Values = "0,0"
    ActiveChart.SeriesCollection(2).XValues = "0,0"
    ActiveChart.SeriesCollection(2).Values = "0,0"
    ActiveChart.SeriesCollection(3).XValues = "0,0"
    ActiveChart.SeriesCollection(3).Values = "0,0"

    Range("C20:V5000").Select
    Selection.ClearContents
    Range("A1").Select

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

    On Error Resume Next
    Dim strFileName
    
    strFileName = Application.GetOpenFilename("Text Files (*.*), *.*")
    If strFileName <> False Then
        
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
        
        Workbooks.OpenText Filename:=strFileName, Origin:= _
            xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
            xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
            Comma:=False, Space:=True, Other:=False
        
        If Err.Number = 1004 Then
            Exit Sub
        End If
        
        ActiveSheet.UsedRange.Select
        Selection.Copy
        ActiveWorkbook.Close
        Sheets("LUT Convverter").Select
        Range("C20").Select
        ActiveSheet.Paste
        Range("C29:E1052").Select
        
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    MsgBox "LUT load done", vbInformation
    
    End If
    
    
End Sub
Which repeats for some 18 different entries...

Then there is the following to call up a specific Form:
Code: 
Sub savePandora3DAvid2DLUT()

    On Error GoTo addJump

    FrmConvPandoraAvid.Show
    Exit Sub
    
addJump:

    MsgBox "An error has occurred: Are you running Excel 2003, or later?", vbCritical
    Application.CutCopyMode = False
    Sheets("LUT Converter").Select
    Range("A1").Select
    
End Sub

Sub savePandora3DChromeImaging3DLUT()

    On Error GoTo addJump

    FrmConvPandoraChromeImaging.Show
    Exit Sub
    
addJump:

    MsgBox "An error has occurred: Are you running Excel 2003, or later?", vbCritical
    Application.CutCopyMode = False
    Sheets("LUT Converter").Select
    Range("A1").Select
    
End Sub

Sub savePandora3DCinetal2DLUT()

    On Error GoTo addJump

    FrmConvPandoraCinetal.Show
    Exit Sub
    
addJump:

    MsgBox "An error has occurred: Are you running Excel 2003, or later?", vbCritical
    Application.CutCopyMode = False
    Sheets("LUT Converter").Select
    Range("A1").Select
    
End Sub
Which also repeats for 18 different entries...

and then the following to act on the loaded *.* file and load into the formula sheets:
Code: 
Sub confirmiQ2DLUT()

    Application.ScreenUpdating = False
    
    Sheets("LUT Converter").Select
    Selection.Copy
    
    VisibleState = Worksheets("iQ2D Conv Data").Visible
    Worksheets("Pandora Conv Data").Visible = xlSheetVisible
    
    Sheets("iQ2D Conv Data").Select
    Range("B1").Select
    ActiveSheet.Paste
    
    Sheets("LUT Converter").Select
    ActiveSheet.ChartObjects("Chart 19").Activate
    ActiveChart.SeriesCollection(1).XValues = "='iQ2D Conv Data'!R1C1:R1024C1"
    ActiveChart.SeriesCollection(1).Values = "='iQ2D Conv Data'!R1C2:R1024C2"
    ActiveChart.SeriesCollection(2).XValues = "='iQ2D Conv Data'!R1C1:R1024C1"
    ActiveChart.SeriesCollection(2).Values = "='iQ2D Conv Data'!R1C3:R1024C3"
    ActiveChart.SeriesCollection(3).XValues = "='iQ2D Conv Data'!R1C1:R1024C1"
    ActiveChart.SeriesCollection(3).Values = "='iQ2D Conv Data'!R1C4:R1024C4"

    Worksheets("iQ2D Conv Data").Visible = VisibleState
    Sheets("LUT Converter").Select
    Application.CutCopyMode = False
    Range("A1").Select
    
    Application.ScreenUpdating = True


End Sub

Sub confirmiQ3DLUT()

    Application.ScreenUpdating = False
    
    Sheets("LUT Converter").Select
    Selection.Copy
    
    VisibleState = Worksheets("iQ3D Conv Data").Visible
    Worksheets("Pandora Conv Data").Visible = xlSheetVisible
    
    Sheets("iQ3D Conv Data").Select
    Range("E1").Select
    ActiveSheet.Paste
    
    Sheets("LUT Converter").Select
    ActiveSheet.ChartObjects("Chart 19").Activate
    ActiveChart.SeriesCollection(1).XValues = "='iQ3D Conv Data'!R1C37:R1024C37"
    ActiveChart.SeriesCollection(1).Values = "='iQ3D Conv Data'!R1C38:R1024C38"
    ActiveChart.SeriesCollection(2).XValues = "='iQ3D Conv Data'!R1C39:R1024C39"
    ActiveChart.SeriesCollection(2).Values = "='iQ3D Conv Data'!R1C40:R1024C40"
    ActiveChart.SeriesCollection(3).XValues = "='iQ3D Conv Data'!R1C41:R1024C41"
    ActiveChart.SeriesCollection(3).Values = "='iQ3D Conv Data'!R1C42:R1024C42"

    Worksheets("iQ3D Conv Data").Visible = VisibleState
    Sheets("LUT Converter").Select
    Application.CutCopyMode = False
    Range("A1").Select
    
    Application.ScreenUpdating = True

End Sub

Sub confirmIridas2DLUT()
    
    MsgBox "Not Yet Active", vbInformation
    
End Sub
Which also repeats some 18 times...

and then there are some 100+ forms save new *.* files generated from the loaded data, which are:
Dim FleNme As String

Private Sub UserForm_Initialize()

LUTname.Value = ""

LUTname.SetFocus

End Sub

Private Sub SaveLUT_Click()

Application.ScreenUpdating = False

On Error Resume Next
MkDir ActiveWorkbook.Path & "\ViewLUTs"
On Error GoTo 0

On Error GoTo addJump

VisibleState = Worksheets("iQ2D Conv Data").Visible
Worksheets("iQ2D Conv Data").Visible = xlSheetVisible

Sheets("iQ2D Conv Data").Select

Range("AE1:AG1024").Select

Dim r As Range, c As Range
Dim sTemp As String

Open ActiveWorkbook.Path & "\ViewLUTs\" & LUTname.Value & ".txt" For Output As #1

For Each r In Selection.Rows
sTemp = ""
For Each c In r.Cells
sTemp = sTemp & c.Text & Chr(9)
Next c

'Get rid of trailing tabs
While Right(sTemp, 1) = Chr(9)
sTemp = Left(sTemp, Len(sTemp) - 1)
Wend
Print #1, sTemp
Next r
Close #1

Worksheets("iQ2D Conv Data").Visible = VisibleState
Sheets("LUT Converter").Select
Application.CutCopyMode = False
Range("A1").Select

Unload Me ' Close the form

Application.ScreenUpdating = True

MsgBox "Avid ViewLUT save done", vbInformation
Exit Sub

addJump:

MsgBox "An error has occurred: Are you running Excel 2003, or later?", vbCritical

End Sub
Click to expand...

Sorry about the length of this post, but you did ask for some examples :(

Please don't be too rude about my coding - I really don't know what I'm doing :eek:

Any help greatly appreciated!

Steve
 
Upvote 0
Steve

You say you are repeating code 18 or so times?

Why? Is the code basically the same with slight differences?

If it is then it could probably all be combined in one routine.

Most of the code looks fine, but then again I don't actually know what it's purpose is.:)

One thing is that you shouldn't really need to use select.

For example this.
Code: 
Range("C20:V5000").Select
Selection.ClearContents
Could be replaced with this.
Code: 
Range("C20:V5000").ClearContents
Note this also applies to selecting worksheets.
 
Upvote 0
Hi Norie,
Yes, the code is basically the same, but with some differences.

In the above examples I've actually shown two or three of each 'Sub' (what were the seperate modules before I combined them into one module) so you can see the differences, and hopefully suggest if they can be combined into one routine?

Thanks for the suggestion about not using select.

Steve
 
Upvote 0
You talk about combining "Modules" and "two or three Sub's that were separate modules". Modules are like Folders in the VB editor. Their number, within reason, should make no difference to the speed your workbook runs. Whether or not you have several Sub's, (macros), or combine them into one larger macro shouldn't make any difference either. I usually like to keep different tasks in their own Sub Procedures and use the "Call" function within a macro to run that procedure. There again, that alone shouldn't effect workbook run speed.
Norie's suggestions on what your macros are actually doing, like "Selecting" cells and ranges will definitely effect speed.
 
Upvote 0
Hi Datsmart,
It's interesting, but if I run a older version of the workbook where the Subs are all in seperate modules the program runs a lot faster than the newere version where I put all the Subs into one or two Modules, as shown above. The physical size of the old version and the new are very similar!
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Need Formula or VLOOKUP
Replies
0
Views
212
Rampage598
R
O
  • Question
Link COUNTIFS formula from old workbook to new
2
Replies
10
Views
305
PeteWright
PeteWright
O
  • Question
slow file help
Replies
0
Views
165
orsm6
O
E
  • Question
Forecasting with VBA or Power Pivot in Excel
Replies
0
Views
359
Emilio30
E
C
  • Question
Porting an Excel application to Access
Replies
6
Views
229
chazrab
C

Forum statistics

Threads
1,214,583
Messages
6,120,383
Members
448,956
Latest member
JPav

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top