Lesson 5 Writing Excel VBA Code

To write the Excel VBA 365 code(Excel macro language)you must use the Excel VBA 365 Editor. The simplest way to launch the Excel VBA 365 Editor is to click on the Developer tab, click insert and select an Active-X control you wish to use, as shown in Figure 5.1.

The most commonly used Active-X control is the command button. Insert the command button, then click on it to enter the Excel VBA 365 editor(VBE), as shown in Figure 5.2.

In the VBE, you can enter the VBA code.

Private Sub CommandButton1_Click()
 Your Code
End Sub
 

Excel VBA 365 code is event-driven therefore it will respond to some events. In the aforementioned example, it will execute the event when the user clicks on the command button.

Try the following examples:

Example 5.1

Private Sub CommandButton1_Click()
 Msgbox "Welcome to Excel VBA 365"
End Sub

Example 5.2

You can modify Example 5.1 and it will produce the same result.

Private Sub CommandButton1_Click()
Dim YourMsg As String
 YourMsg = "Welcome to Excel VBA 365"
 MsgBox YourMsg
End Sub

Example 5.3

This example calculate the mod.

Private Sub CommandButton1_Click()
Dim x,y As String
 x=105
 y=20
 MsgBox x mod y
End Sub

Example 5.4

Private Sub
A = "Tom"
B = "likes"
C = "to"
D = "eat"
E = "burger"
MsgBox A + B + C + D + E
End Sub

Running the code produces the sentence "Tom likes to eat burger". It produces the same effect if we replace the last line with MsgBox A & B & C & D & E.