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

norland

偶用了一个VB中的函数

set myobject=nothing

好像起了一点作用，不知能不能真的起作用

norland

我这段代码怎么设成对象，然后用set myobject=nothing?

Private Sub OkButton_Click() Application.StatusBar = "正在设置页面，请稍候" Application.ScreenUpdating = False Application.EnableEvents = True ActiveSheet.Pagesetup.LeftMargin = Application.InchesToPoints(TextBox6.Text) ActiveSheet.Pagesetup.RightMargin = Application.InchesToPoints(TextBox7.Text) ActiveSheet.Pagesetup.TopMargin = Application.InchesToPoints(TextBox5.Text) ActiveSheet.Pagesetup.BottomMargin = Application.InchesToPoints(TextBox8.Text) ActiveSheet.Pagesetup.HeaderMargin = Application.InchesToPoints(TextBox9.Text) ActiveSheet.Pagesetup.FooterMargin = Application.InchesToPoints(TextBox10.Text) If OptionButton1.Value = True Then ActiveSheet.Pagesetup.Orientation = xlPortrait Else ActiveSheet.Pagesetup.Orientation = xlLandscape End If If OptionButton3.Value = True Then If ComboBox1.Text = "10%" Then ActiveSheet.Pagesetup.Zoom = 10 ElseIf ComboBox1.Text = "20%" Then ActiveSheet.Pagesetup.Zoom = 20 ElseIf ComboBox1.Text = "30%" Then ActiveSheet.Pagesetup.Zoom = 30 ElseIf ComboBox1.Text = "40%" Then ActiveSheet.Pagesetup.Zoom = 40 ElseIf ComboBox1.Text = "50%" Then ActiveSheet.Pagesetup.Zoom = 50 ElseIf ComboBox1.Text = "60%" Then ActiveSheet.Pagesetup.Zoom = 60 ElseIf ComboBox1.Text = "70%" Then ActiveSheet.Pagesetup.Zoom = 70 ElseIf ComboBox1.Text = "80%" Then ActiveSheet.Pagesetup.Zoom = 80 ElseIf ComboBox1.Text = "90%" Then ActiveSheet.Pagesetup.Zoom = 90 ElseIf ComboBox1.Text = "100%" Then ActiveSheet.Pagesetup.Zoom = 100 ElseIf ComboBox1.Text = "150%" Then ActiveSheet.Pagesetup.Zoom = 150 ElseIf ComboBox1.Text = "200%" Then ActiveSheet.Pagesetup.Zoom = 200 ElseIf ComboBox1.Text = "300%" Then ActiveSheet.Pagesetup.Zoom = 300 ElseIf ComboBox1.Text = "400%" Then ActiveSheet.Pagesetup.Zoom = 400 End If ElseIf ComboBox2.Text = "1页宽1页高" Then ActiveSheet.Pagesetup.Zoom = False ActiveSheet.Pagesetup.FitToPagesWide = 1 ActiveSheet.Pagesetup.FitToPagesTall = 1 ElseIf ComboBox2.Text = "1页宽10页高" Then ActiveSheet.Pagesetup.Zoom = False ActiveSheet.Pagesetup.FitToPagesWide = 1 ActiveSheet.Pagesetup.FitToPagesTall = 10 ElseIf ComboBox2.Text = "1页宽100页高" Then ActiveSheet.Pagesetup.Zoom = False ActiveSheet.Pagesetup.FitToPagesWide = 1 ActiveSheet.Pagesetup.FitToPagesTall = 100 ElseIf ComboBox2.Text = "2页宽100页高" Then ActiveSheet.Pagesetup.Zoom = False ActiveSheet.Pagesetup.FitToPagesWide = 2 ActiveSheet.Pagesetup.FitToPagesTall = 100 End If If ComboBox3.Text = "Page 1" Then ActiveSheet.Pagesetup.LeftHeader = "&""Times New Roman,常规""Page &P" ElseIf ComboBox3.Text = "Page 1 of ?" Then ActiveSheet.Pagesetup.LeftHeader = "&""Times New Roman,常规""Page &P of &N" ElseIf ComboBox3.Text = ActiveSheet.Name Then ActiveSheet.Pagesetup.LeftHeader = "&""Times New Roman,常规""&A" ElseIf ComboBox3.Text = ThisWorkbook.Name Then ActiveSheet.Pagesetup.LeftHeader = "&""Times New Roman,常规""&F" Else ActiveSheet.Pagesetup.LeftHeader = ComboBox3.Text End If If ComboBox4.Text = "Page 1" Then ActiveSheet.Pagesetup.CenterHeader = "&""Times New Roman,常规""Page &P" ElseIf ComboBox4.Text = "Page 1 of ?" Then ActiveSheet.Pagesetup.CenterHeader = "&""Times New Roman,常规""Page &P of &N" ElseIf ComboBox4.Text = ActiveSheet.Name Then ActiveSheet.Pagesetup.CenterHeader = "&""Times New Roman,常规""&A" ElseIf ComboBox4.Text = ThisWorkbook.Name Then ActiveSheet.Pagesetup.CenterHeader = "&""Times New Roman,常规""&F" Else ActiveSheet.Pagesetup.CenterHeader = ComboBox4.Text End If If ComboBox5.Text = "Page 1" Then ActiveSheet.Pagesetup.RightHeader = "&""Times New Roman,常规""Page &P" ElseIf ComboBox5.Text = "Page 1 of ?" Then ActiveSheet.Pagesetup.RightHeader = "&""Times New Roman,常规""Page &P of &N" ElseIf ComboBox5.Text = ActiveSheet.Name Then ActiveSheet.Pagesetup.RightHeader = "&""Times New Roman,常规""&A" ElseIf ComboBox5.Text = ThisWorkbook.Name Then ActiveSheet.Pagesetup.RightHeader = "&""Times New Roman,常规""&F" Else ActiveSheet.Pagesetup.RightHeader = ComboBox5.Text End If If ComboBox6.Text = "Page 1" Then ActiveSheet.Pagesetup.LeftFooter = "&""Times New Roman,常规""Page &P" ElseIf ComboBox6.Text = "Page 1 of ?" Then ActiveSheet.Pagesetup.LeftFooter = "&""Times New Roman,常规""Page &P of &N" ElseIf ComboBox6.Text = ActiveSheet.Name Then ActiveSheet.Pagesetup.LeftFooter = "&""Times New Roman,常规""&A" ElseIf ComboBox6.Text = ThisWorkbook.Name Then ActiveSheet.Pagesetup.LeftFooter = "&""Times New Roman,常规""&F" Else ActiveSheet.Pagesetup.LeftFooter = ComboBox6.Text End If If ComboBox7.Text = "Page 1" Then ActiveSheet.Pagesetup.CenterFooter = "&""Times New Roman,常规""Page &P" ElseIf ComboBox7.Text = "Page 1 of ?" Then ActiveSheet.Pagesetup.CenterFooter = "&""Times New Roman,常规""Page &P of &N" ElseIf ComboBox7.Text = ActiveSheet.Name Then ActiveSheet.Pagesetup.CenterFooter = "&""Times New Roman,常规""&A" ElseIf ComboBox7.Text = ThisWorkbook.Name Then ActiveSheet.Pagesetup.CenterFooter = "&""Times New Roman,常规""&F" Else ActiveSheet.Pagesetup.CenterFooter = ComboBox7.Text End If If ComboBox8.Text = "Page 1" Then ActiveSheet.Pagesetup.RightFooter = "&""Times New Roman,常规""Page &P" ElseIf ComboBox8.Text = "Page 1 of ?" Then ActiveSheet.Pagesetup.RightFooter = "&""Times New Roman,常规""Page &P of &N" ElseIf ComboBox8.Text = ActiveSheet.Name Then ActiveSheet.Pagesetup.RightFooter = "&""Times New Roman,常规""&A" ElseIf ComboBox8.Text = ThisWorkbook.Name Then ActiveSheet.Pagesetup.RightFooter = "&""Times New Roman,常规""&F" Else ActiveSheet.Pagesetup.RightFooter = ComboBox8.Text End If ActiveSheet.Pagesetup.PrintArea = RefEdit1.Value ActiveSheet.Pagesetup.PrintTitleRows = RefEdit2.Value ActiveSheet.Pagesetup.PrintTitleColumns = RefEdit3.Value

