How to use Excel VBA to achieve "copy and paste" (below), Uncle Xin Excel VBA Phase 2

Oct 16, 2021

This issue mainly introduces the usage of the While... Note: The teaching text is recommended to be read by computer.

🔷Introduction 🔷

If you haven't read the last issue, you may not know what VBA is. In short, it is a programming language mainly used in Microsoft Office. It is suitable for people who have not learned programming (because it is easy). Here we mainly discuss VBA for Excel. For more information, please see the first issue .

🔷 VBA loop structure 🔷

The loop structure is used to execute the same piece of code repeatedly, and the number of repetitions is controlled by specific conditions.

There are three main loop structures in VBA, namely For, While and Loop, each of which has several small changes, and one is enough at the beginning. This example uses While ... Wend.

The sample code is as follows:

 Sub Macro2()
    r = 0
    c = 2
    
    While r < 100
        r = r + 1
    
        Sheets("B").Cells(r, c) = r
        Sheets("C").Cells(r, c + 1) = Sheets("B").Cells(r, c)
    Wend
End Sub

🔷 VBA Code Explanation (Example 1) 🔷

❇️ Variables

 r = 0
    c = 2

Here "r" and "c" are variables, we define here that one of them is equal to the number 0, and the other is equal to the number 2
Variable (Variable) : Programs often use variables when processing data, mainly to temporarily store data. The data represented by variables will change frequently, hence the name "variables".
Variable naming rules: The first word must be English letters or Chinese characters, and then you can use Chinese, English, numbers, or underscores (“_”) at will. Simply put, if the running program is wrong, change it.

❇️ While ... Wend

 While r < 100
    Wend

One of the While...Wend usages mentioned above, "r < 100" is its loop condition. In human words, as long as r is less than 100, this macro will repeatedly run from While to Wend, and of course execute the code between them, until you can't stand the forced shutdown.

❇️ Addition in arithmetic operators

 r = r + 1

I don’t know if you still remember the addition, subtraction, multiplication and division you learned when you were a child? what, did you forget? Go slowly! ! ! Don't hit me, just kidding... We defined r=0 earlier, so r+1 is 1, the next time adding 1 is 2.
OK, so what's the use of this line? Remember the While...Wend mentioned above? As long as r is less than 100, it will keep looping, so in order to prevent him from looping forever, we add 1 to r every time it loops, so that r will be equal to 100 after 99 loops, and the loop will stop because it does not meet the conditions.
What are the arithmetic operators (Operator) : addition (+), subtraction (-), multiplication (*), division (/), integer division (\), remainder (Mod), power (^). The useful parts will be explained later.

❇️ Application of worksheets and cells

 Sheets("B").Cells(r, c) = r
        Sheets("C").Cells(r, c + 1) = Sheets("B").Cells(r, c)

These two lines are modified on the basis of the previous issue, the difference is only r and c. So before starting While...Wend, r is 1 and c is 2.
Because these two lines are placed between While ... Wend, the variable r keeps changing because r=r+1 of the previous line, so line 1 " Sheets("B").Cells(r, c) = r "Represents: Column C of worksheet B is from row 1 to row 99, and each row will be filled with a number corresponding to the number of rows.
Line 2 " Sheets("C").Cells(r, c + 1) = Sheets("B").Cells(r, c) " means: Sheet C will copy the contents of Sheet B, but work The contents of column B of table B will be copied to column C of worksheet C.
Might be a little confusing, but just run it once and you'll understand

🔷Sample download for this issue🔷

Click me to download . After opening the file, pay attention to whether there is a warning in the picture below. This is to ask the user to confirm whether the macro is credible, so as to avoid executing the problematic macro. Click on " Enable Content" to use it normally.

The initial Excel security settings will automatically turn off macros, so you need to click "Enable Content" to unlock

🔷Add developer tag to ribbon 🔷

This label is not required, but it is much more convenient. For example, you can record macros, and you don't have to use the shortcut key (alt + F11) to open the VB editor.

❇️ The entire opening process:

File >> Other ... >> Options >> Customize Ribbon >> Check Developer

❇️ The first step is to see if your Excel already has the " Developer " tab, you can skip this small unit, if not, click " File ".

Check if your Excel already has the "Developer" tab, if not, click "File"

❇️ Step 2, click " Other " >> " Options " to enable Excel options.

❇️ The third step, click " Customize the Ribbon ", check " Developer ", and then click " OK ", you're done.

🔷What are the commonly used functions under the developer tab? 🔷

Features that Uncle thinks are more commonly used (developer tags)

Visual Basic: Open the VB editor, the same effect as the shortcut key (Alt + F11)
Macro: Call up the Macro dialog box to make further settings, and will continue to introduce this function in the future.
Recording macros: It is a good helper for programming. The code recorded with it is not easy to use, but it is an excellent template. You can directly apply it to your macros after modifying it. Personally, I think this function is not very useful except to make you feel fun in the early stage of learning VBA, so I will explain how to use and how to modify the recorded code in the follow-up.
Macro security: It was mentioned in the last issue that every time you open a file with a macro, there will be a warning as shown below. This is because of the initial security settings of Excel. If you do not want to click "Enable Content" every time, you need to Modify security here.