Subscribe to Software Autopsy        RSS Feed
-----

Tutorials

Icon Leave Comment
Well this tutorials that I was asked to create at work, was supposed to be used , but it never saw the light of day. So here I am putting it here, so that at least it might be useful for someone. I have just written this from my programming experience in Visual Basic and VBA.

Programming Concepts

Before we get into programming let, see some basic programming concepts like variable, procedures, and functions.

Variable

This is the first thing we need to learn before we go anywhere. A variable is name just like humans have name. A variable is like the c containers that we use everyday, they can take any thing that we put in them, now we store sugar in them, later we may store salt and anything else we want. There are other types of containers that we specifically we mark that store only tea, sugar, soap powder, any soap powder for that matter but only soap powder. The same concept applies to programming; we can either store only specific type of values like integer, characters, or decimal values or we can store any type of value in the variable. In the same way as we identify the container, and decided to store contents into it, we can decided that x will store integer, y will store character etc. As the containers vary in capacity to store contents like 1 liter or 2 kgs, or 500 ml, variables also have their own limitation.

Variable Can store
Byte 0 to 255
Boolean True or False
Integer -32,768 to 32,767
Long -2,147,483,648 to 2,147,483,647
Currency -922,337,203,685,477.5808 to 922,337,203,685,477.5807
Single 3.402823E38 to -1.401298E-45 for negative values, and 1.401298E-45 to 3.402823E38 for positive values
Double -1.79769313486232E308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values
Date Date and Time
String Sequence of adjacent characters
Object any Object reference
Source:http://www.ozgrid.com/VBA/variables.htm


To declare a variable use the DIM statement, some examples for variable:

DIM stands for Dimension. To declare a variable the syntax is

Dim <variable name> as <the type of container> (let denote the container as data type)


Note When you name the variables, it should reflect the purpose it is serving and by looking at the name of the variable, it should be easy to understand why it has been declared.

Procedures, functions
Both procedures and functions are statements that carry out a certain task the only difference between them is tat procedures need not return a value, but whereas functions have to return a value. Procedures and functions are like delegation, you have a main program, the main program calls another program that does the work and returns the output. For example, let us take an ATM program as example. ATM displays the following information

1. Customer Information
2. Withdraw
3. Deposit
4. Manage Account
5. Exit
The user will select any of the above options. We can use different approaches for implementing this solution
1) Write the whole program as one program

Select option

If user choose 1 then
‘ execute statements
Otherwise if user choose 2 then
‘ execute statements
Otherwise if user choose 3 then

Otherwise if user choose 4 then
‘ execute statements
Otherwise if user choose 3 then
‘ quit program
No more options

2) Write the whole program as one program

Select option
If user choose 1 then
Goto customer information
Otherwise if user choose 2 then
Goto withdraw
Otherwise if user choose 3 then
Goto deposit
Otherwise if user choose 4 then
Goto manager account
Otherwise if user choose 3 then
‘ quit program
No more options

Instead of having, the main program does all the things.

3) Write the whole program as one program


Select option

If user choose 1 then
call customer information
Otherwise if user choose 2 then
call withdraw
Otherwise if user choose 3 then
call deposit
Otherwise if user choose 4 then
call manager account
Otherwise if user choose 3 then
‘ quit program
No more options

The above three approaches are different. The first approach would be write the entire in a single file in continuous statements one after another, in the second approach, the program shifts to different location in the same file, executes the statements and comes back to the main program. In the third approach, the program calls a different program, which does the processing and returns results to the main program. Same philosophy of delegation applies to programming


Writing procedure/function
To start writing a procedure/function the following syntax needs to be used
Sub <procedure name> (arguments)

End sub

Function <function name) (arguments) return type

End Function

