Microsoft Office Forums Factoring code
 Register FAQ Search Today's Posts Mark Forums Read

#1
06-25-2019, 08:44 PM
 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
06-25-2019, 11:28 PM
 Pecoflyer Windows 7 64bit Office 2010 64bit Expert Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,436

Hi
perhaps explain what you want to achieve ?

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post walber Excel Programming 0 02-20-2017 11:24 AM AJacLo Excel Programming 2 02-19-2017 03:57 PM welcometocandyland Word VBA 4 02-08-2017 06:53 PM rsrasc Word VBA 6 04-01-2014 03:42 PM 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 06:50 AM.

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