Using Visual Basic for Applications in Microsoft Project Sean Vogel
Why VBA? • Automate repetitive tasks – Save time – Reduce error – Impress your neighbor • Calculate metrics on schedules instantly • Customize Interfaces
Orientation to the Visual Basic Editor VB Editor is located under the tools menu, macros, VB editor. This is true for every office product.
Orientation to the Visual Basic Editor Project Explorer Main screen – used for code Debug toolbar Properties Window Watch Window – used in troubleshooting
Orientation to the Visual Basic Editor Object Browser Gives you every possible variable for each Office product
Orientation to the Visual Basic Editor To control other applications you must first install the references. In the VB Editor go to the tools menu, select references. Scroll down until you find the application you want.
Programming 101 • • Definition Section – Define variables used in the code • String – used for text • Array – used to store data in 1, 2, 3 or more dimensional tables • Boolean – true/false, used to check conditions • Long – used to store numbers Code section – Sequential order of what should happen (like building a house) • Pour foundation • Build Walls • Build Roof • Charge way to much for it – Flowchart or map out any complex ideas prior to coding
Programming 101 • 4 most used code statements – If Statement IF you want lunch THEN give me money ELSE keep your money END IF – Select Statement SELECT CASE color CASE = “red” X=1 CASE = “blue” X=2 END SELECT
Programming 101 • 4 most used code statements (cont) – While Statement WHILE leave = false do this WEND – For Statement FOR = 1 to 10 write array(y) NEXT y
Define the Variables
Write the code to count tasks the late starts, off baseline tasks, and task dependencies Use this If statement to find the date that will determine if the task is late. This is the basic For Next statement we will use to loop through every task in the project. We will paste our other code within this statement.
Write the code to count tasks the late starts, off baseline tasks, and task dependencies These IF Statements check for late starts or tasks that are off their baselines. See how we nest a For Next statement to check for task dependencies within each task.
Write the Excel File This statement checks if Excel is already open. If Excel is already open you don’t want to open a second instance. We set the isrunning Boolean to true or false depending on if it is running. We will use this later.
Write the Excel File Writing things to Excel requires you to use X/Y coordinates to determine the exact cell you want to modify. The first slot is for the row index, the second for the column. These last two lines auto size the columns for better format.
Write the Excel File Now that the summary page is written let’s write the late and off baseline tasks. We must first change to another sheet. We will then use a For Next statement to write the task information.
Write the Excel File We will now write the last tab with Off Baseline Task data.
Final Steps Last, we will save the file and close Excel
Скачать презентацию Using Visual Basic for Applications in Microsoft Project
2b88fce66bc5c0ca4d295fbeb9e2b540.ppt