How to avoid running out of memory while running VBA

[yellow_orange]

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.

 

[SamTYler]

Rewrite the macro so it doesn't waste memory.






















We can help you if you will share it with us.

 

[yellow_orange]

Rewrite the macro so it doesn't waste memory.
We can help you if you will share it with us.

I agree with you. I'm still a newbie in writing macros with userforms. Do you have some tips on what things to avoid, best practices, etc.

 

[SamTYler]

Share the code

In general

'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

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.

'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

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

 

[muoicham]

Dear all,
I also have the problem with the " out of memory". The data include about 240000 rows and 55 columns.

Time (hr)	KPCham	M348000U	PrekDam	NekLuong	sp40d	sp35d	sp37d	TBAS5	TBAS24	TLT82	KohKhel	B332000	B272000	TRAB35	2521	TLT122	2525	274	TAFP4
0	2.209	1.326	1.416	0.922	2.123	2.512	0.635	3.151	0.501	0.997	1.156	1.312	0.776	1.162	0.86	1.025	0.941	0.903	0.486
1	2.227	1.317	1.407	1.051	2.123	2.522	0.634	3.151	0.501	1.007	1.165	1.3	0.794	1.161	0.998	1.079	0.926	0.894	0.604
2	2.243	1.321	1.4	1.187	2.122	2.532	0.644	3.151	0.501	1.011	1.166	1.297	0.893	1.216	1.083	1.19	0.935	0.911	0.727
3	2.258	1.349	1.401	1.224	2.122	2.541	0.659	3.151	0.501	1.029	1.164	1.309	0.967	1.308	1.127	1.273	0.975	0.955	0.82
4	2.27	1.393	1.415	1.168	2.122	2.551	0.677	3.151	0.501	1.048	1.17	1.342	1.023	1.347	1.093	1.275	1.034	1.014	0.829
5	2.282	1.418	1.441	1.096	2.122	2.56	0.694	3.151	0.501	1.065	1.184	1.376	1.005	1.324	1.033	1.22	1.076	1.05	0.723
6	2.296	1.42	1.461	1.036	2.122	2.57	0.71	3.151	0.501	1.08	1.202	1.392	0.98	1.275	0.962	1.16	1.082	1.052	0.61
7	2.312	1.413	1.464	0.991	2.121	2.579	0.72	3.151	0.501	1.092	1.216	1.391	0.951	1.225	0.896	1.114	1.065	1.036	0.538
8	2.33	1.401	1.459	0.959	2.121	2.588	0.723	3.151	0.501	1.099	1.228	1.383	0.922	1.181	0.839	1.079	1.038	1.01	0.487
9	2.347	1.389	1.449	0.931	2.121	2.597	0.718	3.151	0.501	1.102	1.236	1.374	0.896	1.143	0.785	1.05	1.007	0.98	0.443
10	2.362	1.378	1.44	0.901	2.121	2.606	0.709	3.151	0.501	1.099	1.239	1.365	0.874	1.108	0.732	1.022	0.975	0.947	0.402
11	2.375	1.369	1.432	0.868	2.12	2.615	0.697	3.151	0.501	1.101	1.239	1.356	0.855	1.073	0.681	0.991	0.942	0.913	0.36

<tbody>
</tbody>

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

 

[StephenCrump]

Reposted: http://www.mrexcel.com/forum/excel-questions/795519-out-memory-visual-basic-applications-excel.html