Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-25-2019, 08:44 PM
Denko Denko is offline Factoring code Windows 10 Factoring code Office 2019
Novice
Factoring code
 
Join Date: Jun 2019
Posts: 1
Denko is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 06-25-2019, 11:28 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Factoring code Windows 7 64bit Factoring code Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
Reply

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
Factoring code Help with Code AJacLo Excel Programming 2 02-19-2017 03:57 PM
Factoring code 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
Factoring code Need Help with Below Code rsrasc Word VBA 6 04-01-2014 03:42 PM
Factoring code Where does my code go? rbaldwin Word VBA 3 03-14-2012 02:31 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:43 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft