VBA Lesson 11: VBA Code General Tips and General Vocabulary

This is an excerpt from Pierre Leclerc (www.excel-vba.com)

When you start assembling VBA words into sentences and paragraphs, it is said that you are coding or developing VBA code. In this lesson you will learn important coding tips and many special VBA words.

Coding Tips

- Always key in your code in lowercase letters. If the spelling is right, the necessary letters will be capitalized.  If no letter gets capitalized .... check your spelling.

Exercise

Step 1: Open a new workbook in Excel and use the ALT/F11 keys to go to the visual basic editor.

Step 2: In the code window of any of the sheet copy/paste the following line of code: worbook.opn

Notice that the W and the O have not been capitalised.

Step 3: Add the missing k in the middle of the word worbook,use the mouse to move the cursor to the end of the sentence and click "Enter".  The W gets capitalised.

Step 4: Add the missing e to opn, click at the end of the sentence to move the cursor there and click "Enter". The O of Open gets capitalised.

You now understand that significant letters are capitalised in each correctly spelled VBA word when you click "Enter" to move to the next line.

Step 5: Close Excel without saving anything

- You should also add comment to your VBA procedures to make them  easier to understand. Any line of code that starts with an apostrophe is considered a comment and the font will be green in the code window. For example:

Exercise 1

Step 1: Open a new workbook in Excel and use the ALT/F11 keys to go to the visual basic editor.

Step 2: In the code window of any of the sheet copy/paste the following macro

Sub testLesson11b1()

     ' In this section we do this and this
     Range("A1").Value=3

End Sub

When the macro is in the Visual Basic Editor you will notice that the color of the font of the hyphen and the code that follows is green. This color means that the sentence is a comment and VBA ignores it altogether. The color of the font off the rest of the macro is black and VBA will execute the order.

Step 3: Use the ALT/F11 key to go to Excel and run the macro.

The value 3 has been entered in cell A1

Step 4: Close Excel without saving anything

Exercise 2

You can also add a comment at the end of any line of code   by adding a space at the end of the line then a comment preceded by an apostrophe.

Step 1: Open a new workbook in Excel and use the ALT/F11 keys to go to the visual basic editor.

Step 2: In the code window of any of the sheet copy/paste the following macro

Sub testLesson11b2()

     Range("A1").Value= ""Peter""   ' Entering a first name between quotes

End Sub

When the macro is in the Visual Basic Editor you will notice that the color of the font of the hyphen and the code that follows is green. But on the same line and on the other lines the font of the effective code is black and VBA will execute the order to enter Peter in cell A1.

Step 3: Use the ALT/F11 key to go to Excel and run the macro.

The name Peter has been entered in cell A1

Step 4: Close Excel without saving anything

VBA General Vocabulary

- A VBA procedure always ends with End Sub but you can always exit a macro at any point with Exit Sub. You will use Exit Sub a lot with an If statement like the following:
If Range("A1").Value=12 Then
    Exit Sub
End if
meaning if the value of cell A1 is 12 stop the macro.

Exercise 1

Step 1: Open a new workbook in Excel and use the ALT/F11 keys to go to the visual basic editor.

Step 2: In the code window of any of the sheet copy/paste the following macro

Sub testLesson11b1()

     Range("A1").Value=12
     Range("A2").Value=12
     Range("A3").Value=12

 End Sub

Step 3: Use the ALT/F11 key to go to Excel and run the macro.

After the macro has been executed cells A1, A2 and A3 carry the value 12.

Step 4: Close Excel without saving anything

Exercise 2

We will now add an Exit Sub to the code.

Step 1: Open a new workbook in Excel and use the ALT/F11 keys to go to the visual basic editor.

Step 2: In the code window of any of the sheet copy/paste the following macro

Sub testLesson11b2()

     Range("A1").Value=12
     Range("A2").Value=12
     Exit Sub
     Range("A3").Value=12

 End Sub

Step 3: Use the ALT/F11 key to go to Excel and run the macro.

After the macro has been executed cells A1 and A2 the value 12. The value 12 has never been entered in cell A3 because there was an order to exit the macro without finishing its execution.

Step 4: Close Excel without saving anything

 Go to the next lesson
Lesson 12 : VBA for Excel to Manage  Errors