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,436
Pecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to all
Default

Hi
perhaps explain what you want to achieve ?
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 - Senior Forums

All times are GMT -7. The time now is 06:50 AM.


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