Excel-VBA Solutions

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.

Getting started with Visual Basic Editor in Excel

In my earlier posts, I have explained lot of things on how to automate your excel workbooks with VBA. Also I explained how to do data mining from websites and download files automatically using VBA. However if you are new to Excel VBA, you may probably don't know how to get Visual Basic Editor in Excel 2013. So today I will show you how to open Visual Basic Editor in your Excel application and give some brief introduction. First open an excel workbook where you want to develop your VBA.

Then click on the "DEVELOPER" tab. You will see several categories like Code, Add-Ins etc under that tab. Go to the "Code" category and click on the "Visual Basic" Icon

Once you click on that icon the Visual Basic Editor will be open up.

There is an alternative way to go to the Visual Basic Editor very quickly. It is using short cut keys. Once you open the workbook just press Alt+F11. Visual basic editor will be open in the excel application.

So now I will explain few things about Visual Basic Editor. You can find the "Menu Bar" in the top.

You can see the "Tool Bar" below that.

As you can see there are two other windows open in the Visual basic editor. Top one is called "Project Explorer window"

Other one is called "Properties Window"

When you developing applications you may need an another important window. It is "Immediate" window. Just click on the "View" menu of the Menu bar. You will see a drop down list like below.

Then click on the Immediate Window. Immediate Window will be open at the bottom of the Visual basic editor. Also you can use short cut keys Ctrl+G to open that window.

This can be use for debugging the code.

Check whether folder exist and create if it is not

We can use VB scripts to manage our data. In this kind of applications we often need to get data from folders as well as save data to folders. This data may in any form like text files, XML files, Excel files etc. Some of our folders contains sub folders. Those sub folders can even have their sub folders. So a particular folder can have folders inside them in different levels. If we know the structure of those folders we can even communicate with folder which are located inside several folders.

Below code is a similar example used in a excel file. It check whether there is a folder call "Image Files" in the folder where the excel file is located. If there is no folder, It creates a one with that. If folder exist it tells that folder is exists.

If Dir(ThisWorkbook.Path & "/Image Files/", vbDirectory) = "" Then
MkDir ThisWorkbook.Path & "/Image Files/"

Else
MsgBox "Directory Exists", vbInformation, ""
End If