VBA Coding – Range, Conditional Statements and Loops

Feb 15, 2013 by

Range is an object in VBA which is used to refer to cell or cells. If you wish to set the value 5 to the cell A2, then you need to write the code as:

Range(“A2”).Value = 5

If you wish to set a value to cells from A1 to A5 and B1 to B5, then your code should be:

Range(“A1:B5”).Value = 5

You can also write the statement as

Range(“A1:B3, C5”).Value = 5

You have the freedom to declare Range object using keywords Dim and Set. Consider the example given below:

Dim ranObj As Range

Set ranObj = Range(“A2:B4, C3″)

ranObj = 10

You can select the whole range, or portions of range using Select property. Consider the code below.

Dim ranObj As Range

Set ranObj = Range(“A1:C5″)

ranObj = 10

ranObj.Select

Instead of the last line, you can also write ranObj.Rows(4).Select or ranObj.Columns(2).Select to select 4th row and 2nd column respectively. You can clear the contents using ranObj.ClearContents.

 

Conditional statements are very much required to make the code execution based on certain conditions. Two types of conditional statements available with VBA are If..Then and Select Case statements. Both types evaluate one or more conditions and execute a single statement or a block of code based on the result.

Format of If..Then statement is as follows:

If Condition1 Then

Actions to be executed Condition1 is true

ElseIf Condition2 Then

Actions to be executed Condition2 is true

————————-

————————–

Else

Actions to be executed if all the above conditions fail

EndIf

Format of Select Case statement is as follows:

Select Case Expression

Case Value1

Actions to be executed if Expression matches Value1

Case Value2

Actions to be executed if Expression matches Value2

…………………..

Case Else

Actions to be executed if the expression does not match any of the above cases

End Select

Loops are used to perform a particular action repeatedly as far as the specific condition met. Different types of loops are For Next loop, For Each loop, Do While loop and Do Until loop. For Each loop initially sets a value to a variable and executes a set of code until the variable gets a particular value. For example,

For a = 1 To 5

MsgBox (“Current value is “& a)

Next a

This will display 5 message boxes one by one with values 1 to 5. In this example, step size is not specified as it 1 by default. Now, if you wish to display the message for 1, 1.5, 2 etc, then your code should be as follows.

For a = 1 To 5 Step .5

MsgBox (“Current value is “& a)

Next a

You can also set step size value as negative.

For Each loop runs through each object within a collection of objects. For example, the given below code would loop through each worksheet object and displays the name of the worksheet.

DimwSheetAs Worksheet

For EachwSheetIn Worksheets

MsgBox (“Current worksheet is “&wSheet.Name)

NextwSheet

If you wish to exit a particular loop when certain condition is met, then you can use Exit For statement.

Do While and Do Until are similar loops. Do While performs the task repeatedly as far as the specified condition is met and Do Until performs the task until a specified condition is met. Consider two sets of code given below:

Set 1:

a = 1

DoWhile a <= 5

MsgBox (“Current value is “& a)

a = a + 1

Loop

Set 2:

a = 1

DoUntil a > 5

MsgBox (“Current value is “& a)

a = a + 1

Loop

Both set of codes execute the same thing. Only difference is in the condition specified.

Related Posts

Tags

Share This