VBA Code For Parsing a MultiValue Text Field

If you are in Project Server 2007 you can have an Enterprise Text using a lookup table that can contain more than one value. If you then need to work with that field data in code (VBA or VSTO) you can use the GetField function to get the value of that field. The catch is that it is returned as a comma delimited string with all the values in it. So if your field had three values selected (Value1, Value19 and Value26) it gets returned as “Value1, Value19, Value26”.

The code below shows how to parse this into an array where it will be easier to work with in code.

As always, take this code and make it your own. This includes doing testing to make sure it does what it is supposed to do. It worked for me on a very limited set of test data. Don’t just throw this into production. ;-)

Sub Parse_Comma_Delimited_Field_Into_Array()

Dim Char As Integer
'Char is used to hold each character in the string to see if it is a comma

Dim WholeField As String
'Wholefield is a string that holds the entire value of the field to be parsed

Dim Values() As String
'Values is the string array that will contain the parsed values

Dim NumberofValues As Integer
'NumberofValues holds the number of values in the 'wholefield'
'so that the 'Values' array can be redim'd

Dim StartofLastValue As Integer
'holds the number of characters from the left of the string where the last
'word started so the value can be pulled from the wholefield string and
'placed into the array

StartofLastValue = 1
NumberofValues = 0

'---Setting value of WholeField to be parsed
WholeField = ActiveProject.Tasks(1).Text1
'---
If Len(WholeField) > 0 Then
For Char = 1 To Len(WholeField)
If Mid$(String:=WholeField, Start:=Char, Length:=1) = "," _
Or Char = Len(WholeField) Then
NumberofValues = NumberofValues + 1
ReDim Preserve Values(1, NumberofValues)
If Char < Len(WholeField) Then
Values(1, NumberofValues) = Trim(Mid$(String:=WholeField, _
Start:=StartofLastValue, Length:=Char - StartofLastValue))
Else
Values(1, NumberofValues) = Trim(Mid$(String:=WholeField, _
Start:=StartofLastValue, Length:=Char - (StartofLastValue - 1)))
End If
StartofLastValue = Char + 1
End If
Next Char
'-------------------------
'this section just loops through and shows that the array
'contains the correct data.
'Remove after you are finished testing
Dim count As Integer
For count = 1 To NumberofValues
Debug.Print Values(1, count)
Next count
'-------------------------

End If
End Sub