SOLVED

VBA - Different custom fields used depending upon schedule

Occasional Contributor

Hello,

I'm pretty sure the answer is that this can't be done but I thought I would ask in case someone smarter than me has a way.

 

Is it possible to set a variable that refers to a field and use that variable for field manipulation later?

For example:

FieldVar=Text1

For each oTask in ActiveProject.Tasks

    Debug.Print oTask.FieldVar

    oTask.FieldVar = oTask.FieldVar & "-Reported"

Next oTask 

 

The reason for the question is that different PMs use custom fields for different purposes and I'm looking at having some utilities that could be portable if they could use different fields like that. I could simply change the variable assignment (such as FieldVar=Text8) and the rest of the code would not need changing.

The alternative being I just set fixed fields to use and the PMs have to adapt.

 

I could use CASE statements or compiler directives to achieve but this would lead to huge unwieldly code. I was hoping there would be a smarter way.

1 Reply
best response confirmed by Gilgamesh1964 (Occasional Contributor)
Solution
I recall doing something like this in the past but I'd have to do some digging to see how I might have done it, or maybe I'm just remembering wrong.

However, there are other options. One is to detect the user (e.g. Application.UserName) and use that as a branch to appropriate lines of code. Another is to do a find and "replace all" in the code. But I believe the best option is to develop a consistent set of custom fields and then train the PMs to use those. It really makes things a lot smoother for everybody.

John