英文:
Memory usage still high for Powerpoint even after VBA Macro is executed
问题
I have created some VBA tools in Powerpoint to help save time while working on data charts. One of the tools sorts the data in the chart on each selected slide from highest to lowest.
当我在多个幻灯片上同时运行此代码时,VBA的内存使用量显着增加(根据图表数量不同,增加2倍至3倍以上)。然而,一旦宏运行完成,Powerpoint的内存分配就不会下降,除非我退出程序,通常会导致计算机完全无法使用。
Any help on how I can prevent this would be greatly appreciated!
我如何防止这种情况发生将不胜感激!
Here is the code in question:
以下是相关的代码:
英文:
I have created some VBA tools in Powerpoint to help save time while working on data charts. One of the tools sorts the data in the chart on each selected slide from highest to lowest.
When I run this code on multiple slides at once, the memory usage for vba goes up significantly (2x to 3x or more depending on the number of charts). However, once the macro is finished running, the memory allocation for Powerpoint never goes back down until I quit the program, often making the computer completely unusable.
Any help on how I can prevent this would be greatly appreciated!
Here is the code in question:
Sub Sortchart()
Dim shp As PowerPoint.Shape
Dim sld As Slide
Dim mychart As PowerPoint.Chart
Dim lastRow As Long
Dim LastCol As Long
For Each sld In ActiveWindow.Selection.SlideRange
For Each shp In sld.Shapes
If shp.HasChart Then
Set mychart = shp.Chart
With mychart
Set mysheet = .ChartData.Workbook.Sheets(1)
With mysheet
lastRow = mysheet.usedrange.Rows.Count
LastCol = mysheet.usedrange.Columns.Count + 1
If LastCol > 3 Then
MsgBox "This chart is not sortable. Please do it manually."
Else
.Range(.Cells(1, LastCol - 1), .Cells(lastRow, LastCol)).sort Key1:=.Range("C1"), Order1:=1, Header:=xlyes
End If
End With
End With
End If
Next shp
Next sld
Set shp = Nothing
Set sld = Nothing
Set mychart = Nothing
End Sub
答案1
得分: 2
我最近遇到了类似的问题,似乎在访问 PowerPoint 中的 myShape.Chart.ChartData.Workbook
属性后,默认情况下会加载并保留 Workbook 在内存中。
为了防止不必要的内存使用,可以在代码中不再访问特定的 Workbook 时,通过调用 myShape.Chart.ChartData.Workbook.Application.Quit
来关闭并从内存中卸载 Workbook。
英文:
I encountered a similar problem recently, and it seems that after accessing the
myShape.Chart.ChartData.Workbook
property in PowerPoint, the Workbook is loaded and kept in memory by default.
To prevent unnecessary memory usage, the Workbook can be closed and unloaded from memory by calling
myShape.Chart.ChartData.Workbook.Application.Quit
when the specific Workbook is not accessed anymore in the code.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论