Wednesday, October 27, 2010

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.

No comments: