How to avoid running out of memory while running VBA

Y

yellow_orange

New Member
Joined
Feb 27, 2010
Messages
49
I created a sample size calculator macro. My problem is that when I run the macro to calculate sample size for a dozen times. Error msgbox just pops out the message out of memory. The macro is running smoothly and gives me accurate results.
 
Sort by date Sort by votes
Rewrite the macro so it doesn't waste memory.






















We can help you if you will share it with us.
 
Upvote 0
Share the code :)

In general
Code: 
'Avoid
Object.Select
Selection.Activity
'use
Object.Activity
'same for 
Object.Activate
ChildObect.Acitcty
'sue
Object.Child.Activity
'Unless you intend that the ActiveObject be in view in the Excel Window after all Process run out.
The use of as below is recommended for all but the most simple macros
Code: 
Sub myProcedure()
Application. ScreeenUpdate = False
Application.EnableEvents = False
My Code here
Application. ScreeenUpdate = True
Application.EnableEvents = True
End Sub
Prevent memory Creep. kill objects when they are no longer needed. End Sub kills most objects, not all.
Code: 
'Dim myVar As Range, Sheet, Workbook, etc Will Die at End Sub.
'Dim MyVar As New Workbook, Window, Worksheet, Etc Won't.
Dim myVar As New Workbook 'Won't Die at End Sub
'Code here
Set MyVar = Nothing
End Sub
Avoid passing parameters "ByVal," they take more memory than "ByRef."

Count the Dots in loops
Code: 
For L = 1 to 100
   Sheets("sheet1").Range("A" & L &"").Value = L * x
Next L
' 200 Dots
 
With Sheets("sheet1")
For L = 1 to 100
   Range("A" & L &"").Value = L * x
Next L
' 100 Dots
 
Upvote 0
Dear all,
I also have the problem with the " out of memory". The data include about 240000 rows and 55 columns.
[TABLE="width: 1280"]
<tbody>[TR]
[TD="width: 64"]Time (hr)[/TD]
[TD="width: 64"]KPCham[/TD]
[TD="width: 64"]M348000U[/TD]
[TD="width: 64"]PrekDam[/TD]
[TD="width: 64"]NekLuong[/TD]
[TD="width: 64"]sp40d[/TD]
[TD="width: 64"]sp35d[/TD]
[TD="width: 64"]sp37d[/TD]
[TD="width: 64"]TBAS5[/TD]
[TD="width: 64"]TBAS24[/TD]
[TD="width: 64"]TLT82[/TD]
[TD="width: 64"]KohKhel[/TD]
[TD="width: 64"]B332000[/TD]
[TD="width: 64"]B272000[/TD]
[TD="width: 64"]TRAB35[/TD]
[TD="width: 64, align: right"]2521[/TD]
[TD="width: 64"]TLT122[/TD]
[TD="width: 64, align: right"]2525[/TD]
[TD="width: 64, align: right"]274[/TD]
[TD="width: 64"]TAFP4[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]2.209[/TD]
[TD="align: right"]1.326[/TD]
[TD="align: right"]1.416[/TD]
[TD="align: right"]0.922[/TD]
[TD="align: right"]2.123[/TD]
[TD="align: right"]2.512[/TD]
[TD="align: right"]0.635[/TD]
[TD="align: right"]3.151[/TD]
[TD="align: right"]0.501[/TD]
[TD="align: right"]0.997[/TD]
[TD="align: right"]1.156[/TD]
[TD="align: right"]1.312[/TD]
[TD="align: right"]0.776[/TD]
[TD="align: right"]1.162[/TD]
[TD="align: right"]0.86[/TD]
[TD="align: right"]1.025[/TD]
[TD="align: right"]0.941[/TD]
[TD="align: right"]0.903[/TD]
[TD="align: right"]0.486[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2.227[/TD]
[TD="align: right"]1.317[/TD]
[TD="align: right"]1.407[/TD]
[TD="align: right"]1.051[/TD]
[TD="align: right"]2.123[/TD]
[TD="align: right"]2.522[/TD]
[TD="align: right"]0.634[/TD]
[TD="align: right"]3.151[/TD]
[TD="align: right"]0.501[/TD]
[TD="align: right"]1.007[/TD]
[TD="align: right"]1.165[/TD]
[TD="align: right"]1.3[/TD]
[TD="align: right"]0.794[/TD]
[TD="align: right"]1.161[/TD]
[TD="align: right"]0.998[/TD]
[TD="align: right"]1.079[/TD]
[TD="align: right"]0.926[/TD]
[TD="align: right"]0.894[/TD]
[TD="align: right"]0.604[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2.243[/TD]
[TD="align: right"]1.321[/TD]
[TD="align: right"]1.4[/TD]
[TD="align: right"]1.187[/TD]
[TD="align: right"]2.122[/TD]
[TD="align: right"]2.532[/TD]
[TD="align: right"]0.644[/TD]
[TD="align: right"]3.151[/TD]
[TD="align: right"]0.501[/TD]
[TD="align: right"]1.011[/TD]
[TD="align: right"]1.166[/TD]
[TD="align: right"]1.297[/TD]
[TD="align: right"]0.893[/TD]
[TD="align: right"]1.216[/TD]
[TD="align: right"]1.083[/TD]
[TD="align: right"]1.19[/TD]
[TD="align: right"]0.935[/TD]
[TD="align: right"]0.911[/TD]
[TD="align: right"]0.727[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]2.258[/TD]
[TD="align: right"]1.349[/TD]
[TD="align: right"]1.401[/TD]
[TD="align: right"]1.224[/TD]
[TD="align: right"]2.122[/TD]
[TD="align: right"]2.541[/TD]
[TD="align: right"]0.659[/TD]
[TD="align: right"]3.151[/TD]
[TD="align: right"]0.501[/TD]
[TD="align: right"]1.029[/TD]
[TD="align: right"]1.164[/TD]
[TD="align: right"]1.309[/TD]
[TD="align: right"]0.967[/TD]
[TD="align: right"]1.308[/TD]
[TD="align: right"]1.127[/TD]
[TD="align: right"]1.273[/TD]
[TD="align: right"]0.975[/TD]
[TD="align: right"]0.955[/TD]
[TD="align: right"]0.82[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]2.27[/TD]
[TD="align: right"]1.393[/TD]
[TD="align: right"]1.415[/TD]
[TD="align: right"]1.168[/TD]
[TD="align: right"]2.122[/TD]
[TD="align: right"]2.551[/TD]
[TD="align: right"]0.677[/TD]
[TD="align: right"]3.151[/TD]
[TD="align: right"]0.501[/TD]
[TD="align: right"]1.048[/TD]
[TD="align: right"]1.17[/TD]
[TD="align: right"]1.342[/TD]
[TD="align: right"]1.023[/TD]
[TD="align: right"]1.347[/TD]
[TD="align: right"]1.093[/TD]
[TD="align: right"]1.275[/TD]
[TD="align: right"]1.034[/TD]
[TD="align: right"]1.014[/TD]
[TD="align: right"]0.829[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]2.282[/TD]
[TD="align: right"]1.418[/TD]
[TD="align: right"]1.441[/TD]
[TD="align: right"]1.096[/TD]
[TD="align: right"]2.122[/TD]
[TD="align: right"]2.56[/TD]
[TD="align: right"]0.694[/TD]
[TD="align: right"]3.151[/TD]
[TD="align: right"]0.501[/TD]
[TD="align: right"]1.065[/TD]
[TD="align: right"]1.184[/TD]
[TD="align: right"]1.376[/TD]
[TD="align: right"]1.005[/TD]
[TD="align: right"]1.324[/TD]
[TD="align: right"]1.033[/TD]
[TD="align: right"]1.22[/TD]
[TD="align: right"]1.076[/TD]
[TD="align: right"]1.05[/TD]
[TD="align: right"]0.723[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]2.296[/TD]
[TD="align: right"]1.42[/TD]
[TD="align: right"]1.461[/TD]
[TD="align: right"]1.036[/TD]
[TD="align: right"]2.122[/TD]
[TD="align: right"]2.57[/TD]
[TD="align: right"]0.71[/TD]
[TD="align: right"]3.151[/TD]
[TD="align: right"]0.501[/TD]
[TD="align: right"]1.08[/TD]
[TD="align: right"]1.202[/TD]
[TD="align: right"]1.392[/TD]
[TD="align: right"]0.98[/TD]
[TD="align: right"]1.275[/TD]
[TD="align: right"]0.962[/TD]
[TD="align: right"]1.16[/TD]
[TD="align: right"]1.082[/TD]
[TD="align: right"]1.052[/TD]
[TD="align: right"]0.61[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]2.312[/TD]
[TD="align: right"]1.413[/TD]
[TD="align: right"]1.464[/TD]
[TD="align: right"]0.991[/TD]
[TD="align: right"]2.121[/TD]
[TD="align: right"]2.579[/TD]
[TD="align: right"]0.72[/TD]
[TD="align: right"]3.151[/TD]
[TD="align: right"]0.501[/TD]
[TD="align: right"]1.092[/TD]
[TD="align: right"]1.216[/TD]
[TD="align: right"]1.391[/TD]
[TD="align: right"]0.951[/TD]
[TD="align: right"]1.225[/TD]
[TD="align: right"]0.896[/TD]
[TD="align: right"]1.114[/TD]
[TD="align: right"]1.065[/TD]
[TD="align: right"]1.036[/TD]
[TD="align: right"]0.538[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]2.33[/TD]
[TD="align: right"]1.401[/TD]
[TD="align: right"]1.459[/TD]
[TD="align: right"]0.959[/TD]
[TD="align: right"]2.121[/TD]
[TD="align: right"]2.588[/TD]
[TD="align: right"]0.723[/TD]
[TD="align: right"]3.151[/TD]
[TD="align: right"]0.501[/TD]
[TD="align: right"]1.099[/TD]
[TD="align: right"]1.228[/TD]
[TD="align: right"]1.383[/TD]
[TD="align: right"]0.922[/TD]
[TD="align: right"]1.181[/TD]
[TD="align: right"]0.839[/TD]
[TD="align: right"]1.079[/TD]
[TD="align: right"]1.038[/TD]
[TD="align: right"]1.01[/TD]
[TD="align: right"]0.487[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]2.347[/TD]
[TD="align: right"]1.389[/TD]
[TD="align: right"]1.449[/TD]
[TD="align: right"]0.931[/TD]
[TD="align: right"]2.121[/TD]
[TD="align: right"]2.597[/TD]
[TD="align: right"]0.718[/TD]
[TD="align: right"]3.151[/TD]
[TD="align: right"]0.501[/TD]
[TD="align: right"]1.102[/TD]
[TD="align: right"]1.236[/TD]
[TD="align: right"]1.374[/TD]
[TD="align: right"]0.896[/TD]
[TD="align: right"]1.143[/TD]
[TD="align: right"]0.785[/TD]
[TD="align: right"]1.05[/TD]
[TD="align: right"]1.007[/TD]
[TD="align: right"]0.98[/TD]
[TD="align: right"]0.443[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]2.362[/TD]
[TD="align: right"]1.378[/TD]
[TD="align: right"]1.44[/TD]
[TD="align: right"]0.901[/TD]
[TD="align: right"]2.121[/TD]
[TD="align: right"]2.606[/TD]
[TD="align: right"]0.709[/TD]
[TD="align: right"]3.151[/TD]
[TD="align: right"]0.501[/TD]
[TD="align: right"]1.099[/TD]
[TD="align: right"]1.239[/TD]
[TD="align: right"]1.365[/TD]
[TD="align: right"]0.874[/TD]
[TD="align: right"]1.108[/TD]
[TD="align: right"]0.732[/TD]
[TD="align: right"]1.022[/TD]
[TD="align: right"]0.975[/TD]
[TD="align: right"]0.947[/TD]
[TD="align: right"]0.402[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]2.375[/TD]
[TD="align: right"]1.369[/TD]
[TD="align: right"]1.432[/TD]
[TD="align: right"]0.868[/TD]
[TD="align: right"]2.12[/TD]
[TD="align: right"]2.615[/TD]
[TD="align: right"]0.697[/TD]
[TD="align: right"]3.151[/TD]
[TD="align: right"]0.501[/TD]
[TD="align: right"]1.101[/TD]
[TD="align: right"]1.239[/TD]
[TD="align: right"]1.356[/TD]
[TD="align: right"]0.855[/TD]
[TD="align: right"]1.073[/TD]
[TD="align: right"]0.681[/TD]
[TD="align: right"]0.991[/TD]
[TD="align: right"]0.942[/TD]
[TD="align: right"]0.913[/TD]
[TD="align: right"]0.36[/TD]
[/TR]
</tbody>[/TABLE]

I am writing small code to convert it into daily average or will find the max, min ..... later. Here are the code

Private Sub CommandButton1_Click()
Dim sal(1 To 240000, 0 To 23, 2 To 54) As Double
Dim saldaily(1 To 9855, 2 To 54) As Double
Dim s As Double
Dim salmonthly(1 To 12, 2 To 54) As Single
Dim nrow, nhour, ncol, nday, nmonth, k As Integer
nday = 1
nrow = 7
'For nrow = 1 To 8760
' If nrow = 8760 Then
' MsgBox "hi"
' End If
While nrow <= 239067
For nhour = 0 To 23
For ncol = 2 To 54
sal(nday, nhour, ncol) = Sheets("Sheet1").Cells(nrow, ncol).Value
Next ncol
nrow = nrow + 1
If nhour = 23 Then
nday = nday + 1
End If
Next nhour
Wend
For nday = 1 To 9961
For ncol = 2 To 15
s = 0
For nhour = 0 To 23
s = s + sal(nday, nhour, ncol)
Next nhour
saldaily(nday, ncol) = s / 24
Next ncol
Next nday


For nday = 1 To 9961
For ncol = 2 To 15
Sheets("daily").Cells(nday + 1, ncol).Value = saldaily(nday, ncol)
Next ncol
Next nday


End Sub


When running the code it said:
Run time error '7'
Out of memory

Please help to over come this issue.
Thank you
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Macro runs more slowly each time it is run
2 3 4
Replies
34
Views
2K
Jehannum_2000
J
B
  • Solved
Macro to run on timer errors out on the second cycle
Replies
4
Views
196
BigShanny
B
K
  • Question Question
How to avoid a userform running in hidden mode, to be closed by other workbooks?
Replies
9
Views
266
Jaafar Tribak
Jaafar Tribak
D
  • Solved
Using VLOOKUp to find a Date in a Data Worksheet
2
Replies
13
Views
749
Alex Blakenburg
A
K
  • Solved
How to avoid a userform running in hidden mode, to be closed by other workbooks?
Replies
2
Views
105
keromero
K

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