【请教】excel中用vba如何释放内存？

cofe2000

以下是引用taller在2004-5-10 10:49:00的发言：

楼主为什么一定要在执行过程中释放内存呢

再问taller版主：

可以实现在执行过程中释放内存吗？

我的问题还是没有解决啊

goots

用楼主的代码，在我的电脑C566+128M，空白的工作表可复制556张。 如果楼主的表格很复杂，可复制的表数可能会少很多。

taller

没有研究过,无法给出答案,抱歉

cofe2000

以下是引用Emily在2004-5-10 12:27:00的发言：

Excel and memory (English)

http://www.decisionmodels.com/memlimits.htm

看不懂啊，麻烦哪位老大给简单的讲一下

Windows Memory and RAM

Your Windows PC has various kinds of memory: physical memory (RAM), Windows virtual memory, Excel useable memory etc.

Ultimately Windows virtual memory is limited by the size of your swap file on your hard disk. However hard disks are very slow compared to physical memory (RAM), so Windows tries to maintain the things it needs to use in RAM and swaps stuff it doesn’t need right now to the Windows swapfile on disk.

The Excel program itself usually occupies between 8MB and 15 MB of Windows virtual memory. In addition Excel uses additional Windows virtual memory to store workbook formulae and data etc.

Excel uses its own pools of memory (Excel useable memory) for this workbook data. Unfortunately these memory pools are not in general as large as the Windows memory, and may well be a lot less than the RAM on your PC. Excel 95, Excel 97 and Excel 2000 have a limit for workbook formula memory of about 80 MB, and Excel 2002 has a limit of about 160MB. Excel 2003 has a significantly larger memory capacity which may be the same as Windows virtual memory. See Memory Limits for details.

How much RAM do you need?

Paging to your virtual memory swap-file is extremely slow. You need enough physical RAM for the operating system, the Excel program and your workbook(s), and RAM is not expensive.

• If you have more than very occasional hard disk activity during calculation, you need more RAM.

For Windows 95/98 you need about 24MB for the operating system and 8-24MB for Excel s

• 32 MB RAM is OK for small workbooks.
• 64 MB RAM is OK unless you need to open more than about 32MB of workbooks.
• 128 MB RAM works well except when using workbooks containing large amounts of data.
• You will continue to see small speed improvements with 256MB RAM when using large workbooks, particularly with Excel 2002.

For Windows ME and Windows NT you need about 32MB for the operating system and 8-24MB for Excel s

• 48 MB RAM is OK for small workbooks.
• 72 MB RAM is OK unless you need to open more than about 32MB of workbooks.
• 128 MB RAM is OK except when using workbooks containing large amounts of data.
• You will continue to see small speed improvements with 256MB RAM when using large workbooks, particularly with Excel 2002.

For Windows 2000 you need about 64MB for the operating system and 8-24MB for Excel s

• 72 MB RAM is OK for small workbooks.
• 128 MB RAM is OK unless you need to open more than about 32MB of workbooks.
• 196 MB RAM is OK except when using workbooks containing large amounts of data.
• You will continue to see small speed improvements with 256MB RAM when using large workbooks, particularly with Excel 2002.

For Windows XP you need about 128MB for the operating system and 8-24MB for Excel s

• 128 MB RAM is OK for small workbooks.
• 196 MB RAM is OK unless you need to open more than about 32MB of workbooks.
• 256 MB RAM works well except when using workbooks containing large amounts of data.
• You will continue to see small speed improvements with 512MB RAM when using large workbooks, particularly with Excel 2002.
• Using Excel 2003 with very large workbooks you can make effective use of at least 1GB of RAM.

How large a Swapfile do you need?

Usually you can let Windows automatically control the size of your swapfile, but if the hard disk containing your Windows system (usually C drive) has very little space you may need to manually control the size and location of your swapfile using the Windows Control Panel (System Properties, Advanced Tab, Performance Settings, Memory Useage). If you work with large workbooks your swapfile should be at least 256MB and preferably 512MB to 1024MB.

You can reduce the amount of swapfile being used by closing programs which are loaded but not in use, and by reducing the number of programs that are loaded at startup.

cofe2000

是自动的

老刀

以下是引用cofe2000在2004-5-10 12:37:00的发言：

我的问题还是没有解决啊

