Compile an Excel macro

Author: Zhu Jincan

Source: http://blog.csdn.net/clever101

 

When a friend used EXCEL to create a table, he asked me to write a calculation formula in one column and automatically calculate the calculation result in the other column, for example:

I told him that this can be implemented by writing a macro. The procedure is as follows:

1. Find the macro (m) under the macro (m) menu under the "tool" menu bar, as shown in:

2. Enter the name calc in the pop-up dialog box and click Create, as shown in the following figure:

3. delete all the code in the pop-up interface and enter the following code:

Sub Calc()a = [A1].Value[B1] = Evaluate(a)a = [A2].Value[B2] = Evaluate(a)a = [A3].Value[B3] = Evaluate(a)a = [A4].Value[B4] = Evaluate(a)End Sub

As shown in:

Here is a simple explanation of the Code:

Sub calc () is to define a function, and end sub is to end a function.

A = [a1]. value indicates that the expression in table A1 is assigned to variable A, [B1] = evaluate (A), which is used to calculate the value of expression, assign the value of this expression to Table B1. The above Code only calculates the values of the four grids A1, A2, A3, and A4. If you want to calculate the values of other grids, such as A5, A6, and A7, you can modify this code.

 

4. Click the Save button to save the macro, as shown below:

5. Close the macro editing interface as follows:

6. Enter four formulas in the four grids A1, A2, A3, and A4, for example:

7. Find the macro (m) under the macro (m) menu under the "tool" menu bar, as shown in:

8. Select the calc macro and click the execute button, as shown in:

9. The execution result is shown in:

 

Now I find that the Office macros are basically the same as the Visual Studio macros, so I have to admire Microsoft's architecture. You only need to understand one and others.

 

References:

 

1. Use the evaluate () function in Excel to calculate the calculation result of a text Mathematical Expression