 Register FAQ Search Today's Posts Mark Forums Read

#1
 Denko  Windows 10 Office 2019 Novice Join Date: Jun 2019 Posts: 1  Factoring code

These functions look so big and unruly. How do you even begin to slim this down into something lightweight and sleek?
Code:
```Function AddressFriend(rawdress As String, Optional fancy As Integer, Optional ztype As Boolean = False, Optional delim As String = "+", Optional lines As Integer = 0) As String
Dim datahousen() As String
Dim zip As String
datahousen = Split(rawdress, ",", 10, vbTextCompare)

'Get them commas right
'slot
If UBound(datahousen) = 3 Then
ReDim Preserve datahousen(4)
datahousen(4) = datahousen(3)
datahousen(3) = datahousen(2)
datahousen(2) = datahousen(1)
datahousen(1) = ""
ElseIf UBound(datahousen) < 3 Then
AddressFriend = "!!! [not enough commmas, Jack.]"
Exit Function
ElseIf UBound(datahousen) > 4 Then
AddressFriend = "!!! [Too many commas, pal.]"
Exit Function
Else
End If

'if you wanted it fancy, all you had to do was ask. we can keep it uppercase too
If fancy = 0 Then
For i = 0 To 4
datahousen(i) = StrConv(Trim(datahousen(i)), vbUpperCase)
Next i
ElseIf fancy = 1 Then
For i = 0 To 4
If i = 3 Then
datahousen(i) = StrConv(Trim(datahousen(i)), vbUpperCase)
Else
datahousen(i) = StrConv(Trim(datahousen(i)), vbProperCase)
End If
Next i
ElseIf fancy = 2 Then
For i = 0 To 4
datahousen(i) = StrConv(Trim(datahousen(i)), vbLowerCase)
Next i
End If

'XYZ, you know what that meeeaaanns
If Len(datahousen(4)) < 5 Then
ElseIf Len(datahousen(4)) = 9 And ztype = False Then
datahousen(4) = Left(datahousen(4), 5) & "-" & Right(datahousen(4), 4)
ElseIf Len(datahousen(4)) = 9 And ztype = True Then
datahousen(4) = Left(datahousen(4), 5)
ElseIf Len(datahousen(4)) = 5 Then
ElseIf Len(datahousen(4)) = 6 Then
AddressFriend = "!!! ZIP [That's a postal code, eh?]"
Exit Function
Else
AddressFriend = "!!! ZIP [Too long, too short, idk. needs to be 5 or 9 chars.]"
Exit Function
End If

'then we check to see if addr2 actually exists and try to present everything delimited in the manner you've requested
If datahousen(1) = "" Then
If lines = 0 Or lines = 1 Then
AddressFriend = datahousen(0) & delim & datahousen(2) & ", " & datahousen(3) & " " & datahousen(4)
ElseIf lines = 2 Then
AddressFriend = datahousen(0) & delim & delim & datahousen(2) & delim & datahousen(3) & delim & datahousen(4)
End If
Else
If lines = 0 Then
AddressFriend = datahousen(0) & ", " & datahousen(1) & delim & datahousen(2) & ", " & datahousen(3) & " " & datahousen(4)
ElseIf lines = 1 Then
AddressFriend = datahousen(0) & delim & datahousen(1) & delim & datahousen(2) & ", " & datahousen(3) & " " & datahousen(4)
Else
AddressFriend = datahousen(0) & delim & datahousen(1) & delim & datahousen(2) & delim & datahousen(3) & delim & datahousen(4)
End If
End If
End Function

Function AddressIso(rawdress As String, part As String, Optional extra As Integer = 0) As String
Dim datahousen() As String
Dim zip As String
datahousen = Split(rawdress, ",", 10, vbTextCompare)

'Get them commas right
'slot
If UBound(datahousen) = 3 Then
ReDim Preserve datahousen(4)
datahousen(4) = datahousen(3)
datahousen(3) = datahousen(2)
datahousen(2) = datahousen(1)
datahousen(1) = ""
ElseIf UBound(datahousen) < 3 Then
AddressIso = "!!! [not enough commmas, Jack.]"
Exit Function
ElseIf UBound(datahousen) > 4 Then
AddressIso = "!!! [Too many commas, pal.]"
Exit Function
Else
End If

'if you wanted it fancy, all you had to do was ask. we can keep it uppercase too
If extra = 0 Then
For i = 0 To 4
datahousen(i) = StrConv(Trim(datahousen(i)), vbUpperCase)
Next i
ElseIf extra = 1 Then
For i = 0 To 4
If i = 3 Or i = 4 Then
datahousen(i) = StrConv(Trim(datahousen(i)), vbUpperCase)
Else
datahousen(i) = StrConv(Trim(datahousen(i)), vbProperCase)
End If
Next i
ElseIf extra = 2 Then
For i = 0 To 4
datahousen(i) = StrConv(Trim(datahousen(i)), vbLowerCase)
Next i
End If

Select Case StrConv(part, vbLowerCase)
Case "a1", 1
Case "a2", 2
Case "city", 3
Case "state", 4
Case "zip", 5
If Len(datahousen(4)) = 9 And extra = 0 Then
AddressIso = Left(datahousen(4), 5) & "-" & Right(datahousen(4), 4)
ElseIf Len(datahousen(4)) = 9 And extra = 1 Then
ElseIf Len(datahousen(4)) = 5 Then
ElseIf Len(datahousen(4)) = 6 Then
AddressIso = "!!! ZIP [That's a postal code, eh?]"
Exit Function
Else
AddressIso = "!!! ZIP [Too long, too short, idk. needs to be 5 or 9 chars.]"
Exit Function
End If
End Select

End Function```

#2 Pecoflyer  Windows 7 64bit Office 2010 64bit Expert Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,438       Hi
perhaps explain what you want to achieve ?

 Thread Tools Show Printable Version Email this Page Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Similar Threads Thread Thread Starter Forum Replies Last Post walber Excel Programming 0 02-20-2017 11:24 AM Help with Code AJacLo Excel Programming 2 02-19-2017 03:57 PM VBA Code to search for field codes with certain text before the Field code and to change style welcometocandyland Word VBA 4 02-08-2017 06:53 PM Need Help with Below Code rsrasc Word VBA 6 04-01-2014 03:42 PM Where does my code go? rbaldwin Word VBA 3 03-14-2012 02:31 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 01:41 PM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top