Split Function

             Split function is a very useful function in VBA. We can use this function to split a string in to sub-strings. Function will return these sub-strings in an array.

There are four parameters in this function

1. Expression
2. Delimiter
3. Limit
4. Compare

First parameter is required and other three are optional.

I will explain how to use this function by simple examples. Assume that we have a string like "a,b,c,d,e,f,g,h,i,j" and need to split this string at every occurrence of  ","
And then need to store those sub strings in a column.

So first we need to define a string array

Dim WrdArray() As String

This array will store the sub-strings once we split our input string.

And we need to define a string variable to hold our input string.

Dim text_string As String

Then we will assign our input string to that string.

text_string = "a,b,c,d,e,f,g,h,i,j"

After that we can use Split function as follows

WrdArray() = Split(text_string, ",")

Now sub-strings are stored in this WrdArray array. So what we need to do now is retrieve those sub strings from that array and store them in a column.
Below code segment will do that. Sub strings will stored in column A.

For i = LBound(WrdArray) To UBound(WrdArray)     Range("A" & i + 1) = WrdArray(i) Next i

Array we get (WrdArray)  is a zero-based, one dimensional array. So LBound(WrdArray) is equal to 0. That's why we used the line Range("A" & i + 1) in above code

And below is the complete VBA code of the above example.

Dim WrdArray() As String Dim text_string As String text_string = "a,b,c,d,e,f,g,h,i,j" WrdArray() = Split(text_string, ",") For i = LBound(WrdArray) To UBound(WrdArray)     Range("A" & i + 1) = WrdArray(i) Next i

If you run it, you will get  below result

In above example we used "," as our delimiter. What if we omit the delimiter string

Here is an example.

Dim WrdArray() As String Dim text_string As String text_string = "This is a example string." WrdArray() = Split(text_string) For i = LBound(WrdArray) To UBound(WrdArray)     Range("A" & i + 1) = WrdArray(i) Next i

We haven't use any delimiter here. So if we omit the delimiter, Split function will assume Space character (" ")  as delimiter.
So you will get the below result.

Third parameter of Split function is limit. Default value is -1. However if the limit is greater than zero, then the string will be divided in to sub-strings at first (Limit-1) occurrences. So the number of sub strings will be equal to the Limit. Below subroutine will give you clear idea about how it works.

Sub Example_Limit_Parameter() Dim WrdArray() As String Dim text_string As String text_string = "This/is/a/example/string." WrdArray() = Split(text_string, "/", 3) For i = LBound(WrdArray) To UBound(WrdArray)     Range("A" & i + 1) = WrdArray(i) Next i End Sub

Here is the result

Compare is the last parameter of the this function. It defines what kind of comparison should be done by the split function. It can have only two values. Either CompareMethod.Binary (0) or CompareMethod.Text (1).  You can use CompareMethod.Binary for case sensitive comparisons and CompareMethod.Text for case in-sensitive comparisons.

Insert an Image to Excel Worksheet using VBA

         Are you working with excel templates in your office life. Then you might able to automate some of your time consuming tasks with VBA. If you go through the posts of this blog you will find lot of resources to improve your workbooks. And if you are new to VBA start with this post.

Getting started with Visual Basic Editor in Excel

 In today's post I will explain how to insert images to your excel sheets and how to set their properties.

So let's begin with a simplest example. I have a worksheet with no data. Think I need to insert below image to this worksheet using a macro.

Actually we can do this with just one line of code.

Set myPicture = ActiveSheet.Pictures.Insert("C:\Users\Mel\Pictures\Photo-0625.jpg")

You should replace the image path of above code with relevant path of your image.
So here below is the result we get.

As you can see this is not in a very useful form. Because image is in it's original size. Also we should have the control to insert image where we need.

So we need to improve our code like below.

Set myPicture = ActiveSheet.Pictures.Insert("C:\Users\Mel\Pictures\Photo-0625.jpg") 'Setting picture properties With myPicture     .ShapeRange.LockAspectRatio = msoTrue     .Height = 250 ' Set your own size     .Top = Rows(2).Top     .Left = Columns(2).Left End With

Value of Left and Top define the place where this image will insert. Also image will be re-sized to the height we given in the code. Value of .ShapeRange.LockAspectRatio determine whether to keep aspect ratio or not.
Below is the result of above code.

And there might be situations, where we need to insert our image to a empty space located middle of some other content. In that case we can't keep our aspect ratio and we need to set height and width to match with that free space. But if the ratio of height and width we set have higher deviation from that of original image, this can have big impact on the appearance of the image. But small deviation won't be visible at all.

Below is the code where both height and width are defined.

