自用Excel VBA技巧整理

1、如果想让图表随着原始数据更新，就不能直接使用源数据的值，而要引用其Address（还必须是R1C1格式），例如：

猛击展开

Dim  s  As  Series
...
Set  rg  =  Range(Worksheets( " SheetXXX " ).Cells( 1 ,  2 ), Worksheets( " SheetXXX " ).Cells( 6 ,  2 ))
...
s.Values  =   " ='SheetXXX'! "   &  rg.Address(ReferenceStyle: = xlR1C1)
' s.Values = rg.Value 这样写的坏处：
' 如果图表的源数据是由公式通过原始数据间接得出，如VLOOKUP
' 那么原始数据变化后图表不会自动更新，即使其源数据已经更新

也遇到过公式实在多，运行慢，图表怎么也不更新的excel文件，就只有手动写Worksheet_Change对应的图表重画了。。

 

2、VBA的逻辑运算AND、OR不是短路的。。倒是VB.Net里面有短路的AndAlso、OrElse。

 

3、用类型说明符声明变量，String$、Integer%、Long&、Double#。我不喜欢显式写Dim，这种在VBA中能同时声明类型并赋值的方法很对胃口。

猛击展开

Sub  TypeDeclare()
    s$  =   " abc "    ' String
    i%  =   32767    ' Integer, 2 bytes
    l &   =   32768    ' Long, 4 bytes
    f!  =   1.5      ' Single, 4 bytes
    d#  =   0.0001   ' Double, 8 bytes
    c@  =   0.00000000001    ' Currency, 8 bytes
    
     MsgBox  s     ' 使用的时候可以不带类型说明符
     MsgBox  l &     ' 当然也可以带
End Sub

 

4、最快遍历一个很大的Range的方法？See Here。是官方blog上的，大意是用大范围Range的值（一个Variant数组）代替不停取小Range（比如取一个Cell）。有机会带Timer()实践下。

 

5、很多时候在单元格里面用自定义Function是个不错的选择，比如可以写点功能更强的VLOOKUP之类的。现在我倾向于看具体情况把VBA、内置公式和自定义函数混用，而不是只用公式或只用VBA。（当然，自定义函数毕竟是解释执行的，感觉比自带公式慢，小心使用。如果用的灰常多的话，写个DLL吧。。）

 

6、装个MZ-Tools开发体验稍微好一些。VBE多少年没更新了，作为IDE来讲实在比较挫了。（VSTO、VSTA泪奔。。）

 

7、进行大量计算和写入的时候最好把UI和公式更新关掉（特别是有很多公式的话，写单元格的时候把Calculation改成Manual）：

猛击展开

Sub  SophisticatedWork()
    Application.ScreenUpdating  =   False
    Application.Calculation  =  xlCalculationManual
    
     ' 中间的计算如果依赖一些值的最新版本
     ' 则手动调用 Application.Calculate
    
    Application.Calculation  =  xlCalculationAutomatic
    Application.ScreenUpdating  =   True
End Sub

 

 

 

To Be Continued ...

转载于:https://www.cnblogs.com/dxz/archive/2009/12/15/vba-tricks-collection.html