What are parameters
We know that the computer is a very obedient slave. Let us say we want our slave to paint a room. If you simple say paint a room, he will just paint the room with the first available paint tin, it may be what we liked or we may not like it. Now suppose you want the slave to paint a room blue you tell him to apply blue paint to the room. Now suppose you want the salve to paint the living room blue, the kitchen yellow you will specify that. Now the blue, yellow are the conditions, considerations, or limitation for the slave to paint the room. The slave cannot choose any other paint other than blue for the living room, and yellow for the kitchen. Ok I do not know why I came up with yellow color for the kitchen, but let us just go with that for a while. The same logic applies to programs when you say add 2 and 4, the program cannot ad any random number; it will add only 2 and 4, or if you write a program to multiple the numbers given by the user, it will multiply the numbers the user inputs, unless you wrote the program wrong. Parameters instruct the program to use the values provided to execute the statements. For example

Add 5,6,7,8,9,10

Sub add(5,6,7,8,9,10)
Print 5+6+7+8+9+10
End sub
Output - 45


Life of variable

People are famous, some only in their house, some in their neighborhood, some all over the world. The life or scope of a variable is exactly same thing. Let us take 3 employees X, Y and Z. X is only known in his team, more than 2-3 teams know about Y, and Z is known all over the organization. In VBA, we have procedure, module, project, workbook or public module level variables. If a variable is declared in a procedure, it is known only in that procedure. If it is declared in, a module it is known only in that module, if it declared workbook/ project level it is know throughout the program. You can use either Public or Global to make a variable and make it available throughout the program. There is no difference between using public or global

