Self-use Excel VBA skills

1. If you want to update the chart with the raw data, you cannot directly use the value of the source data. Instead, you must reference the address (which must also be in r1c1 format). For example:

Click to expand

Dim s as series
...
Set Rg = range (worksheets ("sheetxxx"). cells (1, 2), worksheets ("sheetxxx"). cells (6, 2 ))
...
S. Values = "= 'sheetxxx '! "& RG. Address (referencestyle: = xlr1c1)
The disadvantages of writing's. Values = RG. value are as follows:
'If the source data of the chart is obtained indirectly by the formula through the original data, such as Vlookup
'The chart will not be updated automatically after the original data changes, even if its source data has been updated

I have also encountered many formulas, slow operations, and why I didn't update the charts in an Excel file. I just had to manually write the corresponding worksheet_change chart to reproduce it ..

 

2. The logical operations and or of VBA are not short-circuited .. However, VB. NET contains short-circuit andalso and orelse.

 

3. Declare the variable with the type specifier, string $, integer %, long &, double #. I don't like explicit dim writing. This method can declare types and assign values at the same time in VBA is quite appetizing.

Click to expand

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' can be used without the type specifier
Msgbox L & 'can also contain
End sub

 

4. What is the fastest way to traverse a large range? See here. On the official blog, the general idea is to use a wide range value (a variant array) instead of a non-stop small range (such as a cell ). Has the opportunity to bring timer () practices.

 

5. It is a good choice to use custom functions in cells. For example, you can write more Vlookup functions. Now I am inclined to look at the specific situation to mix VBA, built-in formulas and user-defined functions, instead of using only formulas or VBA. (Of course, udfs are interpreted and executed after all, and it feels slower than built-in formulas. Be careful when using them. If it is often used, write a DLL ..)

 

6. Install MZ-tools for better development experience. VBE has not been updated for many years. As an IDE, it is actually quite frustrating. (Vsto, vsta tears ..)

 

7. When performing a lot of calculations and writing, it is best to turn off the UI and formula Update (especially if there are many formulas, change calculation to manual when writing cells ):

Click to expand

Sub sophisticatedwork ()
Application. screenupdating = false
Application. Calculation = xlcalculationmanual

'Intermediate calculation depends on the latest version of some values.
'Then manually call application. Calculate

Application. Calculation = xlcalculationautomatic
Application. screenupdating = true
End sub

 

 

 

To be continued...