How to get the value of a cell in VBA MS Project

Question

I need a code that gives a message depending the different values of some particular cells in a Project, I have already used VBA in Excel, but never in Project, so I don´t know how to do it

I did a code in VBA Excel that do the same thing that I need to do in Project, but I need the code but with the infomation of a Project, but the functions that I use in Excel, are not defined in Project, so I don´t know how to addecuate the code

Option Explicit
Private Sub Avance()
Dim PtjR As Double
Dim PtjP As Double
Dim FechaRI As Date
Dim FechaRF As Date
Dim FechaPI As Date
Dim FechaPF As Date
Dim ListaT As String
Dim i As Integer
Dim ListaTT As String


For i = 3 To 40

        FechaPI = Round(Cells(i, 2).Value, 2)
        FechaPF = Cells(i, 3).Value
        FechaRI = Cells(i, 4).Value
        FechaRF = Cells(i, 5).Value
        PtjR = Round(Cells(i, 6).Value, 2)
        PtjP = Round(Cells(i, 7).Value, 2)

        If PtjR < PtjP Then

                ListaT = ListaT & vbNewLine & Chr(13) & "La tarea" & " " & Cells(i, 1).Value & " " & "se encuentra en DELATE, lleva" & " " & PtjR * 100 & "% y debería llevar" & " " & PtjP * 100 & "%"

                If FechaPF - DateValue(Now) < 0 Then
                    ListaT = ListaT & ". Esta tarea debió terminar hace " & -(FechaPF - DateValue(Now)) & " días."
                ElseIf FechaPF - DateValue(Now) <= 7 Then
                    ListaT = ListaT & ". Esta tarea termina en " & FechaPF - DateValue(Now) & " días."
                End If


        End If

Next i

MsgBox ListaT, vbCritical, "Advertencia"



End Sub

I am not sure if the way how I get the values of the cells are the only thing that will change about the code, but knowing how to do that, will be a big help

Answer 1

To get the values of a task, use the Task object. In this case you'll want to loop through all of the tasks using the Tasks object (collection of all tasks). It is unclear what task fields you need, but this should get you started:

Private Sub Avance()

Dim PtjR As Double
Dim PtjP As Double
Dim FechaRI As Date
Dim FechaRF As Date
Dim FechaPI As Date
Dim FechaPF As Date
Dim ListaT As String
Dim t As Task

For Each t In ActiveProject.Tasks

    FechaPI = t.Start
    FechaPF = t.Finish
    FechaRI = IIf(t.BaselineStart = "NA", 0, t.BaselineStart)
    FechaRF = IIf(t.BaselineFinish = "NA", 0, t.BaselineFinish)
    PtjR = t.PercentComplete
    PtjP = t.PhysicalPercentComplete

    If PtjR < PtjP Then

        ListaT = ListaT & vbNewLine & Chr(13) & "La tarea" & " " & t.Name & " " & "se encuentra en DELATE, lleva" & " " & PtjR * 100 & "% y debería llevar" & " " & PtjP * 100 & "%"

        If FechaPF - DateValue(Now) < 0 Then
            ListaT = ListaT & ". Esta tarea debió terminar hace " & -(FechaPF - DateValue(Now)) & " días."
        ElseIf FechaPF - DateValue(Now) <= 7 Then
            ListaT = ListaT & ". Esta tarea termina en " & FechaPF - DateValue(Now) & " días."
        End If

    End If

Next t

MsgBox ListaT, vbCritical, "Advertencia"

End Sub

Note that the Percent Complete and Physical Percent Complete properties return a value from 0 to 100, so don't multiply by 100 later on.