Excel VBA优化 [英] Excel VBA Optimization

问题描述

用户要求我从他们创建的Excel书籍中运行宏，以使过程自动化.我通过cscript从Java调用宏.凭空想象，我不是VBA程序员，但是代码效率低下，并且一遍又一遍地简单地复制/粘贴.

I was asked by a user to run a macro from within an Excel book they created in order to automate a process. I call the macro from Java via cscript. I am no VBA programmer by any stretch of the imagination, but the code is inefficient and is simple copy/paste over and over.

当我从Java运行宏时，出现以下错误:

When I run the macro from Java I get the following error:

运行时错误"1004": 范围类的CopyPicture方法失败

Run-Time Error '1004': CopyPicture method of range class failed

当我进入工作簿并手动单击启动该宏的按钮时，不会发生此错误.

This error does not happen when I enter the workbook and manually click the button which launches this macro.

我一直在阅读如何使用.Activate/Selection/.Select/etc确实会使代码变慢并导致这种类型的问题.

I keep reading how using .Activate/Selection/.Select/etc can really slow down the code and cause this type of issue.

这是一个连续使用的代码块(而不是在过程/函数调用中使用).它总是在"Selection.CopyPicture"行上引发错误.

Here is a code block that is used continually (instead of within a procedure/function call). It keeps throwing the error on the "Selection.CopyPicture" line.

在优化此块方面的任何帮助将不胜感激.

Any aid in optimizing this block would be appreciated.

 ActiveSheet.ChartObjects("Chart 9").Activate
 ActiveChart.Pictures.Delete
 Range("InvGrid").Select
 Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture

 ActiveSheet.ChartObjects("Chart 9").Activate
 ActiveChart.ChartArea.Select
 ActiveChart.Paste
 Application.CutCopyMode = False
 ActiveSheet.ChartObjects("Chart 9").Activate
 ActiveChart.ChartArea.Select
 ActiveChart.Export "C:\ABC.gif", "GIF", False

通过csript从Java启动的VBS

VBS launched from Java via csript

 Dim xlsWorkbook
 Dim objExcel
 Set objExcel = CreateObject(\"Excel.Application\")
 objExcel.Application.DisplayAlerts = False
 Set xlsWorkbook = objExcel.WorkBooks.Open(\"" + xlsmFilepath + "\", 3)
 objExcel.Application.Run(xlsWorkbook.Name & \"!ExportCharts\")
 xlsWorkbook.Save
 xlsWorkbook.Close
 objExcel.Quit

推荐答案

类似这样的事情:

Dim cht As Chart
Set cht = ActiveSheet.ChartObjects("Chart 9").Chart
ActiveSheet.Range("InvGrid").CopyPicture Appearance:=xlScreen, Format:=xlPicture
With cht
    If .Pictures.Count > 0 Then .Pictures.Delete
    .Paste
    .Export "C:\ABC.gif", "GIF", False
End With

这篇关于Excel VBA优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！