#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 'adeu, adeu. parting is such sweet sorrow 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 AddressIso = datahousen(0) Case "a2", 2 AddressIso = datahousen(1) Case "city", 3 AddressIso = datahousen(2) Case "state", 4 AddressIso = datahousen(3) 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 AddressIso = Left(datahousen(4), 5) ElseIf Len(datahousen(4)) = 5 Then AddressIso = datahousen(4) 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
|
||||
|
||||
Hi
perhaps explain what you want to achieve ?
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Help with code VBA | 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 |