内存使用量仍然很高,即使在执行VBA宏之后,PowerPoint仍然如此。

huangapple go评论61阅读模式
英文:

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.

huangapple
  • 本文由 发表于 2023年6月13日 00:13:50
  • 转载请务必保留本文链接:https://go.coder-hub.com/76458494.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定