VBA Excel to MS Project

Question

New to VBA, I am successfully importing and reading a Task List and Resources from Excel, executing VBA in Excel and inserting these records into MS Project. I am looking at setting the ActiveProject.Resources.Standardrate = "100p/h", however I am getting an error.

The code being applied (credit to previous answers provided to other related questions on Stackoverflow for the following code).

If Not ExistsInCollection (newproject.Resources, strResource) Then
  newproject.resources.add.name = StrResource  <-- This works, resources are added.

  ' However, inserting the following line:

  newproject.resources.standardrate = "100p/h"  <-- It errors here

End if

Any assistance is greatly appreciated - Thank you.

Answer 1

The code needed a minor modification to get a reference to the newly-added resource so that the StandardRate can then be updated.

This code also demonstrates how to handle the case of a list of comma-delimited resources rather than a single one.

Dim t As Task
Set t = NewProject.Tasks.Add("New task 1")

Dim StrResource As String
StrResource = "Resource 1,Resource 2,Resource 3"
Dim arrRes As Variant
arrRes = Split(StrResource, ",")

Dim i As Variant
For Each i In arrRes
    If Not ExistsInCollection(NewProject.Resources, i) Then
        Dim r As Resource
        Set r = NewProject.Resources.Add(i)
        r.StandardRate = 100
    End If
    t.Assignments.Add , ActiveProject.Resources(i).UniqueID
Next i