Subscribe to Software Autopsy        RSS Feed

Tutorial - Excel Programming

Icon Leave Comment
Using the Visual Basic Editor

I assume you read the previous post "Programming Concepts"

Programming is fun, it is more fun than recording macros. I compare programming to cooking becuase when you cook you cook for either yourself or for others. Same goes for programming you are writing a program for yourself or for some one else to use. For a program the ingridients are variables, statements, conditional statements, loops and all these are tied together with logic. Before we cook for others we or for ourself we make sure we know what exactly we want to cook, once we know for sure only then we begin the cooking, if we are not sure what to cook we order and again we order exactly what we want to. Or we let the person tell us what's on the menu or give us a suggestion and if we are impressed we place the order. If we are not impressed we go back to kitchen/fridge and check what's there and pick what we like. The whole process of software development is similar, sounds ok right? In the last post there were variables, loops, some functions etc. In this post we can see how we can tame excel.

Into the sea

Ok let lauch the Visual Basic Editor by pressing ALT + F11

Before getting into programming one or two things to remember:-

1) Before you write code make sure you have understood what is required from the program and making a flow chart helps a lot. Aslo writing down steps of things you want to do helps a lot, plus it would improve your handwriting if it is like mine.
2) While programming make sure that the variable you declare have appropriate names and indicate their prupose
3) The fucntion/procedures you write make sure they have nice and appropirate name and before you write code in them make sure to write comment block, which includes your name, what purpose the procedure/fucntion serve, date. If the fucntion/procedure has parameters, you could waire a small explanation about them too.
4) Properly indent the code, so that it is easy to read, and easy to maintain. Any one who looks at the code should be able to say "what's a beauty". Again jsut a reminder make sure that the code is appropriately commented.
5) It always helps to have a flow chart of the program.

First Program

