<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta charset="utf-8">
<meta name="generator" content="pdf2htmlEX">
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
<link rel="stylesheet" href="https://static.pudn.com/base/css/base.min.css">
<link rel="stylesheet" href="https://static.pudn.com/base/css/fancy.min.css">
<link rel="stylesheet" href="https://static.pudn.com/prod/directory_preview_static/6270f2a8d973ef42a4494be2/raw.css">
<script src="https://static.pudn.com/base/js/compatibility.min.js"></script>
<script src="https://static.pudn.com/base/js/pdf2htmlEX.min.js"></script>
<script>
try{
pdf2htmlEX.defaultViewer = new pdf2htmlEX.Viewer({});
}catch(e){}
</script>
<title></title>
</head>
<body>
<div id="sidebar" style="display: none">
<div id="outline">
</div>
</div>
<div id="pf1" class="pf w0 h0" data-page-no="1"><div class="pc pc1 w0 h0"><img class="bi x0 y0 w1 h1" alt="" src="https://static.pudn.com/prod/directory_preview_static/6270f2a8d973ef42a4494be2/bg1.jpg"><div class="c x0 y1 w2 h2"><div class="t m0 x1 h3 y2 ff1 fs0 fc0 sc0 ls0 ws0">很多<span class="_ _0"> </span><span class="ff2">Excel VBA<span class="_ _0"> </span></span>文章和图书都介绍过如何优化<span class="_ _0"> </span><span class="ff2">VBA<span class="_ _0"> </span></span>代码，使代码运行得更快。下面搜</div><div class="t m0 x2 h3 y3 ff1 fs0 fc0 sc0 ls0 ws0">集了一些使<span class="_ _0"> </span><span class="ff2">Excel VBA<span class="_ _0"> </span></span>代码运行更快的技术和技巧，基本上都是实践经验的总结。如果您</div><div class="t m0 x2 h3 y4 ff1 fs0 fc0 sc0 ls0 ws0">还有其它优化<span class="_ _0"> </span><span class="ff2">Excel VBA<span class="_ _0"> </span></span>代码的方法，可以在本文后留言或给出链接，与大家分享。</div><div class="t m0 x2 h3 y5 ff1 fs0 fc1 sc1 ls0 ws0">对于应用程序属性，在代码运行时关闭除必需属性以外的其它所有属性</div><div class="t m0 x2 h3 y6 ff1 fs0 fc0 sc0 ls0 ws0">在代码运行时关闭不需要的<span class="_ _0"> </span><span class="ff2">Excel<span class="_ _0"> </span></span>功能。其原因是，如果通过<span class="_ _0"> </span><span class="ff2">VBA<span class="_ _0"> </span></span>更新不同的单元格区域，</div><div class="t m0 x2 h3 y7 ff1 fs0 fc0 sc0 ls0 ws0">或者从不同的单元格区域复制<span class="ff2"> /</span>粘贴来创建汇总表，则不希望<span class="_ _0"> </span><span class="ff2">Excel<span class="_ _0"> </span></span>浪费时间和资源来重新</div><div class="t m0 x2 h3 y8 ff1 fs0 fc0 sc0 ls0 ws0">计算公式、显示粘贴进度或者重绘网格，尤其在每次单独的操作后（更有甚者，如果代码</div><div class="t m0 x2 h3 y9 ff1 fs0 fc0 sc0 ls0 ws0">使用了循环，则每次单独操作后<span class="_ _0"> </span><span class="ff2">Excel<span class="_ _0"> </span></span>都会在后台运行这些功能）。只需要在代码执行结</div><div class="t m0 x2 h3 ya ff1 fs0 fc0 sc0 ls0 ws0">束时进行一次重新计算和重绘就足以使工作簿更新。</div><div class="t m0 x2 h3 yb ff1 fs0 fc0 sc0 ls0 ws0">下面的代码将帮助您提高代码的执行速度。</div><div class="t m0 x2 h3 yc ff1 fs0 fc0 sc0 ls0 ws0">（<span class="ff2">1</span>）放置在主代码前的一段代码，获取<span class="_ _0"> </span><span class="ff2">Excel<span class="_ _0"> </span></span>当前的属性状态，然后将其关闭</div><div class="t m0 x1 h4 yd ff3 fs1 fc2 sc0 ls0 ws0"><span class="ff1">获得当前的<span class="_ _1"> </span></span><span class="_ _1"> </span><span class="ff1">设置状态</span><span class="ff1">将其放置在代码的开头</span></div><div class="t m0 x1 h5 ye ff3 fs1 fc0 sc0 ls0 ws0"></div><div class="t m0 x1 h5 yf ff3 fs1 fc0 sc0 ls0 ws0"></div><div class="t m0 x1 h5 y10 ff3 fs1 fc0 sc0 ls0 ws0"></div><div class="t m0 x1 h5 y11 ff3 fs1 fc0 sc0 ls0 ws0"></div><div class="t m0 x1 h5 y12 ff3 fs1 fc0 sc0 ls0 ws0"> ! </div><div class="t m0 x2 h4 y13 ff3 fs1 fc2 sc0 ls0 ws0"><span class="ff1">注</span>"<span class="ff1">这是工作表级的设置</span></div><div class="t m0 x1 h4 y14 ff3 fs1 fc2 sc0 ls0 ws0"><span class="ff1">关闭一些<span class="_ _1"> </span></span><span class="_ _1"> </span><span class="ff1">功能使代码运行更快</span></div><div class="t m0 x1 h5 y15 ff3 fs1 fc0 sc0 ls0 ws0"><span class="fc3">#</span></div><div class="t m0 x1 h5 y16 ff3 fs1 fc0 sc0 ls0 ws0"><span class="fc3">#</span></div><div class="t m0 x1 h5 y17 ff3 fs1 fc0 sc0 ls0 ws0">$</div><div class="t m0 x1 h5 y18 ff3 fs1 fc0 sc0 ls0 ws0"><span class="fc3">#</span></div><div class="t m0 x1 h4 y19 ff3 fs1 fc0 sc0 ls0 ws0">! <span class="fc3">#</span><span class="ff1">注</span>"<span class="ff1">这是工作表级的</span></div><div class="t m0 x2 h4 y1a ff1 fs1 fc0 sc0 ls0 ws0">设置</div><div class="t m0 x1 h3 y1b ff1 fs0 fc0 sc0 ls0 ws0">（<span class="ff2">2</span>）放置在主代码结束后的一段代码，用来将<span class="_ _0"> </span><span class="ff2">Excel<span class="_ _0"> </span></span>恢复到代码运行前的设置</div><div class="t m0 x1 h4 y1c ff3 fs1 fc2 sc0 ls0 ws0"><span class="ff1">代码运行后</span><span class="ff1">恢复<span class="_ _1"> </span></span><span class="_ _1"> </span><span class="ff1">原来的状态</span>%<span class="ff1">将下面的代码放在代码的末尾</span></div><div class="t m0 x1 h5 y1d ff3 fs1 fc0 sc0 ls0 ws0"></div><div class="t m0 x1 h5 y1e ff3 fs1 fc0 sc0 ls0 ws0"></div></div></div><div class="pi" data-data='{"ctm":[1.611850,0.000000,0.000000,1.611850,0.000000,0.000000]}'></div></div>
</body>
</html>
<div id="pf2" class="pf w0 h0" data-page-no="2"><div class="pc pc2 w0 h0"><img class="bi x0 y0 w1 h1" alt="" src="https://static.pudn.com/prod/directory_preview_static/6270f2a8d973ef42a4494be2/bg2.jpg"><div class="c x0 y1 w2 h2"><div class="t m0 x1 h5 y1f ff3 fs1 fc0 sc0 ls0 ws0"></div><div class="t m0 x1 h5 y20 ff3 fs1 fc0 sc0 ls0 ws0"></div><div class="t m0 x1 h5 y21 ff3 fs1 fc0 sc0 ls0 ws0">!  </div><div class="t m0 x2 h4 y22 ff3 fs1 fc0 sc0 ls0 ws0"><span class="ff1">注</span>"<span class="ff1">这是工作表级的设置</span></div><div class="t m0 x1 h3 y23 ff1 fs0 fc0 sc0 ls0 ws0">下面简要解释这些设置：</div><div class="t m0 x2 h3 y24 ff2 fs0 fc1 sc0 ls0 ws0">Application.ScreenUpdating<span class="ff1 fc0">：将该属性设置为<span class="_ _0"> </span><span class="ff2">False</span>，告诉<span class="_ _0"> </span><span class="ff2">Excel<span class="_ _0"> </span></span>不要重绘屏幕。其优点</span></div><div class="t m0 x2 h3 y25 ff1 fs0 fc0 sc0 ls0 ws0">是不需要<span class="_ _0"> </span><span class="ff2">Excel<span class="_ _0"> </span></span>花费资源来绘制屏幕，因而其改变会更快而不致让用户察觉其变化。因为</div><div class="t m0 x2 h3 y26 ff1 fs0 fc0 sc0 ls0 ws0">如此频繁地绘制屏幕需要大量的资源，所以关闭绘制屏幕直到代码执行结束。在代码结束</div><div class="t m0 x2 h3 y27 ff1 fs0 fc0 sc0 ls0 ws0">前，确保重新开启了该属性。</div><div class="t m0 x2 h3 y28 ff2 fs0 fc1 sc0 ls0 ws0">Application.DisplayStatusBar<span class="ff1 fc0">：将该属性设置为<span class="_ _0"> </span><span class="ff2">False</span>，告诉<span class="_ _0"> </span><span class="ff2">Excel<span class="_ _1"> </span></span>停止显示状态栏。例如，</span></div><div class="t m0 x2 h3 y29 ff1 fs0 fc0 sc0 ls0 ws0">如果使用<span class="_ _0"> </span><span class="ff2">VBA<span class="_ _0"> </span></span>复制<span class="ff2">/</span>粘贴单元格，当粘贴执行时<span class="_ _0"> </span><span class="ff2">Excel<span class="_ _0"> </span></span>将在状态栏中显示操作的进度。关闭</div><div class="t m0 x2 h3 y2a ff1 fs0 fc0 sc0 ls0 ws0">屏幕更新不会关闭状态栏显示，因此，如果需要的话，可以禁用屏幕更新而仍然可以通过</div><div class="t m0 x2 h3 y2b ff1 fs0 fc0 sc0 ls0 ws0">状态栏给用户提供反馈。记住，如果将该属性设置为<span class="_ _0"> </span><span class="ff2">False</span>，在代码结束前应该将其设置</div><div class="t m0 x2 h3 y2c ff1 fs0 fc0 sc0 ls0 ws0">为<span class="_ _0"> </span><span class="ff2">True</span>。</div><div class="t m0 x2 h3 y2d ff2 fs0 fc1 sc0 ls0 ws0">Application.Calculation<span class="ff1 fc0">：该属性允许编程设置<span class="_ _0"> </span><span class="ff2">Excel<span class="_ _0"> </span></span>的计算模式。“手工的”</span></div><div class="t m0 x2 h3 y2e ff1 fs0 fc0 sc0 ls0 ws0">（<span class="ff2">xlCalculationManual</span>）模式意味着<span class="_ _0"> </span><span class="ff2">Excel<span class="_ _0"> </span></span>等待用户（或代码）来触发计算；默认为“自动</div><div class="t m0 x2 h3 y2f ff1 fs0 fc0 sc0 ls0 ws0">的”（<span class="ff2">xlCalculationAutomatic</span>）模式，意味着由<span class="_ _0"> </span><span class="ff2">Excel<span class="_ _0"> </span></span>来决定何时重新计算工作簿（例如，</div><div class="t m0 x2 h3 y30 ff1 fs0 fc0 sc0 ls0 ws0">当在工作表中输入新公式时）。由于重新计算工作簿将花费时间且浪费资源，因此可能不</div><div class="t m0 x2 h3 y31 ff1 fs0 fc0 sc0 ls0 ws0">希望每次改变单元格值时<span class="_ _0"> </span><span class="ff2">Excel<span class="_ _0"> </span></span>都触发重新计算。当代码执行时关闭重新计算，在代码结</div><div class="t m0 x2 h3 y32 ff1 fs0 fc0 sc0 ls0 ws0">束前再设置回重新计算模式。</div><div class="t m0 x2 h3 y33 ff2 fs0 fc1 sc0 ls0 ws0">Application.EnableEvents<span class="ff1 fc0">：将该属性设置为<span class="_ _0"> </span><span class="ff2">False</span>，告诉<span class="_ _0"> </span><span class="ff2">Excel<span class="_ _0"> </span></span>不要触发事件。你可能不</span></div><div class="t m0 x2 h3 y34 ff1 fs0 fc0 sc0 ls0 ws0">希望<span class="_ _0"> </span><span class="ff2">Excel<span class="_ _0"> </span></span>为每个正在通过代码发生改变的单元格触发事件，关闭事件将加速<span class="_ _0"> </span><span class="ff2">VBA<span class="_ _0"> </span></span>代码的</div><div class="t m0 x2 h3 y35 ff1 fs0 fc0 sc0 ls0 ws0">执行。</div><div class="t m0 x2 h3 y36 ff2 fs0 fc1 sc0 ls0 ws0">ActiveSheet.DisplayPageBreaks<span class="ff1 fc0">：当在<span class="ff4">较</span>新<span class="ff4">版</span>本的<span class="_ _0"> </span><span class="ff2">Excel<span class="_ _0"> </span></span>中运行<span class="_ _0"> </span><span class="ff2">VBA<span class="_ _0"> </span></span>时，则可能<span class="ff4">比</span>在<span class="ff4">早</span></span></div><div class="t m0 x2 h3 y37 ff4 fs0 fc0 sc0 ls0 ws0">期版<span class="ff1">本的<span class="_ _0"> </span><span class="ff2">Excel<span class="_ _0"> </span></span>中需要更</span>长<span class="ff1">的时间</span>完成<span class="ff1">。例如，需要</span>几秒钟<span class="ff1">在</span>早期版<span class="ff1">本的<span class="_ _0"> </span><span class="ff2">Excel<span class="_ _0"> </span></span>中</span>完成<span class="ff1">的</span></div><div class="t m0 x2 h3 y38 ff4 fs0 fc0 sc0 ls0 ws0">宏<span class="ff1">可能需要</span>几<span class="ff1">分</span>钟才<span class="ff1">能在更高</span>版<span class="ff1">本的<span class="_ _0"> </span><span class="ff2">Excel<span class="_ _0"> </span></span>中</span>完成<span class="ff1">。或者，</span>第二<span class="ff1">次运行一个</span>宏<span class="ff1">可能</span>比第<span class="ff1">一</span></div><div class="t m0 x2 h3 y39 ff1 fs0 fc0 sc0 ls0 ws0">次运行需要的时间更<span class="ff4">长</span>。这是由于<span class="_ _0"> </span><span class="ff2">VBA<span class="_ _0"> </span><span class="ff4">宏修</span></span>改了多行或<span class="ff4">列</span>的属性，或者必<span class="ff4">须强</span>制执行计算</div><div class="t m0 x2 h3 y3a ff2 fs0 fc0 sc0 ls0 ws0">Excel<span class="_ _0"> </span><span class="ff1">分<span class="ff4">页符</span>。如果<span class="ff4">宏</span>设置了<span class="ff4">任</span>何<span class="_ _0"> </span></span>PageSetup<span class="_ _0"> </span><span class="ff1">属性或者手动设置了<span class="_ _0"> </span></span>PageSetup<span class="_ _0"> </span><span class="ff1">属性，接着</span></div><div class="t m0 x2 h3 y3b ff1 fs0 fc0 sc0 ls0 ws0">运行<span class="ff4">较</span>大区域的行或<span class="ff4">列</span>属性设置时会出<span class="ff4">现</span>这<span class="ff4">样</span>的<span class="ff4">问题</span>。您可以将该属性设置为<span class="_ _0"> </span><span class="ff2">False<span class="_ _0"> </span></span>来提</div><div class="t m0 x2 h3 y3c ff1 fs0 fc0 sc0 ls0 ws0">高代码的运行速度。当然，在代码运行结束前，应将该属性恢复为原设置。</div><div class="t m0 x1 h3 y3d ff1 fs0 fc1 sc1 ls0 ws0">在单个操作中<span class="ff4">读<span class="ff5 sc0">/</span>写</span>大<span class="ff4">块</span>的单元格区域</div><div class="t m0 x2 h3 y3e ff1 fs0 fc0 sc0 ls0 ws0">本技巧用于优化在<span class="_ _0"> </span><span class="ff2">Excel<span class="_ _0"> </span></span>和代码<span class="ff4">之</span>间<span class="ff4">转换数据</span>的次<span class="ff4">数</span>。使用<span class="ff4">数组</span>变量<span class="ff4">存储</span>所需要的值<span class="ff4">并</span>执</div><div class="t m0 x2 h3 y3f ff1 fs0 fc0 sc0 ls0 ws0">行取值或<span class="ff4">赋</span>值操作，而不是一次<span class="ff4">遍历</span>单个单元格<span class="ff4">并</span>获取或设置单个值。</div><div class="t m0 x2 h3 y40 ff1 fs0 fc0 sc0 ls0 ws0">例如，下面的代码在单元格区域<span class="_ _0"> </span><span class="ff2">A1</span>：<span class="ff2">C10000<span class="_ _0"> </span></span>中放置<span class="ff4">随机数</span>。</div><div class="t m0 x2 h3 y41 ff1 fs0 fc0 sc0 ls0 ws0">代码段一：运行速度<span class="ff4">较慢</span>的代码</div><div class="t m0 x1 h5 y42 ff3 fs1 fc3 sc0 ls0 ws0"><span class="fc0">&'(</span></div><div class="t m0 x1 h5 y43 ff3 fs1 fc3 sc0 ls0 ws0">)<span class="fc0">*</span><span class="fc0">*</span></div></div></div><div class="pi" data-data='{"ctm":[1.611850,0.000000,0.000000,1.611850,0.000000,0.000000]}'></div></div>
<div id="pf3" class="pf w0 h0" data-page-no="3"><div class="pc pc3 w0 h0"><img class="bi x0 y0 w1 h1" alt="" src="https://static.pudn.com/prod/directory_preview_static/6270f2a8d973ef42a4494be2/bg3.jpg"><div class="c x0 y1 w2 h2"><div class="t m0 x1 h5 y1f ff3 fs1 fc3 sc0 ls0 ws0">)<span class="fc0">+&</span><span class="fc0"></span>,</div><div class="t m0 x1 h5 y20 ff3 fs1 fc3 sc0 ls0 ws0">)<span class="fc0">+</span><span class="fc0"></span>+</div><div class="t m0 x1 h5 y21 ff3 fs1 fc3 sc0 ls0 ws0">)<span class="fc0">$-</span><span class="fc0"></span></div><div class="t m0 x1 h5 y44 ff3 fs1 fc3 sc0 ls0 ws0"><span class="fc0">**'<span class="fc4">./"/0000.</span>(</span></div><div class="t m0 x1 h5 y45 ff3 fs1 fc0 sc0 ls0 ws0">1</div><div class="t m0 x1 h5 y46 ff3 fs1 fc3 sc0 ls0 ws0">#<span class="fc0">+&/</span>2<span class="fc0">/0000</span></div><div class="t m0 x1 h5 ye ff3 fs1 fc3 sc0 ls0 ws0">#<span class="fc0">+/</span>2<span class="fc0">3</span></div><div class="t m0 x1 h4 y47 ff3 fs1 fc0 sc0 ls0 ws0">$-*'+&+(<span class="fc2"><span class="ff1">从<span class="_ _1"> </span></span><span class="_ _1"> </span><span class="ff1">单元格中<span class="ff4">读</span>取值<span class="_ _1"> </span></span>304</span></div><div class="t m0 x2 h4 y48 ff1 fs1 fc2 sc0 ls0 ws0">次</div><div class="t m0 x1 h5 y49 ff3 fs1 fc3 sc0 ls0 ws0">+5<span class="fc0">$-60</span>2!</div><div class="t m0 x1 h4 y4a ff3 fs1 fc0 sc0 ls0 ws0">$-$-7$-<span class="fc2"><span class="ff1">改变值</span></span></div><div class="t m0 x1 h4 y13 ff3 fs1 fc0 sc0 ls0 ws0">*'+&+($-<span class="fc2"><span class="ff1">将值<span class="ff4">写</span>入<span class="_ _1"> </span></span><span class="_ _1"> </span><span class="ff1">单元格中</span></span></div><div class="t m0 x2 h4 y4b ff3 fs1 fc2 sc0 ls0 ws0">30000<span class="_ _1"> </span><span class="ff1">次</span></div><div class="t m0 x1 h5 y4c ff3 fs1 fc3 sc0 ls0 ws0"><span class="fc0"></span>+5</div><div class="t m0 x1 h5 y4d ff3 fs1 fc3 sc0 ls0 ws0">8<span class="fc0">+</span></div><div class="t m0 x1 h5 y4e ff3 fs1 fc3 sc0 ls0 ws0">8<span class="fc0">+&</span></div><div class="t m0 x1 h5 y4f ff3 fs1 fc3 sc0 ls0 ws0"><span class="fc0"></span></div><div class="t m0 x1 h3 y3c ff1 fs0 fc0 sc0 ls0 ws0">代码段<span class="ff4">二</span>：运行速度更快的代码</div><div class="t m0 x1 h5 y50 ff3 fs1 fc3 sc0 ls0 ws0"><span class="fc0">#'(</span></div><div class="t m0 x1 h5 y51 ff3 fs1 fc3 sc0 ls0 ws0">)<span class="fc0">*</span><span class="fc0"></span>-</div><div class="t m0 x1 h5 y52 ff3 fs1 fc3 sc0 ls0 ws0">)<span class="fc0">+&</span><span class="fc0"></span>,</div><div class="t m0 x1 h5 y1d ff3 fs1 fc3 sc0 ls0 ws0">)<span class="fc0">+</span><span class="fc0"></span>+</div><div class="t m0 x1 h5 y1e ff3 fs1 fc3 sc0 ls0 ws0">)<span class="fc0">$-</span><span class="fc0"></span></div></div></div><div class="pi" data-data='{"ctm":[1.611850,0.000000,0.000000,1.611850,0.000000,0.000000]}'></div></div>
<div id="pf4" class="pf w0 h0" data-page-no="4"><div class="pc pc4 w0 h0"><img class="bi x0 y0 w1 h1" alt="" src="https://static.pudn.com/prod/directory_preview_static/6270f2a8d973ef42a4494be2/bg4.jpg"><div class="c x0 y1 w2 h2"><div class="t m0 x1 h4 y53 ff3 fs1 fc0 sc0 ls0 ws0">**'<span class="fc4">./"/0000.</span>(-<span class="fc2"><span class="ff1">一次从<span class="_ _1"> </span></span><span class="_ _1"> </span><span class="ff1">单元格</span></span></div><div class="t m0 x2 h4 y54 ff1 fs1 fc2 sc0 ls0 ws0">中<span class="ff4">读</span>取所有的值<span class="ff3"></span>将其放入<span class="ff4">数组</span></div><div class="t m0 x1 h5 y55 ff3 fs1 fc3 sc0 ls0 ws0">#<span class="fc0">+&/</span>2<span class="fc0">/0000</span></div><div class="t m0 x1 h5 y56 ff3 fs1 fc3 sc0 ls0 ws0">#<span class="fc0">+/</span>2<span class="fc0">3</span></div><div class="t m0 x1 h5 y57 ff3 fs1 fc0 sc0 ls0 ws0">$-*'+&+(</div><div class="t m0 x1 h5 y58 ff3 fs1 fc3 sc0 ls0 ws0">+5<span class="fc0">$-60</span>2!</div><div class="t m0 x1 h4 y59 ff3 fs1 fc0 sc0 ls0 ws0">$-$-7$-<span class="fc2"><span class="ff1">改变<span class="ff4">数组</span>中的值</span></span></div><div class="t m0 x1 h5 y5a ff3 fs1 fc0 sc0 ls0 ws0">*'+&+($-</div><div class="t m0 x1 h5 y49 ff3 fs1 fc3 sc0 ls0 ws0"><span class="fc0"></span>+5</div><div class="t m0 x1 h5 y5b ff3 fs1 fc3 sc0 ls0 ws0">8<span class="fc0">+</span></div><div class="t m0 x1 h5 y5c ff3 fs1 fc3 sc0 ls0 ws0">8<span class="fc0">+&</span></div><div class="t m0 x1 h4 y14 ff3 fs1 fc0 sc0 ls0 ws0">*'<span class="fc4">./"/0000.</span>(-*<span class="fc2"><span class="ff1">一次将所有结果<span class="ff4">写</span>回单</span></span></div><div class="t m0 x2 h4 y5d ff1 fs1 fc2 sc0 ls0 ws0">元格</div><div class="t m0 x1 h5 y4d ff3 fs1 fc3 sc0 ls0 ws0"><span class="fc0"></span></div><div class="t m0 x1 h3 y38 ff4 fs0 fc1 sc1 ls0 ws0">避免选<span class="ff1">取<span class="ff5 sc0">/</span></span>激活<span class="ff1">对</span>象</div><div class="t m0 x2 h3 y39 ff1 fs0 fc0 sc0 ls0 ws0">使用<span class="ff4">选</span>取的方法更新单元格区域是<span class="ff4">最慢</span>的。在<span class="ff4">试</span>验了使用<span class="_ _0"> </span><span class="ff2">Range<span class="_ _0"> </span></span>对<span class="ff4">象</span>、使用<span class="_ _0"> </span><span class="ff2">Variant<span class="_ _0"> </span><span class="ff4">类型</span></span></div><div class="t m0 x2 h3 y3a ff1 fs0 fc0 sc0 ls0 ws0">和使用<span class="_ _0"> </span><span class="ff2">Select<span class="_ _0"> </span></span>方法对一个大的单元格区域<span class="ff4">读写数据</span>的操作后，<span class="ff2">Select<span class="_ _0"> </span></span>方法是<span class="ff4">最慢</span>的。</div><div class="t m0 x2 h3 y3b ff1 fs0 fc0 sc0 ls0 ws0">再来<span class="ff4">看</span>一个例<span class="ff4">子</span>：在工作表中有<span class="_ _0"> </span><span class="ff2">40<span class="_ _0"> </span></span>个<span class="ff4">形</span>状，在每个<span class="ff4">形</span>状中<span class="ff4">写</span>入“<span class="ff2">Hello”</span>。使用<span class="_ _0"> </span><span class="ff2">Select<span class="_ _0"> </span></span>方法</div><div class="t m0 x2 h3 y3c ff1 fs0 fc0 sc0 ls0 ws0">的代码为：</div><div class="t m0 x1 h5 y50 ff3 fs1 fc3 sc0 ls0 ws0"><span class="fc0">&'(</span></div><div class="t m0 x1 h5 y51 ff3 fs1 fc3 sc0 ls0 ws0">)<span class="fc0"></span><span class="fc0"></span>+</div><div class="t m0 x1 h5 y52 ff3 fs1 fc3 sc0 ls0 ws0">#<span class="fc0">0</span>2<span class="fc0">!!</span></div><div class="t m0 x1 h5 y1d ff3 fs1 fc0 sc0 ls0 ws0">!!'(<span class="fc3"></span></div><div class="t m0 x1 h5 y1e ff3 fs1 fc0 sc0 ls0 ws0">2<span class="fc4">.9.</span></div></div></div><div class="pi" data-data='{"ctm":[1.611850,0.000000,0.000000,1.611850,0.000000,0.000000]}'></div></div>
<div id="pf5" class="pf w0 h0" data-page-no="5"><div class="pc pc5 w0 h0"><img class="bi x0 y0 w1 h1" alt="" src="https://static.pudn.com/prod/directory_preview_static/6270f2a8d973ef42a4494be2/bg5.jpg"><div class="c x0 y1 w2 h2"><div class="t m0 x1 h5 y1f ff3 fs1 fc3 sc0 ls0 ws0">8<span class="fc0"></span></div><div class="t m0 x1 h5 y20 ff3 fs1 fc3 sc0 ls0 ws0"><span class="fc0"></span></div><div class="t m0 x1 h3 y5e ff1 fs0 fc0 sc0 ls0 ws0">运行速度更快的方法是<span class="ff4">完全避免</span>使用<span class="ff4">选</span>取<span class="ff4">并</span>直接<span class="ff4">引</span>用<span class="ff4">形</span>状：</div><div class="t m0 x1 h5 y5f ff3 fs1 fc3 sc0 ls0 ws0"><span class="fc0">#'(</span></div><div class="t m0 x1 h5 y60 ff3 fs1 fc3 sc0 ls0 ws0">)<span class="fc0"></span><span class="fc0"></span>+</div><div class="t m0 x1 h5 y61 ff3 fs1 fc3 sc0 ls0 ws0">#<span class="fc0">0</span>2<span class="fc0">!!</span></div><div class="t m0 x1 h5 y62 ff3 fs1 fc0 sc0 ls0 ws0">!!'(2552<span class="fc4">.9.</span></div><div class="t m0 x1 h5 y63 ff3 fs1 fc3 sc0 ls0 ws0">8<span class="fc0"></span></div><div class="t m0 x1 h5 y64 ff3 fs1 fc3 sc0 ls0 ws0"><span class="fc0"></span></div><div class="t m0 x1 h3 y65 ff1 fs0 fc0 sc0 ls0 ws0">在使用<span class="ff4">宏录</span>制<span class="ff4">器</span>时，所生<span class="ff4">成</span>的程序代码在应用<span class="ff4">任</span>何方法或属性<span class="ff4">之</span>前都会<span class="ff4">激活</span>或者<span class="ff4">选择</span></div><div class="t m0 x2 h3 y66 ff1 fs0 fc0 sc0 ls0 ws0">对<span class="ff4">象</span>。<span class="ff4">但</span>是，<span class="ff4">并</span>不是在所有的<span class="ff4">情况</span>下都需要这<span class="ff4">样做</span>。所以，在您编<span class="ff4">写<span class="_ _0"> </span><span class="ff2">VBA<span class="_ _0"> </span></span></span>程序代码时，不</div><div class="t m0 x2 h3 y67 ff1 fs0 fc0 sc0 ls0 ws0">需要在对对<span class="ff4">象</span>执行<span class="ff4">任</span>何<span class="ff4">任务之</span>前都<span class="ff4">激活</span>或者<span class="ff4">选择</span>每个对<span class="ff4">象</span>。</div><div class="t m0 x2 h3 y68 ff1 fs0 fc0 sc0 ls0 ws0">例如，在<span class="_ _0"> </span><span class="ff2">Excel<span class="_ _0"> </span></span>中，<span class="ff4">我们</span>如果要使<span class="ff4">第</span>一行变<span class="ff4">成粗体</span>就必<span class="ff4">须先选项</span>中它。<span class="ff4">但</span>在<span class="_ _0"> </span><span class="ff2">VBA<span class="_ _0"> </span></span>中<span class="ff2">(</span>除在</div><div class="t m0 x2 h3 y69 ff1 fs0 fc0 sc0 ls0 ws0">图表操作时需要<span class="ff4">选</span>中图表对<span class="ff4">象</span>外<span class="ff2">)</span>，很<span class="ff4">少</span>需要这<span class="ff4">样做</span>，<span class="ff4">即<span class="_ _0"> </span><span class="ff2">VBA<span class="_ _0"> </span></span></span>可以在不<span class="ff4">选</span>中<span class="ff4">第</span>一行的<span class="ff4">情况</span></div><div class="t m0 x2 h3 y6a ff1 fs0 fc0 sc0 ls0 ws0">下，将它变<span class="ff4">成粗体</span>。</div><div class="t m0 x2 h3 y6b ff4 fs0 fc0 sc0 ls0 ws0">宏录<span class="ff1">制</span>器<span class="ff1">的代码：</span></div><div class="t m0 x1 h5 y6c ff3 fs1 fc0 sc0 ls0 ws0">*&'<span class="fc4">./"/.</span>(<span class="fc3"></span></div><div class="t m0 x1 h5 y6d ff3 fs1 fc0 sc0 ls0 ws0">#<span class="fc3">2</span></div><div class="t m0 x1 h3 y6e ff1 fs0 fc0 sc0 ls0 ws0">改编后的代码为：</div><div class="t m0 x1 h5 y6f ff3 fs1 fc0 sc0 ls0 ws0">*&':/"/;(#<span class="fc3">2</span></div><div class="t m0 x1 h3 y70 ff1 fs0 fc0 sc0 ls0 ws0">这<span class="ff4">样做</span>还可以使程序代码更简<span class="ff4">洁</span>，<span class="ff4">并</span>且程序可以运行得更快。</div><div class="t m0 x1 h3 y71 ff1 fs0 fc1 sc1 ls0 ws0">工作簿设计</div><div class="t m0 x2 h3 y72 ff4 fs0 fc0 sc0 ls0 ws0">好<span class="ff1">的工作簿设计和</span>数据组织<span class="ff1">有助于编</span>写<span class="ff1">运行</span>良好<span class="ff1">的代码。</span>良好<span class="ff1">设计的工作簿，其执行</span>效率</div><div class="t m0 x2 h3 y73 ff1 fs0 fc0 sc0 ls0 ws0">和<span class="ff4">维护</span>量将大大优化。可以<span class="ff4">说</span>，工作簿设计是从大的<span class="ff4">宏观</span>方面进行优化，而对代码的优化</div><div class="t m0 x2 h3 y74 ff1 fs0 fc0 sc0 ls0 ws0">只是一些<span class="ff4">微观</span>的<span class="ff4">细节</span>上的优化。</div><div class="t m0 x1 h3 y75 ff1 fs0 fc1 sc1 ls0 ws0">其<span class="ff4">他</span></div></div></div><div class="pi" data-data='{"ctm":[1.611850,0.000000,0.000000,1.611850,0.000000,0.000000]}'></div></div>
