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 ^^
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
' 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 ^^’).