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
Set of codes
If empId = 25 Then
MsgBox "Emp 25 Found"
MsgBox "Emp 25 Not Found"
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.
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.
Step 2: Enter the URL you want to scrape in the address bar and hit Go.
Step 3: You’ll see some little yellow arrow, click on them to get the content you want.
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.
The example below will show you how to separate First name and last name in excel.
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.
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.
Step 3: Under choose the file type that best describe you data Select Delimited Option and click next.
Step 4 : Now Uncheck Tab and Check Comma and click next, again next and click finish
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.
First Name - =RIGHT(A2,LEN(A2)-FIND(",",A2)-1
Last Name - =LEFT(A2,FIND(",", A2)-1) reduces to =LEFT(A2,6-1)
Follow the steps given below to print label on every printed page.
1. Select the worksheet that you want to print
2. Select page layout tab and under page setup group click on Print Titles.
3. Page setup dialog box will open as show given below
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.
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.
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
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
Run the above code and you will get below message box.
If you assign integer value as string for example change x=”Testing String”, you will receive run time error “Type Mismatch”.
How to deleted entire row if a particular column has no value?
You can do this in two ways, either using VBA or using Excel Filter.
Step 1: Select the data range / column you want to perform.
Step 2: A drop-down arrow that show the filter is enabled.
Step 3 : Select the dropdown, unselect “Select All” and select “Blank”
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.
Select the column you want to delete blank rows and run the below code to delete all blank rows.
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
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.
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.
Now please use below formula to split Name and numbers.
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.
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
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)
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
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
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.
MsgBox "Welcome To HelpMeExcel Tutorial"
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.
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
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”:
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.
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
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.