If (condition) Then Code statement :-

There are several ways to implement this code structure. The most basic uses just one line of code.

Private Sub GuessAge()

Dim userGuess As Integer
Dim age As Integer

age = 25

userGuess = Val(InputBox(“Guess a number between 20 and 30.”, “Guess Age”))

If (userGuess > age) Then

MsgBox (“Too high!”)

MsgBox (“The answer is “ & age)

End If

If (userGuess < age) Then

MsgBox (“Too low!”)

MsgBox (“The answer is “ & age)

End If

If (userGuess = age) Then MsgBox (“You got it!”)

End Sub

Select/Case Statement :-

Here is example demonstrates the "select case" statement.

Private Sub FindDay()

d =Weekday(Date)

Select Case d

Case 1

MsgBox "Sleepy Sunday"

Case 2

MsgBox "Monday again!"

Case 3

MsgBox "Just Tuesday!"

Case 4

MsgBox "Wednesday!"

Case 5

MsgBox "Thursday..."

Case 6

MsgBox "Finally Friday!"

Case else

MsgBox "Super Saturday!!!!"

End Select

End Sub

Looping Statement :-

Most of the time when you write code, you want to allow the same block of code to run a number of times. You can use looping statements in your code to do this.

Sub SumUp()
Dim n As Integer
Dim t As Integer

For n = 1 To 10
t = t + n
Range(“A1”).Value = t
Next n
MsgBox " The total is " & t

End Sub

Another example:-


Sub DoWhileNotEmpty()

Do While ActiveCell.Value <> Empty

ActiveCell.Value = ActiveCell.Value * 2

ActiveCell.Offset(1, 0).Select

Loop

End Sub

Scrolling about a chosen worksheet.

Here's a very simple line of code that will stop users from scrolling about a chosen worksheet. To place the code in, right click on the sheet name tab, select "View Code" and paste in this code.

Private Sub Worksheet_Activate()

Me.ScrollArea = "A1:L20"

'To set back to normal use:

Me.ScrollArea = ""

End Sub

Making sure the Combobox selection is part of the list:

Private Sub ComboBox1_Change()

If ComboBox1.ListIndex >= 0 Then

'Your code here

End If

End Sub

Stop a user from closing a UserForm via the X:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

If CloseMode = 0 Then

Cancel = True

MsgBox "Please use the Cancel button", vbCritical

End If

End Sub

When coding with a UserForm use the keyword Me.

Private Sub UserForm_Activate()

MsgBox Me.Name

End Sub

When coding with the Worksheet events use the keyword Me.

Private Sub Worksheet_Activate()

MsgBox Me.CodeName

End Sub

When coding with the Workbook events use the keyword Me.

Private Sub Workbook_Open()

MsgBox Me.FullName

End Sub

Prevent endless loops within events:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo ResetEvents
Application.EnableEvents = False
'Your code here.
Application.EnableEvents = True

Exit Sub
ResetEvents:
Application.EnableEvents = True
End Sub