[fusion_builder_container hundred_percent=”no” hundred_percent_height=”no” hundred_percent_height_scroll=”no” hundred_percent_height_center_content=”yes” equal_height_columns=”no” menu_anchor=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” status=”published” publish_date=”” class=”” id=”” background_color=”” background_image=”” background_position=”center center” background_repeat=”no-repeat” fade=”no” background_parallax=”none” enable_mobile=”no” parallax_speed=”0.3″ video_mp4=”” video_webm=”” video_ogv=”” video_url=”” video_aspect_ratio=”16:9″ video_loop=”yes” video_mute=”yes” video_preview_image=”” border_size=”” border_color=”” border_style=”solid” margin_top=”” margin_bottom=”” padding_top=”” padding_right=”” padding_bottom=”” padding_left=””][fusion_builder_row][fusion_builder_column type=”1_1″ layout=”1_1″ spacing=”” center_content=”no” link=”” target=”_self” min_height=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” background_color=”” background_image=”” background_image_id=”” background_position=”left top” background_repeat=”no-repeat” hover_type=”none” border_size=”0″ border_color=”” border_style=”solid” border_position=”all” padding_top=”” padding_right=”” padding_bottom=”” padding_left=”” margin_top=”” margin_bottom=”” animation_type=”” animation_direction=”left” animation_speed=”0.3″ animation_offset=”” last=”no”][fusion_imageframe image_id=”17743|full” max_width=”” style_type=”” blur=”” stylecolor=”” hover_type=”none” bordersize=”” bordercolor=”” borderradius=”” align=”center” lightbox=”no” gallery_id=”” lightbox_image=”” lightbox_image_id=”” alt=”” link=”” linktarget=”_self” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” animation_type=”” animation_direction=”left” animation_speed=”0.3″ animation_offset=””]https://earnandexcel.com/wp-content/uploads/Excel-Training-London.jpg[/fusion_imageframe][fusion_text columns=”” column_min_width=”” column_spacing=”” rule_style=”default” rule_size=”” rule_color=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”bg-spy” id=””]

 

Excel Funtion: VBA

[/fusion_text][/fusion_builder_column][fusion_builder_column type=”2_3″ layout=”2_3″ spacing=”” center_content=”no” link=”” target=”_self” min_height=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” background_color=”” background_image=”” background_image_id=”” background_position=”left top” background_repeat=”no-repeat” hover_type=”none” border_size=”0″ border_color=”” border_style=”solid” border_position=”all” padding_top=”” padding_right=”” padding_bottom=”” padding_left=”” margin_top=”” margin_bottom=”” animation_type=”” animation_direction=”left” animation_speed=”0.3″ animation_offset=”” last=”no”][fusion_builder_row_inner][fusion_builder_column_inner type=”1_1″ layout=”1_1″ spacing=”” center_content=”no” hover_type=”none” link=”” min_height=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” background_color=”” background_image=”” background_position=”left top” background_repeat=”no-repeat” border_size=”0″ border_color=”” border_style=”solid” padding_top=”” padding_right=”” padding_bottom=”” padding_left=”” dimension_margin=”” animation_type=”” animation_direction=”left” animation_speed=”0.3″ animation_offset=”” last=”no” border_position=”all”][fusion_menu_anchor name=”a1″ class=”” /][fusion_text columns=”” column_min_width=”” column_spacing=”” rule_style=”default” rule_size=”” rule_color=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=””]

Excel VBA: Learn the basics in a few minutes

What is VBA? VBA stands for Visual Basic for Applications. It is an implementation of Microsoft’s event-driven programming language Visual Basic which is now used with Microsoft Office applications such as MS Excel, MS Word and MS Access.