Set myPicture = ActiveSheet.Pictures.Insert("C:\Users\Mel\Pictures\Photo-0625.jpg") 'Setting picture properties With myPicture     .ShapeRange.LockAspectRatio = msoFalse     .Height = 250     .Width = 300     .Top = Rows(2).Top     .Left = Columns(2).Left End With

And here is the result.

In this post I explained how to insert only one image to a excel sheet. But if we need to insert one image we can do it manually. So this method is useful only if we have lot of images to insert to our worksheet/worksheets. So I will explain how to insert several images to a worksheet with a click of a button in another post.

Is Letters Function - Check Whether a String Consist of Only Letters

             Today's post is about how to create a custom function to determine whether a string is only consists of letters or not. When we develop systems, sometimes we need to check this kind of properties of strings. It won't be the major part of the program, but it can be essential. 

After understanding this code you can even develop your own functions to determine the existence or non-existence of any character or characters.

So let's create our custom function. We are going to develop this function with the use of important table call ASCII table. Every character has an ASCII code. ASCII code is the numerical representation of the character. This ASCII table is available in lot of websites. You can easily find a table from internet.

If you carefully observe a ASCII table, you will find that ASCII code of A - Z is 65 - 90. And ASCII code of a - z is 97 - 122. 

That means

Asc(A)=65
Asc(B)=66
'''''''''
'''''''''
Asc(Z)=90

and 
Asc(a)=97
Asc(b)=98
'''''''''
'''''''''
Asc(z)=122

So below is our developed custom function base on this property of the letters.

Function IsLetters(Str As String) As Boolean Dim i As Integer For i = 1 To Len(Str)     Select Case Asc(Mid(Str, i, 1))         Case 65 To 90, 97 To 122             IsLetters = True         Case Else             IsLetters = False             Exit For     End Select Next i End Function

Here below is a detailed explanation about this custom function.

Function IsLetters(Str As String) As Boolean

Name of our function is IsLetters

Str As String

This means that parameters of our function should be string data type.

As Boolean

This part explains about the data type of return value. So the return value should be either true or false.

We use variable i to loop through each and every character of the string

For i = 1 To Len(Str)     ........     ........ Next i

When it loop through the each character Asc(Mid(Str, i, 1)) gives the ASCII value of each character.

And we have used Case statement to determine whether ASCII code of each character is belongs to ASCII code of A-Z or a-z. If ASCII code of every character in that string belongs to 65-90 and 97-122 then it will return true. If any character has value out side that range, IsLetters variable become false and will exit the for loop. Hence the return value of the function will be false.

Import Data from Word Table to Excel sheet

Today's lesson is about how to import data from word table to an Excel sheet.

Here is a sample table in a word document.

Once we run the macro, It will give below result.

Below is an example code to import first table of the word document to an Excel sheet.

Remember to add a reference to the Word-library.

Dim wrdApp As Word.Application Dim wrdDoc As Word.Document Set wrdApp = CreateObject("Word.Application") wrdApp.Visible = True Set wrdDoc = wrdApp.Documents.Open(ThisWorkbook.Path & "\My document.doc") 'Use below line if document is already open. 'Set wrdDoc = Documents("My document.doc") With wrdDoc     N_Of_tbles = .Tables.Count       If N_Of_tbles = 0 Then         MsgBox "There are no tables in word document"     End If       Set wrdTbl = .Tables(1)       ColCount = wrdTbl.Columns.Count     RowCount = wrdTbl.Rows.Count       ' Loop through each row of the table     For i = 1 To RowCount         'Loop through each column of that row         For j = 1 To ColCount             'This gives you the cell contents             Worksheets("sheet1").Cells(i, j) = wrdTbl.Cell(i, j).Range.Text         Next j     Next i End With Set wrdDoc = Nothing Set wrdApp = Nothing MsgBox "completed"

Here is a brief explanation about the code.

You can set the visibility of the word application by assigning true or false to wrdApp.Visible
As we have set that value true in our example it will show the Word application.

Set wrdDoc = wrdApp.Documents.Open(ThisWorkbook.Path & "\My document.doc")

This will open a word file "My document.doc" which is in the same folder as the Excel file which has this code. If your file is in a different location like D:\My files\My document.doc, Then you can open the file by following code.

Set wrdDoc = wrdApp.Documents.Open("D:\My files\My document.doc")

In above, our word document is in closed state. What if our word document is already open. If so we can use below code.

Set wrdDoc = Documents("My document.doc")

Tables.Count

This will tell you how many tables are in our word document.

Set wrdTbl = .Tables(1)

Here we only import data from 1st table.
But if we want to import data from all tables, then we can use for loop to import from all of them.

wrdTbl.Columns.Count wrdTbl.Rows.Count

above will count the number of columns and the rows of the table respectively.

And following part will put data of each cell of Word table to relavent Excel cell.

