Visual Basic
Tutorial
Table of Contents
1. Variable and operator definitions
2. Working with rows and columns of a worksheet
3. Statements and expressions
4. Manipulating strings
VARIABLE AND OPERATOR DEFINITIONS
The following information will show you how to code variables and certain operators.
Here is how to declare variables in Visual Basic:
Dim strlen As Integer „Declaration of integer
Dim newname As String „Declaration of string
Dim isfound As Boolean „Declaration of Boolean
Public qdmfilename As String „Declaration of a public variable
Here is how to initialize the preceding variables:
strlen = 2
newname = "NEW PART NUMBER"
isfound = True
qdmfilename = “GOQ.xls"
Here are some simple operators in Visual Basic:
= „Equal
„Not equal
„Greater than
+ „Add/String Concatenation
- „Subtract
* „Multiply
/ „Divide
Some examples of these operators will be shown later.
WORKING WITH ROWS AND COLUMNS OF A WORKSHEET
The following will show you how to select cells and work with rows and columns.
Here are some useful commands:
Cells(row, column).Select „Selects the desired cell
Selection.Insert shift:=xlToRight „Shifts contents to the right
Rows(row).Select „Selects the desired row
Rows(row).Delete „Deletes desired row
Columns(column).Select „Selects the desired column
Columns(column).Delete „Deletes desired column
Selection.Copy „Copies whatever is selected
ActiveSheet.Paste „Pastes desired information
Windows(filename).Activate „Activates the file filename
Sheets(“Sheet1”).Select „Selects Sheet1 of the spreadsheet
Here is some example code to look at:
Rows(3).Select „Selects row 3
Selection.Copy „Copies row 3
Windows("Temp Dss Database.xls").Activate „Activates file
Sheets("Sales Force Decisions").Select „Selects that sheet
Rows(4).Select „Selects row 4
ActiveSheet.Paste „Pastes information
STATEMENTS AND EXPRESSIONS
The following will teach you how to write if statements and for and do loops.
Here is an if/else statement:
If (statement) Then
…
Else
…
End If
Here is a for and do loop
For x = 1 to 5
…
Next x
Do While (statement)
…
Loop
The following is example code:
If (Cells(rawrownum + 1, columnstart) = "INFORMATION" And _
Cells(rawrownum + 1, columnstart) = "WAS") Then
isFound = True
Else
salesservicerow = salesservicerow + 1
End If
For x = 1 To 5
Cells(salesservicerow, 1).Select
Selection.Insert shift:=xlToRight
Next x
Do While (Cells(salesservicerow, 1) = "")
Cells(salesservicerow, 1).Select
Selection.Delete shift:=xlToLeft
Loop
MANIPULATING STRINGS
This last section will show you how to work with strings. This is very important for our
project because we are dealing with text. Study this section thoroughly!
Here are some useful string commands:
Left(string, length) „Returns the leftmost characters
Len(string) „Returns the length of the string
LCase(string) „Makes the string all lowercase
LTrim(string) „Gets rid of preceding blank spaces
Mid(string, start, length) „Returns a section of the string
Right(string, length) „Returns the rightmost characters
RTrim(string) „Gets rid of proceeding blank spaces
Trim(string) „Gets rid of blank spaces
UCase(string) „Makes the string all uppercase
Here are examples of the functions above:
Left(“ Hello ”, 3) ” He”
Len(“ Hello ”) 7
LCase(“ Hello “) ” hello “
LTrim(“ Hello “) ”Hello “
Mid(“ Hello “, 3, 2) “el”
Right(“ Hello “, 4) “llo “
RTrim(“ Hello “) “ Hello”
Trim(“ Hello “) “Hello”
UCase(“ Hello “) “ HELLO “
Say we have the following string: “Team:Banana Computers City:Atlanta Quarter:5”.
The following code will extract the company name, “Banana Computers”. Here is the
code to extract this information:
textlen = Len(companyinfo) „Gets the length of the string.
colon1 = 4
colon = Mid(companyinfo, colon1, 1) „Will return “m” from the string
„Goes until it finds the first colon.
Do While (colon ":")
colon1 = colon1 + 1
colon = Mid(companyinfo, colon1, 1)
Loop
colon2 = colon1 + 1
tempstring = Mid(companyinfo, colon2, 4) „Will return “Atla” from the string.
„Goes until it finds “City”.
Do While (tempstring "City")
colon2 = colon2 + 1
tempstring = Mid(companyinfo, colon2, 4)
Loop
„This returns the company name.
company = Trim(Mid(companyinfo, colon1 + 1, colon2 - colon1 - 1))
Hopefully this will clear up some confusion for everybody. Study this thoroughly and
work through the example code to understand it.