Tag Archives: vbe

MS Project VBA Column Visibility FTW

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 ^^’).