I am currently working on a MS Project VBA project (yeah, VBA glory ^^’) and am experiencing so many odd issues and problems in finding potential solutions that I have to write a short (hopefully) helpful article.
In general: MS Project VBA is a lot different than everything you might now from e.g. Excel. MS Project seems to be a little outside of the whole Office ecospace and therefore requires some additional handling of potentially easy functionality. One of these oddities are the MS Project planning view columns.
Adding a normal new column is not as easy as you would think. You just cannot add a new column at the end as you would do in Excel, but you have to “reuse” the existing fields of e.g. TaskText, Attributes etc. with their many occurrences (i.e. pjTaskText1-30). Editing the TableEditEx, renaming a column and displaying it… a strange-to-look-at sort of coding.
Is a column visible?
I was able to handle a lot but nearly got insane (insan’er?) trying to find out if a current column/field is visible, as only visible fields can be highlighted. I googled a lot trying to find this problem and a potential solution. There is even a multiple year old post on reddit without any solution π
So, I had no other choice but take everything I was able to find and grasp and create my personal visibility check function, based on the very helpful post by Brian Kennemer and thought I should share it for others restless souls in the future ^^
' Is a MS Project task column field visible
'ββββββββββββββββββββββββ-
' @param Table activeTable - The table to check for a visible column
' @param String columnName - The column name to look for
' @return boolean - True if visible, False otherwise
'ββββββββββββββββββββββββ-
Public Function IsColumnVisible(activeTable As table, columnName As String)
IsColumnVisible = False
Dim FieldCounter As Integer
Dim FieldCount As Integer
' >=2010 has an "Add new column" field at the end
If modUtils.GetDoubleFromString(Application.Version, True) >= 14 Then
FieldCount = activeTable.TableFields.Count - 1
Else
FieldCount = activeTable.TableFields.Count
End If
' Iterate and look for a match
For FieldCounter = 1 To FieldCount
' Matches if the given column name or its constant (in case of renamed custom fields) are equal
If columnName = FieldConstantToFieldName(activeTable.TableFields(FieldCounter).field) Or FieldConstantToFieldName(FieldNameToFieldConstant(columnName)) = FieldConstantToFieldName(activeTable.TableFields(FieldCounter).field) Then
IsColumnVisible = True
Exit For
End If
Next FieldCounter
End Function
Maybe there is an easier or better solution out there. But when it comes to MS Project and VBA I am really starting to become desperate and grab for any solution, no matter how ugly it is (yeah, VBA, I know ^^’).