I wrote this Visual Basic code from scratch yesterday. It works pretty well. It is for my NPDES project. All of the storm water structures in the county that fall within the Chicago MSA area will be surveyed with GPS.

When each point is collected, the field person will also enter into the GPS unit:
- each pipe that enters that structure
- how many inches in diameter each pipe is
- which compass direction each pipe is heading

As the data comes back into the office I need to connect the structures to represent the pipes. This code will put the pipe measurements around the point on the screen in the approximate compass heading. It will look something like this:

48     -    -
-     ·     -
-     -    48

In this example there is a 48" pipe coming into the structure on the NW side and the SE side. The dot in the middle represents the GPS location of the surveyed feature.This visual representation will make it easier for me to validate the accuracy of the data.

The end result of all this will be a network showing how the stormwater is drained out of the county. It is an EPA requirment. In case of a chemical accident they will use this information to see which areas downstream are at risk, and as a diagnostic tool to find out the source of the spill.

Here is the code:

option explicit

Function FindLabel ([Pipe1Size], [Pipe1Dir], [Pipe2Size], [Pipe2Dir], _
[Pipe3Size], [Pipe3Dir], [Pipe4Size], [Pipe4Dir], [Pipe5Size], [Pipe5Dir],_
[Pipe6Size], [Pipe6Dir], [Pipe7Size], [Pipe7Dir], [Pipe8Size], [Pipe8Dir])

dim N, NE, E, SE, S, SW, W, NW, PipeDir, PipeSize
N = "-"
NE = "-"
E = "-"
SE = "-"
S = "-"
SW = "-"
W = "-"
NW = "-"

PipeDir = [Pipe1Dir]
PipeSize = [Pipe1Size]
if PipeDir >23 and PipeDir <= 68 then
NE = AssignValue(PipeSize, NE)
elseif PipeDir >68 and PipeDir <= 113 then
E = AssignValue(PipeSize, E)
elseif PipeDir >113 and PipeDir <= 158 then
SE = AssignValue(PipeSize, SE)
elseif PipeDir >158 and PipeDir <= 203 then
S = AssignValue(PipeSize, S)
elseif PipeDir >203 and PipeDir <= 248 then
SW = AssignValue(PipeSize, SW)
elseif PipeDir >248 and PipeDir <= 293 then
W = AssignValue(PipeSize, W)
elseif PipeDir >293 and PipeDir <= 338 then
NW = AssignValue(PipeSize, NW)
elseif PipeDir >338 and PipeDir <= 360 or PipeDir >= 0 and PipeDir <= 23 then
N = AssignValue(PipeSize, N)
end if

PipeDir = [Pipe2Dir]
PipeSize = [Pipe2Size]
if PipeDir >23 and PipeDir <= 68 then
NE = AssignValue(PipeSize, NE)
elseif PipeDir >68 and PipeDir <= 113 then
E = AssignValue(PipeSize, E)
elseif PipeDir >113 and PipeDir <= 158 then
SE = AssignValue(PipeSize, SE)
elseif PipeDir >158 and PipeDir <= 203 then
S = AssignValue(PipeSize, S)
elseif PipeDir >203 and PipeDir <= 248 then
SW = AssignValue(PipeSize, SW)
elseif PipeDir >248 and PipeDir <= 293 then
W = AssignValue(PipeSize, W)
elseif PipeDir >293 and PipeDir <= 338 then
NW = AssignValue(PipeSize, NW)
elseif PipeDir >338 and PipeDir <= 360 or PipeDir >= 0 and PipeDir <= 23 then
N = AssignValue(PipeSize, N)
end if

PipeDir = [Pipe3Dir]
PipeSize = [Pipe3Size]
if PipeDir >23 and PipeDir <= 68 then
NE = AssignValue(PipeSize, NE)
elseif PipeDir >68 and PipeDir <= 113 then
E = AssignValue(PipeSize, E)
elseif PipeDir >113 and PipeDir <= 158 then
SE = AssignValue(PipeSize, SE)
elseif PipeDir >158 and PipeDir <= 203 then
S = AssignValue(PipeSize, S)
elseif PipeDir >203 and PipeDir <= 248 then
SW = AssignValue(PipeSize, SW)
elseif PipeDir >248 and PipeDir <= 293 then
W = AssignValue(PipeSize, W)
elseif PipeDir >293 and PipeDir <= 338 then
NW = AssignValue(PipeSize, NW)
elseif PipeDir >338 and PipeDir <= 360 or PipeDir >= 0 and PipeDir <= 23 then
N = AssignValue(PipeSize, N)
end if

PipeDir = [Pipe4Dir]
PipeSize = [Pipe4Size]
if PipeDir >23 and PipeDir <= 68 then
NE = AssignValue(PipeSize, NE)
elseif PipeDir >68 and PipeDir <= 113 then
E = AssignValue(PipeSize, E)
elseif PipeDir >113 and PipeDir <= 158 then
SE = AssignValue(PipeSize, SE)
elseif PipeDir >158 and PipeDir <= 203 then
S = AssignValue(PipeSize, S)
elseif PipeDir >203 and PipeDir <= 248 then
SW = AssignValue(PipeSize, SW)
elseif PipeDir >248 and PipeDir <= 293 then
W = AssignValue(PipeSize, W)
elseif PipeDir >293 and PipeDir <= 338 then
NW = AssignValue(PipeSize, NW)
elseif PipeDir >338 and PipeDir <= 360 or PipeDir >= 0 and PipeDir <= 23 then
N = AssignValue(PipeSize, N)
end if

