If Then Else Statement Excel VBA

The Microsoft Excel VBA if then else statement is used to execute a set of code if specific condition is true or another set of code if it return false.

If then Else Statement Syntax:

If condition-1 Then
‘Set of codes
Else
Set of codes
End if

 

Example:

Sub empCheck()
'some codes
If empId = 25 Then
MsgBox "Emp 25 Found"
Else
MsgBox "Emp 25 Not Found"
End If
'some codes<
End Sub

In the above example we are checking if an empID is found or not. The line If empId = 25 Then
Checks does empId 25 is available. If found then message box will display which show Emp 25 Found else it will display message box Emp 25 Not Found.

Scrape the web using Microsoft Excel

You can use VBA to extract data from web pages, either as whole tables or by parsing the HTML source. The below example will shows you how to extract tables from web pages. If you want to get at tables of data published to a website such as stock market, currency exchange rates, weather forecast data etc, the easiest way to do it is by adding a linked table into Excel. Just follow the steps given below

Step 1: Select Data Ribbon -> Get External Data -> and click From Web.

scrape-web

Step 2: Enter the URL you want to scrape in the address bar and hit Go.

scrape-web-1 (1)

Step 3: You’ll see some little yellow arrow, click on them to get the content you want.

scrape-web-1 (2)

Step 4: After selection click Import, select the cell where you want to extraction to start and click OK to insert table into the spreadsheet.

Note: You can also record the entire process using Record a Macro and make use of it again.

Separate First Name and Last Name Using Excel

The example below will show you how to separate First name and last name in excel.

split-name-example

We can split the above example in two ways. Using Formula or Excel Text to Column (Delimited).

Using Text to Column (Delimited)

In the example you can see that all the last name and first name is separate by comma. Follow the steps given below to split the column name in to first name and last name.

Step 1 : Select the column or range in our case A2:A13 and insert in to B2:B13 and select it.

split-name-1 

Step 2 : Select Data Tab and under Data Tools click on Text to Columns. Now text to columns wizard dialog box will appear as shown below.

split-name-text-to-column

 

Step 3: Under choose the file type that best describe you data Select Delimited Option and click next.

split-name-text-to-column-1

Step 4 : Now Uncheck Tab and Check Comma and click next, again next and click finish

.split-name-text-to-column-2  split-name-pre-output

Step 5 : Now you can see the data is separated by last name and First name. If you want you can cut and paste first name before last name as shown below.

split-name-output

Using Formula

