VBA – String Manipulation

Sometimes you just have to not accept what is given to you.  I came across something interesting building a form within Excel today.  I have 7 combo boxes on this form and I needed to assign values to all of the choices.  So when a description of 20A Receptacle was chosen, I needed to assign a value of O to it.  The end result would be a string of these variables concatenated together to form one long string that resembled: (O X G OS S X S).

A few lines of code and were off:

Devices = Array(cmb1.Value, cmb2.Value, cmb3.Value, cmb4.Value, cmb5.Value, cmb6.Value, cmb7.Value)
NumOfDevices = 0
'Find all values for combobox
For i = 0 To UBound(Devices)
If Not Devices(i) = "" Then NumOfDevices = NumOfDevices + 1
'Match Description to Ganged
Select Case True
Case Devices(i) = "20A RECEPTACLE"
Ganged = Ganged & "O "
Case Devices(i) = "20A GFI"
Ganged = Ganged & "G "
Case Devices(i) = "20A GFI PROTECT DS"
Ganged = Ganged & "4G "
Case Devices(i) = "20A HALF SWITCHED DUPLEX"
Ganged = Ganged & "OS "
Case Devices(i) = "SINGLE POLE"
Ganged = Ganged & "S "
Case Else
Ganged = Ganged & "X "
End Select
Next i

Now that we have all of our variables assigned to the description picked, a problem has arose with my approach.  Sometimes, there are empty slots within these boxes that still need to be designated by an X, but only if they are contained within the string.  For example: (O X G OS S X S).  This is valid, but my code will assign X’s to every combo box that is blank or equal to “”.  I need this to happen to accommodate for the empty slots within the box.  The challenge comes when the box has less than 7 devices.  The string could look like this: (S X G X X X X).  Thinking outside of the box, I came up with this solution:

Do While UCase(Left(Right(Trim(Ganged), 1), 1)) = "X"
GangedLength = Len(Ganged)
Ganged = Left(Right(Trim(Ganged), GangedLength), GangedLength - 1)
Loop
Ganged = "(" & Trim(Ganged) & ")"

Seems to be working just fine now.  Any of these strings that end with X get cut off until I’m left with a value that doesn’t equal X.

Order Form using Excel VBA

I’d love to post all of the inner workings of this Order Form, I’m simply not allowed.  Our manufacturing line is very competitive and I’d like to keep our advantage.  However, there are a few things I’ve built that I’d like to show off.  Here’s a simple screenshot of the INPUT page:

This allows my foreman to simply order parts by adding an X in the box.  I’m bound to the legacy database that runs the line, so instead of adding a quantity for each part, the X represents an an individual part sent to the line.  What was an interesting challenge was creating the data for the legacy database.  I had to create custom functions within VBA to handle the logic.  We were able to make most of the calculations within 2007, but my workforce uses 2003 and Office XP…  I had to make it backwards compatible.  Here’s a screenshot of what the data looks like (sorry, I had to cut some data out):

Since the database only accepts one Wire Type at a time, I had to create some macros that separate the data according to Wire Type.  When the macro is ran, a new worksheet is created for each Wire Type that is listed within the ALL FILE.  Once these are created, another macro is fired and sorts each worksheet descending except for the largest Wire Type worksheet.  Since it’s the largest worksheet, it is sorted ascending.  Weird requirement, I know… It’s what I have to work with since I’m forced to use this old database.  Once all of this is done, it’s now time to import these tabs into the database.  Another weird requirement, is that for each Wire Type, a new template database is used.  This doesn’t sound much like a database at all…  What I’ve been told is that on average, there are 3-4 Wire Types.  That means for this project, this *database* has to be copied 3 or 4 times and an import operation completed for each Wire Type.  I could be way off base here, but this doesn’t sound like a database at all, it sounds more like a band-aid.

Enough about my rants… I’m in the process of showing off the benefits of overhauling this anyway :D.  Once this is done, I’ll have to revisit my Order Form for sure.  For now though, I’m wrapping up the BETA version of this Order Form and it’s going to be tested this week. 

I’m sure you are wondering… Where are the custom functions?!?  I didn’t want to leave you hanging, so here’s a custom function that I had to write for this project.  I hope you find it useful.

Function FindWireType(rng As Range) As String
    Dim cell As Range
    Dim intDelim As Integer
    Dim intColCount As Integer
    Dim strColumn As String
    Dim strWireType As String
      
    For Each cell In rng
        If Not IsEmpty(cell.Value) Then
            intDelim = InStr(2, cell.Address, "$") - 1 'Minus one to strip $ sign out. Posts back $A$ or $AA$
            intColCount = Len(Left(cell.Address, intDelim)) - 1 'Trims the left $ sign
            strColumn = Right(Left(cell.Address, intDelim), intColCount) & "8" 'Trims the cell.address to just the columns and appends an 8
            
            strWireType = ActiveWorkbook.Sheets("INPUT").Range(strColumn).Value
            
            FindWireType = strWireType
            Exit Function
        End If
    Next
End Function