Application.ScreenUpdating = True Application.StatusBar = False ms = MsgBox("页面设置完成，您要现在打印吗？", vbYesNo) If ms = vbNo Then Application.EnableEvents = False Exit Sub End If ActiveSheet.PrintOut Application.EnableEvents = False End Sub

norland

真的很是谢谢button, 敬礼...

norland

还有事相问，有没办法让这段代码执行的更快啊？

norland

button兄，佩服佩服

button

关键是精简代码，去除不必要的循环和判断等等。

改了一下，不一定合适，因为没有原文件也不知道是否正确。我这里也没有运行，请根据基本思路自行改善。

Private Sub OkButton_Click() Dim xlsht As Worrksheet Dim comCtl As Control Dim i As Long

With Application .StatusBar = "正在设置页面，请稍候" .ScreenUpdating = False .EnableEvents = True End With

Set xlsht = ActiveSheet With xlsht .PageSetup.LeftMargin = Application.InchesToPoints(TextBox6.Text) .PageSetup.RightMargin = Application.InchesToPoints(TextBox7.Text) .PageSetup.TopMargin = Application.InchesToPoints(TextBox5.Text) .PageSetup.BottomMargin = Application.InchesToPoints(TextBox8.Text) .PageSetup.HeaderMargin = Application.InchesToPoints(TextBox9.Text) .PageSetup.FooterMargin = Application.InchesToPoints(TextBox10.Text) End With If OptionButton1.Value = True Then xlsht.PageSetup.Orientation = xlPortrait Else xlsht.PageSetup.Orientation = xlLandscape End If If OptionButton3.Value = True Then xlsht.PageSetup.Zoom = VBA.CLng(VBA.Left(Len(ComboBox1.Text) - 1)) If ComboBox2.Text <> "" Then With xlsht .PageSetup.Zoom = False .PageSetup.FitToPagesWide = VBA.CLng(Left(ComboBox2.Text, 1)) .PageSetup.FitToPagesTall = VBA.CLng(VBA.Mid(ComboBox2.Text, 4, VBA.Len(ComboBox2.Text) - 5)) End With End If For Each comCtl In Me.Controls If VBA.Left(comCtl.Name, 5) = "Combo" And VBA.CLng(VBA.Right(comCtl.Name, 1)) >= 3 Then If comCtl.Text <> "" Then Select Case comCtl.Text Case "Page 1" xlsht.PageSetup.LeftHeader = "&""Times New Roman,常规""Page &P" Case "Page 1 of ?" xlsht.PageSetup.LeftHeader = "&""Times New Roman,常规""Page &P of &N" Case xlsht.Name xlsht.PageSetup.LeftHeader = "&""Times New Roman,常规""&A" Case ThisWorkbook.Name xlsht.PageSetup.LeftHeader = "&""Times New Roman,常规""&F" Case Else xlsht.PageSetup.LeftHeader = comCtl.Text End Select End If End If Next

With xlsht .PageSetup.PrintArea = RefEdit1.Value .PageSetup.PrintTitleRows = RefEdit2.Value .PageSetup.PrintTitleColumns = RefEdit3.Value End With

Application.ScreenUpdating = True Application.StatusBar = False ms = MsgBox("页面设置完成，您要现在打印吗？", vbYesNo) If ms = vbNo Then Application.EnableEvents = False Exit Sub End If ActiveSheet.PrintOut Application.EnableEvents = True End Sub

[此贴子已经被作者于2005-1-3 13:16:14编辑过]

button

大家习惯了用EXCEL录制宏的方式编程，但是最好不要使用这种方式。

可以声明一个变量如

dim xlsht as worksheet

set xlsht=activesheet

...

'最后释放

set xlsht=nothing

'就可以了。

上述代码中的activesheet用声明的变量xlsht替换即可。

button

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

Emily

簡單來說，內存多對一個極大的檔案沒有幫助，你的 swap file 大小是自動還是自定？

不過你用 P4 2G 只得 256 M 是否過少呢？內存不太貴吧，至小也用 512 M 啊！

[此贴子已经被作者于2004-5-10 13:33:56编辑过]

Emily

Excel and memory (English)

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