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屋!