PipeDir = [Pipe5Dir]
PipeSize = [Pipe5Size]
if PipeDir >23 and PipeDir <= 68 then
NE = AssignValue(PipeSize, NE)
elseif PipeDir >68 and PipeDir <= 113 then
E = AssignValue(PipeSize, E)
elseif PipeDir >113 and PipeDir <= 158 then
SE = AssignValue(PipeSize, SE)
elseif PipeDir >158 and PipeDir <= 203 then
S = AssignValue(PipeSize, S)
elseif PipeDir >203 and PipeDir <= 248 then
SW = AssignValue(PipeSize, SW)
elseif PipeDir >248 and PipeDir <= 293 then
W = AssignValue(PipeSize, W)
elseif PipeDir >293 and PipeDir <= 338 then
NW = AssignValue(PipeSize, NW)
elseif PipeDir >338 and PipeDir <= 360 or PipeDir >= 0 and PipeDir <= 23 then
N = AssignValue(PipeSize, N)
end if

PipeDir = [Pipe6Dir]
PipeSize = [Pipe6Size]
if PipeDir >23 and PipeDir <= 68 then
NE = AssignValue(PipeSize, NE)
elseif PipeDir >68 and PipeDir <= 113 then
E = AssignValue(PipeSize, E)
elseif PipeDir >113 and PipeDir <= 158 then
SE = AssignValue(PipeSize, SE)
elseif PipeDir >158 and PipeDir <= 203 then
S = AssignValue(PipeSize, S)
elseif PipeDir >203 and PipeDir <= 248 then
SW = AssignValue(PipeSize, SW)
elseif PipeDir >248 and PipeDir <= 293 then
W = AssignValue(PipeSize, W)
elseif PipeDir >293 and PipeDir <= 338 then
NW = AssignValue(PipeSize, NW)
elseif PipeDir >338 and PipeDir <= 360 or PipeDir >= 0 and PipeDir <= 23 then
N = AssignValue(PipeSize, N)
end if

PipeDir = [Pipe7Dir]
PipeSize = [Pipe7Size]
if PipeDir >23 and PipeDir <= 68 then
NE = AssignValue(PipeSize, NE)
elseif PipeDir >68 and PipeDir <= 113 then
E = AssignValue(PipeSize, E)
elseif PipeDir >113 and PipeDir <= 158 then
SE = AssignValue(PipeSize, SE)
elseif PipeDir >158 and PipeDir <= 203 then
S = AssignValue(PipeSize, S)
elseif PipeDir >203 and PipeDir <= 248 then
SW = AssignValue(PipeSize, SW)
elseif PipeDir >248 and PipeDir <= 293 then
W = AssignValue(PipeSize, W)
elseif PipeDir >293 and PipeDir <= 338 then
NW = AssignValue(PipeSize, NW)
elseif PipeDir >338 and PipeDir <= 360 or PipeDir >= 0 and PipeDir <= 23 then
N = AssignValue(PipeSize, N)
end if

PipeDir = [Pipe8Dir]
PipeSize = [Pipe8Size]
if PipeDir >23 and PipeDir <= 68 then
NE = AssignValue(PipeSize, NE)
elseif PipeDir >68 and PipeDir <= 113 then
E = AssignValue(PipeSize, E)
elseif PipeDir >113 and PipeDir <= 158 then
SE = AssignValue(PipeSize, SE)
elseif PipeDir >158 and PipeDir <= 203 then
S = AssignValue(PipeSize, S)
elseif PipeDir >203 and PipeDir <= 248 then
SW = AssignValue(PipeSize, SW)
elseif PipeDir >248 and PipeDir <= 293 then
W = AssignValue(PipeSize, W)
elseif PipeDir >293 and PipeDir <= 338 then
NW = AssignValue(PipeSize, NW)
elseif PipeDir >338 and PipeDir <= 360 or PipeDir >= 0 and PipeDir <= 23 then
N = AssignValue(PipeSize, N)
end if

FindLabel = MakeLabel(NE, E, SE, S, SW, W, NW, N)

End Function

' ********** End of main function. Custum functions used above are below.

function MakeLabel (NE, E, SE, S, SW, W, NW, N)
dim Row1Cnt, Row2Cnt, Row3Cnt, MaxLine, _
TopSpace, MidSpace, BottomSpace, TopExtra, BottomExtra

Row1Cnt = len(NW & N & NE)
Row2Cnt = len(W & E)
Row3Cnt = len(SW & S & SE)

' If a row has an odd number of characters, this adds an extra space to make it even
if Row1Cnt mod 2 <> 0 and Row1Cnt > 3 then
TopExtra = " "
else TopExtra = ""
end if

if Row3Cnt mod 2 <> 0 and Row3Cnt > 3 then
BottomExtra = " "
else BottomExtra = ""
end if

' The next two if statements finds how long the longest line is
if Row1Cnt > Row2Cnt then
MaxLine = Row1Cnt
else MaxLine = Row2Cnt
end if

if Row3Cnt > MaxLine then
MaxLine = Row3Cnt
end if

' This makes sure that the longest line will have on space between each section
MaxLine = MaxLine + 2

TopSpace = space(int((MaxLine - Row1Cnt)/2))
MidSpace = space(int((MaxLine - Row2Cnt)))
BottomSpace = space(int((MaxLine - Row3Cnt)/2))

MakeLabel = NW & TopSpace & N & TopSpace & TopExtra & NE & vbNewLine & _
W & MidSpace & E & vbNewLine & _
SW & BottomSpace & S & BottomSpace & BottomExtra & SE
end function

' **********

function AssignValue(PipeSize, DirVar)
' If one of the compass point variables already has a value
' assigned to it, this will append the value rather than overwriting
' it. If there is no value it will just write the value over the
' default dash character.

if DirVar = "-" then
AssignValue = PipeSize
else AssignValue = DirVar & "/" & PipeSize
end if
end function

' ********** End of code