看看 添加空表--copy有数据的单元格(不是表）--粘贴

[此贴子已经被作者于2004-5-10 14:44:59编辑过]

cofe2000

一个不错的变通办法，但是代码怎么写啊

我全选工作表后，用填充的方法到是可行，见录制的代码，晕啊，不知怎么改啊

Sub 宏2() ' ' 宏2 Macro ' cofe 记录的宏 2004-5-10 '

' Sheets("Sheet1").Select ActiveWindow.ScrollWorkbookTabs Position:=xlLast Sheets(Array("Sheet1", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", _ "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24")).Select Sheets("Sheet1").Activate Sheets(Array("25", "26", "27", "28", "29", "30", "31", "32", "33", "34", "35", "36", "37", _ "38", "39", "40", "41", "42", "43", "44", "45", "46", "47", "48", "49")).Select Replace _ :=False Sheets(Array("50", "51", "52", "53", "54", "55", "56", "57", "58", "59", "60", "61", "62", _ "63", "64", "65", "66", "67", "68", "69", "70", "71", "72", "73", "74")).Select Replace _ :=False Sheets(Array("75", "76", "77", "78", "79", "80", "81", "82", "83", "84", "85", "86", "87", _ "88", "89", "90", "91", "92", "93", "94", "95", "96", "97", "98", "99")).Select Replace _ :=False Sheets(Array("100", "101", "102", "103", "104", "105", "106", "107", "108", "109", "110", _ "111", "112", "113", "114", "115", "116", "117", "118", "119", "120", "121", "122", "123", _ "124")).Select Replace:=False Sheets(Array("125", "126", "127", "128", "129", "130", "131", "132", "133", "134", "135", _ "136", "137", "138", "139", "140", "141", "142", "143", "144", "145", "146", "147", "148", _ "149")).Select Replace:=False Sheets(Array("150", "151", "152", "153", "154", "155", "156", "157", "158", "159", "160", _ "161", "162", "163", "164", "165", "166", "167", "168", "169", "170", "171", "172", "173", _ "174")).Select Replace:=False Sheets(Array("175", "176", "177", "178", "179", "180", "181", "182", "183", "184", "185", _ "186", "187", "188", "189", "190", "191", "192", "193", "194", "195", "196", "197", "198", _ "199")).Select Replace:=False Sheets(Array("200", "201", "202")).Select Replace:=False ActiveWindow.ScrollWorkbookTabs Position:=xlFirst Sheets(Array("Sheet1", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", _ "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24")).Select Sheets("Sheet1").Activate Sheets(Array("25", "26", "27", "28", "29", "30", "31", "32", "33", "34", "35", "36", "37", _ "38", "39", "40", "41", "42", "43", "44", "45", "46", "47", "48", "49")).Select Replace _ :=False Sheets(Array("50", "51", "52", "53", "54", "55", "56", "57", "58", "59", "60", "61", "62", _ "63", "64", "65", "66", "67", "68", "69", "70", "71", "72", "73", "74")).Select Replace _ :=False Sheets(Array("75", "76", "77", "78", "79", "80", "81", "82", "83", "84", "85", "86", "87", _ "88", "89", "90", "91", "92", "93", "94", "95", "96", "97", "98", "99")).Select Replace _ :=False Sheets(Array("100", "101", "102", "103", "104", "105", "106", "107", "108", "109", "110", _ "111", "112", "113", "114", "115", "116", "117", "118", "119", "120", "121", "122", "123", _ "124")).Select Replace:=False Sheets(Array("125", "126", "127", "128", "129", "130", "131", "132", "133", "134", "135", _ "136", "137", "138", "139", "140", "141", "142", "143", "144", "145", "146", "147", "148", _ "149")).Select Replace:=False Sheets(Array("150", "151", "152", "153", "154", "155", "156", "157", "158", "159", "160", _ "161", "162", "163", "164", "165", "166", "167", "168", "169", "170", "171", "172", "173", _ "174")).Select Replace:=False Sheets(Array("175", "176", "177", "178", "179", "180", "181", "182", "183", "184", "185", _ "186", "187", "188", "189", "190", "191", "192", "193", "194", "195", "196", "197", "198", _ "199")).Select Replace:=False Sheets(Array("200", "201", "202")).Select Replace:=False Cells.Select ActiveWindow.SelectedSheets.FillAcrossSheets Range:=Selection, Type:=xlAll End Sub

老刀

Sub 宏1() Dim num As Integer, i As Integer Dim arrSht() As Variant num = InputBox("", , 200) Application.ScreenUpdating = False ReDim arrSht(1 To num + 1) arrSht(num + 1) = Sheets("aaa").Name For i = 1 To num Worksheets.Add.Move after:=Worksheets(Worksheets.Count) ActiveSheet.Name = CStr(i) arrSht(i) = CStr(i) Next i Sheets(arrSht).FillAcrossSheets Range:=Sheets("aaa").UsedRange Application.ScreenUpdating = True End Sub

cofe2000

以下是引用老刀在2004-5-10 19:18:00的发言： Sub 宏1() Dim num As Integer, i As Integer Dim arrSht() As Variant num = InputBox("", , 200) Application.ScreenUpdating = False ReDim arrSht(1 To num + 1) arrSht(num + 1) = Sheets("aaa").Name For i = 1 To num Worksheets.Add.Move after:=Worksheets(Worksheets.Count) ActiveSheet.Name = CStr(i) arrSht(i) = CStr(i) Next i Sheets(arrSht).FillAcrossSheets Range:=Sheets("aaa").UsedRange Application.ScreenUpdating = True End Sub

真是太感谢了，老刀出手就是不一样哦，代码执行速度快的是一塌糊涂，呵呵

norland

倒底有没办法在vba中释放内存呢?如有的vba程式，在执行几次后就显示内存不足。

可不可以在执行一次即释放一次内存呢？