自用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 ...