Refer to this link (http://www.ozgrid.com/forum/showthread.php?t=26501)

Control Statements and loops
Imagine you have to execute a set of 100 statements, 200 you need help. That is where control statements and loops come to your rescue. To execute one statement many times or to execute many statements many times we can use loops. In VBA, you can use the following loops
1. For … Next loop
2. Do loop
3. While loop
4. For each next loop

For Loop
For loop is the simplest loop to write. The logic of for loop is to start from a value and do statements until the end value. Let us say you have a stack of 10 coins, start from the first coin and count every coin until you finish all the coins. For loop is written as follows:

For <counter variable>= <start value> to <stop value> step

Next <counter variable>



For iCounter=1 to 10 step 1
Print iCounter*iCounter*iCounter
Next iCounter
Out put – 1,8,27,64 … 100


The word step tells for loop how many values to increment, if a value is not specified, for loop increments the counter by one. You can use the Step when your start value is greater than the stop value. To count down you can write the for loop as follows




Do Loop
Let us take the same example of counting coins. You do not know how many coins you will get, but you have to count them until there are no more coins left in the stack. Two ways of doing it, first ask if there are any coins to count, if there are any coins, count them, the other way is to put your hand to the stack , if there are coins count them, other wise don’t count. Do loop can be implemented in to ways, first you can check for the condition, and then enter the loop, or first you enter the loop, and then check for condition. There is another slight variation for do loop, you can count until certain target is reached, like do count until you reach 100 Rupees. Do loop is written as follows

1st Do loop implementation

Do while something=some value
‘statements
Increment/decrement something
Loop

2nd Do loop implementation

Do
‘statements
Increment/decrement something

Loop while something=some value

3rd Do loop implementation

Do until something=some value
‘statements
Increment/decrement something
loop

One thing to remember while writing the do loop is that you have to make sure that there is a condition to terminate the loop, other wise the loop will continue to run forever.

Do While i <= 100
Print i
i = i + 1
Loop


While Loop
The while loop executes the statements until the condition is true, continuing the coin counting example, count until the coins are on the stack. There is not much difference between While Loop and Do Loop. A While Loop is written as follows

While <some thing> = <some value>
‘statements
Increment/decrement <some thing>
wend


i = 1
While i <= 100
Print i
i = i + 1
Wend
Output 1, 2, 3,… 100


Conditional Statements
At times, you have to run the statements code based on some rules or conditions. For example, let us say that the person filling the stack of coins tells you the number of coins on the stack. You are required to count only if the number of coins on the stack is more than five. VBA offers IF … THEN ... Else and Select Case statements.

If … Then … Else statement
We use the word if in our daily language. For example, a mother tells her child “if you finish you home-work by 8:30, you can watch TV, otherwise no TV for a week.” If the condition of some value we are checking is true or false or matches some value then execute a set of statements, otherwise execute other statements. A if statement is written as follows

If <condition>=<true/false>/<value> then
‘ statements
Elseif
‘ statements
end if


If newmsg = true then
i=i+1
else
Endif


The If … Then .. Elseif statement is similar to if statement. It is useful when you want the program to stop checking for more conditions, once the matching condition is satisfied

If newmsg = 1 then
i=i+1
elseif nwemsg=2 then
j=j+1
elseif nwemsg=3 then
k=k+1
endif


Select Case Statement
Select case statement is an alternative way of executing statements based on matching a certain condition. Select Case is an alternative to If condition when you have a large number of conditions to check. We can use case else if the variable does not match any of value we want to check.

Select Case Statement is written as follows


Select Case <variable>
Case <value>
‘ do something
Case <value>
‘ do something
Case else
End Select


Select case todayis

Case 1
selectedday = “Monday”
Case 2
Selectdeday= “Tuesday”
Case 3
selected= “Wednesday”
Case 4
selectedday = “Thursady”
Case 5
Selectdeday= “Friday”
Case 6
selectedday= “Saturday”
Case 7
Selectedday = “Invalid selection”
Output – Monday if it is 1, and so on.


Concepts of Objects
Human beings, tress, animals, plants and birds are all different elements of the world. Each of these can have common characteristics and individual characteristics. A human being is an element of this world having characteristics gender, two legs, hands, eyes, ears, a nose, a head, height, weight, skin color, race, origin, age, etc., each individual human being have these common characteristics, and have individual characteristics like name, behavioral traits. To describe a person whose name Sam, gender is woman, 5’7’’ tall, skin color fair, race Red Indian, origin Red Indian, 27 years etc. Here person is the object and name, gender, height, color, race etc are properties of human whom we describe. In VBA if we want to denote this we would use person.name= “Sam”. The general notation in VBA is “object.property=value”. In excel we have lot of objects, and each of these objects have some properties that can be read and changed.
In Excel or any of the MS office application there is an object browser, which lists all the objects and their properties, functions, methods and properties. To launch the object browser press “F2”
Module
The module is where all the action takes place. When you record a macro, excel internally creates a module. In a module you can declare variables, write procedures and functions. In modules, you can write small code to accept some values from the user and display some output. We will see some functions to accept input from user and display output.
InputBox
An input box is a function let accept a value from user. The syntax of inputbox is as follows
<some variable>=inputbox (prompt, title, default value, X position, Y position)
Here prompt is the some text you want to display to the user so that he/she can know what to enter, title is the inputbox title, default value is which some variable will take when the use doesn’t enter a value, x and y position are the screen coordinates where the inputbox is displayed.
Messagebox function
A message box is used to alert the user about some error, confirm an action or display some values.
String functions
A string is a set of characters. There are certain situations in which you want to check if text entered by the user has certain characters or not. In case you taking the user’s email address you want to make sure that it is actually of the correct format of an e-mail address, or you want to remove extra spaces, or you want know if a certain string starts with some particular character and you want to do something based on that. VBA offers string functions namely Left, Right, Mid, Instr, Strcomp, and Trim.

Left and Right
Using left function, you can get a specified number of characters from the left of a string say first three characters or five characters or say three characters. For example, the users their user names prefix which determines the type of their account with the system. To login them you need to check the first three characters of the login name and grant them access to the system. Let us consider that we are storing the username entered in a variable called username. We will use inputbox function to accept the username from the user and use msgbox function to display the first three characters of what the user entered.

Dim fThreeCharacters
fThreeCharacters=inputbox(“Enter user name”, “User Name”)
Msgbox left(username,3)
Output – if the username is entered as prn, user1, the output is prn.

Mid and Instr
Mid function is to extract a specified numbers of characters starting from a position. When you want to return the position of the string, in a string you should use Instr function.
The Mid function is written as below:

<some variable>=mid (<the string>, <where you want to start>, <how many characters>)

For example, you want to extract five characters from the seventh character in the username.

Dim extracted
Username=inputbox(“Enter user name”, “user name”)
extracted=mid(username, 7,5)
Msgbox extracted
Output – if the input is


The Instr function is written as below:

<some variable>=Instr (<from where you want to start the compare>, <the string>, <what you want to look for in the string>, <how you want to compare the match>).


The <how you want to compare> option tells the Instr to use which method to compare the two strings. The options available are Binary, Text and Database. When you use Binary method, the binary values of the strings compared. If the Text option is used, the strings are compared as strings itself.

Let us continue the example from the Left and Right functions we saw. Let us consider that the user will enter three characters code, which can be any where in the string depending on the instructions of the day. Let us presume that the code is constant one, but the location of the code in the username is not constant.

Dim usercode
tmp1 = InputBox("enter string 1")

MsgBox InStr(1, tmp1, "let me in", vbBinaryCompare) & " result using binary"
MsgBox InStr(1, tmp1, "let me in", vbTextCompare) & " result using binary"

Strcomp

Strcomp is a string comparison function.

<some variable>=strcomp (<first string>, <second string>, <how you want to compare the match>).


Dim usercode
tmp1 = InputBox("enter string 1")

MsgBox strcomp(“let me in”, tmp1,vbcomparetext)


Len function
Len function returns the length of a string or a variable.
Len (<string/variable>)



Trim function

At times, it so happens that when the user inputs values he might enter strings which have spaces in front of the values or at the end of the value. To remove the extra spaces from the Left we use LTrim, and to remove the space from the right we use RTrim, we want to remove from both side we user Trim function. Trim function is very simple function. It is written as follows:

Trim (<the string you want to trim)>


Val

Val function converts a text to numeric value.

Date and Time
You want to check the date and time, Date function will return the system date, and time will return the system time. Now function will return both system date and time.
Format

Format function is very useful to convert date and time to a different format than the system default date and time format. Format function is written as follows
<some variable>=format (<time/date>, <the format you want>, <first day of the week>, <first week of the year>)

Here the format you want use can be a combination of ‘d’, ‘m’ , ‘y’ for date, ‘h’, ‘m’, ‘s’ for time. Where ‘d’ is day, ‘m’ is for month, ‘y’ is for year, ‘h’ is for hours, ‘m’ is for minutes, ‘s’ is for seconds.

returnformat=format(date,“dddd,mmmm,yyyy”)
Msgbox returnformat


Dateadd and Datediff

Dateadd function is used to add or subtract a specified number of day(s), or month(s) or year(s) to a specified date. You and add a positive number to add, and a negative number to subtract. Datediff is a function to subtract dates. The parameters for adding the date is same as format function.

<some variable>=dateadd (<what you want to add >,<+/- number of day(s)/<number of months>/<number of year(s)>, <the date to which you want to add >)


Msgbox dateadd(“m”, 5,date)
Msgbox dateadd(“m”,-5,date)


The user form part will come as part of another blog, becuase It needs to have screen shots
I hope this was useful so far.

One data type that requires mention is the user defined data type. By using Type... End Type statement you can crate a data type, it is similar to Structure in C

Type <type name>

data types

End type

Eg

Type empdetails

dim empname as string
dim empslo as integer
dim empDOB as date

End type

To use it decalre a varibale of the type ans use it.
dim details as empdetails

details.empname="John"
details.empslo=007
details.DOB="Jan 08 1977"

0 Comments On This Entry

 

January 2021

S M T W T F S
     12
3456789
10111213141516
17181920212223
24 252627282930
31      

0 user(s) viewing

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

Recent Entries

Search My Blog