First Name - =RIGHT(A2,LEN(A2)-FIND(",",A2)-1
Last Name - =LEFT(A2,FIND(",", A2)-1) reduces to =LEFT(A2,6-1)

Print Header Column or Row on Every Page

Follow the steps given below to print label on every printed page.

Method:

1. Select the worksheet that you want to print

2. Select page layout tab and under page setup group click on Print Titles.

print-titles-page-title
3. Page setup dialog box will open as show given below

 

print-titles-page-setup

 

4. Under sheet tab you can find Rows to repeat on top and columns to repeat on left which you can select appropriate rows or columns that you want to print on each page.

Variable in Excel VBA

In this tutorial you are going to learn how to declare, initialize and display a variable in Excel VBA. A powerful feature of programming languages is the ability to store something in a variable so that the contents of the variable can be used or can be changed later in the procedure.

Declare a variable

The simplest way to declare a variable in VBA is by using Dim Statement. Please check syntax given below with example declaring two integer variable.

Syntax: Dim Variable name as Integer
Example: Dim x as integer, Dim y as Integer

In the above example we are telling the VBA that x and y are integer value and set sufficient memory.

variable-code

 

Different Data Types

There are different type of data type are available which we given some of the most use data type.

Integer, String, Double, Long, Date, Boolean etc.

 

Perform a simple Variable Test

Sub Perform_Variable_Test()
Dim x As Integer, y As Integer
x = 10
y = 20
MsgBox "The value of x is " & x & Chr(13) & "The value of y is " & y
End Sub

 

Run the above code and you will get below message box.

variable-msg

If you assign integer value as string for example change x=”Testing String”, you will receive run time error “Type Mismatch”.

variable-error

How to deleted entire row if a particular column has no value?

Question:

How to deleted entire row if a particular column has no value?

Answer:

You can do this in two ways, either using VBA or using Excel Filter.

Using Filter:

Step 1: Select the data range / column you want to perform.

filter

Step 2: A drop-down arrow that show the filter is enabled.

Step 3 : Select the dropdown, unselect “Select All” and select “Blank”

filter1

Step 4 : Now you will get only all the rows that is empty.

Step 5: Select the rows and press CTRL + num(-) to delete the rows.

Step 6 : Remove filter to get the final data.

filter2

 

 

Using VBA:

Select the column you want to delete blank rows and run the below code to delete all blank rows.

Sub DeleteBlank()
Dim N As Long, c As String
c = ""
N = Cells(Rows.Count, 1).End(xlUp).Row
For i = N To 1 Step -1
If Cells(i, ActiveCell.Column).Text = c Then
Cells(i, "C").EntireRow.Delete
End If
Next
End Sub

How to Split Cells before Number?

Question:

Hi, I am having a column of cells that each cell contains a name followed by some numbers. I want to split the cell into two, with one cell containing the journal name and the other cell containing the numbers.

For example, a cell might now contain “Rickson Gracie 56855A4585″

I want it to be split into ” Rickson Gracie” in one cell and “56855A4585″ in another.

In other words, I need it to split at the first numerical character.

Please help me.

 

Answer:

Please check our Post How to find position of First numeric in a string Excel? And make sure that you can able to access the formula.

split-number-text

Now please use below formula to split Name and numbers.

Name:

=TRIM(LEFT(A1,GetIndexFirstNumeric(A1)-1))

Number:

=MID(A1,GetIndexFirstNumeric(A1),LEN(A1)-GetIndexFirstNumeric(A1)+1)

How to find position of First numeric in a string Excel?

Find position of first number in a string can be achieved in two different ways, either by using combination of inbuilt formulas or by creating custom Formula. Now we had create a custom function to get index / position of first found numeric.

find-position-of-First-numeric-in-a-string

Please use the code block given below

Public Function GetIndexFirstNumeric(ByVal sVal As String) As Integer

	For i = 1 To Len(sVal)
		Dim sCharacter As String
		sCharacter = Mid(sVal, i, 1)
		If IsNumeric(sCharacter) = True Then
			GetIndexFirstNumeric = i
			Exit Function
		End If
	Next i
End Function

Copy and paste the above code into the module and after you can find the formula GetIndexFirstNumeric in the formula list.

Syntax: GetIndexFirstNumeric (Text as String)

Example : GetIndexFirstNumeric(A1)

Setup Visual Basic for Applications Development Environment

We can’t write VBA program or Macro in a standard worksheet. We have special Development Environment Called VBE “Visual Basic Editor”. There are few different ways you can open up VBA Editor in Excel. From the developer tab click Visual Basic to open VBE or Press CTRL + F11. Whichever method you choose you should see a screen like given below

VBE

On the first look it seems little difficult to deal with it. But more you work with it, will make your Excel life easier. There are few things you need to note about the Editor. Project Window, Properties & work Area. We can look in to each one on coming tutorial.

To add the code window to the setup, you just have to double click on the name of a component in the Project window (Sheet1, Sheet2, Sheet3 or This Workbook) and its code window will appears on work Area. See the screenshot given below

First Excel Macro Program

In the previous tutorial we learned basic of VBA and VBA editor. Now it time to get in to our first program. All code block in VBA should start and end in particular Way. Syntax given below

Sub name()

End Sub

Where sub is the subroutine and name is the name you want to give your program or code block. Let’s write a simple program which display message box with given message.

Sub firstProgram()

MsgBox "Welcome To HelpMeExcel Tutorial"

End Sub

first-program

Running the Program

To run the program you can simply click on the green “play” arrow in the Visual Basic toolbar. If you can’t see the VBA Toolbar then follow the steps, click on View Menu -> Toolbars and select the Standard option.

Macro-Run

Alternatively you can use Menu item Run -> Start or Press F5 to run the program. Now you should see a dialog box that gives you the choice of which function to run. Select firstProgram and click run to start the program.

Immediately you will get a message box with message “Welcome To HelpMeExcel Tutorial” show below

fst-msbox

Excel VBA Introduction

What is Excel VBA?

Many people don’t know that there is a powerful tool with each office product. That is VBA. VBA stands for Visual Basic for Applications. It’s a programming language that enables you to control just about everything in Microsoft Word, Excel, Access, PowerPoint, etc. The basic syntax and other construction of the VBA are very similar to Visual Basic 6. All Microsoft products such as excel having inbuilt Visual Basic Editor which you can use it to customize and extend the capabilities.

Getting started with Excel VBA

Before you can make a start, you need to add the Developer ribbon to the top of Excel.

Steps for Excel 2007

Click the round Office Button on the top left and then click Excel Options at the bottom of the menu. When you get the options dialog box, click on popular menu item on left and under the section labeled “Top options for working with Excel” check the box for “Show Developer tab in the Ribbon”:

Developer-Tab-2007

Steps for Excel 2010 & 2013

Click the File menu then select Options from the dialog box and click onCustomize Ribbon on the left side. From the right hand side you’ll then see an area called “Customize the Ribbon”. Under “Main Tabs” check the box for Developer.

Developer-Tab-2010

In order to run macros without any security warnings, you need to enable macros. If you are using Excel 2007 just follow the steps below

Click the round Office Button on the top left and then click Excel Options at the bottom of the menu. When you get the options dialog box, select Trusted Center menu item and click “Trusted Center Settings” under “Microsoft Office Excel Excel Trusted Center”.

Now click Macro Settings on Trusted center dialog box and select enable all macros option. Click picture given below

Excel-2007-Trusted-Center

NOTE: If you’re worried about macro security then you can always bring this box up again and disable the macros before you exit Excel.