[/fusion_text][fusion_separator style_type=”single|dashed” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” sep_color=”#008000″ top_margin=”20″ bottom_margin=”20″ border_size=”2″ icon=”” icon_circle=”” icon_circle_color=”” width=”” alignment=”center” /][/fusion_builder_column_inner][/fusion_builder_row_inner][fusion_builder_row_inner][fusion_builder_column_inner type=”1_1″ layout=”1_1″ spacing=”” center_content=”no” hover_type=”none” link=”” min_height=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” background_color=”” background_image=”” background_position=”left top” background_repeat=”no-repeat” border_size=”0″ border_color=”” border_style=”solid” animation_type=”” animation_direction=”left” animation_speed=”0.3″ animation_offset=”” last=”no” border_position=”all”][fusion_menu_anchor name=”a2″ class=”” /][fusion_text columns=”” column_min_width=”” column_spacing=”” rule_style=”default” rule_size=”” rule_color=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=””]

Introduction to this Excel VBA Tutorial

Why do we need to learn VBA? VBA is an incredible skill because it can help us automate our work, that is anything you do in Excel with a certain amount of repetition or frequency can be dealt with automatically by controlling Excel. What do I mean by controlling Excel? Well, quite simply, you can write some code (known as Visual Basic for Applications (or VBA) which sits ‘behind the scenes’ and can do all the tasks you normally would from copying/pasting, writing formulas, creating charts, saving files in a folder and pretty much everything else you can imagine! The potential to save time is incredible, I regularly create VBA scripts that save me up to an hour and my personal record is automating an entire monthly reporting process that lasted over 100 hours down to 5 minutes! What’s even more remarkable is that from my time saved, I was able to solve other problems for the companies I worked for and was promoted several times. My peers simply couldn’t compete with me because I was significantly more productive than them.

So today, I am going to teach you the basics of VBA and how it could be a great foundation to enhance your Excel skills to a higher level of expertise. After this tutorial, I hope you will be confident enough to start creating your own macros and continue practicing it. Soon, you’ll realize how amazing VBA can help you on your day to day work. And in no time, you can be the next Excel VBA Rockstar!

[/fusion_text][fusion_separator style_type=”single|dashed” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” sep_color=”#008000″ top_margin=”20″ bottom_margin=”20″ border_size=”2″ icon=”” icon_circle=”” icon_circle_color=”” width=”” alignment=”center” /][/fusion_builder_column_inner][/fusion_builder_row_inner][fusion_builder_row_inner][fusion_builder_column_inner type=”1_1″ layout=”1_1″ spacing=”” center_content=”no” hover_type=”none” link=”” min_height=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” background_color=”” background_image=”” background_position=”left top” background_repeat=”no-repeat” border_size=”0″ border_color=”” border_style=”solid” animation_type=”” animation_direction=”left” animation_speed=”0.3″ animation_offset=”” last=”no” border_position=”all”][fusion_menu_anchor name=”a3″ class=”” /][fusion_text columns=”” column_min_width=”” column_spacing=”” rule_style=”default” rule_size=”” rule_color=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=””]

VBA Cells

For our first lesson, we are going to make something automatically display in a cell in our Excel spreadsheet. By the way, there are two ways to refer to a cell in Excel, one is using the word Cells and the other one is by using the word Range. The latter I am going to discuss after this topic.

But before that, what we need to do first is to open the Visual Basic window. To do that, go to the Developer tab then click the Visual Basic button. Or, you can simply hold ALT key then press F11 key. (Alt+F11)

Whichever method you choose, a window should appear like the picture below. Double click an object on the left pane under Project – VBA Project. In this case, I clicked Sheet1 to show the coding area for Sheet1. By doing so, all written codes in this object will only affect Sheet1 worksheet. Similarly, if you add codes in object Sheet2 or Sheet3, the codes will only be run in its respective worksheet.

Now let’s start writing some codes. Again, we want some texts to automatically appear in cell B5 in Sheet1 so we will need to add the following code:

Sub Learning()

Cells(5,2) = “Hello”

End Sub

Cells(5,2) also refers to cell B5, where 5 refers Row number, and 2 is the column number, which is B.

Hit the Run/Play button or press F5, and the word Hello should then appear in our Sheet1 cell B5 like this:

Note: A Sub (short for Subroutine) is a series of Visual Basic statements enclosed by the Sub and End Sub statements. A  Sub procedure performs a task provided in a form of code.

[/fusion_text][fusion_separator style_type=”single|dashed” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” sep_color=”#008000″ top_margin=”20″ bottom_margin=”20″ border_size=”2″ icon=”” icon_circle=”” icon_circle_color=”” width=”” alignment=”center” /][/fusion_builder_column_inner][/fusion_builder_row_inner][fusion_builder_row_inner][fusion_builder_column_inner type=”1_1″ layout=”1_1″ spacing=”” center_content=”no” hover_type=”none” link=”” min_height=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” background_color=”” background_image=”” background_position=”left top” background_repeat=”no-repeat” border_size=”0″ border_color=”” border_style=”solid” animation_type=”” animation_direction=”left” animation_speed=”0.3″ animation_offset=”” last=”no” border_position=”all”][fusion_menu_anchor name=”a4″ class=”” /][fusion_text columns=”” column_min_width=”” column_spacing=”” rule_style=”default” rule_size=”” rule_color=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=””]

VBA Range

As I have mentioned previously, we can also use the word Range when cell referencing. The good thing about using Range is it lets you reference either a single cell or multiple cells all at once. It is also easier to use because its format is the common way Excel cells are named like cell A1, A2 or cells A1:A10. But of course, Cells property has its advantages as well when it comes to coding loops.

Let’s use the Range object in VBA.

This is the code to add in the VBA editor:

Sub Learning()

Range(“A1”) = “Hello”

End Sub

To display the text “Hello” in multiple cells or in a range of cells, we simply tweak the code like so:

Sub Learning()

Range(“A1:A10”) = “Hello”

End Sub

What we did is instead of cell “A1” enclosed in our range, we replaced it with “A1:A10”, displaying the word Hello from cells A1 to A10.

[/fusion_text][fusion_separator style_type=”single|dashed” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” sep_color=”#008000″ top_margin=”20″ bottom_margin=”20″ border_size=”2″ icon=”” icon_circle=”” icon_circle_color=”” width=”” alignment=”center” /][/fusion_builder_column_inner][/fusion_builder_row_inner][fusion_builder_row_inner][fusion_builder_column_inner type=”1_1″ layout=”1_1″ spacing=”” center_content=”no” hover_type=”none” link=”” min_height=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” background_color=”” background_image=”” background_position=”left top” background_repeat=”no-repeat” border_size=”0″ border_color=”” border_style=”solid” animation_type=”” animation_direction=”left” animation_speed=”0.3″ animation_offset=”” last=”no” border_position=”all”][fusion_menu_anchor name=”a5″ class=”” /][fusion_text columns=”” column_min_width=”” column_spacing=”” rule_style=”default” rule_size=”” rule_color=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=””]

VBA Copy and Paste

Everyone would agree when I say that Copy and Paste are the two most common Excel operations. You may have done a lot of copying and pasting manually. Now I will show you how quick and easy VBA can do it for you.

First, go back to the VBA window. Assuming we have some text written in cell A1 and we want to copy and paste it in cell A2, add this code:

Sub Learning()

Range(“A1”).Copy Range(“A2”)

End Sub

The 2^nd Range object is the destination range. This can also be a single or multiple range of cells.

This is how it would look like in the VBA editor and Excel sheet.

To paste to multiple cells, add this code:

Sub Learning()

Range(“A1”).Copy Range(“A2:A5”)

[/fusion_text][fusion_separator style_type=”single|dashed” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” sep_color=”#008000″ top_margin=”20″ bottom_margin=”20″ border_size=”2″ icon=”” icon_circle=”” icon_circle_color=”” width=”” alignment=”center” /][/fusion_builder_column_inner][/fusion_builder_row_inner][fusion_builder_row_inner][fusion_builder_column_inner type=”1_1″ layout=”1_1″ spacing=”” center_content=”no” hover_type=”none” link=”” min_height=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” background_color=”” background_image=”” background_position=”left top” background_repeat=”no-repeat” border_size=”0″ border_color=”” border_style=”solid” animation_type=”” animation_direction=”left” animation_speed=”0.3″ animation_offset=”” last=”no” border_position=”all”][fusion_menu_anchor name=”a6″ class=”” /][fusion_text columns=”” column_min_width=”” column_spacing=”” rule_style=”default” rule_size=”” rule_color=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=””]

Color Cells with VBA Color Index

VBA is so useful when we want to automate repetitive tasks. One example at work is, we often use cell highlighting or cell colors for quick overview of statuses etc.

There are two ways to set the color. One is with the use of ColorIndex, which has 56 colors, or Color which makes it possible to use any color at all.

When using the ColorIndex, you can refer to the image below for the color and its corresponding colorindex.

To set the color of our cell to one of these, write the code:

Sub Learning()

Range(“A1”).Interior.ColorIndex = 49

End Sub

This is how it should look like in VBA Editor and Excel sheet:

Second option is to use the Color property. Under this, there are 2 inputs we can use:

1. vbColor – This is the easiest way however; the colors are limited.

Sample code:

Sub Learning()

Range(“A1”).Interior.Color = vbYellow

Range(“A2”).Interior.Color = vbRed

Range(“A3”).Interior.Color = vbBlue

End Sub

2. RGB Colors – RGB which stands for Red Green Blue. To use RGB, enter a value between 0 to 255 for each color code.

Here is an example code:

Sub Learning()

Range(“A1”).Interior.Color = RGB(90, 70, 50)

End Sub

Illustrated in VBA editor and Excel sheet, we’ll see:

[/fusion_text][fusion_separator style_type=”single|dashed” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” sep_color=”#008000″ top_margin=”20″ bottom_margin=”20″ border_size=”2″ icon=”” icon_circle=”” icon_circle_color=”” width=”” alignment=”center” /][/fusion_builder_column_inner][/fusion_builder_row_inner][fusion_builder_row_inner][fusion_builder_column_inner type=”1_1″ layout=”1_1″ spacing=”” center_content=”no” hover_type=”none” link=”” min_height=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” background_color=”” background_image=”” background_position=”left top” background_repeat=”no-repeat” border_size=”0″ border_color=”” border_style=”solid” animation_type=”” animation_direction=”left” animation_speed=”0.3″ animation_offset=”” last=”no” border_position=”all”][fusion_menu_anchor name=”a7″ class=”” /][fusion_text columns=”” column_min_width=”” column_spacing=”” rule_style=”default” rule_size=”” rule_color=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=””]

VBA Control Fonts

We can also control the font’s attributes in VBA. The commonly used attributes are:

Bold, Color, ColorIndex, FontStyle, Italic, Name, Size, Underline, etc.

Here is the sample code for your reference:

Sub Learning()

Range(“A1”).Font.Bold = True

Range(“A2”).Font.Italic = True

Range(“A3”).Font.Underline = True

Range(“A5”).Font.Color = vbRed

Range(“A6”).Font.Name = “Arial Black”

Range(“A7”).Font.Size = 30

End Sub

Let’s see our codes in action. By looking at the result of our codes, we can see that:

Text in A1 was set to Bold

A2 was set to Italic

A3 was underlined

A5 font color was set to red

A6 font name was set to Arial Black; and lastly,

A7 font size was set to 30

[/fusion_text][fusion_separator style_type=”single|dashed” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” sep_color=”#008000″ top_margin=”20″ bottom_margin=”20″ border_size=”2″ icon=”” icon_circle=”” icon_circle_color=”” width=”” alignment=”center” /][/fusion_builder_column_inner][/fusion_builder_row_inner][fusion_builder_row_inner][fusion_builder_column_inner type=”1_1″ layout=”1_1″ spacing=”” center_content=”no” hover_type=”none” link=”” min_height=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” background_color=”” background_image=”” background_position=”left top” background_repeat=”no-repeat” border_size=”0″ border_color=”” border_style=”solid” animation_type=”” animation_direction=”left” animation_speed=”0.3″ animation_offset=”” last=”no” border_position=”all”][fusion_menu_anchor name=”a8″ class=”” /][fusion_text columns=”” column_min_width=”” column_spacing=”” rule_style=”default” rule_size=”” rule_color=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=””]

VBA Clearing Cells

There are different options to clear the cells in Excel. I will show you how to implement it using VBA.

The image below is the list of cell-clearing options. This suggestion box pops-up when you start typing the word ‘clear’, giving you options on what to clear in a cell.

Clear – Clears the entire specified range

Range(“A7”).Clear

ClearComments – Clears all cell comments from the specified range

Range(“A6”).ClearComments

ClearContents – Clears formulas and values from the range

Range(“A6”).ClearContents

ClearFormats – Clears the formatting from the specified range

ClearHyperlinks – Removes all hyperlinks from the specified range

ClearNotes – Clears notes and sound notes from the specified range

ClearOutline – Clears the outline for the specified range

To clear cell color, you can use this code as reference:

Cells.Interior.Color = xlColorIndexNone

[/fusion_text][fusion_separator style_type=”single|dashed” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” sep_color=”#008000″ top_margin=”20″ bottom_margin=”20″ border_size=”2″ icon=”” icon_circle=”” icon_circle_color=”” width=”” alignment=”center” /][/fusion_builder_column_inner][/fusion_builder_row_inner][fusion_builder_row_inner][fusion_builder_column_inner type=”1_1″ layout=”1_1″ spacing=”” center_content=”no” hover_type=”none” link=”” min_height=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” background_color=”” background_image=”” background_position=”left top” background_repeat=”no-repeat” border_size=”0″ border_color=”” border_style=”solid” animation_type=”” animation_direction=”left” animation_speed=”0.3″ animation_offset=”” last=”no” border_position=”all”][fusion_menu_anchor name=”a9″ class=”” /][fusion_text columns=”” column_min_width=”” column_spacing=”” rule_style=”default” rule_size=”” rule_color=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=””]

VBA Columns Widths

Excel column widths can also be manipulated using VBA. This one is quite important especially in reports or data presentations when same and exact column widths are needed to display data properly.

To set the column width in VBA, use this code are reference:

Sub Learning()

Columns(“A:C”).ColumnWidth = 12

End Sub

[/fusion_text][fusion_separator style_type=”single|dashed” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” sep_color=”#008000″ top_margin=”20″ bottom_margin=”20″ border_size=”2″ icon=”” icon_circle=”” icon_circle_color=”” width=”” alignment=”center” /][/fusion_builder_column_inner][/fusion_builder_row_inner][fusion_builder_row_inner][fusion_builder_column_inner type=”1_1″ layout=”1_1″ spacing=”” center_content=”no” hover_type=”none” link=”” min_height=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” background_color=”” background_image=”” background_position=”left top” background_repeat=”no-repeat” border_size=”0″ border_color=”” border_style=”solid” animation_type=”” animation_direction=”left” animation_speed=”0.3″ animation_offset=”” last=”no” border_position=”all”][fusion_menu_anchor name=”a10″ class=”” /][fusion_text columns=”” column_min_width=”” column_spacing=”” rule_style=”default” rule_size=”” rule_color=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=””]

VBA Building Logic

VBA consists of several built-in operators and functions which can be used to build expressions and conditional logic. This includes mathematical, string, comparison and logical operators. So, when logical function and operators are combined into a series codes to evaluate a condition, it executes one set of code at a time, returns TRUE or FALSE as a result. Depending on the result, the following codes will continue to execute until it reaches the end of the conditional logic. Conditional statements are the If-Then with Else or ElseIf and the Select Case.

[/fusion_text][fusion_separator style_type=”single|dashed” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” sep_color=”#008000″ top_margin=”20″ bottom_margin=”20″ border_size=”2″ icon=”” icon_circle=”” icon_circle_color=”” width=”” alignment=”center” /][/fusion_builder_column_inner][/fusion_builder_row_inner][fusion_builder_row_inner][fusion_builder_column_inner type=”1_1″ layout=”1_1″ spacing=”” center_content=”no” hover_type=”none” link=”” min_height=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” background_color=”” background_image=”” background_position=”left top” background_repeat=”no-repeat” border_size=”0″ border_color=”” border_style=”solid” animation_type=”” animation_direction=”left” animation_speed=”0.3″ animation_offset=”” last=”no” border_position=”all”][fusion_menu_anchor name=”a11″ class=”” /][fusion_text columns=”” column_min_width=”” column_spacing=”” rule_style=”default” rule_size=”” rule_color=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=””]

VBA IF Statement

VBA IF Statement is the most commonly used conditional statement. It allows you to evaluate for a condition then performs an action depending on the result.

IF THEN ELSE Statement simply means: “IF a condition is met, THEN what happens? ELSE, what happens when it is not met?”

Syntax # 1:

IF condition THEN code_if_true ELSE code_if_false

*Note that above statement is a simple one-line IF THEN ELSE statement where you don’t need to use the END IF statement. And that the ELSE part is optional.

Syntax # 2:

IF condition THEN

code_if_true

ELSE

Code_if_false

END IF

*Note that the second syntax is helpful if you have multiple lines of code to execute if the condition returns TRUE. Essentially, you need to use the END IF statement when using this syntax to avoid a compile error.

Examples of VBA IF THEN Statement

For instance, you want to check if a product’s quantity (cell B1) is under or above the minimum stock limit (10) and displays the result in cell C1.

Example#1 – One-line code:

Sub Learning()

If Range(“B1”) > 10 Then Range(“C1”) = “Above Minimum Limit” Else Range(“C1”) = “Below Minimum Limit”

End Sub

Example#2 – Block of code:

Sub Learning()

If Range(“B1”) > 10 Then

Range(“C1”) = “Above Minimum Limit”

Else

Range(“C1”) = “Below Minimum Limit”

Range(“C1”).Interior.Color = vbRed

End If

End Sub

[/fusion_text][fusion_separator style_type=”single|dashed” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” sep_color=”#008000″ top_margin=”20″ bottom_margin=”20″ border_size=”2″ icon=”” icon_circle=”” icon_circle_color=”” width=”” alignment=”center” /][/fusion_builder_column_inner][/fusion_builder_row_inner][fusion_builder_row_inner][fusion_builder_column_inner type=”1_1″ layout=”1_1″ spacing=”” center_content=”no” hover_type=”none” link=”” min_height=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” background_color=”” background_image=”” background_position=”left top” background_repeat=”no-repeat” border_size=”0″ border_color=”” border_style=”solid” animation_type=”” animation_direction=”left” animation_speed=”0.3″ animation_offset=”” last=”no” border_position=”all”][fusion_menu_anchor name=”a12″ class=”” /][fusion_text columns=”” column_min_width=”” column_spacing=”” rule_style=”default” rule_size=”” rule_color=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=””]

Select Case Approach

Another logical function that you can use most specifically with three or more conditions is the Case Statement.

The Case statement performs the corresponding code for the first condition that is found to be TRUE.

Below is the syntax of Case statement:

Select Case test_expression

Case condition_1

Result_1

Case condition_2

Result_2

…

Case Else

Result_else

End Select

Once a condition is met, it will execute the corresponding code and will not evaluate the conditions any further.

Example code used in VBA:

Sub Learning()

Select Case Range(“A2”)

Case 90 To 100

Range(“B2”) = “A”

Case 70 To 90

Range(“B2”) = “B”

Case 60 To 70

Range(“B2”) = “C”

Case 50 To 60

Range(“B2”) = “D”

Case Else

Range(“B2”) = “No Score Found”

End Select

End Sub

The code returned “B” since score 75 falls under the case 70 to 90.

Second example, cell A2 is empty and as indicated in our Case Else, if no test_expression match any clause in all the other Case statements, then “No Score Found” will be the value in cell B2.

[/fusion_text][fusion_separator style_type=”single|dashed” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” sep_color=”#008000″ top_margin=”20″ bottom_margin=”20″ border_size=”2″ icon=”” icon_circle=”” icon_circle_color=”” width=”” alignment=”center” /][/fusion_builder_column_inner][/fusion_builder_row_inner][fusion_builder_row_inner][fusion_builder_column_inner type=”1_1″ layout=”1_1″ spacing=”” center_content=”no” hover_type=”none” link=”” min_height=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” background_color=”” background_image=”” background_position=”left top” background_repeat=”no-repeat” border_size=”0″ border_color=”” border_style=”solid” animation_type=”” animation_direction=”left” animation_speed=”0.3″ animation_offset=”” last=”no” border_position=”all”][fusion_menu_anchor name=”a13″ class=”” /][fusion_text columns=”” column_min_width=”” column_spacing=”” rule_style=”default” rule_size=”” rule_color=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=””]

Start To Automate With Loops

Loops plays an essential role in controlling and handling code execution of repetitive tasks. Looping executes code repetitively until the condition is found TRUE. It is an effective way of optimizing your code in terms of speed and accuracy. There are multiple methods to loop, such as:

For – Next

For – Each – Next

Do While Loop

Do Until Loop

While – Wend

[/fusion_text][fusion_separator style_type=”single|dashed” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” sep_color=”#008000″ top_margin=”20″ bottom_margin=”20″ border_size=”2″ icon=”” icon_circle=”” icon_circle_color=”” width=”” alignment=”center” /][/fusion_builder_column_inner][/fusion_builder_row_inner][fusion_builder_row_inner][fusion_builder_column_inner type=”1_1″ layout=”1_1″ spacing=”” center_content=”no” hover_type=”none” link=”” min_height=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” background_color=”” background_image=”” background_position=”left top” background_repeat=”no-repeat” border_size=”0″ border_color=”” border_style=”solid” animation_type=”” animation_direction=”left” animation_speed=”0.3″ animation_offset=”” last=”no” border_position=”all”][fusion_menu_anchor name=”a14″ class=”” /][fusion_text columns=”” column_min_width=”” column_spacing=”” rule_style=”default” rule_size=”” rule_color=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=””]

VBA For Loop

For loop is the most common loop in Excel VBA. This type of loop allows you to run the VBA code in a pre-determined number of times. Once all the statements have been executed, it goes back to the start of the structure, and that is why it is called a loop.

This is the syntax for For Loop:

Sub ()

For counter_variable = <start> to <end>

…statements…

Next [counter_variable]

End Sub

Sample code for your reference:

Sub Learning()

For iCtr = 1 to 10

Cells(iCtr, 1).value = “Hello”

Next iCtr

End Sub

When used in Excel VBA:

Looking at the image above, the statement between For and Next was executed 10 times. For iCtr = 1, enters the value “Hello” to Cells(iCtr, 1) which is cell A1. The code then reaches Next iCtr, and so it increments and goes back to the For statement. Now, our variable iCtr = 2, “Hello” is entered in the cell at the intersection of row 2 and column 1 (cell A2). The code executes until it reaches the end counter, which is 10, and that will be row 10, column 1 (cell A10).

[/fusion_text][fusion_separator style_type=”single|dashed” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” sep_color=”#008000″ top_margin=”20″ bottom_margin=”20″ border_size=”2″ icon=”” icon_circle=”” icon_circle_color=”” width=”” alignment=”center” /][/fusion_builder_column_inner][/fusion_builder_row_inner][fusion_builder_row_inner][fusion_builder_column_inner type=”1_1″ layout=”1_1″ spacing=”” center_content=”no” hover_type=”none” link=”” min_height=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” background_color=”” background_image=”” background_position=”left top” background_repeat=”no-repeat” border_size=”0″ border_color=”” border_style=”solid” animation_type=”” animation_direction=”left” animation_speed=”0.3″ animation_offset=”” last=”no” border_position=”all”][fusion_menu_anchor name=”a15″ class=”” /][fusion_text columns=”” column_min_width=”” column_spacing=”” rule_style=”default” rule_size=”” rule_color=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=””]

Combine Logic And Loops To Automate

Let’s start combining logic and loops to automate our Excel task. Here is one good yet simple example you can start practicing on:

Sub Learning()

Dim iCtr As Integer

For iCtr = 1 To 20

If Cells(iCtr, 1) = “Apple” Then

Cells(iCtr, 1).Interior.Color = vbYellow

End If

Next iCtr

End Sub

In this code, there is an IF statement between the For and Next statements. The IF statement evaluates the data and finds the value “Apple” in the list, and if found, colors the cell yellow. The counter 1 to 20 used as a variable for row number Cells(iCtr,1) indicates that it should loop from row 1 to 20 and then executes the statements that follow.

Here is how it should look like when added in VBA Editor.

[/fusion_text][fusion_separator style_type=”single|dashed” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” sep_color=”#008000″ top_margin=”20″ bottom_margin=”20″ border_size=”2″ icon=”” icon_circle=”” icon_circle_color=”” width=”” alignment=”center” /][/fusion_builder_column_inner][/fusion_builder_row_inner][/fusion_builder_column][fusion_builder_column type=”1_3″ layout=”1_3″ spacing=”” center_content=”no” link=”” target=”_self” min_height=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”side-spy” id=”” background_image_id=”” background_color=”” background_image=”” background_position=”left top” undefined=”” background_repeat=”no-repeat” hover_type=”none” border_size=”0″ border_color=”” border_style=”solid” border_position=”all” padding_top=”” padding_right=”” padding_bottom=”” padding_left=”” margin_top=”” margin_bottom=”” animation_type=”” animation_direction=”left” animation_speed=”0.3″ animation_offset=”” last=”no”][fusion_text columns=”” column_min_width=”” column_spacing=”” rule_style=”default” rule_size=”” rule_color=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=””]

Contents

[/fusion_text][fusion_text columns=”” column_min_width=”” column_spacing=”” rule_style=”default” rule_size=”” rule_color=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”bg-spy” id=””]

Excel VBA: Learn the basics in a few minutes

[/fusion_text][fusion_widget_area name=”avada-custom-sidebar-vba” title_size=”” title_color=”” background_color=”” padding_top=”” padding_right=”” padding_bottom=”” padding_left=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” /][/fusion_builder_column][/fusion_builder_row][/fusion_builder_container]

Blackjacks Rules: Learn the basics in a few minutes

For those who was looking tips to start blackjack find here all the rules you need to know.