"Hello world" is the first program that is written, actually it is kind of boaring so let us do some program that will emphasise on the above 5 points,which will show the first 4 points mentioned above ( I didn't write the flow chart)

To insert a module in which you want to write code, choose Module from the Insert Module. The default name is Module1, you can change it by selecting the module and pressing F4. We will see a bit complex example. In the example below we will use Inputbox,Mid,Instr and Msgbox function The name of the function is extractcharacters. A note about the need of this function. In a software that we developed quite some time back, there was a need to display both the name of the student and the registration which the user would select and it would have to be in this format student name -> student number . From this we needed only student number part to go into a database. In this following example I have added a validation to check if the characters afte "->" character is actually numeric. In the application that we deplyed, both the student name and registration number were populated into a list from the database, from which the end user could select by clicking the name.

Sub extractcharacter()
' Purpose :- This function is for extract all the characters before the "->" character in the given string
' Author:- Anand
' date :- 3rd December 2008

Dim dataInput As String ' This string contains the student name and registration number

dataInput = InputBox("Enter the student number and registration number in the format Name->registration number (no spaces)")

' First Check if the string is of valid format i.e. it has a "->" character
' and check if the input after the "->" is actually numeric or not.
' For that again using the combination of Mid and Instr. First get the postion of "->" character
' in the string,then mid fucntion starts from the second character from that, and the isnumeric function
' checks if the returned character is numeric or not
If InStr(1, dataInput, "->", vbTextCompare) <> 0 And _
IsNumeric(Mid(dataInput, InStr(1, dataInput, "->", vbTextCompare) + 2)) = True Then

' This needs a bit explanation Mid(dataInput,1,Instr(1,dataInput,"->",vbTextComapre)-1)
' Mid function is for obtaining characters from a specified position in a sting, and number of characters
' Instr fucntion is for obtaining the position of a specified character in the a string
' What happens when you combine these two. First the character "->" is searched, returning the potions of "->"
' Then Mid function kicks into action, it starts from the first character in the string and uses the position
' returned by Instr as the number of character that are to be extracted.

dataInput = Mid(dataInput, 1, InStr(1, dataInput, "->", vbTextCompare) - 1)


MsgBox "Not a valid format" ' display the message because it is not valid
Exit Sub
End If
MsgBox dataInput
End Sub

Input:-  John Doe -> 121526
output:- Emp_John Doe
Input:-  Jane Doe -> 121526
output:- Jane Doe
Input:- John Doe -> EMP_121456
output:- Not valid format

[i] In case you happen to tet the code and come accross any bugs in this, let me know, I could get a chance to fix the bug.

Working with Excel

Visual Basic Programming is fun becuase its logical arranged for example if you want to work with range, you have a range object, using which you can access all the properties and methods related to range, same thing applies to a sheet, a workbook, application.
The best reference for learning VBA is the help that comes wiht VB Editor in Excel, but one thing though you would need more examples.

One step further

The next step would be to start s simple program that will interact with a range, sheet, workbook, then we will go on to see how to switch betwenn workbooks etc.

First let us see a live example in which I had to valdiate the range of email addresses in Column A. A valid email address is of the foramt <some [email protected]>, without characters like "-","{","}","+","!","#","$","%","^","&","*","(",")","?","<",">" for starters.
We will use Select Case statements to achieve this. If the email is valid display "Valid" in the corresponding "B" or "Invalid" Column. Here is a bit background infromation on this program. They needed a mass mailer program, not spam, but survey requesting some information that a person/compay needed to fill out. The first requirement was to validate the email addresses, we shall see later how we send the email. In this code below we will see the email validation procedure directly, I have modified it a bit to show how to read a value in a range.
Sub validMail()
Dim emailAdd As String
Dim lastRow As Long
Dim cellCntr As Long


lastRow = Range("A65536").End(xlUp).Row

For cellCntr = 1 To lastRow

emailAdd = Range("A" & cellCntr).Value

If InStr(1, emailAdd, "{", vbcomparetext) <> 0 Or _
InStr(1, emailAdd, "}", vbcomparetext) <> 0 Or _
InStr(1, emailAdd, "(", vbcomparetext) <> 0 Or _
InStr(1, emailAdd, ")", vbcomparetext) <> 0 Or _
InStr(1, emailAdd, "!", vbcomparetext) <> 0 Or _
InStr(1, emailAdd, "#", vbcomparetext) <> 0 Or _
InStr(1, emailAdd, "$", vbcomparetext) <> 0 Or _
InStr(1, emailAdd, "%", vbcomparetext) <> 0 Or _
InStr(1, emailAdd, "^", vbcomparetext) <> 0 Or _
InStr(1, emailAdd, "&", vbcomparetext) <> 0 Or _
InStr(1, emailAdd, "*", vbcomparetext) <> 0 Or _
InStr(1, emailAdd, "+", vbcomparetext) <> 0 Or _
InStr(1, emailAdd, "=", vbcomparetext) <> 0 Or _
InStr(1, emailAdd, "<", vbcomparetext) <> 0 Or _
InStr(1, emailAdd, ">", vbcomparetext) <> 0 Or _
InStr(1, emailAdd, "?", vbcomparetext) <> 0 Or _
InStr(1, emailAdd, "/", vbcomparetext) <> 0 Or _
InStr(1, emailAdd, "\", vbcomparetext) <> 0 Or _
InStr(1, emailAdd, "|", vbcomparetext) <> 0 Or _
InStr(1, emailAdd, "[", vbcomparetext) <> 0 Or _
InStr(1, emailAdd, "]", vbcomparetext) <> 0 Or _
InStr(1, emailAdd, "#", vbcomparetext) <> 0 Or _
InStr(1, emailAdd, ".", vbcomparetext) = 0 _
Range("B" & cellCntr).Value = "invalid"
If Len(Mid(emailAdd, InStr(1, emailAdd, ".", vbTextCompare) + 1)) < 2 Or _
Left(Mid(emailAdd, InStr(1, emailAdd, "@", vbTextCompare) + 1), 1) = " " Or _
Left(Mid(emailAdd, InStr(1, emailAdd, "@", vbTextCompare) + 1), 1) = "." _
Range("B" & cellCntr).Value = "invalid"
Range("B" & cellCntr).Value = "Valid"
End If
End If
Next cellCntr
End Sub

Range("A" & cellCntr) needs a valid explanation I suppose. The simple explanation for that is in the loop it amounts to range("A" & cellCntr) would translater to

range("A1") for the first time
range("A2") for the second time
range("A3") for thet thrid time etc.

In the loop you can't hardcode the cell value , so you concatinate the row number with A.

Next step where do we go now?

Let us check another example to work with range. There is another live situation in which we needed to populate the values in another sheet if values in the columns "A", "B" and "C" of sheet1 matched a patteren, they needed to be pasted in sheet2

The code is simple. Again get the values of lastrow in A, and then continue

sub valuesSheet2()

dim lastRowS1
dim lastRowS2


for cntrCheck=2 to lastRow
if instr(1,range("A" & i).value,"CT55",vbcomparetext)<> 0 and _
instr(1,range("B" & i).value,"LLC",vbcomparetext) <> 0 _
instr(1,range("C" & i).value,"NTT",vbcomparetext) <> 0 _
range("A" & i &":C" & i).select
range("A" & i &":C" & i).copy


range("A" &lastRowS2+1).pasteSpecial

next cntrCheck

end sub

0 Comments On This Entry


March 2021

 1 2 3456

0 user(s) viewing

0 Guests
0 member(s)
0 anonymous member(s)

Recent Entries

Search My Blog