For i = 1 To RowCount     For j = 1 To ColCount         Worksheets("sheet1").Cells(i, j) = wrdTbl.Cell(i, j).Range.Text     Next j Nex i

Open an outlook email and populate the body of the email

             Today I'm going to explain you how to generate Outlook email and populate with the required details of a worksheet automatically using VBA.                                                          
Here below is a example worksheet. It contains ID1, Email Addess, First name and Data.
And there is a button call "E-mail" in that sheet. What we need to do is Once we click the button it should  ask us to enter row number of data we want to email. When we input that number and press OK, Message should be opened with required data.

So first we need to get the row number from the user.

Dim RowNo As Integer RowNo = InputBox("Please enter the row number you need to email:", "")

So it will show this input box.

Then we need to create the body of the email.

Dim WS As Worksheet Dim messageBody As String Set WS = ActiveSheet messageBody = "Dear " & WS.Range("C" & RowNo) & "," & vbCrLf & vbCrLf & _ "Your ID1: " & WS.Range("A" & RowNo) & vbCrLf & vbCrLf & _ "Your email address: " & WS.Range("B" & RowNo) & vbCrLf & vbCrLf & _ "Your data: " & WS.Range("D" & RowNo) & vbCrLf & vbCrLf & _ "Please store this information in a safe place." & vbCrLf & vbCrLf & _ "Kind regards," & vbCrLf & vbCrLf & _ "Paul"

So message will be constructed similar to below.

Finally we need to create the outlook mail. Below code will do that part.

Dim OutApp As Outlook.Application Dim objMsg As MailItem Set OutApp = CreateObject("Outlook.Application") Set objMsg = OutApp.CreateItem(olMailItem) With objMsg   .Body = messageBody   .Display End With

And here is the full code for this automation.

Dim RowNo As Integer RowNo = InputBox("Please enter the row number you need to email:", "") Dim WS As Worksheet Dim messageBody As String Set WS = ActiveSheet messageBody = "Dear " & WS.Range("C" & RowNo) & "," & vbCrLf & vbCrLf & _ "Your ID1: " & WS.Range("A" & RowNo) & vbCrLf & vbCrLf & _ "Your email address: " & WS.Range("B" & RowNo) & vbCrLf & vbCrLf & _ "Your data: " & WS.Range("D" & RowNo) & vbCrLf & vbCrLf & _ "Please store this information in a safe place." & vbCrLf & vbCrLf & _ "Kind regards," & vbCrLf & vbCrLf & _ "Paul" Dim OutApp As Outlook.Application Dim objMsg As MailItem Set OutApp = CreateObject("Outlook.Application") Set objMsg = OutApp.CreateItem(olMailItem) With objMsg   .Body = messageBody   .Display End With

SUMIF function

Today I will explain you how to use SUMIF function. It is much easier to explain this through an example. See below excel sheet.

This is a data set of a business. How it works is each product has its own row. If column B is empty that means they have not sold it yet. If column B has a value that means that they have sold that particular item.

So if we need to get Total Sales or Total Purchase we can use SUM function.

Think If we want to add up all  items that are unsold. That means if column B is empty, We need to  add up all the costs of those products, which is in column C.
So for example in row 5 there is a product call item 3. Cell B5 is blank. So we need to add the purchase price of that item in cell C5.
Row 9 also has same thing. B9 is blank so we need to add C5 with C9 and so on.

How do we do that?

We can use SUMIF function for situations like this. To calculate Unsold Inventory we can use below formula

==SUMIF(x,y,z)

x is the range we are looking.
y is the criteria we check
z is the range we sum

For above example the formula should be written as this

=SUMIF(B2:B20,"",C2:C20)

We check the range B2:B20 for empty cells. that means for "". If empty cell found we add the values in the range C2:C20.

How to show the Developer tab in Excel 2013

                     This post is for people who are new to VBA. If you are thinking of creating macros or writing VBA scripts then this should be the first step you should take.
When you open your MS Excel application, you will see that the Developer tab is not displayed by default. So you need to add Developer tab to the ribbon before write/read macros, use ActiveX/Form controls. This post will explain how to add this Developer tab step by step.

First open the excel application.

Then click on the blank workbook. This will generate a new workbook.

Once the new workbook created you can see that there are tabs like File, Home, Insert , Page Layout etc. But there is no Developer tab. So now click on the File tab. I have marked it by a red circle in the above image.

Then click on the options button. Once you click the button, Excel options dialog box will be opened.

On that dialog box, choose Customize Ribbon button.

Then you will see list of Main tabs in the right hand side of this dialog box. All the tabs are selected except Developer. Select it as well and click on OK button.

Developer tab will appear at the end.

Now you can run and record macros.